example sql script with update and insert statements- for the new oracle dba




You are a new oracle dba and you would need an example of a sql script containing update and insert statements. Here it is. The below script containg both sqlplus commands(set define off etc) and sql statements(insert, update etc).

 

set define off

spool testfile.log

prompt Loading CDEXAMLIST…

update CDEXAMLIST set LISTEDITTYPE = ‘D’ where EXAMLISTNAME = ‘CDEXAMPOSITIONTYPE’;

commit;

 

prompt Loading CDEXAMLISTCOLUMN…

update CDEXAMLISTCOLUMN set SORTORDER = SORTORDER + 1 where SORTORDER >= 3 and EXAMLISTNAME = ‘CDEXAMPOSITIONTYPE’ and not exists (select ‘x’ from CDEXAMLISTCOLUMN where EXAMLISTNAME = ‘CDEXAMPOSITIONTYPE’ and COLUMNNAME = ‘SIGNIFDIGITS4GRP’);

insert into CDEXAMLISTCOLUMN (EXAMLISTNAME, COLUMNNAME, CAPTION, ISREADONLY, ISREQUIRED, ISFILTER, SORTORDER, DBFIELDTYPE, COMBOQUERY) select ‘CDEXAMPOSITIONTYPE’, ‘SIGNIFDIGITS4GRP’ , ‘Significant digits for grouping’ , 0, 0, 0, 3, ‘N’, null from dual where not exists (select ‘x’ from CDEXAMLISTCOLUMN where EXAMLISTNAME = ‘CDEXAMPOSITIONTYPE’ and COLUMNNAME = ‘SIGNIFDIGITS4GRP’);

commit;

 

prompt Loading CDPASSSource…

update CDPASSSource

set SortOrder = SortOrder + 1

where SortOrder > (select SortOrder from CDPASSSource where IDCDPASSSource = ‘PSN’)

and not exists (select ‘x’ from CDPASSSource where IDCDPASSSource = ‘PSNH’);

 

update CDPASSSource

set SortOrder = SortOrder + 1

where SortOrder > (select SortOrder from CDPASSSource where IDCDPASSSource = ‘CFT’)

and not exists (select ‘x’ from CDPASSSource where IDCDPASSSource = ‘CFTH’);

 

commit;

 

spool off

 

exit

Author: admin