problem with 'EXECUTE IMMEDIATE'
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: problem with 'EXECUTE IMMEDIATE'

  1. #1
    Join Date
    May 2001
    Location
    Jacksonville, FL
    Posts
    12
    Gurus,

    I'm trying to execute a stored procedure from within a different stored procedure. It's not working, so I'm trying to get the syntax right by debugging with an anonymous PL/SQL block. Results are:

    SQL> declare
    2 p_sequence number;
    3 sql_stmt varchar2(100);
    4 begin
    5 sql_stmt := 'PROC_MPS_WO_PROJ2';
    6 EXECUTE IMMEDIATE 'exec '||sql_stmt;
    7 end;
    8 /
    declare
    *
    ERROR at line 1:
    ORA-00900: invalid SQL statement
    ORA-06512: at line 6

    A simple query ('SELECT...') works fine, but for some reason the dynamic SQL doesn't seem to like 'EXEC'. Any ideas appreciated. Thanks.

    Joe

  2. #2
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    Just give the statment without the exec (try)


    Sam
    Thanx
    Sam



    Life is a journey, not a destination!


  3. #3
    Join Date
    May 2001
    Location
    Jacksonville, FL
    Posts
    12
    Thanks, Sam. Just coding the name of the procedure with its input variables solved the problem - EXECUTE IMMEDIATE was not even necessary.

  4. #4
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    Thats correct you would use execute immediate for DDL operations like create/ALTER/MODIFY and etc. Not for the procedures

    procedures and packages you can just call

    package.procedure(input1);
    or
    procedure(input1);

    Sam
    Thanx
    Sam



    Life is a journey, not a destination!


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