Call a Stored Procedure with Another in Oracle

Call a stored procedure with another in Oracle

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 <procedure_name> will be passed to the DB server as BEGIN <procedure_name>; END;

Full example:

SQL> SET SERVEROUTPUT ON
SQL> CREATE OR REPLACE PROCEDURE test_sp 
  2  AS 
  3  BEGIN 
  4      DBMS_OUTPUT.PUT_LINE('Test works'); 
  5  END;
  6  /

Procedure created.

SQL> 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> CALL test_sp_1();
Testing
Test works

Call completed.

SQL> exec test_sp_1
Testing
Test works

PL/SQL procedure successfully completed.

SQL> begin
  2      test_sp_1;
  3  end;
  4  /
Testing
Test works

PL/SQL procedure successfully completed.

SQL> 

Calling stored procedure from another stored procedure oracle

Instead

EXECUTE USP_INSERTTICKETINFO( p_SIId, p_NoOfTickets, p_TicketPrice,p_TicketTaxes,p_TicketTotal);

Just to use

USP_INSERTTICKETINFO( p_SIId, p_NoOfTickets, p_TicketPrice,p_TicketTaxes,p_TicketTotal);

how to call a stored procedure from another stored procedure in oracle

You cannot reuse a REF CURSOR from get_data_1 in a subsequent SQL statement because it's just a pointer to a statement handle. The cursor itself contains no data.

You could do something like

CREATE PROCEDURE get_data_2( p_cnt OUT NUMBER )
AS
  l_rc  <<your cursor type>>;
  l_rec <<the type your cursor returns>>;
BEGIN
  get_data_1(<<parameter 1>>, <<parameter 2>>, l_rc);
  p_cnt := 0;
  LOOP
    FETCH l_rc INTO l_rec;
    EXIT WHEN l_rc%NOTFOUND;

    IF( l_rec.id = '12345' )
    THEN
      p_cnt := p_cnt + 1;
    END IF;
  END LOOP;
  CLOSE l_rc;
END;

As you might imagine, though, this tends to get old relatively quickly. Given that, it tends not to be common in Oracle to have stored procedures that return REF CURSOR parameters except in cases where you are returning a finished view of the data to a client application. If there was a shared view, for example, that both GET_DATA_1 and GET_DATA_2 could query rather than having GET_DATA_2 call GET_DATA_1, that would simplify the program. If GET_DATA_1 was a pipelined table function rather than a procedure that returned a REF CURSOR, then it would be much easier to call GET_DATA_1 from GET_DATA_2.

If you want to get started with pipelined table functions (using the SCOTT schema)

create or replace type emp_obj as object (
  empno number,
  ename varchar2(10),
  job   varchar2(9),
  mgr   number,
  hiredate date );
/

create type emp_tbl
as
table of emp_obj;
/

create function emp_pipe( p_deptno IN NUMBER )
  return emp_tbl pipelined
is
begin
  FOR x IN (SELECT * FROM emp WHERE deptno = p_deptno)
  LOOP
    PIPE ROW( emp_obj( x.empno,
                       x.ename,
                       x.job,
                       x.mgr,
                       x.hiredate ) );
  END LOOP;
END;
/

SQL> select * from table( emp_pipe( 10 ) );

     EMPNO ENAME      JOB              MGR HIREDATE
---------- ---------- --------- ---------- ---------
      7782 CLARK      MANAGER         7839 09-JUN-81
      7839 KING       PRESIDENT            17-NOV-81
      7934 MILLER     CLERK           7782 23-JAN-82

How to execute one procedure within another procedure

When you use execute immediate it runs the dynamic statement in an SQL context that isn't able to see your PL/SQL context. That has several impacts here. Firstly, you have to call your procedure from PL/SQL so you need to create an anonymous block, as Egor Skriptunoff said, and exactly the format you need depends on what the table (and thus your vaiable) contains. The shortest it might be is:

execute immdiate 'begin ' || p_proc_name || ' end;' using 1;

But that assumes the varible contains a value like:

test_pkg.d_service(:arg);

If it only contains the name of the procedure with no arguments and no package qualifier, i.e. just d_service, it might need to be as much as:

execute immdiate 'begin test_pkg.' || p_proc_name || '(:arg); end;' using 1;

Or something in between.

The other impact is that the procedure name has to be public as it is effectively being called from outside the package when it's invoked dynamically; so it has to be declared in the package specification. That may already be the case here from the order the procedures are appearing in the body.

But if you are always calling procedures in the same package, and since you must then have a limited number of possible values, it might be simpler to avoid dynamic SQL and use the value to decide which procedure to call:

case p_proc_name
  when 'f_service' then
    f_service(1);
  when 'd_service' then
    d_service(1);
  -- etc.
end case;

That also lets you call private procedures.

Oracle - Calling Stored Procedure in a Loop in another Stored Procedure

(as outlined in comments)

Fix the compile errors in prcShipNextDVD.

Then the call from the other stored procedure will work.

---

Related Topics