How to compile a invalid stored procedure into another stored procedure?

[GKramer]

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

[jmodic]

Only by using dynamic SQL (EXECUTE IMMEDIATE or DBMS_SQL or ref cursors) when referencing those nonexistant tables in the second procedure.

[GKramer]

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

[abhaysk]

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.

[GKramer]

abhaysk

Thanks for the reply ( it wasn't so had afterall !! )

What should I do without you guys !!

Tanks again!

GKramer

[abhaysk]

Originally posted by GKramer
What should I do without you guys !!

Have a cup of coffee, you will do all by urself!!