11 Replies Latest reply: Aug 19, 2014 5:33 AM by Boneist RSS

    use of refcursor prcedure in begin and end blocks

    2734106

      create or replace procedure
      zz_refcur3(p_DEPTID in EMP.DEPTNO%type ,p_outref  out SYS_REFCURSOR)as

          l_refcur
      SYS_REFCURSOR;l_EMPID  EMP.EMPNO%TYPE;l_ENAME
      EMP.ENAME%TYPE;l_DEPTID  EMP.DEPTNO%TYPE;
           
      begin
            
      open  p_outref for

          select EMPNO, ENAME, DEPTNO from EMP where DEPTNO = p_DEPTID ;      
      LOOP
          
      FETCH l_refcur INTO l_EMPID, l_ENAME,
      l_DEPTID;        

            EXIT WHEN l_refcur%NOTFOUND;    
      DBMS_OUTPUT.PUT_LINE ( l_EMPID|| l_ENAME || l_DEPTID);           

           END LOOP;

         CLOSE l_refcur;

      end;

       

      --------------------------------------

      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.

       

      could you  please help me?

      tanks in advance

        • 1. Re: use of refcursor prcedure in begin and end blocks
          Solomon Yakobson

          You never declared l_refcur in BEGIN END block. Use:

           

          DECLARE

          l_refcursor sys_refcursor;

          BEGIN

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

          END;

          /

           

          SY.

          • 2. Re: use of refcursor prcedure in begin and end blocks
            rp0428

            2734106 wrote:

             

            create or replace procedure
            zz_refcur3(p_DEPTID in EMP.DEPTNO%type ,p_outref  out SYS_REFCURSOR)as

                l_refcur
            SYS_REFCURSOR;l_EMPID  EMP.EMPNO%TYPE;l_ENAME
            EMP.ENAME%TYPE;l_DEPTID  EMP.DEPTNO%TYPE;
                 
            begin
                  
            open  p_outref for

                select EMPNO, ENAME, DEPTNO from EMP where DEPTNO = p_DEPTID ;      
            LOOP
                
            FETCH l_refcur INTO l_EMPID, l_ENAME,
            l_DEPTID;        

                  EXIT WHEN l_refcur%NOTFOUND;    
            DBMS_OUTPUT.PUT_LINE ( l_EMPID|| l_ENAME || l_DEPTID);           

                 END LOOP;

               CLOSE l_refcur;

            end;

             

            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.

            • 4. Re: use of refcursor prcedure in begin and end blocks
              2734106

              here am fetching the records and store some another table.in above code am not mentioned that insert query

               

              i tried in anonymous block am getting below error .like

              DECLARE

              l_refcur  sys_refcursor;

              BEGIN

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

              END;

               

               

              ORA-01001: invalid cursor

              ORA-06512: at "ZZ_REFCUR3", line 7

              ORA-06512: at line 4

               

              if you know help me.

              am asking ..........

              is it possible to call the refcursor procedure in begin and block ?

              • 5. Re: use of refcursor prcedure in begin and end blocks
                2734106

                am front end application supproted only begin and end blocks.

                 

                here am fetching from one table to insert into another table one by one records.

                 

                i know its a TERRIBLE code .but if u know  wonderfull code how to write with above example by using refcursor ?

                • 6. Re: use of refcursor prcedure in begin and end blocks
                  SKP

                  You may need like below:

                   

                  create or replace procedure

                  zz_refcur3(p_DEPTID in EMP.DEPTNO%type ,p_outref  out SYS_REFCURSOR)as   

                  begin      

                  open  p_outref for

                      select EMPNO, ENAME, DEPTNO from EMP where DEPTNO = p_DEPTID ;     

                  end;

                  /

                   

                  set serveroutput on

                  DECLARE

                  l_refcur SYS_REFCURSOR;

                  l_EMPID  EMP.EMPNO%TYPE;

                  l_ENAME  EMP.ENAME%TYPE;

                  l_DEPTID  EMP.DEPTNO%TYPE; 

                  BEGIN

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

                  LOOP   

                  FETCH l_refcur INTO l_EMPID, l_ENAME,l_DEPTID;       

                        EXIT WHEN l_refcur%NOTFOUND;   

                    DBMS_OUTPUT.PUT_LINE ( l_EMPID|| l_ENAME || l_DEPTID);          

                  END LOOP;

                  END;

                   

                  /

                  • 7. Re: use of refcursor prcedure in begin and end blocks
                    2734106

                    thanks for that code SKP .

                     

                    i wnat that entire code into one procedure.

                     

                    i want to create one procedure and i will give to that procedure name only to java or .net or remedy developers.

                     

                    they don't want to see my logic.

                     

                    just they are calling my procedure in begin and end block.

                    r u getting my point ?

                    please help me?

                    • 8. Re: use of refcursor prcedure in begin and end blocks
                      BluShadow

                      But if they want the ref cursor returning to their own code, they still have to bind a variable into the OUT parameter of the procedure when they call the procedure in their anonymous call.

                      The ref cursor has to be declared somewhere so that it has somewhere to exist upon returning from the procedure.

                       

                      Your problem is not with SQL or PL/SQL code, it's with how they use other code to call PL/SQL.

                      Would they prefer a function that returns a ref cursor rather than a procedure with an OUT parameter?  Depends on the language doing the calling and what IT is capable of.

                      • 9. Re: use of refcursor prcedure in begin and end blocks
                        BluShadow

                        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.

                        • 10. Re: use of refcursor prcedure in begin and end blocks
                          SKP

                          Try the below one

                           

                          set serveroutput on

                          DECLARE

                          l_refcur SYS_REFCURSOR;

                          l_EMPID  EMP.EMPNO%TYPE;

                          l_ENAME  EMP.ENAME%TYPE;

                          l_DEPTID  EMP.DEPTNO%TYPE;

                          procedure zz_refcur1(p_DEPTID in EMP.DEPTNO%type ,p_outref  out SYS_REFCURSOR)

                          as   

                          --p_outref SYS_REFCURSOR;

                          begin      

                          open  p_outref for

                              select EMPNO, ENAME, DEPTNO from EMP where DEPTNO = p_DEPTID ;     

                          end zz_refcur1;

                          BEGIN

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

                          LOOP   

                          FETCH l_refcur INTO l_EMPID, l_ENAME,l_DEPTID;       

                                EXIT WHEN l_refcur%NOTFOUND;   

                            DBMS_OUTPUT.PUT_LINE ( l_EMPID|| l_ENAME || l_DEPTID);          

                          END LOOP;

                          END;

                           

                          /

                          • 11. Re: Re: use of refcursor prcedure in begin and end blocks
                            Boneist

                            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:

                             

                            create or replace procedure zz_refcur3 (p_deptid in emp.deptno%type,
                                                                    p_outref out sys_refcursor)
                            as
                            begin
                              insert into table1 (col1, col2, col3)
                              select col1, col2, col3
                              from   table2
                              where  col4 = p_deptid;
                            
                              open p_outref for (select col1, col2, col3
                                                 from table2 where col4 = p_deptid);
                            end;
                            /
                            
                            

                             

                            Then you'd be able to call it with:

                             

                            declare
                              l_refcur sys_refcursor;
                            begin
                              zz_refcur3 (p_deptid =>30, p_outref => l_refcur);
                            end;
                            /