The above procedure is complied with zero errors.in that am using refcursor.know am calling that procedure in BEGIN and END block,am getting error.

am calling as below.

BEGIN

zz_refcur3(p_DEPTID =>30,p_outref => l_refcur);

END;

my requirment is i wnat to call that above refcursor procedure in this block only.

You can't reference 'l_refcur' in the call since it is only defined INSIDE the procedure you are calling.

Define the cursor in a package OUTSIDE the procedure or in an anonymous block as Solomon has shown.

Why don't you just tell us what PROBLEM you are trying to solve by using this code to begin with?

The code clearly can NOT be doing anything useful. Worse, it uses slow-by-slow (row by row) processing. If you are trying to learn about cursors or PL/SQL programming this is a TERRIBLE code example to learn from.

Better still, if the intention is to fetch data from one table to insert into another table, then just do all of that inside a procedure (using an INSERT ... SELECT ... statement) and then they can call that procedure to do the whole thing without having to worry about any ref cursors.

Row by row fetching and inserting will be slow.

Just fetching data to re-insert it in a loop is bad practice and bad design.

First off, you're getting "ORA-01001: invalid cursor" because you define a cursor for p_outref (ie. it has a select statement assigned to it), but you don't define a query for l_refcur. You can't open a cursor that isn't defined!

Secondly, if all you're wanting to do is insert records from one table into another, and the front end guys don't care to see your logic, why do you need a ref cursor at all?

If they need to see the records that have been inserted, then perhaps you need something like: