Geek Answers Handbook

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

I have an existing stored procedure that takes 2 parameters and returns the oracle cursor back. The cursor contains from 30 to 60 lines of data.

I want to use the previously existing stored procedure above in another stored procedure as a table ... basically I want to call a pre-existing stored procedure and see if the returned rows contain the specified value back.

For instance:

SP 1 = get_data_1 (returns oracle cursor) SP 2 = get_data_2

in get_data_2

 select count(*) from get_data_1 (pass_input_parms) A where A.ID = '12345'

It is clear that it seems to me a trivial matter, but, being new to the oracle world, I do not know how to use pre-existing stored procedures that return cursors.

How can I do it?

+4
source share
2 answers

You cannot reuse the REF CURSOR from get_data_1 in the following SQL statement because it is just a pointer to an instruction descriptor. The cursor itself does not contain data.

You can 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 can imagine, this usually becomes outdated pretty quickly. Given that Oracle generally doesn’t store procedures that return REF CURSOR parameters, unless you return a ready-made view of the data to a client application. For example, if there was a general idea that GET_DATA_1 and GET_DATA_2 could request, rather than GET_DATA_2, call GET_DATA_1, which would simplify the program. If GET_DATA_1 was the pipeline function of the table, and not the procedure that returned REF CURSOR, then it would be much easier to call GET_DATA_1 from GET_DATA_2.

If you want to get started with the pipeline functions of the table (using the SCOTT scheme)

 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
+5
source

At this point, you can also define a new package and learn how to use the cursor paths to handle this, this will give you more programmatic control. PL / SQL is what you need to find.

0
source

More articles:


All Articles