Home » SQL & PL/SQL » SQL & PL/SQL » Updating Accounts and Displaying Changes (PL/SQL)
Updating Accounts and Displaying Changes [message #563418] Mon, 13 August 2012 11:06 Go to next message
jesfunshinebear
Messages: 2
Registered: August 2012
Junior Member
Hey Guys,

New here, so sorry if I'm breaking any rules.
Please tell me so that I may change my ways Smile

Anyways. to get to the point.

I'm trying to write procedures to make updating account owners and the like easier for a group of DBA's.

What I want to do, is create a procedure that displays the changes live.

e.g. If I changed the owner of 5 users from owner 100 to owner 200 it will display:

User test1 owner changed from 100 to 200
User test2 owner changed from 100 to 200
User test3 owner changed from 100 to 200
User test4 owner changed from 100 to 200
User test5 owner changed from 100 to 200

I can not get a loop to work to save my life.
Here's what I have to update the account so far...

PROCEDURE UPDATE_OWNER (OWNER NUMBER, NEW_OWNER NUMBER) IS
BEGIN

UPDATE ACCOUNT_TRACKING
SET ACCOUNT_OWNER=NEW_OWNER WHERE ACCOUNT_OWNER = OWNER
AND ACCOUNT_TYPE !='P';
DBMS_OUTPUT.PUT_LINE ('Account Owner '||OWNER||' Changed to '||NEW_OWNER);

END UPDATE_OWNER;


Thanks for any help...
Re: Updating Accounts and Displaying Changes [message #563419 is a reply to message #563418] Mon, 13 August 2012 11:07 Go to previous messageGo to next message
BlackSwan
Messages: 22718
Registered: January 2009
Senior Member
Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/
Re: Updating Accounts and Displaying Changes [message #563420 is a reply to message #563419] Mon, 13 August 2012 11:11 Go to previous messageGo to next message
jesfunshinebear
Messages: 2
Registered: August 2012
Junior Member
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production

I believe that's the only information I left out.
I do not exactly have an error to show you, I simply do not have a guess as to what code I need to perform the task at hand.
Re: Updating Accounts and Displaying Changes [message #563422 is a reply to message #563418] Mon, 13 August 2012 11:39 Go to previous messageGo to next message
Michel Cadot
Messages: 58938
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It is not clear what you have and what you want from it and above all what are the columns of your table.

To answer to your question "I believe that's the only information I left out."

Post a working Test case: create table and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Please read How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.

Regards
Michel
Re: Updating Accounts and Displaying Changes [message #563428 is a reply to message #563422] Mon, 13 August 2012 14:20 Go to previous message
Littlefoot
Messages: 19525
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Jes, you wrote a procedure and now what? Does it do what you wanted it to? I suppose not (otherwise, you won't post a question here). The procedure, when executed, displays a single "message" (DBMS_OUTPUT.PUT_LINE). If you wanted to update multiple records at once, check whether UPDATE statement's WHERE clause can be set in order to make that possible. Generally speaking, you should do in SQL everything you can and switch to PL/SQL only if you have to.

Because, if you wanted to update multiple records - one by one - you'd have to use a loop. Some people call it "row by row, slow by slow" as it *would* be slower than pure SQL (most probably you wouldn't even notice the difference for tens or hundreds of updates, but it might be significant when many records are involved). But, it would make it possible to display a message for every record you update.

As you were already told, a test case would make it simpler for people who would like to assist. It is kind of difficult to imagine what you have and what you'd want to have at the end.
Previous Topic: INSTR
Next Topic: Cursor reffering tablename
Goto Forum:
  


Current Time: Fri Aug 29 19:40:58 CDT 2014

Total time taken to generate the page: 0.14619 seconds