DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Returning Value to UNIX Command Line

  1. #1
    Join Date
    Feb 2000
    Location
    Alexandria, VA, 22314
    Posts
    41
    Hi,

    I am calling SQLPLUS from the UNIX command line with the following command:

    SQLPLUS odsload/odsload@odsd.com @value.sql


    The value.sql file is the following:

    DECLARE
    RetVal NUMBER;
    BEGIN
    RetVal := ODSLOAD.FUNC_EXTRACT_TRAILER ( 1 );
    END;


    Now, my question is how do I return the value RetVal to the UNIX command line?

    Thanks in advance, Keith

  2. #2
    Join Date
    Aug 2000
    Posts
    194
    Try this:

    DECLARE
    RetVal NUMBER;
    BEGIN
    RetVal := ODSLOAD.FUNC_EXTRACT_TRAILER ( 1 );
    END;
    / -- Added
    exit :RetVal; -- Added

    And, test the value of "$?"

  3. #3
    Join Date
    Nov 2000
    Posts
    79
    there are two ways to do this

    1.use utl_file and write it to a file and then extract the value
    from that file to a unix variable.

    2. Write a pro*c program.


    regards

    gtm

  4. #4
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    The magic of ksh. You can just embed you sqlplus command inside some forward quotes and bingo, your variable is set.

    #!/bin/ksh
    THE_VALUE=`sqlplus -s system/manager <<EOF
    set feedback off
    set echo off
    set pages 0
    DECLARE
    RetVal NUMBER;
    BEGIN
    dbms_output.put_line(ODSLOAD.FUNC_EXTRACT_TRAILER ( 1 ));
    END;
    EOF
    `
    echo $THE_VALUE


    # end of ksh

    This way, you can check the value of $? to see of the sqlplus command was successful and also get your value back.



    Jeff Hunter

  5. #5
    Join Date
    Feb 2000
    Location
    Alexandria, VA, 22314
    Posts
    41
    Jeff,

    THis might be a stupid question, but I can get the value for $?, which tells me whether the SQLPLUS command was OK.

    Where do i find the value that was sent to DBMS_OUPUT by using $...i.e. the result of ODSLOAD.SP blah blah blah?

    Thanks, Keith

  6. #6
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Its in $THE_VALUE
    Jeff Hunter

Posting Permissions

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


Click Here to Expand Forum to Full Width