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!

Condition to check the table exist in cursor

451840Jul 23 2009 — edited Jul 23 2009
Hi Guru's
I m using Oracle 10G in windows. I wrote a procedure in which the declaration of a cursor was done in declare section and i found that the table in the cursor is not available in the database. Is there any way to put a check before the cursor declaration for the existance of the table. Kindly provide ur inputs on this. Below is the procedure that we have

create or replace procedure MoveDelayClass is

cursor getDelayClass is
select user_def_oid, string_1
from user_def /* Table does not exist in Database */
where ecf_class_id like '%Entity.DelayClass%';

UserDefRec getDelayClass%ROWTYPE;

processRecords BOOLEAN := TRUE;

numberRecords NUMBER := 0;

stmt VARCHAR(4000);

BEGIN
-- Check that records don't exist yet:
select table_name into sec1 from user_tables where table_name = 'DELAYCLASS';
IF SEC1 = 'DELAYCLASS' THEN
EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM DelayClass' INTO numberRecords;
IF numberRecords > 0 THEN
processRecords := FALSE;
END IF;

stmt := 'insert into DelayClass (DelayClass_oid, NAME) '
|| ' VALUES (:1, :2)';

OPEN getDelayClass;
FETCH getDelayClass INTO UserDefRec;
WHILE (getDelayClass%FOUND AND processRecords) LOOP
EXECUTE IMMEDIATE stmt
USING migrateOIDPkg.convertOID(UserDefRec.user_def_oid), UserDefRec.string_1;
FETCH getDelayClass INTO UserDefRec;
END LOOP;
CLOSE getDelayClass;

-- Now delete the original USER_DEF records:

stmt := 'Delete from USER_DEF Where ECF_CLASS_ID like ' || CHR(39) || '%DelayClass%'|| CHR(39);
EXECUTE IMMEDIATE stmt;
COMMIT;
--
-- Handle exceptions by doing nothing!
--
-- EXCEPTION
-- WHEN OTHERS THEN
-- --
END IF;
END;


Thanks
Ram
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Aug 20 2009
Added on Jul 23 2009
8 comments
1,202 views