Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2003
    Location
    down on the cube farm, left then right then another left
    Posts
    467

    Unanswered: "Alter User" command in PL/SQL block

    SETUP: Forms 9i, 9iDB

    What do I need to do to execute an "ALTER USER" command in a PL/SQL block? When I submit

    BEGIN
    ALTER USER :connect_screen.id
    IDENTIFIED BY :new_password.password;

    END;

    I get the boiler plate error:
    PLS-00103: Encountered the symbol "ALTER" when expecting one of
    the following:
    begin case declare exit for goto if loop mod null pragma
    raise return select update etc.

    Thanks
    NOTE: Please disregard the label "Senior Member".

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    At a minimum you will need the ALTER USER system privilege granted explicitly to the owner of the PL/SQL code (& not done via a ROLE).
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Nov 2003
    Location
    down on the cube farm, left then right then another left
    Posts
    467
    Not just to compile would I? (

    I do have the permission regardless).
    NOTE: Please disregard the label "Senior Member".

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    You can't perform DDL directly from PL/SQL, but you can use dynamic SQL:

    BEGIN
    EXECUTE IMMEDIATE 'ALTER USER ' || :connect_screen.id
    ' IDENTIFIED BY ' || :new_password.password;
    END;

  5. #5
    Join Date
    Nov 2003
    Location
    down on the cube farm, left then right then another left
    Posts
    467
    Thanks.

    I had just discovered that myself.
    NOTE: Please disregard the label "Senior Member".

Posting Permissions

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