Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Invalid Cursor (follow my previous post)?

Invalid Cursor (follow my previous post)?

From: C Chang <cschang_at_maxinter.net>
Date: Thu, 27 Jun 2002 22:54:23 -0400
Message-ID: <3D1BCFDF.9F9@maxinter.net>


Hi Daniel and others:
Thank for your help. The Output return to an ASP page which creates a HTML table using the record set. I am looking to your suggestion to use the EXPLAIN PLAN. In the meantime, I modify my code of the v_sqlstmt string for the implicit cursor.
I used the "BULK COLLECT INTO" for a implicit cursor in BEGIN section of my package. However, it generated an error of "INVALID CURSOR ORA-06512" for line "FETCH v_cursor..", what went wrong here? According to the reference books, that BULK COLLECT is still applicable to such cursor. Thanks for anyone's help.  

PROCEDURE getVendorConsumption
( .. )
 IS
  ... other variables

	TYPE  cursor_T IS REF CURSOR;
   	v_cursor    cursor_T;
     v_count			NUMBER DEFAULT 0;
      v_sqlstmt		VARCHAR2(2000);	
		
	TYPE a_nsn          IS TABLE OF VARCHAR2(13);
	TYPE a_vendor_id    IS TABLE OF catalog_items.vendor_id%TYPE;
	TYPE a_vendor_list  IS TABLE OF catalog_items.vendor_list%TYPE;
	TYPE a_vendor_lin   IS TABLE OF catalog_items.vendor_lin%TYPE;
	TYPE a_qty_period   IS TABLE OF catalog_items.qty_period%TYPE;
	TYPE a_contract_id  IS TABLE OF catalog_items.contract_id%TYPE;
	
	r_nsn           a_nsn;
	r_vendor_id     a_vendor_id;
	r_vendor_list   a_vendor_list;
	r_vendor_lin    a_vendor_lin;
	r_qty_period    a_qty_period;
	r_contract_id   a_contract_id;
	..        
	BEGIN	

...
... v_sqlstmt:= 'SELECT ci.vendor_id,ci.fsc||ci.niin,'; v_sqlstmt:= v_sqlstmt || ' ci.vendor_list, c.vendor_lin,c.qty_period,ci.contract_id'; v_sqlstmt:= v_sqlstmt || ' FROM vp$contract_status c, catalog_items ci'; v_sqlstmt:= v_sqlstmt || ' WHERE ci.contract_id =' ||''''|| v_siteContract ||''''; v_sqlstmt:= v_sqlstmt || ' AND ci.contract_id=c.contract '; v_sqlstmt:= v_sqlstmt || ' AND ci.niin=c.niin ';
...
OPEN v_cursor FOR v_sqlstmt; FETCH v_cursor BULK COLLECT INTO r_vendor_id,r_nsn,r_vendor_list, r_vendor_lin,r_qty_period,r_contract_id; CLOSE v_cursor; /* INTO r_vendor,v_nsn,v_source_list, v_source_lin,v_qty_period,v_contractId; EXIT WHEN v_cursor%NOTFOUND; */ FOR i IN 1 .. r_nsn.COUNT LOOP v_count:= v_count + 1; ... to assign all the variables of the COLLECTION to output. END LOOP; ... ...
Received on Thu Jun 27 2002 - 21:54:23 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US