Condition to check the table exist in cursor
451840Jul 23 2009 — edited Jul 23 2009Hi 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