My script has first one invalid procedure and 2nd one valid procedure. I want the script to execute for at least 2nd valid object

Question

I have below PLSQL Script where the PROC1 is invalid due to some reason(we are not discussing why) but PROC2 is valid.

Question: I want the script to run without any issue for at least PROC2? I want Valid PROC2 to be executed irrespective of invalid PROC 1.

                         BEGIN
                              PROC1; --This is due to x reason is invalid
                              PROC2;--This is valid.
                        END;

Then don't run proc1 until it's valid. – Belayer Nov 19 '19 at 03:33 — Belayer, Nov 19 '19 at 03:33

Popeye · Accepted Answer · 2019-11-19T05:13:09.507

1

You can not execute any INVALID object in your PL/SQL block.

You can not even catch such exception in PL/SQL block. Oracle checks for the status of the all objects used in the pl/sql block and throws error if it is INVALID.

The best way to handle this is to change the proc1, recompile it so that it becomes VALID. That's it.

But, one way of doing it is using execute immediate as following:

declare
lv_status user_objects.status%type;
begin
select status into lv_status from user_objects where object_name = 'PROC1';
if lv_status = 'VALID' THEN
execute immediate 'call PROC1()';
END IF;
proc2;
end;
/

db<>fiddle demo

Cheers!!

edited Nov 19 '19 at 05:13

answered Nov 19 '19 at 05:06

Popeye

35,427
4
10
31

Thanks My dear Friend Tejash for your answer! The problem here is the PROC1 is calling some DBLINk Which is not in our control So we want at least PROC2 to execute. we cannot do anything on the DBLINK as the other ends DB is not in our control. This DBLINk is not accessible which is impacting PROC2 also. – Pramod Kumar Nov 19 '19 at 13:40
Yes, you have answered this question but i was just checking with you if there is BETTER way to handle DBLink which is having some issue? I do have uncertainty on the DBLINK and i do not want this to impact any object which is referecing this DBLINK – Pramod Kumar Nov 19 '19 at 13:46
https://stackoverflow.com/questions/3849621/how-to-check-if-a-database-link-is-valid-in-oracle – Popeye Nov 19 '19 at 13:48
Kya baat hai, Tusi great ho paaji ! ( Awesome, you are great ) Thanks, No more questions . – Pramod Kumar Nov 19 '19 at 13:53

My script has first one invalid procedure and 2nd one valid procedure. I want the script to execute for at least 2nd valid object

1 Answers1