Reference cursor bugs

Toad for Oracle
1

I am using a reference cursor in TOAD that compiles just fine with no error, but it causes an unpredictable error that gets the page that I’m loading to bom-b out with a 404 error. The log that gets generated points to the line in my code where I open the cursor. I tried using a template cursor instead of the record and got the same result. Keep in mind that I was able to get it to the point of compiling, so there are no technical bugs in the code. That is what makes it difficult. Here is what the code for the cursor looks like: (copy and paste it into notepad for better visibility)

v_testCur varchar2(32767) := ‘SELECT …;’;

…

…

/create ref cursor type, then create variables of this type/
TYPE rc IS REF CURSOR;
cur_docs_found rc;

…

TYPE cur_docs_template IS RECORD (
testkey doc_view_hcas.dockey%TYPE

);

i cur_docs_template;

BEGIN

…

OPEN cur_docs_found FOR v_testCur;
LOOP
FETCH cur_docs_found INTO i;
EXIT WHEN cur_docs_found%NOTFOUND;

…

END Loop;

CLOSE cur_docs_found;

END;

/

I need professional help, so please give me some ideas if you are a debugging expert. The rest of my team couldn’t figure it out either. They are clueless!

2

Here is the code that you can play around with in TOAD: It doesn’t compile even though there are no problems:

declare

v_search varchar2(50) := ‘HHSP233201350021A’;

result varchar2(50);

cursor myTest_cursor is

SELECT *

FROM doc_view_hcas

WHERE docnum LIKE v_search || ‘%’

OR ordernum LIKE v_search || ‘%’

ORDER BY docnum, ordernum, verkey;

/* v_myCur1 is used only when the v_search textfield has content */

v_testCur varchar2(32767) := 'SELECT docnum, ordernum, verkey

FROM doc_view_hcas

WHERE docnum LIKE ‘’%’ || v_search || ‘%’’

OR ordernum LIKE ‘’%’ || v_search || ‘%’’

ORDER BY docnum, ordernum, verkey;’;

/create ref cursor type, then create variables of this type/

TYPE rc IS REF CURSOR;

cur_docs_found rc;

TYPE cur_docs_template IS RECORD (

ordernum doc_view_hcas.ordernum%TYPE,

docnum doc_view_hcas.docnum%TYPE,

verkey doc_view_hcas.verkey%TYPE

);

myVar cur_docs_template;

begin

/*

for myTest in myTest_cursor

loop

result := myTest.docnum || ’ Test concatination’;

DBMS_OUTPUT.PUT_LINE(result);

end loop;

*/

OPEN cur_docs_found FOR v_testCur;

loop

FETCH cur_docs_found INTO myVar;

EXIT WHEN cur_docs_found%NOTFOUND;

result := myVar.docnum || ’ Test concatination’;

DBMS_OUTPUT.PUT_LINE(result);

end loop;

CLOSE cur_docs_found;

end;