oracle sqlplus command line question

Question

Is there a way to detect missing command line parameters from the oracle script

for example my oracle script test.sql expects 3 parameters to run. my script is executed from the install program like this sqlplus /NOLOG @test.sql userid pw tablespace When one of the parameters is missing, the sqlplus prompting for the userinput. This cannot be recognized from install program. I need to find the missing param inside the script and exit with error code. Is it feasible ?

Answer 1

I don't think there is a way to do this directly with sqlplus. You can wrap the sqlplus execution in a command/shell script that checks the parameters, then calls sqlplus with the validated parameter list.

Answer 2

Yes, I believe you can with the SQL*Plus command accept, check this link for more info: http://www.dba-oracle.com/concepts/displaying_pl_sql_output.htm

Good luck!

Ricardo.

Answer 3

Wrapping the call to sqlplus in a shell script seems like the easiest way to do this but could you also call the script using:

sqlplus /NOLOG @test.sql <userid> <pw> <tablespace> empty empty empty

and check inside the script if any of the values are equal to "empty"? That way the script won't hang even if no parameters are passed because you'll still have the three filler parameters.

You could check using something like this: (using && instead of & means you will only be prompted for the value once)

var status number;
set verify on
whenever sqlerror exit 1; 
begin
   :status := 0;
   select decode('&&1','empty',1,:status) into :status from dual;
   select decode('&&2','empty',2,:status) into :status from dual;
   select decode('&&3','empty',3,:status) into :status from dual;
   IF :status != 0 THEN
   RAISE_APPLICATION_ERROR (
   -20000+-1*:status,'parameter '|| :status || ' is missing');
   END IF;
end;
/

The return value when the inputs are invalid will be 1. Without knowing what valid values are for the parameters, it's hard to know which one was omitted.

Answer 4

mytext.txt