Home » SQL & PL/SQL » SQL & PL/SQL » bulk collect problem
bulk collect problem [message #314027] Tue, 15 April 2008 10:26 Go to next message
soujanya_srk
Messages: 111
Registered: November 2006
Location: HYDERABAD
Senior Member
create table emp1(eno number,ename varchar2(20),sal number);
/
create table t2 as select * from emp1 where 1=0;
/
insert into emp1 values(1,'one',80);
/
insert into emp1 values(2,'two',40);
/;
insert into emp1 values(3,'ddd',20);
/



my aim is to populate table t2 using bulk collect, and forall

SQL> declare
  2    type emptype is table of emp1%rowtype;
  3    v_emptype emptype;
  4   begin
  5   select * bulk collect into v_emptype from emp1;
  6           forall i in v_emptype.first..v_emptype.last
  7            dbms_output.put_line(v_emptype(i));
  8    insert into t2 values(v_emptype(i));
  9  end;
 10  /
          dbms_output.put_line(v_emptype(i));
          *
ERROR at line 7:
ORA-06550: line 7, column 11:
PLS-00103: Encountered the symbol "DBMS_OUTPUT" when expecting one of the
following:
. ( * @ % & - + / at mod remainder rem select update with
<an exponent (**)> delete insert || execute multiset save
merge
The symbol "." was substituted for "DBMS_OUTPUT" to continue.
ORA-06550: line 7, column 45:
PLS-00103: Encountered the symbol ";" when expecting one of the following:
. ( * % & - + / at mod remainder rem select update with
<an exponent (**)> delete insert || execute multiset save
merge
ORA-06550: line 9, column 4:
PLS-00103: Encountered the symbol "end-of-file" when expecting one of the
following:
end not pragma final instantiable order overriding static
member constructor map



whats going on here?
Re: bulk collect problem [message #314029 is a reply to message #314027] Tue, 15 April 2008 10:28 Go to previous messageGo to next message
BlackSwan
Messages: 25037
Registered: January 2009
Location: SoCal
Senior Member
>whats going on here?
DBMS_OUTPUT is only valid within PL/SQL block.
Re: bulk collect problem [message #314031 is a reply to message #314027] Tue, 15 April 2008 10:35 Go to previous messageGo to next message
soujanya_srk
Messages: 111
Registered: November 2006
Location: HYDERABAD
Senior Member
SQL> declare
  2    type emptype is table of emp1%rowtype;
  3    v_emptype emptype;
  4   begin
  5   select * bulk collect into v_emptype from emp1;
  6           forall i in v_emptype.first..v_emptype.last
  7            -- dbms_output.put_line(v_emptype(i));
  8    insert into t2 values(v_emptype(i));
  9  end;
 10  /
  insert into t2 values(v_emptype(i));
              *
ERROR at line 8:
ORA-06550: line 8, column 15:
PL/SQL: ORA-00947: not enough values
ORA-06550: line 8, column 3:
PL/SQL: SQL Statement ignored


i expected the above to work, so should i use the last and first
properties of a plsql table to insert into my target table?

i mean, looping through the plsql table using first and last
attributes?
Re: bulk collect problem [message #314032 is a reply to message #314027] Tue, 15 April 2008 10:42 Go to previous messageGo to next message
soujanya_srk
Messages: 111
Registered: November 2006
Location: HYDERABAD
Senior Member
declare
  type emptype is table of emp1%rowtype;
  v_emptype emptype;
 begin
 select * bulk collect into v_emptype from emp1;
         forall i in v_emptype.count
          -- dbms_output.put_line(v_emptype(i));
	 insert into t2 values(v_emptype(i));
end;
/

  insert into t2 values(v_emptype(i));
  *
ERROR at line 8:
ORA-06550: line 8, column 3:
PLS-00103: Encountered the symbol "INSERT" when expecting one of the following:
. ( * @ % & - + / at mod remainder rem .. <an exponent (**)>
|| multiset
ORA-06550: line 9, column 1:
PLS-00103: Encountered the symbol "END"



oh god !
Re: bulk collect problem [message #314033 is a reply to message #314027] Tue, 15 April 2008 10:47 Go to previous messageGo to next message
BlackSwan
Messages: 25037
Registered: January 2009
Location: SoCal
Senior Member
While some folks can learn coding by trial & error, others benefit from Reading The Fine Manual & following the examples.
http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/toc.htm
Re: bulk collect problem [message #314128 is a reply to message #314032] Tue, 15 April 2008 17:02 Go to previous message
Barbara Boehmer
Messages: 8631
Registered: November 2002
Location: California, USA
Senior Member
When inserting a whole row of columns from a collection, without specifying each column name, you don't use the parentheses for the values clause:

SCOTT@orcl_11g> create table emp1(eno number,ename varchar2(20),sal number)
  2  /

Table created.

SCOTT@orcl_11g> create table t2 as select * from emp1 where 1=0
  2  /

Table created.

SCOTT@orcl_11g> insert into emp1 values(1,'one',80)
  2  /

1 row created.

SCOTT@orcl_11g> insert into emp1 values(2,'two',40)
  2  /

1 row created.

SCOTT@orcl_11g> insert into emp1 values(3,'ddd',20)
  2  /

1 row created.

SCOTT@orcl_11g> declare
  2    type emptype is table of emp1%rowtype;
  3    v_emptype emptype;
  4  begin
  5    select * bulk collect into v_emptype from emp1;
  6    forall i in 1 .. v_emptype.count
  7  	 insert into t2 values v_emptype(i);
  8  end;
  9  /

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> select * from t2
  2  /

       ENO ENAME                       SAL
---------- -------------------- ----------
         1 one                          80
         2 two                          40
         3 ddd                          20

SCOTT@orcl_11g> 

[Updated on: Tue, 15 April 2008 17:03]

Report message to a moderator

Previous Topic: Select Previous Record!?
Next Topic: Purging
Goto Forum:
  


Current Time: Mon Dec 05 09:10:30 CST 2016

Total time taken to generate the page: 0.05059 seconds