Display error messages in spool file
407048Oct 22 2007 — edited Oct 23 2007I am running the below code (ssn_run.sql) using sqlplus 10.2.0.1.0 on my windows XP professional client PC.
The database is a Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit running on Solaris
There are 3 insert statements and 3 update statements. I have mentioned 'show sqlcode' after each of these
dml statements. Hence currently, I look at the spool file, analyze before I confirm that every sqlcode is a 0.
Now, is there a way to alter this code so that it can show if any of the sqlcode is not 0(if there is a problem) somewhere
at the end of the spool file ? Or even if I am able to just indicate if there is any ORA- error anywhere in the code and can indicate this somewhere at the end of the spool file, that is fine as well.
--The code
accept input_ssn prompt 'Enter SSN :'
set feedback on
set echo on
set term on
set heading on
set pagesize 0
set linesize 10000
set verify on
undefne sdate input_ssn
col sdate new_value sdate
col input_ssn new_value input_ssn
select to_char(sysdate,'YYYYMMDD') sdate from dual;
spool C:\PERSON_DATA_&&sdate._&&input_ssn..TXT
select 'REPORT GENERATED ON : '||SYSDATE FROM DUAL;
INSERT INTO ADDRESS_OLD SELECT * FROM ADDRESS WHERE ADDRESSID IN(SELECT ADDRESSID FROM PERSON_ADDRESS
WHERE PERSONID IN(SELECT PERSONID FROM PERSON WHERE SIN = '&&input_ssn'));
show sqlcode;
commit;
INSERT INTO PERSON_OLD
(PERSONID, TITLE, FNAME, MNAME, LNAME, ACFM, SIN, UNAME, AKANAME, DCFM,
IROWID, SUFFIX, PTYPE, OLD_SSN)
SELECT PERSONID, TITLE, FNAME, MNAME, LNAME, ACFM, SIN, UNAME,
AKANAME, SIN FROM PERSON WHERE SIN = '&&input_ssn';
show sqlcode;
commit;
INSERT INTO MEMBER_OLD
(CLNT, MKEY, PERSONID, MEMNO, OLD_MEMNO)
SELECT CLNT, MKEY, PERSONID, MEMNO,MEMNO
FROM MEMBER WHERE PERSONID IN(SELECT PERSONID FROM PERSON WHERE SIN = '&&input_ssn');
show sqlcode;
commit;
UPDATE ADDRESS SET STREET1 = 'ROCKY RD',STREET2=NULL,CITY = 'ALABASTER',PROVINCE = 'AL',POSTAL='34216',
PHONE1='1111111111' WHERE ADDRESSID IN(SELECT ADDRESSID FROM PERSON_ADDRESS WHERE PERSONID
IN(SELECT PERSONID FROM PERSON WHERE SIN = '&&input_ssn'));
show sqlcode;
commit;
UPDATE PERSON SET FNAME = TRANSLATE(SIN,'0123456789','ACEGIKMOQS'),
LNAME = TRANSLATE(SIN,'0123456789','SQOMKIGECA'),
UNAME = TRANSLATE(SIN,'0123456789','ACEGIKMOQS')||' '||TRANSLATE(SIN,'0123456789','SQOMKIGECA'),
AKANAME=NULL WHERE SIN = '&&input_ssn';
show sqlcode;
commit;
UPDATE MEMBER SET MEMNO = MKEY WHERE PERSONID IN(SELECT PERSONID FROM PERSON WHERE SIN = '&&input_ssn');
show sqlcode;
commit;
spool off;
set term on
set feedback on
set HEADING on
set verify on
--End of code
Thanks