From www.orafaq.com - Create an Oracle SQL Script that will accept a password - Edward Stoever.

Hi,
I have to create a script which will ask for a password and validate it and if its correct,
then call some procedures ,otherwise display an error.
How can i prompt for an password using script ?
Is it possible that when the user enters the password ,it should be displayed in encypted form(asterisks) ?
Can you suggest some examples or references on this ?
I am using Oracle 9i.

Thanks and Regards,
Vishal

Create a script called x.sql :
rem x.sql
rem
whenever sqlerror exit rollback;
set echo off
prompt "The value entered for the SYSTEM password will not display."
accept system_password char prompt "Enter the password for SYSTEM: " hide
connect system/&&system_password@test

select dbms_random.string('A',100) from dual;

exit

Here is an example of calling that script from the command line:
D:\temp1>sqlplus /nolog @x

SQL*Plus: Release 9.0.1.3.0 - Production on Thu Oct 20 11:18:47 2005

(c) Copyright 2001 Oracle Corporation.  All rights reserved.

"The value entered for the SYSTEM password will not display."
Enter the password for SYSTEM:
Connected.

DBMS_RANDOM.STRING('A',100)
--------------------------------------------------------------------------------
VesrnNQUVADGmMXOyMhjEoWoQmGiajqussnGFQdLpwUDdYLcbXYSywTlQyaNwGPNsqNsdAZnJCniMhRr
KeAYgJgIqOUCXsYinhwG


Disconnected from Oracle9i Enterprise Edition Release 9.2.0.5.0 - Production
With the Partitioning option
JServer Release 9.2.0.5.0 - Production

D:\temp1>

This should work on any OS.

Edward Stoever
http://www.database-expert.com
White Papers Home