Index By Table [message #333034] |
Thu, 10 July 2008 06:08  |
ORAGENASHOK
Messages: 240 Registered: June 2006 Location: Chennai
|
Senior Member |
|
|
Can anyone rectify the below error and explain what is the mistake.
SQL> Declare
2 cursor c1 is select * from emp where comm is null;
3 TYPE b_int is TABLE OF c1%rowtype INDEX BY binary_integer;
4 bint b_int;
5 begin
6 open c1;
7 FORALL i IN bint.FIRST..bint.LAST
8 insert into emp_mod values (bint(i));
9 end;
10 /
insert into emp_mod values (bint(i));
*
ERROR at line 8:
ORA-06550: line 8, column 19:
PL/SQL: ORA-00947: not enough values
ORA-06550: line 8, column 7:
PL/SQL: SQL Statement ignored
ORA-06550: line 8, column 7:
PLS-00435: DML statement without BULK In-BIND cannot be used inside FORALL
|
|
|
|
|
Re: Index By Table [message #333042 is a reply to message #333036] |
Thu, 10 July 2008 06:30   |
rajatratewal
Messages: 507 Registered: March 2008 Location: INDIA
|
Senior Member |
|
|
Fields within collections of records may not be referenced within the DML statement. Instead, you can only reference the row in the collection as a whole, whether the fields are collections of scalars or collections of more complex objects.
Try
insert into emp_mod values bint(i)
Regards,
Rajat
[Updated on: Thu, 10 July 2008 06:34] Report message to a moderator
|
|
|
|
|
Re: Index By Table [message #333225 is a reply to message #333042] |
Thu, 10 July 2008 23:00   |
ORAGENASHOK
Messages: 240 Registered: June 2006 Location: Chennai
|
Senior Member |
|
|
rajatratewal
I cant understand below one
Fields within collections of records may not be referenced within the DML statement.
Instead, you can only reference the row in the collection as a whole,
whether the fields are collections of scalars or collections of more complex objects.
can you please explain with some practical example
I will reference the row in the collection as a whole.
[Updated on: Thu, 10 July 2008 23:12] by Moderator Report message to a moderator
|
|
|
Re: Index By Table [message #333226 is a reply to message #333225] |
Thu, 10 July 2008 23:10   |
rajatratewal
Messages: 507 Registered: March 2008 Location: INDIA
|
Senior Member |
|
|
just try the insert statement.I have replaced the parenthesis after values.
instead of
insert into emp_mod values (bint(i));
Try
insert into emp_mod values bint(i);
I said you can't refer values of collections one via one
in FORALL statement but a row.
values(bint(i)) is wrong syntax.
Compiler thinks that you are passing values one via one.
correct syntax is
Regards,
Rajat
|
|
|
Re: Index By Table [message #333228 is a reply to message #333226] |
Thu, 10 July 2008 23:18   |
ORAGENASHOK
Messages: 240 Registered: June 2006 Location: Chennai
|
Senior Member |
|
|
Then i got this below error
1 Declare
2 cursor c1 is select * from emp where comm is null;
3 TYPE b_int is TABLE OF c1%rowtype INDEX BY binary_integer;
4 bint b_int;
5 begin
6 open c1;
7 FORALL i IN bint.FIRST..bint.LAST
8 insert into emp_mod values bint(i);
9* end;
SQL> /
TYPE b_int is TABLE OF c1%rowtype INDEX BY binary_integer;
*
ERROR at line 3:
ORA-06550: line 8, column 34:
PL/SQL: ORA-00906: missing left parenthesis
ORA-06550: line 8, column 7:
PL/SQL: SQL Statement ignored
|
|
|
Re: Index By Table [message #333231 is a reply to message #333228] |
Thu, 10 July 2008 23:25   |
rajatratewal
Messages: 507 Registered: March 2008 Location: INDIA
|
Senior Member |
|
|
Tell me onething
where you have stored data in bint variable.
bint has no Data.
Why cursor is opened and not closed.
Regards,
Rajat
|
|
|
Re: Index By Table [message #333232 is a reply to message #333231] |
Thu, 10 July 2008 23:32   |
rajatratewal
Messages: 507 Registered: March 2008 Location: INDIA
|
Senior Member |
|
|
DECLARE
TYPE b_int IS TABLE OF emp%ROWTYPE INDEX BY BINARY_INTEGER;
bint b_int;
BEGIN
SELECT *
BULK COLLECT INTO bint
FROM emp where comm is null;
FORALL i IN bint.FIRST..bint.LAST
INSERT INTO emp_mod VALUES bint(i);
END;
Happy now.
Regards,
rajat
|
|
|
Re: Index By Table [message #333236 is a reply to message #333232] |
Fri, 11 July 2008 00:00   |
ORAGENASHOK
Messages: 240 Registered: June 2006 Location: Chennai
|
Senior Member |
|
|
It's also shows error
1 DECLARE
2 TYPE b_int IS TABLE OF emp%ROWTYPE INDEX BY BINARY_INTEGER;
3 bint b_int;
4 BEGIN
5 SELECT * BULK COLLECT INTO bint
6 FROM emp where comm is null;
7 FORALL i IN bint.FIRST..bint.LAST
8 INSERT INTO emp_mod VALUES bint(i);
9* END;
SQL> /
TYPE b_int IS TABLE OF emp%ROWTYPE INDEX BY BINARY_INTEGER;
*
ERROR at line 2:
ORA-06550: line 8, column 28:
PL/SQL: ORA-00906: missing left parenthesis
ORA-06550: line 8, column 1:
PL/SQL: SQL Statement ignored
|
|
|
|
Re: Index By Table [message #333710 is a reply to message #333236] |
Mon, 14 July 2008 04:00   |
wangfans
Messages: 7 Registered: July 2008 Location: PRC
|
Junior Member |
|
|
1 create or replace procedure test2
2 as
3 TYPE b_int IS TABLE OF test%ROWTYPE INDEX BY BINARY_INTEGER;
4 bint b_int;
5 BEGIN
6 SELECT * BULK COLLECT INTO bint
7 FROM test where col1 is not null;
8 FORALL i IN bint.FIRST..bint.LAST
9 INSERT INTO test_bk VALUES bint(i);
10* END;
QL> /
rocedure created.
QL> exec test2;
L/SQL procedure successfully completed.
QL> select * from test_bk;
COL1 COL2
--------- --------------------
1 a
3 a
5 a
6 b
7 b
8 c
9 c
19 WANGFAN
22 FF
24 DD
1 a
COL1 COL2
--------- --------------------
3 a
5 a
6 b
7 b
8 c
9 c
19 WANGFAN
22 FF
24 DD
20 rows selected.
[Mod-Edit: Frank added code-tags to improve readability]
[Updated on: Mon, 14 July 2008 04:05] by Moderator Report message to a moderator
|
|
|
Re: Index By Table [message #333716 is a reply to message #333244] |
Mon, 14 July 2008 04:14   |
flyboy
Messages: 1903 Registered: November 2006
|
Senior Member |
|
|
@wangfans
flyboy wrote on Fri, 11 July 2008 07:22 | And your Oracle version (in four decimal places) is?
According to the documentation, this feature shall be available from version 9.2 on.
|
Do you think that ORAGENASHOK has the same (or latter) Oracle version as you?
|
|
|
|
Re: Index By Table [message #334275 is a reply to message #334246] |
Wed, 16 July 2008 01:14  |
 |
Michel Cadot
Messages: 68761 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
So read the other posts BEFORE posting yourself and read the forum guide as it has been requested to you several times.
Regards
Michel
|
|
|