Home » SQL & PL/SQL » SQL & PL/SQL » BULK COLLECT problem (oracle 9i)
BULK COLLECT problem [message #323296] Wed, 28 May 2008 02:06 Go to next message
pointers
Messages: 410
Registered: May 2008
Senior Member
Hi,

I have created a block to display rows from a table using bulk collect. I dint get the proper output.

SQL> desc emp1;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 SAL                                                NUMBER(7,2)
 DEPTNO                                             NUMBER(2)


//this code is not working
<<main>>
declare
cursor emp1_cursor is select sal,deptno from emp1;
type emp1_type is table of emp1%rowtype
index by binary_integer;
v_emp1_type  emp1_type;
begin
open emp1_cursor;
fetch emp1_cursor bulk collect into v_emp1_type.sal,v_emp1_type.deptno;
for a in v_emp1_type.first..v_emp1_type.last loop
dbms_output.put_line(v_emp1_type(a).sal||'  '||v_emp1_type(a).deptno);
end loop;
close emp1_cursor;
end;






In the above code I am able to create two plsql tables and use like
//this one is working
type sall is table of emp1.sal%type;
type  empid is table of emp1.deptno%type;
and can use in the fetch statement 
fetch emp1_cursor bulk collect into mysall,empidd;


(or)

Even this one is working for the same type
fetch emp1_cursor bulk collect into v_emp1_type;


But why the following fetch in the <<main>> block throwing error
fetch emp1_cursor bulk collect into v_emp1_type.sal,v_emp1_type.deptno;


 
ORA-06550: line 8, column 49:
PLS-00302: component 'SAL' must be declared
ORA-06550: line 8, column 1:
PL/SQL: SQL Statement ignored


Cant we use typename.columnname (e.g:- v_emp1_type.deptno)
But,I declared a record

a emp1%rowtype


and tried like
fetch emp1_cursor into a.sal,a.deptno; 

here i am able to define record.columnnname (e.g:- a.sal)


Please advice.

Regards,
Pointers.


Re: BULK COLLECT problem [message #323310 is a reply to message #323296] Wed, 28 May 2008 02:54 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
sal is not a component of emp1_type, it is a component of an element of emp1_type.

Regards
Michel
Re: BULK COLLECT problem [message #323314 is a reply to message #323310] Wed, 28 May 2008 03:09 Go to previous messageGo to next message
pointers
Messages: 410
Registered: May 2008
Senior Member
Can you please explain it elaborately. I have not undersood.
What is element over here..?

Regards,
Pointers.
Re: BULK COLLECT problem [message #323342 is a reply to message #323314] Wed, 28 May 2008 04:36 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Say you have an apartment type containing kitchen, living-room, bedroom...
Then you have a building which is a collection (table) of apartment.

Now what do you think if I say: "enter the building kitchen"?

Regards
Michel
Re: BULK COLLECT problem [message #323376 is a reply to message #323342] Wed, 28 May 2008 06:00 Go to previous messageGo to next message
pointers
Messages: 410
Registered: May 2008
Senior Member
I got what you are saying....No confusion at all....
Thanks a lot Michel Cadot.

Regards,
Pointers
Re: BULK COLLECT problem [message #323409 is a reply to message #323376] Wed, 28 May 2008 07:31 Go to previous messageGo to next message
jramya
Messages: 42
Registered: April 2008
Member
Hi,
I am not getting it,can you pls share it if you can
Thanks
Re: BULK COLLECT problem [message #323425 is a reply to message #323409] Wed, 28 May 2008 08:06 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What don't you get, can you explain?

Regards
Michel
Re: BULK COLLECT problem [message #323436 is a reply to message #323425] Wed, 28 May 2008 08:29 Go to previous messageGo to next message
jramya
Messages: 42
Registered: April 2008
Member
Oracle 8i
I tried the same with my local table.I have used cursor,plsql tbl but it not works in this case.
<Code>
Declare
cursor c_rfatrval is select * from test1;
type t_rfatrval is table of test1%rowtype index by binary_integer;
v_t t_rfatrval;
Begin
OPEN c_rfatrval;
FETCH c_rfatrval bulk collect into v_t;
for a in v_t.first..v_t.last loop
dbms_output.put_line(v_t(a).typecode||' '||v_t(a).value);
end loop;
end;
<Code>

Its getting me the following error

ORA-06550: line 7, column 36:
PLS-00597: expression 'V_T' in the INTO list is of wrong type
ORA-06550: line 7, column 1:
PL/SQL: SQL Statement ignored

Hope any clue must trigger 'Ding' in my mind.
Thanks
Re: BULK COLLECT problem [message #323447 is a reply to message #323436] Wed, 28 May 2008 08:44 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I didn't check it but something may work in 9i and not in 8i.

Regards
Michel
Re: BULK COLLECT problem [message #323506 is a reply to message #323447] Wed, 28 May 2008 10:56 Go to previous messageGo to next message
jramya
Messages: 42
Registered: April 2008
Member
So,my code will work in 9i +??
Re: BULK COLLECT problem [message #323512 is a reply to message #323506] Wed, 28 May 2008 11:27 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I don't know I don't have your table and your code is not formatted, so I can't read it.

Regards
Michel
Re: BULK COLLECT problem [message #323513 is a reply to message #323506] Wed, 28 May 2008 11:30 Go to previous messageGo to next message
BlackSwan
Messages: 25040
Registered: January 2009
Location: SoCal
Senior Member
jramya wrote on Wed, 28 May 2008 08:56
So,my code will work in 9i +??


What is preventing you from testing your code on 9i?
Re: BULK COLLECT problem [message #323628 is a reply to message #323513] Thu, 29 May 2008 01:08 Go to previous messageGo to next message
pointers
Messages: 410
Registered: May 2008
Senior Member
Hi ramya,
I got the same problem.....But your code is right and works in 9i.
I tried in 8i and got error and later in 9i it was resolved.
If you have any doubts post here..
Regards,
Pointers
Re: BULK COLLECT problem [message #323679 is a reply to message #323628] Thu, 29 May 2008 03:53 Go to previous message
jramya
Messages: 42
Registered: April 2008
Member

@ Pointers Thank you very much.
@anadacent I am going to upgrade system later this yr,so was curious to see will that work,thats it.
Regards
Previous Topic: Problem in converting Month to month numbers?
Next Topic: Reg: Materialized view logs
Goto Forum:
  


Current Time: Mon Dec 05 23:59:15 CST 2016

Total time taken to generate the page: 0.09745 seconds