Results 1 to 7 of 7
  1. #1
    Join Date
    Oct 2004
    Posts
    12

    SQLPlus Return Value Help!

    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

  2. #2
    Join Date
    Apr 2004
    Posts
    246
    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%
    Give a man a fish, you feed him for a day. Club him over the head with a fish, he'll leave you alone.

  3. #3
    Join Date
    Oct 2004
    Posts
    12
    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;

  4. #4
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,672

    Cool

    Another way:

    In sql code use/do something like this example:
    Code:
    var rc number;
    whenever sqlerror exit :rc
    exec :rc:=1;
    select count(*) from emp
    /
    exec :rc:=2;
    create table emp2 as
      select * from emp
       where deptno='10'
    /   
    exec :rc:=0;
    exit :rc
    In your DOS cmd file do:
    Code:
    rem test.cmd
    sqlplus scott/tiger@mydb @test.sql
    IF ERRORLEVEL==1 GOTO ONE
    IF ERRORLEVEL==2 GOTO TWO
      echo 'No errors.'
    GOTO END
    :ONE
      echo 'Error Level 1'
    GOTO END
    :TWO
      echo 'Error Level 2'
    :END
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  5. #5
    Join Date
    Oct 2004
    Posts
    12
    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.

  6. #6
    Join Date
    Oct 2004
    Posts
    12
    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?

  7. #7
    Join Date
    Apr 2004
    Posts
    246
    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.
    Give a man a fish, you feed him for a day. Club him over the head with a fish, he'll leave you alone.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •