Home » SQL & PL/SQL » SQL & PL/SQL » using bulk collect with select
using bulk collect with select [message #254854] Sun, 29 July 2007 10:55 Go to next message
rolex.mp
Messages: 161
Registered: February 2007
Senior Member
i am working on oracle 10g release 2 .

My requirement is like this


  1  declare
  2     type id_type is table of fnd_menus.menu_id%type;
  3     id_t id_type;
  4     cursor cur_menu is select menu_name from menu;
  5     type name_type is table of menu.menu_name%type;
  6     name_t name_type;
  7  begin
  8     open cur_menu;
  9     fetch cur_menu bulk collect into name_t;
 10     forall i in name_t.first..name_t.last
 11             select menu_id into id_t(i) from fnd_menus where menu_name = name_t(i);
 12* end;
SQL> /
                select menu_id into id_t(i) from fnd_menus where menu_name = name_t(i);
                                    *
ERROR at line 11:
ORA-06550: line 11, column 23:
PLS-00437: FORALL bulk index cannot be used in INTO clause
ORA-06550: line 11, column 31:
PL/SQL: ORA-00904: : invalid identifier
ORA-06550: line 11, column 3:
PL/SQL: SQL Statement ignored




So how i can bulk select into a table the rows that satisfy a particular condition ?
Re: using bulk collect with select [message #254856 is a reply to message #254854] Sun, 29 July 2007 11:11 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can't use forall.
There is no FORALL for SELECT as I already told you in http://www.orafaq.com/forum/mv/msg/85581/251629/102589/#msg_251629 this is only for INSERT, UPDATE or DELETE (please bookmark this).

Just use FOR:
declare
  type id_type is table of fnd_menus.menu_id%type;
  id_t id_type;
  cursor cur_menu is select menu_name from menu;
  type name_type is table of menu.menu_name%type;
  name_t name_type;
begin
  open cur_menu;
  fetch cur_menu bulk collect into name_t;
  for i in name_t.first..name_t.last loop
    select menu_id bulk collect into id_t from fnd_menus where menu_name = name_t(i);
  end loop;
end;
/

Of course you can do it in one shot (without loop) and of course you can also do it in pure SQL.

Regards
Michel
Re: using bulk collect with select [message #254858 is a reply to message #254854] Sun, 29 July 2007 11:28 Go to previous messageGo to next message
rolex.mp
Messages: 161
Registered: February 2007
Senior Member
If I am not going to use the loop then should I use table function to achieve the same ?

I tried something like this


 1  declare
  2     type id_type is table of fnd_menus.menu_id%type;
  3     id_t id_type;
  4     cursor cur_menu is select menu_name from menu;
  5     type name_type is table of menu.menu_name%type;
  6     name_t name_type;
  7  begin
  8     open cur_menu;
  9     fetch cur_menu bulk collect into name_t;
 10     select menu_id bulk collect into id_t from fnd_menus where menu_name in 
(select value(e) from table(cast(select * from name_t) as name_type) e);
 11* end;
SQL> /
        select menu_id bulk collect into id_t from fnd_menus where menu_name in 
(select value(e) from table(cast(select * from name_t) as name_type) e);
                            *
ERROR at line 10:
ORA-06550: line 10, column 107:
PL/SQL: ORA-00936: missing expression
ORA-06550: line 10, column 2:
PL/SQL: SQL Statement ignored


Let me know how to proceed

[Updated on: Sun, 29 July 2007 11:36] by Moderator

Report message to a moderator

Re: using bulk collect with select [message #254859 is a reply to message #254858] Sun, 29 July 2007 11:39 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ Please keep the lines in less then 80 characters.

Just use
select menu_id from fnd_menus where menu_name in (select menu_name from menu);

Regards
Michel
Re: using bulk collect with select [message #254860 is a reply to message #254854] Sun, 29 July 2007 11:51 Go to previous messageGo to next message
rolex.mp
Messages: 161
Registered: February 2007
Senior Member
I have to do it in pl/sql thats why I am getting more confused doing it with minimum context switch since I am coding some transformation logic later in the same procedure .
Re: using bulk collect with select [message #254861 is a reply to message #254860] Sun, 29 July 2007 12:17 Go to previous message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
just add a bulk collect to the query.

Regards
Michel
Previous Topic: Caching SQL sattements
Next Topic: equivalent to rank in 7.3
Goto Forum:
  


Current Time: Fri Dec 02 20:49:15 CST 2016

Total time taken to generate the page: 0.11776 seconds