Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

creating tables in pl/sql block

407048Oct 31 2007 — edited Nov 1 2007
I 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

----------------------------------------------------------
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Nov 29 2007
Added on Oct 31 2007
16 comments
1,797 views