Home » SQL & PL/SQL » SQL & PL/SQL » PLS-00497: cannot mix between single row and multi-row (BULK) in INTO list
PLS-00497: cannot mix between single row and multi-row (BULK) in INTO list [message #188451] Fri, 18 August 2006 11:10 Go to next message
Akshar
Messages: 116
Registered: May 2006
Senior Member


Could anybody help me to find out what am i doing wrong here?




      curCD                        SYS_REFCURSOR;   

  

      TYPE tCurCDRecType IS TABLE OF tCurCDRec index by  PLS_integer;
       
      recCD                        tCurCDRec;   



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



IF v_table = 'C' THEN

 OPEN curCD 
     FOR   
	 ' SELECT /*+RULE  CD.CLAIMID,CD.CLAIMLINENUMB,0 CLAIMDETAILHISTSEQNUMB,''A'' CLAIMDETAILHISTRECTYPECD,'||
           ' CD.PATIENTNUMBER,CD.INTAKEID,CD.OPERATIONCENTERCODE,CD.SERVICECODE,CD.UOMCODE,CD.PROVID,'||
           ' CD.FROMDATE,CD.TODATE,CD.PROVINVAMOUNT,CD.APNUMB,CD.APPROVEDUNITS,CD.PROVAMOUNT,CD.PLANAMOUNT,CD.FEEFORSERVICE,'||
           ' CD.STATUSCD,CD.PLANSVCSCHEDULERATE,CD.PROVSVCSCHEDULERATE,CD.AUTHORIZATIONID,CD.EXTRACONTRACTREASONCODE,'||
           ' CD.PLANOVERRIDERATE,CD.PROVOVERRIDERATE,CD.CLAIMDETADJTYPECD,CD.ACCOUNTINGDEPARTMENTRPTCD,CD.PROVTYPECD,'||
           ' CD.CARRIERSERVICECODE,CD.ARNumb,C.PROVINVNUMB,C.PROVINVDT,C.BRANCHNUMB, CD.FincTypeRevCd  '||
          'FROM akdadhan.tblClaimDetail_new CD, tblClaim C '||
          'WHERE (AuthorizationID IS NOT NULL OR ProvAmount > 0) '||
          'AND   APNumb IS NOT NULL '||
          'AND   CD.ClaimID = C.ClaimiD '||
          'AND   CD.ClaimID >= ' || v_FirstCD ||
          'AND   CD.ClaimID < '  ||v_MaxCD    ||
          'AND   CD.Statuscd <>'||'''IGNORE''' ;
        
	  
	  ELSE
        
   OPEN curCD FOR 
    ' SELECT CD.CLAIMID ,CD.CLAIMLINENUMB,CD.CLAIMDETAILHISTSEQNUMB,CD.CLAIMDETAILHISTRECTYPECD,'||
         ' CD.PATIENTNUMBER,CD.INTAKEID,CD.OPERATIONCENTERCODE,CD.SERVICECODE,CD.UOMCODE,CD.PROVID, '||
          ' CD.FROMDATE,CD.TODATE,CD.PROVINVAMOUNT,CD.APNUMB,CD.APPROVEDUNITS,CD.PROVAMOUNT,CD.PLANAMOUNT,CD.FEEFORSERVICE,'||
           ' CD.STATUSCD,CD.PLANSVCSCHEDULERATE,CD.PROVSVCSCHEDULERATE,CD.AUTHORIZATIONID,CD.EXTRACONTRACTREASONCODE,'||
           ' CD.PLANOVERRIDERATE,CD.PROVOVERRIDERATE,CD.CLAIMDETADJTYPECD,CD.ACCOUNTINGDEPARTMENTRPTCD,CD.PROVTYPECD, '||
           ' CD.CARRIERSERVICECODE,CD.ARNumb,C.PROVINVNUMB,C.PROVINVDT,C.BRANCHNUMB, CD.FincTypeRevCd  '||
         ' FROM tblClaimDetail_H CD, tblClaim C '||
         ' WHERE (AuthorizationID IS NOT NULL OR ProvAmount <> 0) '|| -- 6/9/2006 - RRARMSTR - Check for all non-zero amounts
         ' AND   APNumb IS NOT NULL '||
         ' AND   (ClaimDetailHistRecTypeCD = ''E'' or ClaimDetailHistRecTypeCD = ''O'') '||
         ' AND   CD.ClaimID = C.ClaimID '||
         ' AND   CD.ClaimID >= ' ||v_FirstCD ||
         ' AND   CD.ClaimID < '  || v_MaxCD  ||
         ' AND   CD.StatusCD <>'||'''IGNORE''' ;
  
   
   
 END IF;
    
      TimingStop(v_CurFiscalMonth, v_CurStep, v_Timings);
      TimingStart('BEGIN INNER DETAIL LOOP (' || v_FirstCD || ' - ' || v_MaxCD || ')',v_Timings);
      
		 
LOOP 
	 
	      FETCH curCD 
          BULK COLLECT  INTO recCD LIMIT 100000;

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

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



Re: PLS-00497: cannot mix between single row and multi-row (BULK) in INTO list [message #188464 is a reply to message #188451] Fri, 18 August 2006 12:47 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
Are you unwilling or incapable of using GOOGLE?
Results 1 - 10 of about 415 for PLS-00497.

Re: PLS-00497: cannot mix between single row and multi-row (BULK) in INTO list [message #188508 is a reply to message #188464] Fri, 18 August 2006 17:02 Go to previous message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Closed: look at your original thread.

If you have any additional questions post them there. I know I told you this before but you should read up on bulk operations.

http://www.orafaq.com/forum/fa/448/0/

MHE
Previous Topic: Restricting recordset before joining to large table?
Next Topic: how I use Arbic oracle database , form and report
Goto Forum:
  


Current Time: Sun Dec 04 18:32:20 CST 2016

Total time taken to generate the page: 0.08217 seconds