Re: Pass sqlplus Parm thru DOS ?

kb () wrote:
: I'm writing a DOS batch for WIN NT to do a number of things including
: executing an sql block. The sql invocation goes something like this:

: sqlplus @'name of sql file'

: That works fine, but I would like to communicate something from the
: sqlplus functions back to the calling batch file. Is there some way
: that I can pass a parameter to sqlplus? Or is there a better way to
: get this done?

One way to return results is to spool the results into a file with an appropriate format - one useful format is as a batch file that sets a variable.

in FILE.SQL do something like the following...

   spool TEMP.BAT
   SELECT 'SET RESULT='||whatever from dual;    spool off

Your main batch file should look something like this...

	sqlplus  @FILE.SQL
        rem temp.bat is created by FILE.SQL.  It sets the symbol RESULT
	call TEMP.BAT
        if %RESULT%.==XXX. goto was_XXX
	(etc...)

You may have to play with the output options of the spool command to produce the exact output you need.

An other example of the general technique is to spool the results into a file and then use the FIND command to check for specific values in the file.

       FILE.SQL:  spool count.txt
                  select count(*) from table;
                  spool off

       main .bat file
                  sqlplus @FILE.SQL
                  find " 0 "
                  if errorlevel 1 goto not_0_rows