Home » SQL & PL/SQL » SQL & PL/SQL » nested table creation error (oracle 10G)
nested table creation error [message #451033] Mon, 12 April 2010 03:55 Go to next message
jitender.sadh
Messages: 86
Registered: May 2007
Member
hi all,

i am getting compilation error when create nested table type depend on table structure
SQL> CREATE OR REPLACE TYPE typ$nttb$1 IS TABLE OF emp%rowtype
  2  ;
  3  /

Warning: Type created with compilation errors.

SQL> show error
Errors for TYPE TYP$NTTB$1:

LINE/COL ERROR
-------- -----------------------------------------------------------------
0/0      PL/SQL: Compilation unit analysis terminated
1/29     PLS-00329: schema-level type has illegal reference to SCOTT.EMP
SQL> 


please suggest whats wrong in that.


best regards
jitender sadh
Re: nested table creation error [message #451041 is a reply to message #451033] Mon, 12 April 2010 04:35 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
please suggest whats wrong in that

emp%rowtype

Regards
Michel
Re: nested table creation error [message #451050 is a reply to message #451033] Mon, 12 April 2010 04:43 Go to previous messageGo to next message
jitender.sadh
Messages: 86
Registered: May 2007
Member
hi,

i know its wrong (emp%rowtype) but when i use same declaration in pl/sql its working

SQL> DECLARE
  2  TYPE   typ$nsttb$1 IS TABLE OF emp%ROWTYPE;
  3  v_typ$nsttb$1 typ$nsttb$1;
  4  BEGIN
  5  SELECT * BULK COLLECT INTO v_typ$nsttb$1 FROM emp;
  6  FOR i IN v_typ$nsttb$1.first .. v_typ$nsttb$1.last LOOP
  7  Dbms_Output.put_line(v_typ$nsttb$1(i).empno||'-'||v_typ$nsttb$1(i).ename);
  8  END LOOP;
  9  END;
 10  
 11  /

PL/SQL procedure successfully completed.



can you please tell me the reason why it behave like that when work with sql.

best regards
jitender sadh
Re: nested table creation error [message #451055 is a reply to message #451050] Mon, 12 April 2010 04:47 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL type and PL/SQL type are 2 different things.

Regards
Michel
Re: nested table creation error [message #451068 is a reply to message #451033] Mon, 12 April 2010 05:17 Go to previous messageGo to next message
jitender.sadh
Messages: 86
Registered: May 2007
Member
hi

so you mean that oracle do not support to declare type of emp%rowtype in sql and support same in pl/sql.


best regards
jitender
Re: nested table creation error [message #451070 is a reply to message #451033] Mon, 12 April 2010 05:19 Go to previous message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
Yes
Previous Topic: Retrieving records where all the rows satisfies the condition
Next Topic: sql query
Goto Forum:
  


Current Time: Fri Mar 29 10:15:26 CDT 2024