I am trying to create an sql script that creates a user and if a password is supplied as a parameter to use it and if no parameter is passed have the sql prompt for the password.
This is what i tried but I am obviously a bit off since it asks for the Value of &1 immediately if it is not passed.
If I do pass it a password it gives errors and then prompts for Password and creates user using that password.
Create_TEST_User.sql
define Password = '&1'
SET VERIFY OFF;
if &Password is NULL
THEN
ACCEPT Password PROMPT 'Enter value for Password: ' HIDE
end if;
CREATE USER "TEST" IDENTIFIED BY "&Password"
DEFAULT TABLESPACE "USERS"
TEMPORARY TABLESPACE "TEMP";
GRANT "CONNECT" TO "TEST";
@Create_TEST_User.sql testpassword-1234
Error starting at line : 3 File @ /opt/oracle/sql/Create_TEST_User.sql
In command -
if &Password is NULL
Error report -
Unknown Command
Error starting at line : 4 File @ /opt/oracle/sql/Create_TEST_User.sql
In command -
THEN
Error report -
Unknown Command
Enter value for Password: ********
Error starting at line : 6 File @ /opt/oracle/sql/Create_TEST_User.sql
In command -
end if
Error report -
Unknown Command
User "TEST" created.
"IF" is a PLSQL command, not a SQL or SQL Plus command. so its going to give an error.
Something like this should work
SQL> @c:\tmp\demopass
pass? mypassword
Password to be used is mypassword
SQL> @c:\tmp\demopass ihaveapassword
Password to be used is ihaveapassword
the script for demopass.sql is
set termout off
set ver off
undefine x
undefine y
column p1 new_value 1
select null as p1 from dual where 1 = 2;
col x new_value y
select nvl('&1', 'default') x from dual;
set pages 0
spool c:\tmp\passprompt.sql
select
case
when '&&y' = 'default' then 'accept password prompt ''pass? '''
else
'define password = '||chr(38)||chr(38)||'y'
end
from dual;
spool off
set termout on
@c:\tmp\passprompt.sql
pro Password to be used is &&password