Dynamic SQL problem

[Quoted] Hi all,

I am trying to build a procedure which can be used to change the password of a user. To verify that a user can only change his own password I'm using a connect-statement in a DBMS_SQL statemant. However [Quoted] this results in a ORA-0900, invalid statement, error. Does anybody have [Quoted] any idea how this can be solved?

t.i.a.

B.J. Meinders
Oracle DBA
ASR-ICT [Quoted] Source Code:

Create or replace procedure

CHANGE_PASSWORD ( OldPass     IN VARCHAR2,
                  NewPass     IN VARCHAR2,
                  NewPass_Ver IN VARCHAR2 )

• Declarations
  --
  Cursor_Num INTEGER; Length_Passw INTEGER; Cmd_String VARCHAR2(100); Cursor USERNAME_CURSOR is select User from DUAL; UserNm VARCHAR2(32);
  --
  Cursor DATABASE_CURSOR is select name from v$database; Dbasenm VARCHAR2(4);
  --
  Begin
  --
  
• Verify new password
  --
  If NewPass <> NewPass_Ver then DBMS_OUTPUT.PUT_LINE('Password verification error, password not changed'); Else Begin
  --
  
• Get username
  --
  open USERNAME_CURSOR; fetch USERNAME_CURSOR into UserNm; close USERNAME_CURSOR; Exception When others then Raise_Application_Error(-20001,'Error while fetching username'); End;
  --
  Begin
  --
  
• Get databasename
  --
  open DATABASE_CURSOR; [Quoted] fetch DATABASE_CURSOR into DbaseNm; close DATABASE_CURSOR; Exception When others then Raise_Application_Error(-20002,'Error while fetching databasename'); End;
  --
  Begin
  --
  
• Check old username / password
  --
  Cmd_String := 'connect '||UserNm||'/'||OldPass; DBMS_OUTPUT.PUT_LINE(cmd_string); Cursor_Num := DBMS_SQL.OPEN_CURSOR; DBMS_SQL.PARSE(Cursor_Num, Cmd_String, DBMS_SQL.NATIVE); DBMS_SQL.CLOSE_CURSOR(Cursor_Num); Exception When others then DBMS_OUTPUT.PUT_LINE(sqlerrm); Raise_Application_error(-20000,'Error during login'); End;
  --
  
• Change password
  --
  Begin Cmd_String:= 'alter user '||UserNm||' identified by '||NewPass ; Cursor_Num := DBMS_SQL.OPEN_CURSOR; If Cursor_Num is null then null; Else DBMS_SQL.PARSE(Cursor_Num, Cmd_String, DBMS_SQL.Native); DBMS_SQL.CLOSE_CURSOR(Cursor_Num); End If; Exception When others then Raise_Application_Error(-20003,'Error during changing password'); End; End If; Exception When others then DBMS_OUTPUT.PUT_LINE(sqlerrm); Raise_Application_Error(-20004,'Unhandled error'); End;

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Wed May 17 2000 - 00:00:00 CEST