pl/sql passing exception to a variable

Question

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

With an EXCEPTION block, within your Pl/SQL Code. That's a way for you to raise specific exceptions, based on some logic within your code. If you put your code here, it might have EXCEPTION WHEN OTHERS THEN .... exception block, but for those specific ones, you need to declare a custom_exception EXCEPTION; and then if a certain condition is met, to RAISE custom_exception;. Finally, in the EXCEPTION block, you would have :EXCEPTION WHEN custom_exception then raise_application_error(-20001, 'You custom message goes here');. I think I exhausted it .. — g00dy
– g00dy, Commented Jul 31, 2017 at 11:01
@Rene - because I need to see a code sample, before deciding that this is indeed an answer. That's why I asked for code samples, to see if that's the thing that's missing. Either that, or this error should be passed to the OUT variable ... without code I can't give a relevant answer, I just suppose that's what's missing. — g00dy
– g00dy, Commented Jul 31, 2017 at 11:10
@Rene The point is I don't want to create "My custom message" but copy into variable exact message generated by password verify function. — user3863616
– user3863616, Commented Jul 31, 2017 at 11:34

Kaushik Nayak · Accepted Answer · 2017-07-31 12:18:48Z

1

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

answered Jul 31, 2017 at 12:18

Kaushik Nayak

32k6 gold badges36 silver badges54 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

sers · Accepted Answer · 2017-07-31 12:07:51Z

0

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

answered Jul 31, 2017 at 12:07

sers

3,7794 gold badges24 silver badges30 bronze badges

Collectives™ on Stack Overflow

pl/sql passing exception to a variable

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related