[DYNAMIC SQL] ORA 01002 Please help !!

From: Myster Cool <mystercool_at_hotmail.com>
Date: 29 Apr 2002 08:34:07 -0700
Message-ID: <36d2401a.0204290734.4b4c229c_at_posting.google.com>

Hi all !

i must set a stored procedure in dynamic pl/sql which must update a group of tables owned by two different users.

I've set up correctly the authorisations to those users, no problems for this.

My problem is this one : when I try to fetch a second cursor based on a id which comes form the first one, i've got an ORA-01002 error.

Since I 've already read a lot of news and nearly all the technet on this topic (and my collegues don't find any problem to the code), I really wonder what's going on :(
Each help, idea, or even smoke signal (even without fire) would be *very* grateful for me, because I'm really lost ! Please help !!

Thanks for your answers !

Here is a little sample of the code which seems to be wrong (i do not give the complete package which is nearly 400 lines long).

---

CREATE OR REPLACE PROCEDURE TEST IS
  cursor_work INTEGER; --- for the primary table   cursor_work2 INTEGER; --- for the secondary table linked to the first one by a foreign key
  dir_id NUMBER; --- primary key of my primar table   id NUMBER; --- primary key of my secondary table   ignore NUMBER;
  ignore2 NUMBER;
BEGIN
curseur_travail := DBMS_SQL.OPEN_CURSOR; -- open my cursor

DBMS_SQL.PARSE(curseur_travail, 'SELECT DIR_ID FROM USER1.TABLE1',
DBMS_SQL.native); -- parse it
DBMS_SQL.DEFINE_COLUMN(curseur_travail, 1, dir_id);  -- definition of

my column
ignore := DBMS_SQL.EXECUTE(curseur_travail); -- execution LOOP
  IF DBMS_SQL.FETCH_ROWS(curseur_travail)>0 THEN -- fetch it while not empty
  DBMS_SQL.COLUMN_VALUE(curseur_travail, 1, dir_id); -- get data   BEGIN
    EXECUTE IMMEDIATE 'INSERT INTO USER1.TABLE1 SELECT * FROM USER2.TABLE1 WHERE ID='||id'; -- try to insert   EXCEPTION WHEN DUP_VAL_ON_INDEX THEN -- if it doesn'ty work i update it

    EXECUTE IMMEDIATE 'UPDATE USER1.TABLE1 SET [..] WHERE ID='||dir_id;
  END;   BEGIN -- open the secondary cursor based on the result of my first cursor (dir_id)

    curseur_travail2 := DBMS_SQL.OPEN_CURSOR; -- goes right     DBMS_SQL.PARSE(curseur_travail2, 'SELECT * FROM USER1.TABLE1-1 WHERE ID = '||dir_id, DBMS_SQL.native); -- ok, goes well

    DBMS_SQL.DEFINE_COLUMN(curseur_travail2, 1, id); -- no problems     ignore2 := DBMS_SQL.EXECUTE(curseur_travail2); -- it works     LOOP --- ok
      IF DBMS_SQL.FETCH_ROWS(curseur_travail2)>0 THEN --- ==[ HERE IS THE BUG ORA-01002]==

      BEGIN
        DBMS_SQL.DEFINE_COLUMN(curseur_travail2, 1, id);
        EXECUTE IMMEDIATE 'INSERT_INTO USER1.TABLE1-1 SELECT
USER2.TABLE1-1 WHERE ID='||id;
      EXCEPTION WHEN DUP_VAL_ON_INDEX THEN
        EXECUTE IMMEDIATE 'UPDATE USER1.TABLE1-1 SET [..] WHERE ID =
'||id;
      END;
      END IF;

    END LOOP;
    DBMS_SQL.CLOSE_CURSOR(cursor_work2);   END;
END LOOP;
DBMS_SQL.CLOSE_CURSOR(cursor_work);
END; Received on Mon Apr 29 2002 - 17:34:07 CEST