How to compile a invalid stored procedure into another stored procedure?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

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

  1. #1
    Join Date
    Apr 2003
    Location
    Rosmalen, Netherlands.
    Posts
    73

    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

  2. #2
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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?

  3. #3
    Join Date
    Apr 2003
    Location
    Rosmalen, Netherlands.
    Posts
    73
    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

  4. #4
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    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"

  5. #5
    Join Date
    Apr 2003
    Location
    Rosmalen, Netherlands.
    Posts
    73
    abhaysk

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

    What should I do without you guys !!

    Tanks again!

    GKramer

  6. #6
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    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
  •  



Click Here to Expand Forum to Full Width