Home » SQL & PL/SQL » SQL & PL/SQL » Update another table (9.2.0.6)
Update another table [message #316350] Thu, 24 April 2008 13:15 Go to next message
Lucky A
Messages: 65
Registered: October 2007
Member
Hi,

I created a procedure which would copy all of the information from table_a to table_b for a new user. This procedure should also copy the information from table_a to table_b after a new user has been created. However I don't know why it does not seem to do so. Below is the procedure. I'll appreciate any input.


CREATE OR REPLACE PROCEDURE newuser (
a_username VARCHAR2
)
IS
BEGIN
UPDATE table_a
SET last_login=TO_DATE('1/1/2006','MM/DD/YYYY')
WHERE username=a_username;
COMMIT;

UPDATE table_b
SET last_login=TO_DATE('1/1/2006','MM/DD/YYYY')
WHERE username=a_username;
commit;
END;
/


Re: Update another table [message #316352 is a reply to message #316350] Thu, 24 April 2008 13:46 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ If you want to do the same thing at the same (transaction) time, then you MUST remove the commit between the 2.

2/ If it is a "newuser" then it is not in the table and update will do nothing

3/ otherwise, what does not work?

4/ Forgot: please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.

Regards
Michel

[Updated on: Thu, 24 April 2008 13:46]

Report message to a moderator

Re: Update another table [message #316516 is a reply to message #316350] Fri, 25 April 2008 08:16 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
Lucky A wrote on Thu, 24 April 2008 14:15
This procedure should also copy the information from table_a to table_b after a new user has been created. However I don't know why it does not seem to do so.



Because your code does not do anything other than update a single column in two different tables to a hardcoded value. How can a last login value be hardcoded?
Re: Update another table [message #316584 is a reply to message #316516] Fri, 25 April 2008 23:54 Go to previous messageGo to next message
Lucky A
Messages: 65
Registered: October 2007
Member
The reason the last login value is hard coded is because new users are not able to login to the system. They receive a login failure from the system on the change password screen. To get around this, I created the stored procedure and hard coded the last login field.
Re: Update another table [message #316585 is a reply to message #316584] Sat, 26 April 2008 00:17 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You don't answer my questions.

Regards
Michel
Re: Update another table [message #316586 is a reply to message #316350] Sat, 26 April 2008 00:21 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
> They receive a login failure from the system on the change password screen.

Which failure? Which system? Which password (Oracle or application one)?
I am not aware about any "change password screen" in the database. Is this some Oracle Applications stuff?

> To get around this, I created the stored procedure

So you have stored procedure. What do you suppose it to do?
It will do nothing until you call it. You will not be able to call it until you to login to DB as user privileged to execute it.
Re: Update another table [message #316843 is a reply to message #316350] Mon, 28 April 2008 09:23 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
Lucky A wrote on Thu, 24 April 2008 14:15

I created a procedure which would copy all of the information from table_a to table_b for a new user. This procedure should also copy the information from table_a to table_b after a new user has been created. However I don't know why it does not seem to do so.



Do you think this procedure does that? Please show us the line or code that does this.
Re: Update another table [message #317918 is a reply to message #316585] Sun, 04 May 2008 23:24 Go to previous messageGo to next message
Lucky A
Messages: 65
Registered: October 2007
Member
Hi,

Below is the error message I am getting when I execute the NewUser procedure.
I need your input to resolve this.


SQL> exec NewUser('jbrown');
BEGIN NewUser('jbrown'); END;

*
ORA-01400: cannot insert NULL into ("MOUNTIN"."table_b"."COLUMN_ID")
ORA-06512: at "JBROWN.NEWUSER:, line 10ORA-06512: at line 1


Please I need your input to resolve this.


Lucky
Re: Update another table [message #317922 is a reply to message #317918] Mon, 05 May 2008 00:00 Go to previous messageGo to next message
Littlefoot
Messages: 20888
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Oracle told you everything you need to know:
Quote:
ORA-01400: cannot insert NULL into ("MOUNTIN"."table_b"."COLUMN_ID")

Now, it is up to you to make sure that this procedure doesn't try to insert NULL into 'column_id' column of the 'table_b' table within 'mountin' schema.
Re: Update another table [message #317926 is a reply to message #317918] Mon, 05 May 2008 00:49 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
In addition, having a table name in lower case is a bad idea.

Regards
Michel
Re: Update another table [message #317929 is a reply to message #317926] Mon, 05 May 2008 00:52 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Michel Cadot wrote on Mon, 05 May 2008 07:49
In addition, having a table name in lower case is a bad idea.

Also, table_b is not really a descriptive name...
Re: Update another table [message #318051 is a reply to message #317929] Mon, 05 May 2008 08:12 Go to previous message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
Nor is COLUMN_ID, but I guess it's fits a pattern.
Previous Topic: single row col value should reflect in multiple rows
Next Topic: select *, "app" as Status from tablename
Goto Forum:
  


Current Time: Fri Dec 02 20:42:54 CST 2016

Total time taken to generate the page: 0.12283 seconds