bulk collect problem [message #314027] |
Tue, 15 April 2008 10:26  |
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 #314031 is a reply to message #314027] |
Tue, 15 April 2008 10:35   |
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   |
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 #314128 is a reply to message #314032] |
Tue, 15 April 2008 17:02  |
 |
Barbara Boehmer
Messages: 9104 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
|
|
|