daZza daZza - 1 month ago 13
SQL Question

"ORA-01001 invalid cursor" in procedure (PL/SQL package)

I am developing a PL/SQL package and just finished with the general framework so that I can go for a first test.

However, I am getting a strange

ORA-01001 invalid cursor
error within my procedure, which I can't seem to figure out.

Any ideas what might be invalid about my cursors? The SELECT statements of both cursors do return values and I've escaped
"KEY"
and
"VALUE"
using double quotes to avoid running into problems with reserved words...

Here's my package body code, thanks for any hints in advance:

create or replace PACKAGE BODY MY_PP AS

PROCEDURE my_proc(execProc IN NUMBER DEFAULT 1) IS

cursor c_cursor1 is SELECT "KEY", "VALUE" FROM MY_TABLE_1;
cursor c_cursor2 is SELECT "KEY", "VALUE" FROM MY_TABLE_2;

vc_my_variable1 VARCHAR2(100);
vc_my_variable2 VARCHAR2(100);
vc_my_variable3 VARCHAR2(100);
vc_my_variable4 VARCHAR2(100);

sql_query VARCHAR2(400);

v_data_type VARCHAR2(100);

BEGIN

OPEN c_cursor1;
LOOP

FETCH c_cursor1 INTO vc_my_variable1, vc_my_variable2;
EXIT WHEN c_cursor1%NOTFOUND;

SELECT DATA_TYPE INTO v_data_type FROM USER_TAB_COLS WHERE COLUMN_NAME = vc_my_variable1 AND TABLE_NAME = 'MY_TABLE_3';

IF v_data_type = 'VARCHAR2' THEN
sql_query := 'UPDATE MY_TABLE_3 SET :1 = :2;';
END IF;


IF v_data_type = 'NUMBER' THEN
sql_query := 'UPDATE MY_TABLE_3 SET :1 = TO_NUMBER(:2);';
END IF;

EXECUTE IMMEDIATE sql_query USING vc_my_variable1, vc_my_variable2;




END LOOP;

CLOSE c_cursor1;


OPEN c_cursor2;
LOOP

FETCH c_cursor2 INTO vc_my_variable3, vc_my_variable4;
EXIT WHEN c_cursor2%NOTFOUND;

SELECT "DATA_TYPE" INTO v_data_type FROM USER_TAB_COLS WHERE COLUMN_NAME = vc_my_variable3 AND TABLE_NAME = 'MY_TABLE_4';

IF v_data_type = 'VARCHAR2' THEN
sql_query := 'UPDATE MY_TABLE_4 SET :1 = :2;';
END IF;


IF v_data_type = 'NUMBER' THEN
sql_query := 'UPDATE MY_TABLE_4 SET :1 = TO_NUMBER(:2);';
END IF;

EXECUTE IMMEDIATE sql_query USING vc_my_variable3, vc_my_variable4;




END LOOP;

CLOSE c_cursor2;

commit;

END my_proc;

END MY_PP;

Answer

EDIT: I find some mistakes in your package

1) you try to update

        sql_query := 'UPDATE MY_TABLE_3 SET :1 = :2;';

and after binds with for instance 'col1' and 'val2'; It will look like

UPDATE MY_TABLE_3 SET 'col1' = 'val2';

but when you use bind variables in execute immediate statement you send just literals. And you cant update one string with another. If you want write correct update you should add SQL injection;

        sql_query := 'UPDATE MY_TABLE_3 SET '||vc_my_variable1 ||' = :2;';
  ... 
  EXECUTE IMMEDIATE sql_query USING vc_my_variable2;

2) Next one (done)

move EXIT WHEN c_cursor1%NOTFOUND;

after FETCH c_cursor1 INTO vc_my_variable1, vc_my_variable2;

3) Error ORA-01001 invalid cursor. May be appear if your MAXOPENCURSORS too small or The Logon Data Area (LDA) must be defined by using OLON or OLOGON. If the LDA is not defined, this message is issued for the following calls: OPEN, COM, CON, ROL, and LOGOFF. http://www.dba-oracle.com/t_ora_01001_invalid_cursor.htm.

Comments