2014-01-22 | Leave a comment While working heavily on script automation I came across the issue of having an SQLPlus script with optional parameter. Oracle does not allow that by default, and one needs to use a dirty trick to make it work. Vladimir made a great article on how to achieve that. Thank you Vlad, this is really helpful. Vladimir’s Diary: On SQL*Plus Defines. I’ll just add a use case, as there is not much more to add. I have a script that i’d like to run on the connected user’s credentials and by default in the connected users schema. I’d also like it to accept a user’s schema name, to execute in as a parameter. To achieve this i’ll use the Vladimir’s solution SET ECHO OFF SET FEEDBACK OFF SET HEADING OFF SET PAGESIZE 0 SET VERIFY OFF COLUMN 1 NEW_VALUE 1 SELECT '' "1" FROM DUAL WHERE ROWNUM = 0; REM Just to use more meaningfull variable, i will give it a name DEF WORK_SCHEMA_NAME='&1' BEGIN IF '&&WORK_SCHEMA_NAME' IS NOT NULL THEN EXECUTE IMMEDIATE 'ALTER SESSION SET CURRENT_SCHEMA=&&WORK_SCHEMA_NAME'; END IF; END; / SELECT 'Now i''m working on schema: ' || SYS_CONTEXT('USERENV','CURRENT_SCHEMA') || ' as user: '|| USER FROM DUAL; PROMPT I can do my processing here