Home » SQL & PL/SQL » SQL & PL/SQL » nested tables in pl/sql (11g )
nested tables in pl/sql [message #571522] Mon, 26 November 2012 19:23 Go to next message
neymarleo
Messages: 5
Registered: November 2012
Junior Member
let's say i have table emp_test with two columns ename and job both varchar2


1 DECLARE
2 TYPE EMP_TAB_TYPE IS TABLE OF EMP_TEST%ROWTYPE;
3 EMP_TAB EMP_TAB_TYPE;
4 BEGIN
5 EMP_TAB:=EMP_TAB_TYPE('john','ENG');
6 NULL;
7* END;
SQL> /
EMP_TAB:=EMP_TAB_TYPE('john','ENG');
*
ERROR at line 5:
ORA-06550: line 5, column 10:
PLS-00306: wrong number or types of arguments in call to 'EMP_TAB_TYPE'
ORA-06550: line 5, column 10:
PLS-00306: wrong number or types of arguments in call to 'EMP_TAB_TYPE'
ORA-06550: line 5, column 1:
PL/SQL: Statement ignored

--------------------------------------------------------------------------
how do i assign multi column to this nested table , can any one advise?
Re: nested tables in pl/sql [message #571523 is a reply to message #571522] Mon, 26 November 2012 19:29 Go to previous messageGo to next message
BlackSwan
Messages: 21944
Registered: January 2009
Senior Member
you should avoid TYPE which only add complexity & confusion

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/
Re: nested tables in pl/sql [message #571524 is a reply to message #571523] Mon, 26 November 2012 19:34 Go to previous messageGo to next message
neymarleo
Messages: 5
Registered: November 2012
Junior Member
so u don't know how the answer blackswan
Re: nested tables in pl/sql [message #571525 is a reply to message #571524] Mon, 26 November 2012 20:13 Go to previous messageGo to next message
BlackSwan
Messages: 21944
Registered: January 2009
Senior Member
until you become more proficient in basic SQL you should only use basic & existing DB objects
Re: nested tables in pl/sql [message #571530 is a reply to message #571522] Mon, 26 November 2012 21:21 Go to previous messageGo to next message
tony123
Messages: 9
Registered: August 2012
Junior Member

this is a example!

SQL> set serverout on
SQL>
SQL> CREATE TABLE emp_test(ename VARCHAR2(100),job VARCHAR2(100));

Table created
SQL> INSERT INTO emp_test VALUES('john', 'ENG');

1 row inserted
SQL> INSERT INTO emp_test VALUES('tom', 'USA');

1 row inserted
SQL> COMMIT;

Commit complete
SQL> DECLARE
2 TYPE EMP_TAB_TYPE IS TABLE OF EMP_TEST%ROWTYPE INDEX BY BINARY_INTEGER;
3 EMP_TAB EMP_TAB_TYPE ;
4 J INTEGER DEFAULT 1;
5 BEGIN
6 FOR I IN (SELECT * FROM EMP_TEST) LOOP
7 EMP_TAB(J) := I;
8 DBMS_OUTPUT.PUT_LINE(EMP_TAB(J).ENAME || '*' || EMP_TAB(J).JOB);
9 J := J + 1;
10 END LOOP;
11 --EMP_TAB := EMP_TAB_TYPE('john', 'ENG');
12 NULL;
13 END;
14 /

john*ENG
tom*USA

PL/SQL procedure successfully completed

SQL>
Re: nested tables in pl/sql [message #571549 is a reply to message #571530] Tue, 27 November 2012 01:20 Go to previous messageGo to next message
Michel Cadot
Messages: 57611
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.

Regards
Michel
Re: nested tables in pl/sql [message #571588 is a reply to message #571522] Tue, 27 November 2012 07:37 Go to previous messageGo to next message
Solomon Yakobson
Messages: 1794
Registered: January 2010
Senior Member
neymarleo wrote on Mon, 26 November 2012 20:23
how do i assign multi column to this nested table , can any one advise?


This has nothing to do with nested tables. This is RECORD type issue. PL/SQL RECORD type has no constructor so we can't construct a record "on-the-fly" like you are trying to do. Record can be only assigned another record of same structure. Or we need to assign values to each attribute individually. So you have two choices (I'll use SCOTT.EMP table):

DECLARE
    TYPE EMP_TAB_TYPE IS TABLE OF EMP%ROWTYPE;
    EMP_TAB EMP_TAB_TYPE := EMP_TAB_TYPE();
BEGIN
    EMP_TAB.EXTEND;
    EMP_TAB(1).EMPNO := 9999;
    EMP_TAB(1).ENAME := 'john';
    EMP_TAB(1).JOB   := 'janitor';
END;
/


or

DECLARE
    TYPE EMP_TAB_TYPE IS TABLE OF EMP%ROWTYPE;
    EMP_TAB EMP_TAB_TYPE;
    EMP_REC EMP%ROWTYPE;
BEGIN
    EMP_REC.EMPNO := 9999;
    EMP_REC.ENAME := 'john';
    EMP_REC.JOB   := 'janitor';
    EMP_TAB:=EMP_TAB_TYPE(EMP_REC);
END;
/


SY.
Re: nested tables in pl/sql [message #571591 is a reply to message #571588] Tue, 27 November 2012 08:33 Go to previous message
neymarleo
Messages: 5
Registered: November 2012
Junior Member
THANK YOU SOLOMON , APPRECIATED.
Previous Topic: Select query with rownum=1 how to use order by
Next Topic: performance tuning of the query
Goto Forum:
  


Current Time: Fri Apr 18 20:52:36 CDT 2014

Total time taken to generate the page: 0.13323 seconds