Skip to Main Content
  • Questions
  • Check if a pasword parameter is passed to an sql script and process based on that

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, John.

Asked: December 12, 2024 - 3:36 pm UTC

Last updated: December 19, 2024 - 7:00 am UTC

Version: 19c

Viewed 1000+ times

You Asked

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.

and Connor said...

"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