CODEWITHSUNDEEP
oracle-databaseplsql
user3863616
user3863616

Reputation: 185

pl/sql passing exception to a variable

I have a procedure which using execute immediate changes a user password. If password is correct it passes a result (new password with some additional text) to OUT parameter .
The point is how to pass an error (like below) which is a result of password verify function to OUT parameter:

ORA-28003: password verification for the specified password failed
ORA-20004: Password should contain at least one digit, one character and one
punctuation

would you give me some hint/example ?

Below a code of my procedure:

CREATE OR REPLACE procedure SYS.SYSChangePassword(
  pUsername in varchar2, 
  pNewPassword in varchar2,
  pResult out varchar2(300))  as

begin
    execute immediate 'alter user '||pUsername||' identified by "' || pNewPassword || '";';
    pResult := 'New password for '|| pUsername||' has been set:  '|| pNewPassword ;
end;
/

Upvotes: 0

Views: 2721

Answers (2)

Kaushik Nayak
Kaushik Nayak

Reputation: 31726

You cannot specify size for an out variable varchar2(300) during procedure compilation. 

CREATE OR REPLACE procedure SYSChangePassword(
  pUsername in varchar2, 
  pNewPassword in varchar2,
  pResult out varchar2 )  as

begin
    execute immediate 'alter user '||pUsername||' identified by "' || pNewPassword || '";';
    pResult := 'New password for '|| pUsername||' has been set:  ' || pNewPassword ;

    EXCEPTION
    WHEN OTHERS THEN
    pResult := SQLERRM;
end;
/

Now, You can call the procedure and display the error message.

DECLARE
vResult varchar2(400);
BEGIN
SYSChangePassword (pUsername=>'HR',pNewPassword=> 'hr',pResult=>vResult );

DBMS_OUTPUT.PUT_LINE(vResult);
END;
/

Upvotes: 1

sers
sers

Reputation: 3689

Somehing like:

CREATE OR REPLACE procedure SYS.SYSChangePassword(
  pUsername in varchar2, 
  pNewPassword in varchar2,
  pResult out varchar2(300))  as

begin
  execute immediate 'alter user '||pUsername||' identified by "' || pNewPassword || '";';
  pResult := 'New password for '|| pUsername||' has been set:  '|| pNewPassword ;
exception
  WHEN OTHERS THEN
    pResult := SQLERRM;
end;
/

Upvotes: 0

Related Questions