Home » SQL & PL/SQL » SQL & PL/SQL » bulk collect into nested tables (oracle 10G)
bulk collect into nested tables [message #343333] Wed, 27 August 2008 00:06 Go to next message
jainpaul
Messages: 2
Registered: August 2008
Junior Member
Hi
I need a help. I was trying to do bulk collect into a nested table as a database object.
This si the script I wrote

CREATE OR REPLACE
type vmi_emp as object
(empno varchar2(500),
ename varchar2(500),
hiredate date,
sal number(20));
/

CREATE OR REPLACE
type
nested_emp
is table of
vmi_emp;


declare
v_type nested_emp;
begin
select empno,ename,hiredate,sal
bulk collect into v_type from emp;
-----
end;

I am getting an error

PL/SQL: ORA-00947: not enough values
Cant we use nested table as database object in bulk collect.
I can use it as type defiened in pl/sql but not at database level
Re: bulk collect into nested tables [message #343334 is a reply to message #343333] Wed, 27 August 2008 00:08 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & FOLLOW the Posting Guidelines as stated in URL above
Re: bulk collect into nested tables [message #343355 is a reply to message #343333] Wed, 27 August 2008 00:49 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> CREATE OR REPLACE
  2  type vmi_emp as object 
  3    (empno varchar2(500),
  4     ename varchar2(500),
  5     hiredate date,
  6     sal number(20));
  7  /

Type created.

SQL> CREATE OR REPLACE
  2  type 
  3  nested_emp
  4  is table of 
  5  vmi_emp;
  6  /

Type created.

SQL> declare
  2    v_type nested_emp;
  3  begin
  4    select empno,ename,hiredate,sal
  5    bulk collect into v_type from emp;
  6  -----
  7  end;
  8  /
bulk collect into v_type from emp;
                         *
ERROR at line 5:
ORA-06550: line 5, column 26:
PL/SQL: ORA-00947: not enough values
ORA-06550: line 4, column 1:
PL/SQL: SQL Statement ignored


SQL> @v

Version Oracle : 10.2.0.4.0

This is not an answer but this is how you should post it.

Regards
Michel

[Updated on: Wed, 27 August 2008 00:50]

Report message to a moderator

Re: bulk collect into nested tables [message #343362 is a reply to message #343355] Wed, 27 August 2008 01:00 Go to previous messageGo to next message
jainpaul
Messages: 2
Registered: August 2008
Junior Member
Sure Michel.
I will post the code in that way from next time.
Re: bulk collect into nested tables [message #343364 is a reply to message #343362] Wed, 27 August 2008 01:06 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can't use such an array but you may use:
SQL> declare
  2    cursor c is select empno,ename,hiredate,sal from emp;
  3    type nested_emp is table of c%rowtype;
  4    v_type nested_emp;
  5  begin
  6    select empno,ename,hiredate,sal bulk collect into v_type from emp;
  7  end;
  8  /

PL/SQL procedure successfully completed.

Regards
Michel
Re: bulk collect into nested tables [message #343587 is a reply to message #343364] Wed, 27 August 2008 10:29 Go to previous message
Barbara Boehmer
Messages: 8636
Registered: November 2002
Location: California, USA
Senior Member
SCOTT@orcl_11g> CREATE OR REPLACE type vmi_emp as object
  2    (empno	  varchar2(500),
  3  	ename	  varchar2(500),
  4  	hiredate  date,
  5  	sal	  number(20));
  6  /

Type created.

SCOTT@orcl_11g> CREATE OR REPLACE type nested_emp is table of vmi_emp;
  2  /

Type created.

SCOTT@orcl_11g> declare
  2    v_type nested_emp;
  3  begin
  4    select vmi_emp (empno, ename, hiredate, sal)
  5    bulk collect into v_type from emp;
  6    for i in 1 .. v_type.count loop
  7  	 dbms_output.put_line (v_type(i).empno);
  8  	 dbms_output.put_line (v_type(i).ename);
  9  	 dbms_output.put_line (v_type(i).hiredate);
 10  	 dbms_output.put_line (v_type(i).sal);
 11  	 dbms_output.put_line ('--------------');
 12    end loop;
 13  end;
 14  /
7369
SMITH
17-DEC-80
800
--------------
7499
ALLEN
20-FEB-81
1600
--------------
7521
WARD
22-FEB-81
1250
--------------
7566
JONES
02-APR-81
2975
--------------
7654
MARTIN
28-SEP-81
1250
--------------
7698
BLAKE
01-MAY-81
2850
--------------
7782
CLARK
09-JUN-81
2450
--------------
7788
SCOTT
09-DEC-82
3000
--------------
7839
KING
17-NOV-81
5000
--------------
7844
TURNER
08-SEP-81
1500
--------------
7876
ADAMS
12-JAN-83
1100
--------------
7900
JAMES
03-DEC-81
950
--------------
7902
FORD
03-DEC-81
3000
--------------
7934
MILLER
23-JAN-82
1300
--------------

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> 

Previous Topic: ORA-28546: connection initialization failed, probable Net8 admin error
Next Topic: UTL_FILE and csv [merged]
Goto Forum:
  


Current Time: Sun Dec 11 08:12:13 CST 2016

Total time taken to generate the page: 0.12754 seconds