|
Re: Problem with collections [message #440684 is a reply to message #440681] |
Tue, 26 January 2010 10:51   |
 |
Michel Cadot
Messages: 68737 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
You have 3 columns, so you must 3 output variables.
You can change
SELECT empno,ename,sal,deptno ...
to
SELECT pr_info_ty (empno,ename,sal,deptno) ...
Regards
Michel
|
|
|
Re: Problem with collections [message #440685 is a reply to message #440684] |
Tue, 26 January 2010 10:57   |
jitender.sadh
Messages: 86 Registered: May 2007
|
Member |
|
|
hi Michel,
i have four columns .if you see second code in the below image in which i am fetching records in table for type record which have four attribute , working fine. but whent i am using the same logic by using object type its giving me error .. can you please why its giving error with object type.
second thing code suggested by you is working ...why we use the code that way...
Best Regards
Jitender Sadh
|
|
|
|
Re: Problem with collections [message #440696 is a reply to message #440693] |
Tue, 26 January 2010 11:19   |
jitender.sadh
Messages: 86 Registered: May 2007
|
Member |
|
|
ok i am agree on that but i want to know that if i create a table type of record(which have four attribute),its working fine when i fetch four column in that table but when i try to do the same but at that i create table type of object(having four attribute) gives error.
Regards
jitender sadh
|
|
|
|
Re: Problem with collections [message #440701 is a reply to message #440700] |
Tue, 26 January 2010 11:51   |
jitender.sadh
Messages: 86 Registered: May 2007
|
Member |
|
|
sorry for that...
now i am putting the code using sql*plus
In the below code i defined one table type of record and use it to fetch data using bulk collect and its working fine
SQL> declare
2 TYPE rr IS RECORD(id NUMBER,NAME VARCHAR2(100),sal NUMBER(4),dept NUMBER(3));
3 TYPE tt IS TABLE OF rr;
4 tt1 tt;
5 BEGIN
6 SELECT empno,ename,sal,deptno bulk COLLECT INTO tt1 FROM emp ;
7 END;
8 /
PL/SQL procedure successfully completed.
SQL>
now when i define table of user object type and use it to fetch data, it gives error..
SQL> CREATE OR REPLACE TYPE pr_info_ty IS object (id NUMBER,NAME VARCHAR(100),sal NUMBER(10),dept N
UMBER(6))
2 /
Type created.
SQL>
SQL> declare
2 TYPE tt IS TABLE OF pr_info_ty;
3 tt1 tt;
4 BEGIN
5 SELECT empno,ename,sal,deptno bulk COLLECT INTO tt1 FROM emp ;
6 END;
7
8 /
SELECT empno,ename,sal,deptno bulk COLLECT INTO tt1 FROM emp ;
*
ERROR at line 5:
ORA-06550: line 5, column 56:
PL/SQL: ORA-00947: not enough values
ORA-06550: line 5, column 3:
PL/SQL: SQL Statement ignored
SQL>
Regards
jitender
|
|
|
Re: Problem with collections [message #440702 is a reply to message #440696] |
Tue, 26 January 2010 11:55   |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
The answer to the question I think you're asking is this:
You can do a bulk collect into a table of type number without using any other functions because this is a table of a single value - no constructor functions are required to create a row in the table.
With a table of a record type, each row in the table is a single value, so if you want to do a bulk collect into it, you need to use a constructor function to map the values returned by the query into values that can go into the table.
If your query returned 3 numeric values, you could bulk collect them into 3 seperate table of Numbers, without using any constructor functions.
|
|
|
Re: Problem with collections [message #440704 is a reply to message #440702] |
Tue, 26 January 2010 12:12   |
jitender.sadh
Messages: 86 Registered: May 2007
|
Member |
|
|
actually what i understand during testing that if your table type depend upon another user defined object type, we need constructor method for insert value in it.if it depends on any record type then there is no need of constructor.
please suggest is it true?
Regards
Jitender Sadh
==============
|
|
|
|