Home » SQL & PL/SQL » SQL & PL/SQL » PL/SQL collection with bulk collect (oracle 10g)
PL/SQL collection with bulk collect [message #307818] Thu, 20 March 2008 01:27 Go to next message
AnamikaChaudhary
Messages: 33
Registered: December 2007
Location: Mumbai
Member
Hi,
I created this procedure by using BULK COLLECT...selected all the column into proh_details.

create or replace procedure p1_comm
(v_comm_name IN prohibited_commodity.CCOMMODITY_NAME%type,
v_comm_port IN prohibited_commodity. CPROHIBITED_PORT%type,
v_record_found out varchar2)
as
record_found NUMBER:=0;
v_port_name varchar2(30):= 'ALL';
TYPE proh_typ IS TABLE of prohibited_commodity%rowtype index by binary_integer;
proh_details proh_typ;
BEGIN
select *
bulk collect into proh_details
from prohibited_commodity;
for i in proh_details.first.. proh_details.last
loop
if proh_details(i).CPROHIBITED_PORT = v_comm_port
or proh_details(i).CPROHIBITED_PORT = 'ALL'
and proh_details(i).CCOMMODITY_NAME = v_comm_name
then
record_found := 1;
dbms_output.put_line (' EQUAL');
else
dbms_output.put_line ('NOT EQUAL');
end if ;
end loop;
Exception
when no_data_found then null ;
end p1_comm ;

This Procedure gives me proper result but i want to select Two Column name in select statement. , Then how i reference the column & match the criteria ???
(I am having this select statement)
select *
bulk collect into proh_details
from prohibited_commodity;

REQUIRED SELECT STATEMENT:

select a,b
bulk collect into proh_details
from prohibited_commodity;


please help me for this.

Thanks

Re: PL/SQL collection with bulk collect [message #307821 is a reply to message #307818] Thu, 20 March 2008 01:38 Go to previous messageGo to next message
dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai
Senior Member
It would have been better if you had formatted your code.Read the forum guidelines on how to format your code.Now for your problem just declare two variable of that type and use it in the SELECT statement.And one more thing
Quote:
Exception
when no_data_found then null ;

its meaningless, you are catching the EXCEPTION but , giving out no useful information about the exception.


regards,
Re: PL/SQL collection with bulk collect [message #307828 is a reply to message #307818] Thu, 20 March 2008 01:57 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Define a record type with your 2 columns and use a table of this type instead of table of %rowtype.

By the way, formatting your posts has already been asked to you. Why don't you do it?

Regards
Michel

[Updated on: Thu, 20 March 2008 01:59]

Report message to a moderator

Re: PL/SQL collection with bulk collect [message #307834 is a reply to message #307821] Thu, 20 March 2008 02:09 Go to previous messageGo to next message
AnamikaChaudhary
Messages: 33
Registered: December 2007
Location: Mumbai
Member
Hi,

I created this procedure by using BULK COLLECT...selected all the column into proh_details.

create or replace procedure p1_comm
(v_comm_name IN prohibited_commodity.CCOMMODITY_NAME%type,
v_comm_port IN prohibited_commodity. CPROHIBITED_PORT%type,
v_record_found out varchar2)
as
record_found NUMBER:=0;
v_port_name varchar2(30):= 'ALL';
TYPE proh_typ IS TABLE of prohibited_commodity%rowtype index by binary_integer;
proh_details proh_typ;
BEGIN
select *
bulk collect into proh_details
from prohibited_commodity;
for i in proh_details.first.. proh_details.last
loop
if proh_details(i).CPROHIBITED_PORT = v_comm_port
or proh_details(i).CPROHIBITED_PORT = 'ALL'
and proh_details(i).CCOMMODITY_NAME = v_comm_name
then
record_found := 1;
dbms_output.put_line (' EQUAL');
else
dbms_output.put_line ('NOT EQUAL');
end if ;
end loop;
Exception
when no_data_found then null ;
end p1_comm ;


This Procedure gives me proper result but i want to select Two Column name in select statement. , Then how i reference the column & match the criteria ???
(I am having this select statement)
select *
bulk collect into proh_details
from prohibited_commodity;

REQUIRED SELECT STATEMENT:

select a,b
bulk collect into proh_details
from prohibited_commodity;


please help me for this.

Thanks


Re: PL/SQL collection with bulk collect [message #307835 is a reply to message #307828] Thu, 20 March 2008 02:16 Go to previous messageGo to next message
AnamikaChaudhary
Messages: 33
Registered: December 2007
Location: Mumbai
Member
Hi,

I tried this also...Procedure created sucessfully but the IF condition doesn't work.Directly jump to else portion . It shows record_found = 0.

 create or replace procedure P_COMM
       (v_comm_name IN prohibited_commodity.CCOMMODITY_NAME%type,
       v_comm_port IN prohibited_commodity. CPROHIBITED_PORT%type,
       v_record_found out varchar2)
       as
       record_found NUMBER:=0;
       v_port_name  varchar2(30):= 'ALL';
   TYPE t_proh_cname is table of  prohibited_commodity.CCOMMODITY_NAME%type;
   TYPE t_proh_port     is table of  prohibited_commodity.CPROHIBITED_PORT%type;
   l_proh_cname  t_proh_cname ;
   l_proh_port   t_proh_port ;
   BEGIN
   SELECT CCOMMODITY_NAME,CPROHIBITED_PORT bulk collect into l_proh_cname,l_proh_port
   FROM  prohibited_commodity;
   for i in l_proh_cname.first ..l_proh_cname.last loop
      if l_proh_port(i) IN ('v_comm_port','ALL')
         and l_proh_cname(i) = v_comm_name
     then 
    record_found := 1; 
    dbms_output.put_line (' EQUAL');

    else

    dbms_output.put_line ('NOT EQUAL');

    end if ;

    end loop;
    v_record_found := record_found ;

    Exception
    when no_data_found then null ;
    end P_COMM ;


Re: PL/SQL collection with bulk collect [message #307837 is a reply to message #307835] Thu, 20 March 2008 02:24 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Don't put 'v_comm_port' between ' in your test.

Regards
Michel
Re: PL/SQL collection with bulk collect [message #307845 is a reply to message #307835] Thu, 20 March 2008 02:43 Go to previous messageGo to next message
dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai
Senior Member
How about adding a WHERE clause in the SELECT statement and using the PL/SQL table attribute COUNT to find the no of rows.Just any example.
DECLARE
  TYPE l_Name IS TABLE OF emp.eName%TYPE INDEX BY BINARY_INTEGER ;
   TYPE l_sal IS TABLE OF emp.sal%TYPE INDEX BY BINARY_INTEGER ;
   l_Name_Tab  L_NAME;
   l_sal_Tab   L_SAL;
BEGIN
  SELECT eName,
         sal
  BULK COLLECT INTO l_Name_Tab,
         l_sal_Tab
  FROM   emp
  WHERE  deptNo IN (1,
                    2);
  
  IF (l_Name_Tab.COUNT) > 0 THEN
    dbms_Output.Put_Line('equal');
  ELSE
    dbms_Output.Put_Line('not equal');
  END IF;
END;


regards,
Re: PL/SQL collection with bulk collect [message #307935 is a reply to message #307845] Thu, 20 March 2008 07:29 Go to previous messageGo to next message
AnamikaChaudhary
Messages: 33
Registered: December 2007
Location: Mumbai
Member
Hi,

When i an using this code its gives me count 0.

create or replace procedure P_COMM1
        (v_comm_name IN prohibited_commodity.CCOMMODITY_NAME%type,
        v_comm_port IN prohibited_commodity. CPROHIBITED_PORT%type,
        v_record_found out varchar2)
        as
        record_found NUMBER:=0;
        v_port_name  varchar2(30):= 'ALL';
    TYPE t_proh_cname is table of  prohibited_commodity.CCOMMODITY_NAME%type;
    TYPE t_proh_port     is table of  prohibited_commodity.CPROHIBITED_PORT%type;
    l_proh_cname  t_proh_cname ;
    l_proh_port   t_proh_port ;
    BEGIN
    SELECT CCOMMODITY_NAME,CPROHIBITED_PORT bulk collect into l_proh_cname,l_proh_port
    FROM  prohibited_commodity
    WHERE CPROHIBITED_PORT IN ('v_comm_port','ALL')
     and  CCOMMODITY_NAME = ('v_comm_name') ;
       if (l_proh_cname.count) > 0
      then
       record_found := 1;
    else
       record_found := 0;
     end if;
  v_record_found := record_found ;
   Exception
   when no_data_found then null ;
   end P_COMM1 ;



Re: PL/SQL collection with bulk collect [message #307945 is a reply to message #307818] Thu, 20 March 2008 08:19 Go to previous message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
WHERE CPROHIBITED_PORT IN ('v_comm_port','ALL')
  and  CCOMMODITY_NAME = ('v_comm_name') ;

I doubt you have column CCOMMODITY_NAME with value 'v_comm_name' in your table. The same issue is with column CPROHIBITED_PORT and value 'v_comm_port'. Rather use the variable with this name.
Have a look into documentation about difference between string literal and variable in PL/SQL.
Previous Topic: using, in the select-clause, a result calculated in the where-clause
Next Topic: the EXCEPTION didn't work correctly
Goto Forum:
  


Current Time: Sat Dec 03 13:53:11 CST 2016

Total time taken to generate the page: 0.04875 seconds