rem
rem  Copyright (c) Oracle Corporation 1993.  All Rights Reserved, Worldwide.
rem
rem  rosupdt.sql - cumulative update script for ROS database tables.
rem
rem  This script may be applied repeatedly without damage to the tables or
rem  their data they contain.  Some SQL warnings or errors will occur when
rem  you do this, they may be safely ignored.
rem
rem  Changes and the versions in which they appeared:
rem
rem    2.0.9.2  - Drop primary keys from ROSLFDESC and ROSTFDESC
rem               Change the ROSATTRIBS view
rem
rem    2.0.8.6  - Add storage clauses to ROSLFDESC and ROSTFDESC
rem               Change the ROSATTRIBS view
rem

rem
rem Add storage clause to ROSLFDESC and ROSTFDESC tables
rem

alter table ROSLFDESC
  storage
  (
    next    100K
  );

alter table ROSTFDESC
  storage
  (
    next    100K
  );

rem
rem  Drop and re-create the ROSATTRIBS view
rem

drop view ROSATTRIBS;
create view ROSATTRIBS  as
select owner pdstrcownerP, table_name pdstrcnameP, column_id pdrelpos,
 column_name pdfnameP,
 data_type||'('||TO_CHAR(data_length)||','
        ||TO_CHAR(data_scale)||')'pdoratype,
 decode
   ( data_type,
        'CHAR',
         decode
           (data_length,1,'rosTYPE_TEXTCHAR',
                decode
                  (substr(column_name,length(column_name)-1,2),
                        '_P','rosTYPE_TEXTP',
                        'rosTYPE_TEXTARR'
                  )
           ),
        'NUMBER',
         decode
           (data_scale,0,'rosTYPE_SB4','rosTYPE_FLOAT'
           ),
        'DATE','rosTYPE_DATEP',
        'RAW', 'rosTYPE_BINARYP',
        'LONG','rosTYPE_LTEXTP',
        'LONG RAW','rosTYPE_LBINARYP',
        'VARCHAR2',
        decode
           (data_length,1,'rosTYPE_TEXTCHAR',
           decode
              (substr(column_name,length(column_name)-1,2),
                    '_A','rosTYPE_TEXTARR',
              decode
                 (substr(column_name,length(column_name)-1,2),
                       '_P','rosTYPE_TEXTP',
                       'rosTYPE_VARCHARP'
                 )
              )
           ),
        'OTHER'
   ) pdrosusertype,
 decode
   (data_type,
        'CHAR',
        decode
        (data_length,1,24576,
                decode
                  (substr(column_name,length(column_name)-1,2),
                        '_P',28672,
                        26624
                  )
        ),
        'NUMBER',decode(data_scale,0,16384,18432),
        'DATE',34816,
        'RAW', 30720,
        'LONG',28672,
        'LONG RAW',43008,
        'VARCHAR2',
        decode
           (data_length,1,24576,
           decode
              (substr(column_name,length(column_name)-1,2),
                    '_A',26624,
              decode
                 (substr(column_name,length(column_name)-1,2),
                       '_P',28672,
                       32768
                 )
              )
           ),
        0
   )pdtype,
 data_length pdmaxlen,0 pdmaskw,0 pdflagw
 from ALL_TAB_COLUMNS;

rem
rem  Drop primary key constraints from ROSLFDESC and ROSTFDESC
rem  These may give warnings if the tables do not have primary keys.
rem

alter table ROSLFDESC drop primary key;
alter table ROSTFDESC drop primary key;
