Hi there.

I am writing a DOS batch script that calls SQLPlus and I want to be able to have SQLPlus return a value back to the DOS batch script.

My sql "code" calls a User-defined function that returns different values based on various inputs.

I have seen examples of how to return error codes (not values) in Unix, but NOT values and NOT in DOS. Any help would be appreciated.

Mike

Have your script output the result you want to capture as the last thing it does (select it, use dbms_output, whatever). The FOR command is then used to capture that last line of output to variable x. x can then be passed to everything else. Set "delims" to something that won't be in your output - the default is whitespace, so the output "10/10/2004 11:23" would be parsed as two fields, and you'd only get the first value. The "-s" option prevents sqlplus disconnect messages from being retrieved.

@echo off
FOR /F "usebackq delims=!" %%i IN (`sqlplus -s u/p @t`) DO set x=%%i
echo %x%

Thanks for your help!

So, the real key is the %%i. Can I set it another way? Do I have to do it in a FOR loop as I am only going through the loop once? I pass in the current community and get either a '1' or '2' back as the partition.

Here is my UDF:

FUNCTION GETPARTITION (
l_Comm IN VARCHAR2
)
RETURN VARCHAR2 IS
l_partition VARCHAR2(1) default null;
/************************************************** ****************************
NAME: GETPARTITION
PURPOSE: To return the Current Partition for a given community.
%USERNAME% (set in TOAD Options, Procedure Editor)
************************************************** ****************************/
BEGIN

SELECT CURRENT_PARTITION_NUMBER INTO l_partition FROM CURRENT_PARTITION WHERE Community = l_Comm;
EXCEPTION
WHEN NO_DATA_FOUND THEN
Null;
WHEN OTHERS THEN
Null;

RETURN l_partition;
END GETPARTITION;

Another way:

In sql code use/do something like this example:
In your DOS cmd file do:

thanks for your help LK. I certainly could use error codes as I only have a couple of potential values, but it seems a little kludgy. But, I may have to get off of my high horse! Thanks again.

I may end up just writing a little 'C' program to overcome other issues (Like enhanced error handling). Can I get the return value into another program in a similar way?

Don't use the exit code - it makes it impossible to check for actual errors.

I've done the pro*c method, but then it becomes very static - one program for each request.

The FOR method loop over all the output from the entire sqlplus session (hence the -s option to prevent disconnected messages), but will only store the final value. It actually sets the variable on every line, overwriting whatever it had before, so only the last one is actually captured. If you need multiple rows, you could play around with the FOR command and try to set the variables as i_#, where # increments. If you do it, please send it back for me. The FOR command can also be used to break the output line into multiple vars: output="123/abc/9-sep-04", you could set the delim to "/", and then DO (set x=%%i ) ; (set y=%%j) ; (set z=%%k)
(not sure about the parens and semicolons, didn't have time to fully test)


If you need multiple lines of output captured, you could spool it to a file, and then use that file to set the variables. Spool a file that actually has the SET commands in it, then just execute the file after you exit sqlplus.