Home » SQL & PL/SQL » SQL & PL/SQL » Problem with collections (oracle 10G)
Problem with collections [message #440681] Tue, 26 January 2010 10:45 Go to next message
jitender.sadh
Messages: 82
Registered: May 2007
Member
Hi All,

i am facing the problem when slecting data into table type using bulk collect.please help me to find out what is the problem in below code(please see below image).

1)creating one object type

CREATE OR REPLACE TYPE pr_info_ty IS object (id NUMBER,NAME VARCHAR(100),sal NUMBER(10),dept NUMBER(6))

2) create another type of table

CREATE OR REPLACE TYPE pr_info_tab_ty IS TABLE OF pr_info_ty


3) getting error ORA-00947 Not enough value

declare
vr_pr_info_tab_ty pr_info_tab_ty;
BEGIN
SELECT empno,ename,sal,deptno bulk COLLECT INTO
vr_pr_info_tab_ty FROM emp ;
END;


./fa/7352/0/



Best Regards
Jitender Sadh
  • Attachment: 1.jpg
    (Size: 101.30KB, Downloaded 241 times)
Re: Problem with collections [message #440684 is a reply to message #440681] Tue, 26 January 2010 10:51 Go to previous messageGo to next message
Michel Cadot
Messages: 63805
Registered: March 2007
Location: Nanterre, France, http://...
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 Go to previous messageGo to next message
jitender.sadh
Messages: 82
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 #440693 is a reply to message #440685] Tue, 26 January 2010 11:04 Go to previous messageGo to next message
Michel Cadot
Messages: 63805
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
A record with 4 attributes is not 4 variables, it is only one variable, you need to tell Oracle what you want to return (SELECT) is a record and so the statement I gave.

Regards
Michel
Re: Problem with collections [message #440696 is a reply to message #440693] Tue, 26 January 2010 11:19 Go to previous messageGo to next message
jitender.sadh
Messages: 82
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 #440700 is a reply to message #440696] Tue, 26 January 2010 11:36 Go to previous messageGo to next message
Michel Cadot
Messages: 63805
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Sorry but it is beyond my skills to scroll up and down your image and at the same time left to right what you posted.
Next time, use command line SQL*Plus and copy and paste in text mode then I can see and even reproduce what you say.
Maybe someone else is not so limited that I am.

Regards
Michel
Re: Problem with collections [message #440701 is a reply to message #440700] Tue, 26 January 2010 11:51 Go to previous messageGo to next message
jitender.sadh
Messages: 82
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
jitender.sadh
Messages: 82
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
==============
Re: Problem with collections [message #440726 is a reply to message #440681] Tue, 26 January 2010 16:15 Go to previous message
scottwmackey
Messages: 505
Registered: March 2005
Senior Member
SQL>     declare
  2       TYPE tt IS TABLE OF pr_info_ty;
  3     tt1  tt;
  4   BEGIN 
  5    SELECT pr_info_ty(empno,ename,sal,deptno)  bulk COLLECT INTO tt1 FROM emp ;
  6   END;
  7  
  8  /

Previous Topic: 24 hr report from audit script
Next Topic: case insensitive search
Goto Forum:
  


Current Time: Tue Sep 27 02:35:20 CDT 2016

Total time taken to generate the page: 0.14149 seconds