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 >; l_rec >; BEGIN get_data_1(>, >, 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. 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.

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 >; l_rec >; BEGIN get_data_1(>, >, 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.

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.

Thanks that makes sense. Though looping seems inefficient. Any good resource you can point to that shows making pipelined functions and then using them in another SP?

– Reeth Nov 11 '10 at 15:51 @Reeth - added a quick demo of using pipelined table functions. – Justin Cave Nov 11 '10 at 20:12.

At this point, you might as well define a new package and learn how to use cursor loops to deal with this, it will give you more programmatic control. PL/SQL is what you need to look up.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.