Home » SQL & PL/SQL » SQL & PL/SQL » script -> changing password and checking in all_users
script -> changing password and checking in all_users [message #214302] Mon, 15 January 2007 15:04 Go to next message
kazik
Messages: 13
Registered: July 2006
Location: Poland
Junior Member

hey,

I need to write a script, which changing password for user but I need to check table all_users before I do this and if user is not there I want to write "sorry, there is no such user". so I wrote:

accept us prompt 'User: ';
accept pass prompt 'New pass: ' hide;
declare
i number(1):=0;
begin
select count(*) into i from all_users where username=&us;
if i=0 then
dbms_output.put_line('sorry, there is no such user!');
else
alter user &us identified by &pass;
dbms_output.put_line('Password was changed'||&us);
end if;
end;

but it doesn't work... any ideas?
Re: script -> changing password and checking in all_users [message #214305 is a reply to message #214302] Mon, 15 January 2007 15:20 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>but it doesn't work... any ideas?

My car does not work. Tell me how to fix it.

There are a gazillion of reasons why "it doesn't work", but without any clues I refuse to speculate.
You are NOT charged by the character to post here.
Give us a hand & actually CUT & PASTE what really happens.
otherwise You're On Your Own (YOYO)!

[Updated on: Mon, 15 January 2007 15:28] by Moderator

Report message to a moderator

Re: script -> changing password and checking in all_users [message #214306 is a reply to message #214302] Mon, 15 January 2007 15:38 Go to previous messageGo to next message
kazik
Messages: 13
Registered: July 2006
Location: Poland
Junior Member

it looks like this:

SQL> start test
User: DANIEL
New pass: ******
12 /
stare 4: select count(*) into i from all_users where username=&us;
nowe 4: select count(*) into i from all_users where username=DANIEL;
stare 8: alter user &us identified by &pas;
nowe 8: alter user DANIEL identified by DANIEL;
stare 9: dbms_output.put_line('Password was changed'||&us);
nowe 9: dbms_output.put_line('Password was changed'||DANIEL);
alter user DANIEL identified by DANIEL;
*
BŁĄD w linii 8:
ORA-06550: linia 8, kolumna 1:
PLS-00103: Napotkano symbol "ALTER" gdy oczekiwano jednego z następujących:
begin case declare exit for goto if loop mod null pragma
raise return select update while with <an identifier>
<a double-quoted delimited-identifier> <a bind variable> <<
close current delete fetch lock insert open rollback
savepoint set sql execute commit forall merge
<a single-quoted SQL string> pipe
Re: script -> changing password and checking in all_users [message #214307 is a reply to message #214305] Mon, 15 January 2007 15:39 Go to previous message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
You were close; ALTER command needs to be executed using EXECUTE IMMEDIATE.

Also, take care for the username case - if you leave it in lowercase, query will not return anything because - by default - all_users.username is in capital letters. Therefore, you might perhaps use UPPER function.
Previous Topic: execute procedure
Next Topic: Need a view that returns the most recent dates! Urgent, please help!
Goto Forum:
  


Current Time: Sun Dec 11 08:33:37 CST 2016

Total time taken to generate the page: 0.22249 seconds