Home » RDBMS Server » Server Utilities » execute immediate with Alter user statement
execute immediate with Alter user statement [message #199093] Fri, 20 October 2006 02:19 Go to next message
h_jitendras
Messages: 36
Registered: October 2006
Member
How do I alter the user profile using Execute Immediate statement?
The Alter Statement would be like(in a loop):
execute immediate 'ALTER USERNAME' ||<value retrieved from a table> ||'PASSWORD EXPIRE'
I guess in this we have to use a variable without which Dyn.SQL won't work.

Kindly help me with the same.
Re: execute immediate with Alter user statement [message #199116 is a reply to message #199093] Fri, 20 October 2006 04:51 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
You only altering the user. Not the profile.
scott@9i > declare
  2  cursor c1 is select username from dba_users where username in ('OLD','NEW');
  3  begin
  4  for mag in c1 loop
  5  execute immediate ('alter user '||mag.username||' password expire');
  6  end loop;
  7  end;
  8  /

PL/SQL procedure successfully completed.

scott@9i > connect old/old
ERROR:
ORA-28001: the password has expired


Changing password for old
New password:
Retype new password:
ERROR:
ORA-00988: missing or invalid password(s)


Password unchanged
Warning: You are no longer connected to ORACLE.
Re: execute immediate with Alter user statement [message #199122 is a reply to message #199116] Fri, 20 October 2006 05:32 Go to previous message
h_jitendras
Messages: 36
Registered: October 2006
Member
Thanks for the reply.
I was caught up in the wrong format of the 'execute immediate' statement.

[Updated on: Fri, 20 October 2006 06:48]

Report message to a moderator

Previous Topic: running sql loader from unix shell script
Next Topic: loading data into two tables from single input file
Goto Forum:
  


Current Time: Sat Dec 03 16:24:54 CST 2016

Total time taken to generate the page: 0.07749 seconds