Call a stored procedure with another in Oracle

Question

Does anyone know of a way  or even if its possible  to call a stored procedure from within another  If so  how would you do it   Here is my test code   SET SERVEROUTPUT ON   DROP PROCEDURE test sp 1  DROP PROCEDURE test sp   CREATE PROCEDURE test sp AS BEGIN     DBMS OUTPUT PUT LINE  Test works    END     CREATE PROCEDURE test sp 1 AS BEGIN     DBMS OUTPUT PUT LINE  Testing        test sp  END     CALL test sp 1

User · Answer

To invoke the procedure from the SQLPlus command line  try one of these   CALL test sp 1    EXEC test sp 1

User · Answer

Your stored procedures work as coded  The problem is with the last line  it is unable to invoke either of your stored procedures   Three choices in SQL Plus are  call  exec  and an anoymous PL SQL block   call appears to be a SQL keyword  and is documented in the SQL Reference  http   download oracle com docs cd B19306 01 server 102 b14200 statements 4008 htm BABDEHHG The syntax diagram indicates that parentesis are required  even when no arguments are passed to the call routine   CALL test sp 1      An anonymous PL SQL block is PL SQL that is not inside a named procedure  function  trigger  etc  It can be used to call your procedure   BEGIN     test sp 1  END      Exec is a SQL Plus command that is a shortcut for the above anonymous block  EXEC  lt procedure name gt  will be passed to the DB server as BEGIN  lt procedure name gt   END   Full example   SQL gt  SET SERVEROUTPUT ON SQL gt  CREATE OR REPLACE PROCEDURE test sp    2  AS    3  BEGIN    4      DBMS OUTPUT PUT LINE  Test works       5  END    6     Procedure created   SQL gt  CREATE OR REPLACE PROCEDURE test sp 1    2  AS   3  BEGIN   4      DBMS OUTPUT PUT LINE  Testing       5      test sp     6  END    7     Procedure created   SQL gt  CALL test sp 1    Testing Test works  Call completed   SQL gt  exec test sp 1 Testing Test works  PL SQL procedure successfully completed   SQL gt  begin   2      test sp 1    3  end    4    Testing Test works  PL SQL procedure successfully completed   SQL gt

User · Answer

Sure  you just call it from within the SP  there s no special syntax   Ex      PROCEDURE some sp    AS    BEGIN       some other sp  parm1   10  20 42      END    If the procedure is in a different schema than the one the executing procedure is in  you need to prefix it with schema name      PROCEDURE some sp    AS    BEGIN       other schema some other sp  parm1   10  20 42      END

User · Answer

Calling one procedure from another procedure   One for a normal procedure    CREATE OR REPLACE SP 1   AS  BEGIN     BODY    END SP 1    Calling procedure SP 1 from SP 2   CREATE OR REPLACE SP 2   AS BEGIN    CALL PROCEDURE SP 1    SP 1    END SP 2    Call a procedure with REFCURSOR or output cursor   CREATE OR REPLACE SP 1   oCurSp1 OUT SYS REFCURSOR   AS BEGIN   BODY    END SP 1    Call the procedure SP 1 which will return the REFCURSOR as an output parameter   CREATE OR REPLACE SP 2    oCurSp2 OUT SYS REFCURSOR   AS  enter code here  BEGIN    CALL PROCEDURE SP 1 WITH REF CURSOR AS OUTPUT PARAMETER    SP 1 oCurSp2   END SP 2

User · Answer

Michael Lockwood - you don t need to use the keyword  CALL  anywhere  You just need to mention the procedure call directly    That is   Begin    proc1 input1  input2   end      instead of   Begin    call proc1 input1  input2   end

[sql] Call a stored procedure with another in Oracle

Examples related to sql

Examples related to oracle

Examples related to stored-procedures