creating tables in pl/sql block
407048Oct 31 2007 — edited Nov 1 2007I have the following piece of code that I execute in sqlplusw as @c:\process.sql
On running @c:\process.sql, an input is provided as prompted. There is a
declare and begin that processes some code. What I am looking for is -- Is there
a way that I can check the all_tables view for the following 3 tables :
TABLE1, TABLE2, TABLE3. Only on finding these tables, this
anonymous sql (that is inside starting from declare ... should execute).
If these tables are not found, I want to attempt creating the tables. If the attempt
was successful, then process. If creating tables attempt fails, exit with a dbms_output.put_line message and don't process
anything....
Can this be done ? If so, any hints would be great.....
Thanks
-----------------------------------------------------------------
ACCEPT input_ssn prompt 'Enter SSN :'
SET feedback off
SET serveroutput on
SET echo off
SET term off
SET heading off
SET pagesize 0
SET linesize 10000
SET verify off
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:\SCRUB_DATA_&&sdate._&&input_ssn..TXT
SELECT 'REPORT GENERATED ON : ' || SYSDATE
FROM DUAL;
ALTER TRIGGER TRIG1 DISABLE;
DECLARE
..............
BEGIN
FOR i IN .........
LOOP
.............
END LOOP;
BEGIN
SELECT ..........
EXCEPTION
WHEN NO_DATA_FOUND
THEN
NULL;
END;
UPDATE ...........
FOR i IN .....
LOOP
UPDATE ....
BEGIN
SELECT ...
EXCEPTION
WHEN NO_DATA_FOUND
THEN
NULL;
END;
UPDATE ...
BEGIN
SELECT ....
EXCEPTION
WHEN NO_DATA_FOUND
THEN
NULL;
END;
UPDATE ...
END LOOP;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
NULL;
WHEN OTHERS
THEN
ecode := SQLCODE;
emesg := SQLERRM;
DBMS_OUTPUT.put_line ('Error code is : '||' - '|| ecode);
DBMS_OUTPUT.put_line ('Error message is :' || ' - ' || emesg);
END;
/
COMMIT ;
ALTER TRIGGER TRIG1 ENABLE;
SET term on
SET HEADING on
SELECT 'Processing complete for ' || '&&input_ssn'
FROM DUAL;
SPOOL off;
SET verify on
SET feedback on
----------------------------------------------------------