Home » SQL & PL/SQL » SQL & PL/SQL » Invalid reference to variable, ORA-22806: not an object or REF (Toad 10.1.6.3)
Invalid reference to variable, ORA-22806: not an object or REF [message #622659] Thu, 28 August 2014 06:02 Go to next message
lvrvln
Messages: 5
Registered: August 2014
Junior Member
Hi Team

Please help me on below issue

DROP TABLE test PURGE;

CREATE TABLE test (id NUMBER);

DROP TABLE test1 PURGE;

CREATE TABLE test` (id NUMBER);

BEGIN
   INSERT INTO test
        VALUES (1);

   INSERT INTO test
        VALUES (2);

   INSERT INTO test
        VALUES (3);

   INSERT INTO test
        VALUES (4);

   INSERT INTO test
        VALUES (5);

   INSERT INTO test
        VALUES (6);

   INSERT INTO test
        VALUES (7);

   INSERT INTO test
        VALUES (8);

   COMMIT;
END;


BEGIN
   INSERT INTO test1
        VALUES (1);

   INSERT INTO test1
        VALUES (7);

   INSERT INTO test1
        VALUES (8);

   COMMIT;
END;

SELECT * FROM sad1;


DECLARE
   TYPE t1 IS TABLE OF test1.id%TYPE;

   a_id   t1;
   a      NUMBER;
BEGIN
   SELECT id
     BULK COLLECT INTO a_id
     FROM test
    WHERE id NOT IN (SELECT id
                       FROM test1
                      WHERE test.id = test1.id);

   FOR r IN 1 .. a_id.COUNT
   LOOP
        INSERT INTO test1 VALUES (a_id(r).id);

      a := SQL%ROWCOUNT;
      DBMS_OUTPUT.put_line (a);
   END LOOP;
END;

I am getting below  error.

ORA-06550: line 16, column 34:
PLS-00487: Invalid reference to variable 'TEST1.ID%TYPE'
ORA-06550: line 16, column 34:
PL/SQL: ORA-22806: not an object or REF
ORA-06550: line 16, column 9:
PL/SQL: SQL Statement ignored


please help me to solve above error


Lalit :

1. Added code tags
2. Changed topic name from "Need Help" to something meaningful

[Updated on: Thu, 28 August 2014 06:15] by Moderator

Report message to a moderator

Re: Need Help [message #622662 is a reply to message #622659] Thu, 28 August 2014 06:08 Go to previous messageGo to next message
Lalit Kumar B
Messages: 2411
Registered: May 2013
Location: World Wide on the Web
Senior Member
Welcome to the forum!

Please read and follow the forum guidelines, to enable us to help you: http://www.orafaq.com/forum/t/88153/0/ and read http://www.orafaq.com/forum/t/174502/
Re: Need Help [message #622663 is a reply to message #622659] Thu, 28 August 2014 06:09 Go to previous messageGo to next message
Michel Cadot
Messages: 59291
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> DECLARE
  2  TYPE t1 IS TABLE OF test1.id%TYPE;
  3  
  4  a_id t1;
  5  a NUMBER;
  6  BEGIN
  7  SELECT id
  8  BULK COLLECT INTO a_id
  9  FROM test
 10  WHERE id NOT IN (SELECT id
 11  FROM test1
 12  WHERE test.id = test1.id);
 13  
 14  FOR r IN 1 .. a_id.COUNT
 15  LOOP
 16  INSERT INTO test1 VALUES (a_id(r).id);
 17  
 18  a := SQL%ROWCOUNT;
 19  DBMS_OUTPUT.put_line (a);
 20  END LOOP;
 21  END;
 22  /
INSERT INTO test1 VALUES (a_id(r).id);
                          *
ERROR at line 16:
ORA-06550: line 16, column 27:
PLS-00487: Invalid reference to variable 'TEST1.ID%TYPE'
ORA-06550: line 16, column 27:
PL/SQL: ORA-22806: not an object or REF
ORA-06550: line 16, column 1:
PL/SQL: SQL Statement ignored

SQL> DECLARE
  2  TYPE t1 IS TABLE OF test1.id%TYPE;
  3  
  4  a_id t1;
  5  a NUMBER;
  6  BEGIN
  7  SELECT id
  8  BULK COLLECT INTO a_id
  9  FROM test
 10  WHERE id NOT IN (SELECT id
 11  FROM test1
 12  WHERE test.id = test1.id);
 13  
 14  FOR r IN 1 .. a_id.COUNT
 15  LOOP
 16  INSERT INTO test1 VALUES (a_id(r));
 17  
 18  a := SQL%ROWCOUNT;
 19  DBMS_OUTPUT.put_line (a);
 20  END LOOP;
 21  END;
 22  /
1
1
1
1
1
1
1
1

PL/SQL procedure successfully completed.

Of course, it should be better to do it in a single INSERT SELECT:
SQL> rollback;

Rollback complete.

SQL> insert into test1 select id from test;

8 rows created.


Re: Invalid reference to variable, ORA-22806: not an object or REF [message #622666 is a reply to message #622659] Thu, 28 August 2014 06:24 Go to previous messageGo to next message
Lalit Kumar B
Messages: 2411
Registered: May 2013
Location: World Wide on the Web
Senior Member
lvrvln,

It was your first post. I did few things on your behalf, in future please keep in mind these two things :

1. Use code tags.
2. Mention a meaningful topic title than "Need Help".

It will help you. Good luck!
Re: Need Help [message #622667 is a reply to message #622663] Thu, 28 August 2014 06:25 Go to previous messageGo to next message
lvrvln
Messages: 5
Registered: August 2014
Junior Member
Thanks Michel.
Re: Need Help [message #622684 is a reply to message #622667] Thu, 28 August 2014 07:56 Go to previous messageGo to next message
Michel Cadot
Messages: 59291
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Also forget to mention, if you want to get profit of array processing (for something other than this one), you must use FORALL instead of FOR (and there is no LOOP):
SQL> DECLARE
  2  TYPE t1 IS TABLE OF test1.id%TYPE;
  3  
  4  a_id t1;
  5  a NUMBER;
  6  BEGIN
  7  SELECT id
  8  BULK COLLECT INTO a_id
  9  FROM test
 10  WHERE id NOT IN (SELECT id
 11  FROM test1
 12  WHERE test.id = test1.id);
 13  
 14  FORALL r IN 1 .. a_id.COUNT
 15  INSERT INTO test1 VALUES (a_id(r));
 16  a := a_id.COUNT;
 17  DBMS_OUTPUT.put_line (a);
 18  END;
 19  /
8

PL/SQL procedure successfully completed.

Re: Need Help [message #622720 is a reply to message #622667] Thu, 28 August 2014 12:58 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2036
Registered: January 2010
Senior Member
Or you could use database types. And there is even no need to create one, we can use pre-defined type sys.OdciNumberList:

DECLARE
    v_id_list sys.OdciNumberList;
    v_cnt number;
BEGIN
   SELECT id
     BULK COLLECT INTO v_id_list
     FROM test
    WHERE id NOT IN (SELECT id
                       FROM test1
                      WHERE test.id = test1.id);
   INSERT INTO test1 SELECT * FROM TABLE(v_id_list);
   v_cnt := SQL%ROWCOUNT;
   DBMS_OUTPUT.put_line(v_cnt);
END;
/
5

PL/SQL procedure successfully completed.

SQL>


And, of course, there is no need for PL/SQL here at all. It can be done with single SQL:

SQL> INSERT
  2    INTO test1
  3    SELECT  id
  4      FROM test
  5      WHERE id NOT IN (SELECT id
  6                         FROM test1
  7                        WHERE test.id = test1.id);

5 rows created.

SQL> 


SY.
SY.
Re: Need Help [message #622721 is a reply to message #622684] Thu, 28 August 2014 13:04 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2036
Registered: January 2010
Senior Member
Hi Michel,

Code is supposed to return 5, so something doesn't add up, unless you didn't populate test1.

SY.
Re: Need Help [message #622728 is a reply to message #622721] Thu, 28 August 2014 13:35 Go to previous message
Michel Cadot
Messages: 59291
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Yes I didn't populate it, I just populated the first table and focused on the code.
Sorry for any misleading.

Previous Topic: update a table based on another table with no specific order
Next Topic: insert a character within a string
Goto Forum:
  


Current Time: Wed Oct 01 21:53:45 CDT 2014

Total time taken to generate the page: 0.13864 seconds