Hide response

Hi,

I have a small SQL script which I run from a shell script that allows me to change a users' password. The SQL script works fine in that when it promtps me to enter and verify the new password, my keystrokes are not echoed to the screen. However, when the script completes, it reports this:

alter user "USER" identified by 1234test

Here is a portion of this script from the beginning to the execution of the password change:

set heading off;
set feedback off;
set verify off;
set pagesize 0;
set linesize 79;
set echo off;
rem set termout on;

column ord noprint;

ttitle off;

prompt -----------------------------------------;
prompt Oracle Password Change Utility;
prompt -----------------------------------------;
accept new_passwd1 char prompt " Enter user's new password: " hide;
accept new_passwd2 char prompt " Verify user's new password: " hide;
prompt -----------------------------------------;
prompt ;

rem Build the xxx.tmp file ...

spool $HOME/xxx.tmp;

select 'alter user "USER" identified by '||'&&new_passwd1'||';'
from dual
where upper('&&new_passwd1') = upper('&&new_passwd2')
union
select 'Your first and second new password entries did not match!'
from dual
where upper('&&new_passwd1') != upper('&&new_passwd2');

spool off;

Basically, what I'd like to do is suppress/hide that "alter user "USER" identified by 1234test" statement that appears at the completion of the script. Is there a way to do this?

Thanks,

You are creating dynamic sql to change the password.
How do you expect the password to get changed if the command soen't get spool to the screen?

What about creating a procedure to change the password with an execute immediate?

[QUOTE]Originally posted by jhoug
[B]Hi,

I have a small SQL script which I run from a shell script that allows me to change a users' password. The SQL script works fine in that when it promtps me to enter and verify the new password, my keystrokes are not echoed to the screen. However, when the script completes, it reports this:

alter user "USER" identified by 1234test

Here is a portion of this script from the beginning to the execution of the password change:

set heading off;
set feedback off;
set verify off;
set pagesize 0;
set linesize 79;
set echo off;
rem set termout on;

column ord noprint;

ttitle off;

prompt -----------------------------------------;
prompt Oracle Password Change Utility;
prompt -----------------------------------------;
accept new_passwd1 char prompt " Enter user's new password: " hide;
accept new_passwd2 char prompt " Verify user's new password: " hide;
prompt -----------------------------------------;
prompt ;

rem Build the xxx.tmp file ...

spool $HOME/xxx.tmp;
set feedback off
select 'alter user "USER" identified by '||'&&new_passwd1'||';'
from dual
where upper('&&new_passwd1') = upper('&&new_passwd2')
union
select 'Your first and second new password entries did not match!'
from dual
where upper('&&new_passwd1') != upper('&&new_passwd2');

spool off;

Basically, what I'd like to do is suppress/hide that "alter user "USER" identified by 1234test" statement that appears at the completion of the script. Is there a way to do this?

Thanks,


Try the modified script