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.
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
Abhay.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
abhaysk
Thanks for the reply ( it wasn't so had afterall !! )
What should I do without you guys !!
Tanks again!
GKramer
Have a cup of coffee, you will do all by urself!!Quote:
Originally posted by GKramer
What should I do without you guys !!