Home » SQL & PL/SQL » SQL & PL/SQL » Problem with FORALL
Problem with FORALL [message #321513] Tue, 20 May 2008 09:47 Go to next message
ora1980
Messages: 251
Registered: May 2008
Senior Member
SQL> create table book(bid number,bname varchar2(20));

Table created.

SQL> insert into book values(1,'book1');

1 row created.

SQL> ed
Wrote file afiedt.buf

  1* insert into book values(2,'book2')
SQL> /

1 row created.

SQL> ed
Wrote file afiedt.buf

  1* insert into book values(3,'book3')
SQL> /

1 row created.

SQL> commit;

Commit complete.

SQL> select * from book;

       BID BNAME
---------- --------------------
         1 book1
         2 book2
         3 book3

create or replace procedure forall_ex is

type bookat is table of book%rowtype index by binary_integer;
booktab bookat;
begin

	forall i in booktab.first..booktab.last
		insert into book values(booktab.bid,booktab.bname);
            
end;
/

Warning: Procedure created with compilation errors.

SQL> sho err
Errors for PROCEDURE FORALL_EX:

LINE/COL ERROR
-------- -----------------------------------------------------------------
8/3      PL/SQL: SQL Statement ignored
8/47     PL/SQL: ORA-00984: column not allowed here
8/47     PLS-00302: component 'BNAME' must be declared
SQL> 



how to user forall properly here?
Re: Problem with FORALL [message #321520 is a reply to message #321513] Tue, 20 May 2008 10:20 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You used SQL*Plus for your statement why don't you use it for your procedure? This will help to know which lines is number 8...

Where is "i" in your insert statement?

Depending on your version, this may or not work. So ALWAYS post your version (4 decimals).

Thanks for having formatted the rest of your post.

Regards
Michel
Re: Problem with FORALL [message #321529 is a reply to message #321513] Tue, 20 May 2008 10:39 Go to previous messageGo to next message
ora1980
Messages: 251
Registered: May 2008
Senior Member

  1  CREATE OR REPLACE PROCEDURE EXAMPLE_FORALL IS
  2  type bookat is table of book%rowtype index by binary_integer;
  3  booktab bookat;
  4  begin
  5     forall i in booktab.first..booktab.last
  6             insert into book values(booktab(i),booktab(i));
  7* end;
SQL> 
SQL> /

Warning: Procedure created with compilation errors.

SQL> SHO ERR
Errors for PROCEDURE EXAMPLE_FORALL:

LINE/COL ERROR
-------- -----------------------------------------------------------------
6/27     PLS-00382: expression is of wrong type
6/38     PLS-00382: expression is of wrong type


i cant think of any other way of populating both the
columns of book table using forall..

SQL> select banner from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

Re: Problem with FORALL [message #321530 is a reply to message #321513] Tue, 20 May 2008 10:42 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
many, many fine coding examples can be found at http://asktom.oracle.com
Re: Problem with FORALL [message #321533 is a reply to message #321529] Tue, 20 May 2008 11:11 Go to previous message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
OK, now where disappear bid and booktab in the same insert?

Regards
Michel
Previous Topic: columns to row
Next Topic: How to put query result into a table
Goto Forum:
  


Current Time: Fri Dec 02 16:21:05 CST 2016

Total time taken to generate the page: 0.14298 seconds