-
How to compile a invalid stored procedure into another stored procedure?
Hi,
I wonder how to compile a invalid stored procedure into another stored procedure?
The first proc is creating some tables ( and is valid ) the second proc is based on those new tables ( but while compliling these tables they do not excist yet) and is invalid.
How to accomplish this running these procs within a package?
Regards
GKramer
-
Only by using dynamic SQL (EXECUTE IMMEDIATE or DBMS_SQL or ref cursors) when referencing those nonexistant tables in the second procedure.
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
-
jmodic
Thanx for the quick reply.
I'm familiar with "execute immediate" and "DBMS_SQL" but do not know how to use it in this situation.( thats why I asked )
Can you give me an example?
GKramer
-
Code:
WW04_PRO> create or replace procedure dy_proc_compile as
2 id number;
3 begin
4 select e_id into id from non_existant_tab;
5 end;
6 /
Warning: Procedure created with compilation errors.
WW04_PRO> show err
Errors for PROCEDURE DY_PROC_COMPILE:
LINE/COL ERROR
-------- -----------------------------------------------------------------
4/1 PL/SQL: SQL Statement ignored
4/26 PL/SQL: ORA-00942: table or view does not exist
WW04_PRO> create table non_existant_tab(e_id number);
Table created.
WW04_PRO> begin
2 execute immediate('alter procedure dy_proc_compile compile');
3 end;
4 /
PL/SQL procedure successfully completed.
WW04_PRO> select substr(object_name,1,30), status from dba_objects
2 where object_name='DY_PROC_COMPILE';
SUBSTR(OBJECT_NAME,1,30) STATUS
------------------------------ -------
DY_PROC_COMPILE VALID
Abhay.
Last edited by abhaysk; 10-09-2003 at 02:51 AM.
funky...
"I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."
"Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"
-
abhaysk
Thanks for the reply ( it wasn't so had afterall !! )
What should I do without you guys !!
Tanks again!
GKramer
-
Originally posted by GKramer
What should I do without you guys !!
Have a cup of coffee, you will do all by urself!!
funky...
"I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."
"Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|