Home » SQL & PL/SQL » SQL & PL/SQL » When I use weak REF CURSOR to use BULK COLLECTION
When I use weak REF CURSOR to use BULK COLLECTION [message #187360] Sat, 12 August 2006 14:55 Go to next message
Akshar
Messages: 116
Registered: May 2006
Senior Member
Hi Everybody,

I have an old code where i want to user BULK COLLECTION
AND FORALL , but I am facing an error:

Please help to sort out the error.

here is the old code summary :

PROCEDURE CDTABLE(v_LastDate  IN DATE,
                     v_Table  IN VARCHAR2,
                        v_Rc OUT INTEGER)
  IS

     TYPE tCurCDRec IS RECORD
    
      (CLAIMID                tblClaimDetail_H.CLAIMID%TYPE,
       CLAIMLINENUMB          tblClaimDetail_H.CLAIMLINENUMB%TYPE,
       ..............................
       ..............................
       BRANCHNUMB             tblClaim.BRANCHNUMB%TYPE,
       FincTypeRevCd          tblClaimDetail.FincTypeRevCd%TYPE
      );
      

      recCD                   tcurCDRec;
       
        
      TYPE tcurCD IS REF CURSOR RETURN tcurCDRec;  ---------------->HERE IS STRONG REF CURSOR.
      
      curCD                        tcurCD;

 
BEGIN
 
      
IF v_Table = 'C' THEN
      
     OPEN curCD FOR
          SELECT /*+RULE*/ CD.CLAIMID,CD.CLAIMLINENUMB,0 CLAIMDETAILHISTSEQNUMB,....
               ....................
               ....................
          FROM tblClaimDetail CD, tblClaim C
          WHERE (AuthorizationID IS NOT NULL OR ProvAmount > 0)
          AND   APNumb IS NOT NULL
          AND   CD.ClaimID = C.ClaimiD;
          
ELSE
      
        OPEN curCD FOR
          SELECT CD.CLAIMID,CD.CLAIMLINENUMB,CD.CLAIMDETAILHISTSEQNUMB,....................
           .............................
           ................................
          FROM tblClaimDetail_H CD, tblClaim C
          WHERE (AuthorizationID IS NOT NULL OR ProvAmount <> 0)
          AND   CD.ClaimID = C.ClaimID
          .....................

END IF;
    
   LOOP

        FETCH curCD INTO  recCD;

        EXIT WHEN curCD%NOTFOUND;
      
        .................................



*************************************************************
THE ABOVE IS AN OLD CODE , IN ORDER TO USE COLLECTION AND FORALL IN THE CODE
I HAVE CHANGED IT AS FOLLOWING SRUCTURE , BUT THAT IS GIVING ME ERROR ON THE ROW OF 'BULK COLLECTIN INTO..........'

( ERROR : ORA-01722: invalid number
ORA-06512: at "AKDADHAN.PK_CDTABLE_PART_TEST", line 296
ORA-06512: at line 11
************************************************************
changed code summary:


PROCEDURE CDTABLE(v_LastDate  IN DATE,
                     v_Table  IN VARCHAR2,
                        v_Rc OUT INTEGER)
  IS

     TYPE tCurCDRec IS RECORD
    
      (CLAIMID                tblClaimDetail_H.CLAIMID%TYPE,
       CLAIMLINENUMB          tblClaimDetail_H.CLAIMLINENUMB%TYPE,
       ..............................
       ..............................
       BRANCHNUMB             tblClaim.BRANCHNUMB%TYPE,
       FincTypeRevCd          tblClaimDetail.FincTypeRevCd%TYPE
      );
      
TYPE tCurCDRecType IS TABLE OF tCurCDRec INDEX BY PLS_INTEGER;

      recCD                   tcurCDRectYPE;
       
TYPE tblclaimdetmonthtype    IS TABLE OF tblclaimdetmonthly_s_part%ROWTYPE;
      
      tblclaimonthly               tblclaimdetmonthtype := tblclaimdetmonthtype ();
           


      curCD                   SYS_REFCURSOR;   --------------> I HAVE DECLARED WEAK REF CURSOR HERE.

 
BEGIN
 
      
IF v_Table = 'C' THEN
      
   OPEN curCD FOR
          SELECT /*+RULE*/ CD.CLAIMID,CD.CLAIMLINENUMB,0 CLAIMDETAILHISTSEQNUMB,....
               ....................
               ....................
          FROM tblClaimDetail CD, tblClaim C
          WHERE (AuthorizationID IS NOT NULL OR ProvAmount > 0)
          AND   APNumb IS NOT NULL
          AND   CD.ClaimID = C.ClaimiD;
          
ELSE
      
   OPEN curCD FOR
          SELECT CD.CLAIMID,CD.CLAIMLINENUMB,CD.CLAIMDETAILHISTSEQNUMB,....................
           .............................
           ................................
          FROM tblClaimDetail_H CD, tblClaim C
          WHERE (AuthorizationID IS NOT NULL OR ProvAmount <> 0)
          AND   CD.ClaimID = C.ClaimID
          .....................

END IF;
    
   LOOP

        FETCH curCD 
        BULK COLLECT INTO  recCD LIMIT 100000;  ---------------->  LINE 296, ERROR GENERATES HERE.

        EXIT WHEN recCD.COUNT = 0; 
      
    
           
          FOR i IN 1..recCD.COUNT
          LOOP
          
          
          tblclaimonthly.EXTEND;
      
      
   ......... COLLECTION PROCESSING ........................


        tblclaimonthly(tblclaimonthly.LAST).ClaimID             	   := recCD(i).ClaimID;
        tblclaimonthly(tblclaimonthly.LAST).ClaimLineNumb       	   := recCD(i).ClaimLineNumb;
        ............................................................................
        ...................................................................
        tblclaimonthly(tblclaimonthly.LAST).OperationCenterCode 	   := recCD(i).OperationCenterCode;
    
       END LOOP;
          
            FORALL i IN 1 .. tblclaimonthly.COUNT 
               INSERT INTO tblclaimdetmonthly_s_part
                    VALUES  tblclaimonthly (i);
         

EXIT WHEN curCD%NOTFOUND;

END LOOP;
COMMIT;
CLOSE curCD;
      
END;



Re: When I use weak REF CURSOR to use BULK COLLECTION [message #187472 is a reply to message #187360] Mon, 14 August 2006 02:51 Go to previous messageGo to next message
amit_kiran
Messages: 50
Registered: July 2006
Location: UK
Member

Try this one out..

while creating Type Change the datatypes to
From To
varchar2 dbms_sql.VARCHAR2_table
number dbms_sql.NUMBER_table
date dbms_sql.DATE_table


while fetching instead of recCD write
FETCH curCD BULK COLLECT INTO
recCD.CLAIMID,recCD.CLAIMLINENUMB,..... LIMIT 100000;


PROCEDURE CDTABLE(v_LastDate  IN DATE,
                     v_Table  IN VARCHAR2,
                        v_Rc OUT INTEGER)
  IS

     TYPE tCurCDRec IS RECORD
    
      (CLAIMID                tblClaimDetail_H.CLAIMID%TYPE,
       CLAIMLINENUMB          tblClaimDetail_H.CLAIMLINENUMB%TYPE,
       ..............................
       ..............................
       BRANCHNUMB             tblClaim.BRANCHNUMB%TYPE,
       FincTypeRevCd          tblClaimDetail.FincTypeRevCd%TYPE
      );
      
--TYPE tCurCDRecType IS TABLE OF tCurCDRec INDEX BY PLS_INTEGER;

      recCD                   tCurCDRec;
       
TYPE tblclaimdetmonthtype    IS TABLE OF tblclaimdetmonthly_s_part%ROWTYPE;
      
      tblclaimonthly               tblclaimdetmonthtype := tblclaimdetmonthtype ();
           


      curCD                   SYS_REFCURSOR;   --------------> I HAVE DECLARED WEAK REF CURSOR HERE.

 
BEGIN
 
      
IF v_Table = 'C' THEN
      
   OPEN curCD FOR
          SELECT /*+RULE*/ CD.CLAIMID,CD.CLAIMLINENUMB,0 CLAIMDETAILHISTSEQNUMB,....
               ....................
               ....................
          FROM tblClaimDetail CD, tblClaim C
          WHERE (AuthorizationID IS NOT NULL OR ProvAmount > 0)
          AND   APNumb IS NOT NULL
          AND   CD.ClaimID = C.ClaimiD;
          
ELSE
      
   OPEN curCD FOR
          SELECT CD.CLAIMID,CD.CLAIMLINENUMB,CD.CLAIMDETAILHISTSEQNUMB,....................
           .............................
           ................................
          FROM tblClaimDetail_H CD, tblClaim C
          WHERE (AuthorizationID IS NOT NULL OR ProvAmount <> 0)
          AND   CD.ClaimID = C.ClaimID
          .....................

END IF;
    
   LOOP

        FETCH curCD 
        BULK COLLECT INTO  recCD LIMIT 100000;  ---------------->  LINE 296, ERROR GENERATES HERE.

        EXIT WHEN recCD.COUNT = 0; 
      
    
           
          FOR i IN 1..recCD.COUNT
          LOOP
          
          
          tblclaimonthly.EXTEND;
      
      
   ......... COLLECTION PROCESSING ........................


        tblclaimonthly(tblclaimonthly.LAST).ClaimID             	   := recCD(i).ClaimID;
        tblclaimonthly(tblclaimonthly.LAST).ClaimLineNumb       	   := recCD(i).ClaimLineNumb;
        ............................................................................
        ...................................................................
        tblclaimonthly(tblclaimonthly.LAST).OperationCenterCode 	   := recCD(i).OperationCenterCode;
    
       END LOOP;
          
            FORALL i IN 1 .. tblclaimonthly.COUNT 
               INSERT INTO tblclaimdetmonthly_s_part
                    VALUES  tblclaimonthly (i);
         

EXIT WHEN curCD%NOTFOUND;

END LOOP;
COMMIT;
CLOSE curCD;
      
END;
Re: When I use weak REF CURSOR to use BULK COLLECTION [message #187572 is a reply to message #187472] Mon, 14 August 2006 09:10 Go to previous messageGo to next message
Akshar
Messages: 116
Registered: May 2006
Senior Member
Amit Thansk a lot for your reply.

when i did assigned recCD elements
individually into BULK COLLECT statment
i came across with this error :

PLS-00497: cannot mix between single row and multi-row (BULK) in INTO list

Is there any other way ?

[Updated on: Mon, 14 August 2006 09:11]

Report message to a moderator

Re: When I use weak REF CURSOR to use BULK COLLECTION [message #187696 is a reply to message #187572] Mon, 14 August 2006 23:55 Go to previous messageGo to next message
Akshar
Messages: 116
Registered: May 2006
Senior Member



Is there any other approach to above query?
Re: When I use weak REF CURSOR to use BULK COLLECTION [message #187876 is a reply to message #187696] Wed, 16 August 2006 02:55 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
You were on the right track. I've created a simplified test scenario. It uses the demo HR.EMPLOYEES table.

the script:
set serverout on
PROMPT Wrong procedure:
DECLARE

     TYPE t_emprec IS RECORD( employee_id employees.employee_id%TYPE
                            , first_name  employees.first_name%TYPE 
                            , last_name   employees.last_name%TYPE  
                            );
    
     recCD  t_emprec;
     curCD  SYS_REFCURSOR;

BEGIN    
   OPEN curCD FOR
   SELECT employee_id
        , first_name
        , last_name
   FROM   employees
   WHERE  department_id < 40;
        
             
    
   LOOP
     FETCH curCD 
     BULK COLLECT INTO  recCD LIMIT 100000;  

     EXIT WHEN recCD.COUNT = 0; 
      
    
           
     FOR i IN 1..recCD.COUNT
     LOOP
       dbms_output.put_line('Processing employee '||recCD(i).last_name);
     END LOOP;
           
  END LOOP;
  CLOSE curCD;   
END;
/

PROMPT Corrected version:
DECLARE

     TYPE t_emprec IS RECORD( employee_id employees.employee_id%TYPE
                            , first_name  employees.first_name%TYPE 
                            , last_name   employees.last_name%TYPE  
                            );
     
     TYPE t_emptab IS TABLE of t_emprec;
      
     recCD  t_emptab;
     
     curCD  SYS_REFCURSOR;

BEGIN    
   OPEN curCD FOR
   SELECT employee_id
        , first_name
        , last_name
   FROM   employees
   WHERE  department_id < 40;
        
             
    
--   LOOP
     FETCH curCD 
     BULK COLLECT INTO  recCD LIMIT 100000;  

--     EXIT WHEN recCD.COUNT = 0; 
      
    
           
     FOR i IN 1..recCD.COUNT
     LOOP
       dbms_output.put_line('Processing employee '||recCD(i).last_name);
     END LOOP;
           
--  END LOOP;
  CLOSE curCD;   
END;
/

The test run:
SQL> @orafaq
Wrong procedure:
     BULK COLLECT INTO  recCD LIMIT 100000;
                        *
ERROR at line 23:
ORA-06550: line 23, column 25:
PLS-00497: cannot mix between single row and multi-row (BULK) in INTO list
ORA-06550: line 25, column 22:
PLS-00302: component 'COUNT' must be declared
ORA-06550: line 25, column 6:
PL/SQL: Statement ignored
ORA-06550: line 29, column 24:
PLS-00302: component 'COUNT' must be declared
ORA-06550: line 29, column 6:
PL/SQL: Statement ignored


Corrected version:
Processing employee Whalen
Processing employee Hartstein
Processing employee Fay
Processing employee Raphaely
Processing employee Khoo
Processing employee Baida
Processing employee Tobias
Processing employee Himuro
Processing employee Colmenares

PL/SQL procedure successfully completed.

SQL>


This should give you an idea. You should read up on bulk processing. The loop is not necessary.

MHE
Re: When I use weak REF CURSOR to use BULK COLLECTION [message #187961 is a reply to message #187876] Wed, 16 August 2006 09:17 Go to previous message
Akshar
Messages: 116
Registered: May 2006
Senior Member
Hey Maaher,

Greatfull to see your reply!

Thanks for your reply i will remove the loop portion and give a try and let you know.
Previous Topic: CREATE CLUSTER - convert a string to a number?
Next Topic: Tuning a Plsql procedure
Goto Forum:
  


Current Time: Fri Dec 09 19:28:03 CST 2016

Total time taken to generate the page: 0.11291 seconds