Home » SQL & PL/SQL » SQL & PL/SQL » PLS-00302: component 'A' must be declared (Oracle, 10g, windows XP)
PLS-00302: component 'A' must be declared [message #335716] Wed, 23 July 2008 07:09 Go to next message
jyothsna1612
Messages: 68
Registered: June 2008
Member
Hi,
I tried to get the o/p of table's column with the collections
but giving the error.I searched for this unable to grasp why the error.
I need suggestion in my code.
SQL> declare
  2  type typ_dup1 is table of dup1%rowtype;
  3  v_dup1 typ_dup1;
  4  begin
  5  select * bulk collect into v_dup1 from dup1;
  6  dbms_output.put_line(v_dup1.a);
  7  end;
  8  /
dbms_output.put_line(v_dup1.a);
                            *
ERROR at line 6:
ORA-06550: line 6, column 29:
PLS-00302: component 'A' must be declared
ORA-06550: line 6, column 1:
PL/SQL: Statement ignored


SQL> desc dup1
 Name       Null?    Type                                                               
 ----------------- ------------
 A                            NUMBER



Thanks

Re: PLS-00302: component 'A' must be declared [message #335718 is a reply to message #335716] Wed, 23 July 2008 07:13 Go to previous messageGo to next message
jyothsna1612
Messages: 68
Registered: June 2008
Member
Hi,
I'm sorry in hurry i forgot to format the code.
DECLARE
  TYPE TYP_DUP1 IS TABLE OF DUP1%ROWTYPE;
   V_DUP1  TYP_DUP1;
BEGIN
  SELECT *
  BULK COLLECT INTO V_DUP1
  FROM   DUP1;
  
  DBMS_OUTPUT.PUT_LINE(V_DUP1.A);
END;


ERROR at line 6:
ORA-06550: line 6, column 29:
PLS-00302: component 'A' must be declared
ORA-06550: line 6, column 1:
PL/SQL: Statement ignored
Re: PLS-00302: component 'A' must be declared [message #335722 is a reply to message #335716] Wed, 23 July 2008 07:25 Go to previous messageGo to next message
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
v_dup1 is not a record but a table of record; a table needs an index to know which element it has to access. Otherwise how does it know which record it has to return?

Regards
Michel
Re: PLS-00302: component 'A' must be declared [message #335733 is a reply to message #335716] Wed, 23 July 2008 07:57 Go to previous messageGo to next message
ora_2007
Messages: 430
Registered: July 2007
Location: Mumbai
Senior Member
You can use FOR clause V_DUP1.first..V_DUP1.last to select the record.
Re: PLS-00302: component 'A' must be declared [message #335870 is a reply to message #335716] Thu, 24 July 2008 01:05 Go to previous messageGo to next message
jyothsna1612
Messages: 68
Registered: June 2008
Member
Hi
I tried with the following code . Still getting error

DECLARE
  TYPE TYP_DUP1 IS TABLE OF DUP1%ROWTYPE;
   V_DUP1  TYP_DUP1;
BEGIN
  SELECT A
  BULK COLLECT INTO V_DUP1
  FROM   DUP1;
  
  FOR I IN V_DUP1.FIRST.. V_DUP1.LAST LOOP
    DBMS_OUTPUT.PUT_LINE(V_DUP1(I).A);
  END LOOP;
END;
/


but getting the error
********************
declare
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at line 6
********************

Thanks
Jyothsna
Re: PLS-00302: component 'A' must be declared [message #335872 is a reply to message #335870] Thu, 24 July 2008 01:08 Go to previous messageGo to next message
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> select * from DUP1;
         A
----------
        10

1 row selected.

SQL> DECLARE
  2    TYPE TYP_DUP1 IS TABLE OF DUP1%ROWTYPE;
  3     V_DUP1  TYP_DUP1;
  4  BEGIN
  5    SELECT A
  6    BULK COLLECT INTO V_DUP1
  7    FROM   DUP1;
  8    
  9    FOR I IN V_DUP1.FIRST.. V_DUP1.LAST LOOP
 10      DBMS_OUTPUT.PUT_LINE(V_DUP1(I).A);
 11    END LOOP;
 12  END;
 13  /
10

PL/SQL procedure successfully completed.

SQL> delete DUP1;

1 row deleted.

SQL> DECLARE
  2    TYPE TYP_DUP1 IS TABLE OF DUP1%ROWTYPE;
  3     V_DUP1  TYP_DUP1;
  4  BEGIN
  5    SELECT A
  6    BULK COLLECT INTO V_DUP1
  7    FROM   DUP1;
  8    
  9    FOR I IN V_DUP1.FIRST.. V_DUP1.LAST LOOP
 10      DBMS_OUTPUT.PUT_LINE(V_DUP1(I).A);
 11    END LOOP;
 12  END;
 13  /
DECLARE
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at line 9

Verify if there is something in your array.

Regards
Michel
Re: PLS-00302: component 'A' must be declared [message #335877 is a reply to message #335870] Thu, 24 July 2008 01:18 Go to previous messageGo to next message
raghu2110
Messages: 5
Registered: July 2008
Location: INDIA
Junior Member
Hi,

When you are using a PL/SQL record it has to be indexed by a binary integer or PLS_INTEGER or VARCHAR2 depending on the PL/SQL table

DECLARE
TYPE TYP_DUP1 IS TABLE OF DUP1%ROWTYPE;
INDEX BY BINARY_INTEGER;
V_TYP_DUP1 TYP_DUP1;

Kindly check this out

Regards,
P.Raghuveer

[Updated on: Thu, 24 July 2008 01:52]

Report message to a moderator

Re: PLS-00302: component 'A' must be declared [message #335878 is a reply to message #335872] Thu, 24 July 2008 01:23 Go to previous messageGo to next message
jyothsna1612
Messages: 68
Registered: June 2008
Member
Thanks Michel,

As you explained ...
There is no data in DUP1 table.

Now i got the o/p
Thanks again
Re: PLS-00302: component 'A' must be declared [message #335880 is a reply to message #335716] Thu, 24 July 2008 01:26 Go to previous messageGo to next message
himang
Messages: 282
Registered: March 2005
Location: Bangalore
Senior Member

If you are not sure of data in table and to avoid the error in case of no values in the collection, make use of COUNT


SQL> select * from dup1;

no rows selected

SQL> DECLARE
  2    TYPE TYP_DUP1 IS TABLE OF DUP1%ROWTYPE;
  3     V_DUP1  TYP_DUP1;
  4  BEGIN
  5    SELECT A
  6    BULK COLLECT INTO V_DUP1
  7    FROM   DUP1;
  8    FOR I IN 1.. V_DUP1.COUNT LOOP
  9      DBMS_OUTPUT.PUT_LINE(V_DUP1(I).A);
 10    END LOOP;
 11* END;
SQL> /

PL/SQL procedure successfully completed.

Re: PLS-00302: component 'A' must be declared [message #335882 is a reply to message #335877] Thu, 24 July 2008 01:34 Go to previous message
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
raghu2110 wrote on Thu, 24 July 2008 08:18
When you are using a PL/SQL record it has to be indexed by a binary integer

This is not true as the provided examples prove it.
You should upgrade your PL/SQL knowledge from V7 to at least 10.2, collections were introduced in 8.0.

Regards
Michel

Previous Topic: Sum value of ASCII characters
Next Topic: Need help on using join
Goto Forum:
  


Current Time: Thu Apr 25 19:59:31 CDT 2024