Home » SQL & PL/SQL » SQL & PL/SQL » Populate Ref Cursor (8.1.7.0.0)
Populate Ref Cursor [message #281100] Thu, 15 November 2007 13:27 Go to next message
jriggs
Messages: 12
Registered: October 2007
Junior Member
How would I populate a ref cur using the following code:

CREATE OR REPLACE PROCEDURE jrTest
IS
  repcode  VARCHAR2(30);
  CURSOR Cur_Revs IS 
    SELECT   RepairCode
    FROM     Repair_Codes
    WHERE    RepairCode LIKE 'JOE%'
    GROUP BY RepairCode;
BEGIN
  OPEN Cur_Revs;
   LOOP
    FETCH Cur_Revs INTO repcode;
    
    EXIT WHEN Cur_Revs%NOTFOUND;
    
    BEGIN
      UPDATE Repair_Codes
      SET    RepairCode = 'TESTING' --i know this will cause an error
      WHERE  RepairCode = repcode;
    EXCEPTION
      WHEN OTHERS THEN
        dbms_Output.Put_Line('bad data at '
                             ||repcode
                             ||'--->'
                             ||SQLERRM);
    END; 
   END LOOP;
  CLOSE Cur_Revs;
END;
/


What I want to accomplish is instead of printing this line " dbms_Output.Put_Line('bad data at..." I want to somehow insert it into a ref cursor that can be returned to the calling application. It should have 1 row for each error encountered.
Re: Populate Ref Cursor [message #281101 is a reply to message #281100] Thu, 15 November 2007 13:35 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
using SQL would be much simpler & much more efficient
INSERT INTO raises
   SELECT employee_id, salary*1.1 FROM employees
   WHERE commission_pct > .2
   LOG ERRORS INTO errlog ('my_bad') REJECT LIMIT 10;
Re: Populate Ref Cursor [message #281206 is a reply to message #281100] Fri, 16 November 2007 01:55 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You could create a userdefined table type in SQL, and then record the errors in a variable of that type.
You would then be able to open a refcursor that selected from this variable. You'd use the TABLE keyword for tha in 9i and above, and there is a syntax in 8i for doing it, but I can't remember the details.
Re: Populate Ref Cursor [message #281357 is a reply to message #281206] Fri, 16 November 2007 08:52 Go to previous messageGo to next message
jriggs
Messages: 12
Registered: October 2007
Junior Member
Thanks for the replies. Here's what I came up with trying to create a table type. I'm just wondering if I'm on the right track or not with this solution-

CREATE OR REPLACE PROCEDURE JERROR
(V OUT MYVARS.COMMONCURTYP)
IS
BEGIN
DECLARE
N INTEGER := 3;
TYPE ERROR_CODES_T IS TABLE OF VARCHAR2(255) INDEX BY BINARY_INTEGER ;
ERROR_CODES ERROR_CODES_T;
BEGIN
FOR J IN 1.. N LOOP
ERROR_CODES(J) := 'JOEJOE';

DBMS_OUTPUT.PUT_LINE(ERROR_CODES(J));
END LOOP;

OPEN V FOR
SELECT *
FROM ERROR_CODES;
END;
END JERROR;
/

Warning: Procedure created with compilation errors.

SQL> sho err
Errors for PROCEDURE JERROR:

LINE/COL ERROR
---------------------------------------------------------
17/2 PL/SQL: SQL Statement ignored
17/16 PLS-00201: identifier 'ERROR_CODES' must be declared
Re: Populate Ref Cursor [message #281361 is a reply to message #281100] Fri, 16 November 2007 09:15 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

First Mistake

You need to create Type outside the scope of PL/SQL .(in SQL)

refer the link

Thumbs Up
Rajuvan
Re: Populate Ref Cursor [message #281362 is a reply to message #281357] Fri, 16 November 2007 09:48 Go to previous messageGo to next message
Michel Cadot
Messages: 64106
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
More precisely PL/SQL types are not known of SQL engine. In addition you have to use the syntax "from table(error_codes)".
You have to create an external type using "CREATE TYPE" statement.

Note: In 11g, this restriction is removed.

Regards
Michel
Re: Populate Ref Cursor [message #281376 is a reply to message #281362] Fri, 16 November 2007 10:31 Go to previous message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Hmhm .. thats a good news

Thumbs Up
Rajuvan
Previous Topic: PLSQL procedure
Next Topic: Pls -000306
Goto Forum:
  


Current Time: Mon Dec 05 02:59:17 CST 2016

Total time taken to generate the page: 0.11077 seconds