Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.misc -> Invalid Cursor (follow my previous post)?
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; .. BEGINReceived on Thu Jun 27 2002 - 21:54:23 CDT
...
... 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; ... ...
![]() |
![]() |