Home » SQL & PL/SQL » SQL & PL/SQL » Getting this ora-06550
icon13.gif  Getting this ora-06550 [message #269343] Fri, 21 September 2007 15:05 Go to next message
adesai
Messages: 11
Registered: September 2007
Junior Member
Can someone help on this cursor issue, not sure where is the promble:

declare cursor nm is
select m.rowid RID, m.bill_to_mailer, m.list_owner, m.ord, n.order_no, m.suf, n.order_suffix, m.list, n.list, m.list_owner, n.mailer MM
from msale_hist m, newmhist n where ( m.BILL_TO_MAILER <> m.LIST_OWNER AND
m.ORD=n.ORDER_NO and m.SUF=n.ORDER_SUFFIX and m.LIST=n.LIST
AND m.list_owner <> 'RODALE' AND ( m.bill_to_mailer != n.mailer));
begin

FOR i in nm LOOP
update msale_hist set bill_to_mailer=i.MM where rowid=i.RID;
commit;
end LOOP;
end;


Error Messages:
PLS-00402: alias required in SELECT list of cursor to avoid duplicate column names
ORA-06550: line 10, column 1:
PL/SQL: Statement ignored

Script Terminated on line 1.
Re: Getting this ora-06550 [message #269344 is a reply to message #269343] Fri, 21 September 2007 15:09 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
> not sure where is the promble:
PLS-00402: alias required in SELECT list of cursor to avoid duplicate column names

Please read & follow posting guidelines as stated in #1 STICKY post located at top of this forum.
Re: Getting this ora-06550 [message #269350 is a reply to message #269344] Fri, 21 September 2007 15:20 Go to previous messageGo to next message
adesai
Messages: 11
Registered: September 2007
Junior Member
Hi,

I am new user at this site and not sure what you have mentioned about STICKY POST.

Thanks,
Re: Getting this ora-06550 [message #269356 is a reply to message #269343] Fri, 21 September 2007 16:08 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
the post in ORANGE/YELLOW at the very top (just above this/your post).

http://www.orafaq.com/forum/t/88153/0/

[Updated on: Fri, 21 September 2007 16:08] by Moderator

Report message to a moderator

Re: Getting this ora-06550 [message #269358 is a reply to message #269350] Fri, 21 September 2007 16:13 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8625
Registered: November 2002
Location: California, USA
Senior Member
You are getting the error because m.list and n.list have the same name, so you need to use aliases to make them different like "m.list as m_list, n.list as n_list".

It might be more efficient to do the whole thing in one update statement instead of looping through a cursor updating one row at a time.

Please read the forum guide, which is the first post stuck to the top of the list of posts so that it stays there, which is also called a sticky. You need to follow the posting guidelines in it, such as providing create table and insert statements for sample data, desired output based on that data, and Oracle version, in order to get help.

[Updated on: Fri, 21 September 2007 16:15]

Report message to a moderator

Re: Getting this ora-06550 [message #269361 is a reply to message #269350] Fri, 21 September 2007 16:21 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8625
Registered: November 2002
Location: California, USA
Senior Member
I just noticed that you have m.list_owner in there twice as well. I am not sure what you intended. There is no point in having the same column in your cursor twice. If the second one was intended to be n.list_owner, then they just need separate aliases.
icon14.gif  Re: Getting this ora-06550 [message #269362 is a reply to message #269358] Fri, 21 September 2007 16:24 Go to previous messageGo to next message
adesai
Messages: 11
Registered: September 2007
Junior Member
Thank you for your resolution..I have removed those columns and it worked out very well. But i have noticed that cursor is too slow. Is it way to speed up. Currently i am updating 200K rows.
Your suggestion on "one update statement instead of looping through a cursor updating one row at a time," i did not understand.
Thanks,
Re: Getting this ora-06550 [message #269400 is a reply to message #269362] Sat, 22 September 2007 01:20 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Instead of:
for rec in (select t2.col2, t1.rowid rid from t1, t2 where ...
loop
  update t1 set t1.col1=rec.col2 where t1.rowid=rec.rid
end loop;

Use, for instance:
update (select t1.col1, t2.col2 from t1,t2 where ...) set col1=col2;

Or:
update t1 set col1 = (select col2 from t2 where ...)
where exist (select null from t2 where ...);

There are many other ways to write it in a single statement.

Regards
Michel
Re: Getting this ora-06550 [message #269470 is a reply to message #269400] Sat, 22 September 2007 12:23 Go to previous messageGo to next message
adesai
Messages: 11
Registered: September 2007
Junior Member
Hello Michel,

Are you suggeting this:

DECLARE
i varchar2;
Begin
FOR i in (select m.rowid RID, m.bill_to_mailer BTM, n.mailer MM
from msale_hist m, newmhist n where ( m.BILL_TO_MAILER <> m.LIST_OWNER AND
m.ORD=n.ORDER_NO and m.SUF=n.ORDER_SUFFIX and m.LIST=n.LIST
AND m.list_owner <> 'RODALE' AND ( m.bill_to_mailer != n.mailer))Wink
LOOP
update msale_hist set i.BTM=i.MM where rowid=i.RID;
commit;
end LOOP;
end;

Let me know if this correct. I will try this on monday. Thanks,
-Ashish
Re: Getting this ora-06550 [message #269477 is a reply to message #269470] Sat, 22 September 2007 13:47 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
No, I am suggesting no declare, no begin, no loop, no PL/SQL, only one single SQL statement.

And I also suggest you read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button.

Regards
Michel
Re: Getting this ora-06550 [message #269788 is a reply to message #269477] Mon, 24 September 2007 10:58 Go to previous messageGo to next message
adesai
Messages: 11
Registered: September 2007
Junior Member
Hello Michel,

I think your suggetion on 'update statement' will work for single row which is fine; but my query, stated in cursor, is returned multiple rows with joins table. I am not sure there is other way round without using cursor.

declare
cursor nm is select n.order_no RID, n.mailer, m.parent_id,n.offer, m.parent_offer, n.pay_to_code, m.MAILER MM, 
m.MAILER_OFFER MOF from newmhist n, mpomo m
where ( (n.MAILER=m.PARENT_ID and n.offer=m.PARENT_OFFER ) and n.pay_to_code <> 'RODALE' );
begin
for i in nm loop
update newmhist set mailer  = i.MM where  order_no = i.RID;
update newmhist set offer = i.MOF where order_no = i.RID;
commit;
end loop;
end;


Thanks,
-Ashish
Re: Getting this ora-06550 [message #269794 is a reply to message #269788] Mon, 24 September 2007 11:27 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It is working fine for any number of rows or any number of columns to set.
Once again you don't need PL/SQL.

Regards
Michel

[Updated on: Mon, 24 September 2007 11:28]

Report message to a moderator

Re: Getting this ora-06550 [message #270370 is a reply to message #269794] Wed, 26 September 2007 12:44 Go to previous messageGo to next message
adesai
Messages: 11
Registered: September 2007
Junior Member
Michel,

I tried to use single statement as you suggested previously, and it not work for multiple rows modification.
update newmhist set (mailer, offer) = (select m.MAILER MM, m.MAILER_OFFER MOF from newmhist n, mpomo m
where ( (n.MAILER=m.PARENT_ID and n.offer=m.PARENT_OFFER ) and n.pay_to_code NOT IN ('RODALE','MERETH') ););



Cursor is too slow for updating > 100K rows.

Thanks,
-Ashish
Re: Getting this ora-06550 [message #270372 is a reply to message #270370] Wed, 26 September 2007 12:51 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You didn't carefully read my example.
I didn't have t1 in in the subquery and there is an "exists" clause.

Regards
Michel
Re: Getting this ora-06550 [message #270374 is a reply to message #269343] Wed, 26 September 2007 12:52 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
>and it not work
My car not work.
Tell me how to make my car work.

Posting complete session including actual error code would be MOST helpful.
Re: Getting this ora-06550 [message #270961 is a reply to message #269343] Fri, 28 September 2007 10:22 Go to previous messageGo to next message
Ericle
Messages: 44
Registered: April 2006
Location: United States of America ...
Member

How about this:

update newmhist nm
   set (mailer, offer) = 
        (select m.MAILER, m.MAILER_OFFER
           from mpomo m
          where nm.MAILER = m.PARENT_ID
            and nm.offer = m.PARENT_OFFER
            and nm.pay_to_code NOT IN ('RODALE','MERETH');
commit;

Re: Getting this ora-06550 [message #270965 is a reply to message #270961] Fri, 28 September 2007 10:29 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Are you sure?
I wonder why I have an "exists" part in my query. And you?

Regards
Michel
Re: Getting this ora-06550 [message #270972 is a reply to message #269343] Fri, 28 September 2007 10:41 Go to previous messageGo to next message
Ericle
Messages: 44
Registered: April 2006
Location: United States of America ...
Member

No, I'm not sure. He hasn't provided many details or any sample data to go on. Feel free to give him the correct solution if you have it since he has at least tried to figure it out himself.
Re: Getting this ora-06550 [message #270974 is a reply to message #270972] Fri, 28 September 2007 10:43 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The example who choose has 2 parts. You took the first part now add the second one. It is (about) the same.

Regards
Michel
icon14.gif  Re: Getting this ora-06550 [message #270976 is a reply to message #270961] Fri, 28 September 2007 10:50 Go to previous messageGo to next message
adesai
Messages: 11
Registered: September 2007
Junior Member
Mechel,

The query that gave me it will work for sigle row and have tried it before. But i have worked out to speed the cursor by creating usefull indexes. One problem is that first time when i run, it did not update the rows but second time it does..why?
Is that any way that i can trace the line by line update rows.
Thank for you help.

Re: Getting this ora-06550 [message #270984 is a reply to message #270976] Fri, 28 September 2007 11:06 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
it did not update the rows but second time it does

I don't trust you. Prove it.
Quote:
Is that any way that i can trace the line by line update rows.

No. Add a new column that you set during the query if you want to make a test.

Regards
Michel
Re: Getting this ora-06550 [message #270995 is a reply to message #270984] Fri, 28 September 2007 12:28 Go to previous messageGo to next message
adesai
Messages: 11
Registered: September 2007
Junior Member
Michel,

Again tahnks; am currently exec your query and if i find any problem, i will update this message. Thanks,
-Ashish
Re: Getting this ora-06550 [message #270997 is a reply to message #270995] Fri, 28 September 2007 12:42 Go to previous messageGo to next message
adesai
Messages: 11
Registered: September 2007
Junior Member
Hello Michel,

Please see the Code and errors:

SET SERVEROUTPUT ON
SET TIMING ON

update temp_newmhist nm
   set (mailer, offer) =
        (select m.MAILER, m.MAILER_OFFER
           from temp_mpomo m
          where nm.MAILER = m.PARENT_ID
            and nm.offer = m.PARENT_OFFER
            and nm.pay_to_code NOT IN ('RODALE','MERETH'));
commit;
/
SET TIMING OFF



Errors:
@aparent_8.sql
(select m.MAILER, m.MAILER_OFFER
*
ERROR at line 3:
ORA-01427: single-row subquery returns more than one row


Elapsed: 00:04:29.09

Commit complete.

Elapsed: 00:00:00.00

Commit complete.

Elapsed: 00:00:00.00
Re: Getting this ora-06550 [message #271000 is a reply to message #270997] Fri, 28 September 2007 12:49 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You have to understand how the query works and not just trying one thing after another.

Regards
Michel
Re: Getting this ora-06550 [message #271160 is a reply to message #269343] Sun, 30 September 2007 04:34 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

What's the result of it?
select m.MAILER, m.MAILER_OFFER
           from temp_mpomo m
          where nm.MAILER = m.PARENT_ID
            and nm.offer = m.PARENT_OFFER
            and nm.pay_to_code NOT IN ('RODALE','MERETH'));

Of course it will return more than one row? How Oracle will know by which one to be updated the row?
Re: Getting this ora-06550 [message #271171 is a reply to message #271160] Sun, 30 September 2007 07:03 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
The result will be an error, since nm is not specified.
Re: Getting this ora-06550 [message #271190 is a reply to message #271171] Sun, 30 September 2007 12:28 Go to previous messageGo to next message
adesai
Messages: 11
Registered: September 2007
Junior Member
Frank,

Thank you. That i have taken care off. That query return 11K rows and when i execute cursor it updated only 9345 rows. This is another issue. Why it did not update 11K rows?
-Ashish
Re: Getting this ora-06550 [message #271191 is a reply to message #271160] Sun, 30 September 2007 12:36 Go to previous message
adesai
Messages: 11
Registered: September 2007
Junior Member
Arju,

It returns 11K rows. Table col does not contain PK key, therefore, rows are duplicted. And cursor updated those rows by useing ROWID for each row that is fetched by cursor, nm.
Thanks,
-Ashish
Previous Topic: sql to return a flag rather than records
Next Topic: how to use remote database data
Goto Forum:
  


Current Time: Sun Dec 04 20:52:29 CST 2016

Total time taken to generate the page: 0.07637 seconds