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!

How to check if the Object is valid or not?

1002358May 20 2013 — edited May 21 2013
I have a scenario in my Stored Procedure, where the functions looks for an User_Object, and if found (=1), then it execute.
ex:
IF TEXISTS('LAB_IN') = 1 THEN
FOR x IN (SELECT COLUMN_NAME, SRC_COL, SRC_TBL, COLUMN_PREFX, COLUMN_STRING_OPP, DEST_TABLE FROM L_UTIL_SRC WHERE SRC_TBL = 'L')
LOOP
VCAT:=CASE WHEN ISCAT>0 THEN REPLACE(x.DEST_TABLE, 'UTIL_', '') ELSE '' END;
vblJOB := '';
vblJOB := vblJOB || 'CREATE OR REPLACE PROCEDURE SP_' ||x.DEST_TABLE||' AS ';
vblJOB := vblJOB || 'PRAGMA AUTONOMOUS_TRANSACTION; ';
vblJOB := vblJOB || 'vblQueryName VARCHAR2(150); ';
..........
..........
..........

My question is, what if the object (LAB_IN) is not valid. Or if any changes have been made to it. How can i make this Function NOT execute, if the object is not Valid.... and shows error message without proceeding further...

Any suggestions/comments, would apprecaite it......

Thanks!
This post has been answered by Frank Kulash on May 20 2013
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jun 18 2013
Added on May 20 2013
12 comments
2,139 views