Results 1 to 7 of 7
  1. #1
    Join Date
    Apr 2004
    Posts
    69

    Unanswered: Execute SP from another SP

    Is it possible to execute a stored procedure from within another stored procedure where the called stored procedure's name is got dynamically using a variable ?

    For example, the following will work in SQL Server:

    create proc testt
    as
    declare @var varchar(100)
    declare @rtn int
    select @var = 'testing' --"testing" is another proc
    exec @rtn = @var --Is it possible to do this kind of execution in Oracle ?
    GO

    Is there an equivalent for the above in Oracle ?

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Yes:
    Code:
    create or replace procedure testt
    as
      var varchar2(100);
      rtn int;
    begin
      var := 'testing';
      execute immediate 'begin :1 := ' || var || '; end;' using out rtn;
    end;

  3. #3
    Join Date
    Apr 2004
    Posts
    69
    Thanks for the help ! It did work ! In extension to this, what should we do if there are parameters to be passed alongside to 'testing' procedure.

  4. #4
    Join Date
    Apr 2004
    Posts
    69
    Oops.. I made a mistake. The code you attached in previous reply did compile but threw execution errors :

    ERROR at line 1:
    ORA-06550: line 1, column 13:
    PLS-00201: identifier 'TESTING' must be declared
    ORA-06550: line 1, column 7:
    PL/SQL: Statement ignored
    ORA-06512: at "STRAW.TESTT", line 7
    ORA-06512: at line 1

  5. #5
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Quote Originally Posted by strawstun
    Oops.. I made a mistake. The code you attached in previous reply did compile but threw execution errors :

    ERROR at line 1:
    ORA-06550: line 1, column 13:
    PLS-00201: identifier 'TESTING' must be declared
    ORA-06550: line 1, column 7:
    PL/SQL: Statement ignored
    ORA-06512: at "STRAW.TESTT", line 7
    ORA-06512: at line 1
    That's because it relies on a function called TESTING existing to be run.

  6. #6
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Quote Originally Posted by strawstun
    Thanks for the help ! It did work ! In extension to this, what should we do if there are parameters to be passed alongside to 'testing' procedure.
    Code:
    create or replace procedure testt
    as
      var varchar2(100);
      rtn int;
    begin
      var := 'testing';
      execute immediate 'begin :1 := ' || var || '(:2,:3); end;'
        using out rtn, in 123, 'hello';
    end;

  7. #7
    Join Date
    Apr 2004
    Posts
    69
    THanks Tony !

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •