Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2003
    Location
    India
    Posts
    114

    Unanswered: Procedure call through Shell script

    I have a procedure having one OUT variable.I want to call this procedure from the shell without making a annonymous PL/SQl block in shell. Also the option of writing the procedure output in flat file and reading this file from shell is out.

    Can I directly call the procedure with output paramter from shell script.

  2. #2
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    Yes, it is possible. Not using a shell variable though (I dont know if that's possible), but you can use SQL*plus var to capture the output of the procedure's out var and pass this value back to the shell script, capturing it into the shell var you want.
    Code:
    SQL> conn scott/tiger
    Connected.
    SQL>
    SQL>
    SQL> create or replace procedure test( i out number ) as
      2  begin
      3     i := trunc( dbms_random.value * 100 );
      4  end;
      5  /
    
    Procedure created.
    
    SQL> exit
    Disconnected from Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production
    With the Partitioning option
    JServer Release 8.1.7.0.0 - Production
    #
    # cat test.sh
    #! /usr/bin/ksh
    
    outputvar=`sqlplus -s scott/tiger << EOF
    set feedback off pages 0
    variable outputvar number
    begin
            test( :outputvar );
    end;
    /
    print :outputvar
    exit :outputvar
    EOF`
    
    echo "Value of outputvar is: $outputvar"
    
    exit 0
    #
    # ./test.sh
    Value of outputvar is:  31
    # ./test.sh
    Value of outputvar is:  98
    #

Posting Permissions

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