DBAsupport.com Forums - Powered by vBulletin
Results 1 to 10 of 10

Thread: Procedure to update table

  1. #1
    Join Date
    Oct 2007
    Posts
    37

    Procedure to update table

    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;
    /



    Thanks,

    Lucky

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,334
    so what happens?

  3. #3
    Join Date
    Oct 2007
    Posts
    37

    Update table

    Information from Table_a is not updated to Table_b. I need the information from table_a to update table_b. How could I modify the procedure to do just that?

    Thanks,

    Lucky

  4. #4
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,334
    you still havent said what happens apart from 'it doesnt work' show the data before, you executing the query and the data after

  5. #5
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,555
    Quote Originally Posted by Lucky A
    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.
    Your UPDATE statements are naming just the target table either table_a or table_b... how do you expect data to be moved from one table to the other if you are not selecting from the other table at any time?

    Remember the old saying... "Oracle" is just a commecial name, you have to tell what you want
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  6. #6
    Join Date
    Oct 2007
    Posts
    37

    Procedure to Update table

    "you still havent said what happens apart from 'it doesnt work' show the data before, you executing the query and the data after"


    What happens is that the new user login failed. New users cannot login to the system. They receive a login failure from the system on changing their password. To get around this, I created the stored procedure and hard coded the last login field.

    Regards,

    Lucky

  7. #7
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,334
    again, no errors, no example, no nothing - cant help

  8. #8
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,555
    Quote Originally Posted by Lucky A
    What happens is that the new user login failed. New users cannot login to the system. They receive a login failure from the system on changing their password. To get around this, I created the stored procedure and hard coded the last login field.
    huh?... are you still talking about "updating a table" here? I remember that was your original posting, am I missing something?
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  9. #9
    Join Date
    Oct 2007
    Posts
    37

    Procedure to update table

    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

  10. #10
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,555
    I'm wondering what part of...
    Quote Originally Posted by Lucky A
    ORA-01400: cannot insert NULL into ("MOUNTIN"."table_b"."COLUMN_ID")
    ...is not clear enough.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width