Home » SQL & PL/SQL » SQL & PL/SQL » bulk collect
bulk collect [message #631887] Wed, 21 January 2015 08:19 Go to next message
palpali
Messages: 138
Registered: December 2014
Location: India
Senior Member
Hallo all Smile

I am just trying to execute following code but i am getting error
"ORA-06550"
c
can anyone please help me?

thanking you in advance.
rg,
Declare
    TYPE Emplist IS VARRAY(100) of Number;
    Empids Emplist := Emplist(7369,7521,7566,7654,7698);
    Type Bonlist is Table of emp.sal%type;
    Bonlist_inst Bonlist;
Begin
    Bonlist_inst := Bonlist(1,2,3,4,5);
    
    FORALL i in empids.FIRST..empids.LAST
        update emp
        set comm = 0.1 * sal
        where empno = empids(i)
        returning sal BULK COLLECT INTO Bonlist;
        
    FOR i IN Empids.FIRST..Empids.LAST LOOP
      UPDATE Emp_tab Set comm = 0.1 * sal        
         WHERE Empno = Empids(i)
       RETURNING Sal INTO BONLIST(i);
   END LOOP;
End;
/  
Re: bulk collect [message #631888 is a reply to message #631887] Wed, 21 January 2015 08:27 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
Oracle version? Also please include the error stack. It will have useful details.
Re: bulk collect [message #631889 is a reply to message #631887] Wed, 21 January 2015 08:28 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
never do in PL/SQL that which can be done in plain SQL.

06550, 00000, "line %s, column %s:\n%s"
// *Cause: Usually a PL/SQL compilation error.


which line is in error?
Re: bulk collect [message #631894 is a reply to message #631889] Wed, 21 January 2015 08:43 Go to previous messageGo to next message
palpali
Messages: 138
Registered: December 2014
Location: India
Senior Member
Hallo..
thankx for your prompt Response..
i am using Oracle 11g

error Detail:

ORA-06550: line 13, column 41: PLS-00321: Expression 'BONLIST' is not suitable for the left side of an assignment Statement
ORA-06550: line 13, column 48: PL/SQL: ORA-00904 invalid identifier
ORA-06550: line 10, column 9: PL/SQL: SQL Statement ignored
ORA-06550: line 16, column 14: PL/SQL: ORA-00942: Table or View does not exist
ORA-06550: line 16, column 7: PL/SQL: SQL Statement ignored

regards.


Re: bulk collect [message #631899 is a reply to message #631894] Wed, 21 January 2015 09:05 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
when all else fails Read The Fine Manual

https://docs.oracle.com/database/121/LNPLS/toc.htm
Re: bulk collect [message #631900 is a reply to message #631899] Wed, 21 January 2015 09:11 Go to previous messageGo to next message
_jum
Messages: 577
Registered: February 2008
Senior Member
You can't RETURN INTO Bonlist (TYPE), use Bonlist_inst instead.
Re: bulk collect [message #631903 is a reply to message #631900] Wed, 21 January 2015 09:31 Go to previous messageGo to next message
palpali
Messages: 138
Registered: December 2014
Location: India
Senior Member
hi @_jum
thankx for your Suggestion, now it works.. Smile
found my mistake, that i have choosed the wront type Smile
Re: bulk collect [message #631905 is a reply to message #631903] Wed, 21 January 2015 09:34 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
So please post the corrected code.
Re: bulk collect [message #631913 is a reply to message #631905] Wed, 21 January 2015 13:01 Go to previous messageGo to next message
sanodani
Messages: 98
Registered: October 2014
Member
Here it is.. Smile

Declare
    TYPE Emplist IS VARRAY(100) of Number;
    Empids Emplist := Emplist(7369,7521,7566,7654,7698);
    Type Bonlist is Table of emp.sal%type;
    Bonlist_inst Bonlist;
Begin
    Bonlist_inst := Bonlist(1,2,3,4,5);
    
    FORALL i in empids.FIRST..empids.LAST
        update emp
        set comm = 0.1 * sal
        where empno = empids(i)
        returning sal BULK COLLECT INTO Bonlist_inst;
        
    FOR i IN Empids.FIRST..Empids.LAST LOOP
      UPDATE Emp_tab Set comm = 0.1 * sal        
         WHERE Empno = Empids(i)
       RETURNING Sal INTO BONLIST_inst(i);
   END LOOP;
End;
/  
Re: bulk collect [message #631914 is a reply to message #631913] Wed, 21 January 2015 13:06 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
never do in PL/SQL that which can be done in plain SQL.
update emp
        set comm = 0.1 * sal
        where empno in (7369,7521,7566,7654,7698);
update emp_tab
        set comm = 0.1 * sal
        where empno in (7369,7521,7566,7654,7698);


why are you returning values & then doing nothing with these values?
BTW - Be sure to COMMIT is you wish to retain the new values.
Re: bulk collect [message #631974 is a reply to message #631914] Thu, 22 January 2015 09:11 Go to previous message
palpali
Messages: 138
Registered: December 2014
Location: India
Senior Member
Hallo @Blackswan

Thankyou very much for your Suggestion, I am just trying to use Bulk COllect method, in other word to say, I am just practicing to develop my knowledge Smile but i will consider your Suggestion in real life definitely. Smile
and hope for the further guidance as well.
regards
Previous Topic: Distributing a total amount (payment) according to individual records (requests)
Next Topic: Need help on a query
Goto Forum:
  


Current Time: Tue Mar 19 05:14:56 CDT 2024