Home » SQL & PL/SQL » SQL & PL/SQL » Index By Table
Index By Table [message #333034] Thu, 10 July 2008 06:08 Go to next message
ORAGENASHOK
Messages: 238
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 #333036 is a reply to message #333034] Thu, 10 July 2008 06:16 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
Is the table structure for emp and emp_mod is same.

i.e Same number of columns and in same sequence as in emp;

Regards,
Rajat
Re: Index By Table [message #333041 is a reply to message #333034] Thu, 10 July 2008 06:29 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Search for the error PLS-00435
Re: Index By Table [message #333042 is a reply to message #333036] Thu, 10 July 2008 06:30 Go to previous messageGo to next message
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 #333223 is a reply to message #333042] Thu, 10 July 2008 22:52 Go to previous messageGo to next message
ORAGENASHOK
Messages: 238
Registered: June 2006
Location: Chennai
Senior Member
Both emp and emp_mod are in same structure.
Re: Index By Table [message #333224 is a reply to message #333223] Thu, 10 July 2008 22:56 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
Have you tried what i have told you.
Try the insert statement.

Regards,
Rajat
Re: Index By Table [message #333225 is a reply to message #333042] Thu, 10 July 2008 23:00 Go to previous messageGo to next message
ORAGENASHOK
Messages: 238
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 Go to previous messageGo to next message
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

values bint(i) 


Regards,
Rajat
Re: Index By Table [message #333228 is a reply to message #333226] Thu, 10 July 2008 23:18 Go to previous messageGo to next message
ORAGENASHOK
Messages: 238
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
ORAGENASHOK
Messages: 238
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 #333244 is a reply to message #333034] Fri, 11 July 2008 00:22 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
> It's also shows error
And your Oracle version (in four decimal places) is?
According to the documentation, this feature shall be available from version 9.2 on:
9.2 documentation: http://download.oracle.com/docs/cd/B10501_01/appdev.920/a96624/05_colls.htm#28087
9.0.1 documentation: http://download.oracle.com/docs/cd/A91202_01/901_doc/appdev.901/a89856/05_colls.htm#28087

Either upgrade to supported Oracle version (10g or 11g; 9.2 is desupported already) or you are out of luck and have to specify each record value explicitely.
Re: Index By Table [message #333710 is a reply to message #333236] Mon, 14 July 2008 04:00 Go to previous messageGo to next message
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 Go to previous messageGo to next message
flyboy
Messages: 1832
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 #334246 is a reply to message #333716] Tue, 15 July 2008 23:51 Go to previous messageGo to next message
wangfans
Messages: 7
Registered: July 2008
Location: PRC
Junior Member

sorry, I have no idea bout this
My oracle version is 11g
Re: Index By Table [message #334275 is a reply to message #334246] Wed, 16 July 2008 01:14 Go to previous message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
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
Previous Topic: Process very slow while retrieving large amount of data.....
Next Topic: Rows Data into Columns
Goto Forum:
  


Current Time: Fri Dec 02 14:06:11 CST 2016

Total time taken to generate the page: 0.08178 seconds