CODEWITHSUNDEEP
sqloracle-databaseplsql
daZza
daZza

Reputation: 1689

"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;

Upvotes: 0

Views: 3963

Answers (1)

Michael Piankov
Michael Piankov

Reputation: 1997

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.

Upvotes: 3

Related Questions