This discussion is archived
13 Replies Latest reply: Oct 15, 2012 3:41 PM by Dude! RSS

GET VALUE FROM ORACLE PROCEDURE AND DISPLAY IN UNIX VARIABLE

Gor_Mahia Explorer
Currently Being Moderated
All,
I need help with unix script Ive written that calls oracle procedure which has out parameter as ...PROC1(PARAMS OUT VARCHAR2) ...
so when called from shell script i want to pass and display the value of PARAMS in unix script. How do i go about this? thanks in advance.
  • 1. Re: GET VALUE FROM ORACLE PROCEDURE AND DISPLAY IN UNIX VARIABLE
    Dude! Guru
    Currently Being Moderated
    There are several ways, for instance:

    <pre>
    status=$(sqlplus -s /nolog <<-EOF
    connect / as sysdba
    set pages 0 feed off
    select status from v\$instance;
    EOF
    )

    echo $status
    OPEN
    </pre>
  • 2. Re: GET VALUE FROM ORACLE PROCEDURE AND DISPLAY IN UNIX VARIABLE
    Gor_Mahia Explorer
    Currently Being Moderated
    Dude
    im a bit lost so where where are you calling the procedure and passing the parameter to get the value?

    ...PROC1(PARAMS OUT VARCHAR2) ...

    when you execute the procedure(EXEC PROC1() ) the variable is passed for params then you display/echo its value,

    regards,
  • 3. Re: GET VALUE FROM ORACLE PROCEDURE AND DISPLAY IN UNIX VARIABLE
    Dude! Guru
    Currently Being Moderated
    You can run it at the Bash command prompt or put it into a script. It is only an example to show you how to get a value from a sql query and put it into a variable. Isn't this what you are basically trying to do? If you need more help I suggest you provide more details and also the relevant content of your script.
  • 4. Re: GET VALUE FROM ORACLE PROCEDURE AND DISPLAY IN UNIX VARIABLE
    Veeresh.S Pro
    Currently Being Moderated
    I took DUDE's code & replaced select with procedure, also i have passed the variable with it. See if this is what you were looking for. There are many ways,better ways to display the output of the oracle on linux script, below is one method where you pass a variable with the procedure and display back the output on linux/unix.

    SQL> create or replace procedure testv(params in varchar2)
    is
    var1 varchar(20);
    begin
    select status into var1 from v$instance where instance_name=params;
    dbms_output.put_line(var1);
    end;
    /

    Procedure created.

    [Test1 ~]$ cat 2testv.sh
    status=$(sqlplus -s /nolog <<-EOF
    connect / as sysdba
    set pages 0 feed off;
    set serveroutput on;
    exec testv('TESTDB');
    EOF
    *)*
    echo $status


    [Test1 ~]$ sh 2testv.sh
    OPEN
  • 5. Re: GET VALUE FROM ORACLE PROCEDURE AND DISPLAY IN UNIX VARIABLE
    Gor_Mahia Explorer
    Currently Being Moderated
    Veeresh.S
    Thanks this is close but below ive given details what iam trying to do:
    1. this is my sample procedure pls note the parameter mode is OUT,
    CREATE OR REPLACE PROCEDURE PROC1 ( p_stmt OUT VARCHAR2)
    IS
    cursor CUR1 is
    select EMPNAME FROM EMPS;
    v_alert_msg VARCHAR2 (4000) DEFAULT NULL;
    BEGIN
    DBMS_OUTPUT.ENABLE(200000);
    FOR i_cnt IN CUR1 LOOP
    v_alert_msg := v_alert_msg ||'Employee Name = '||i_cnt.empname||CHR(10);
    END LOOP;
    p_stmt := v_alert_msg;
    EXCEPTION
    WHEN OTHERS
    THEN
    NULL;
    END PROC1;

    2. Ive written as anonymous block what the Unix shell should do but this is where i need help this should be written in Unix shell script but functionality is like this,

    pls write the below equivalent in unix shell script
    DECLARE
    VAR1 VARCHAR2(4000) := NULL;
    var2 varchar2(4000);
    BEGIN

    PROC1 ( VAR1);
    var2 := 'All employees:' || var1;
    DBMS_OUTPUT.PUT_LINE(VAR2)
    END;
  • 6. Re: GET VALUE FROM ORACLE PROCEDURE AND DISPLAY IN UNIX VARIABLE
    Dude! Guru
    Currently Being Moderated
    There is no equivalent for PL/SQL in Unix shell. What you can do is to execute sqlplus using a SQL script or procedure and have the result or whatever appears at stdout (display) redirected into a Unix shell variable as already explained.

    The same principle applies also to other commands, for instance:

    my_var=$(ls -l)

    my_var=$(sqlplus ..... <<EOF
    .... output any value
    EOF
    )

    then to show the value of my_var:

    echo "$my_var"
  • 7. Re: GET VALUE FROM ORACLE PROCEDURE AND DISPLAY IN UNIX VARIABLE
    Gor_Mahia Explorer
    Currently Being Moderated
    Dude
    I am a newbie when it comes to unix scripting but your explanation is too brief to catch what you are talking about here....thanks.
  • 8. Re: GET VALUE FROM ORACLE PROCEDURE AND DISPLAY IN UNIX VARIABLE
    Dude! Guru
    Currently Being Moderated
    [oracle@vm002 ~]$ var="this is a test"
    [oracle@vm002 ~]$ echo $var
    this is a test
    
    [oracle@vm002 ~]$ var="$(date +%s)"
    [oracle@vm002 ~]$ echo $var
    1350162091
    The first example stores a string into variable $var. The second example stores the output of a command into $var. You can put any command in between the $() and the output will be stored into $var. Instead of the date command you can also execute, e.g. sqlplus as outlined in the previous replies and the output of your procedure will be stored into $var.
  • 9. Re: GET VALUE FROM ORACLE PROCEDURE AND DISPLAY IN UNIX VARIABLE
    Gor_Mahia Explorer
    Currently Being Moderated
    Veeresh.S
    I took your code and changed the procedure to what i need as below if the parameter mode is OUT how do you change the unix shell to work now?
    SQL> create or replace procedure testv(params out varchar2)
    is
    var1 varchar(20);
    begin
    select empname into var1 from emp where emp_id = 1113277;
    params := var1;
    end;
    /
  • 10. Re: GET VALUE FROM ORACLE PROCEDURE AND DISPLAY IN UNIX VARIABLE
    Dude! Guru
    Currently Being Moderated
    You can export a variable to a subshell or pass it on as a command-line argument, but you cannot access a variable in the memory space of the parent process. In other words, whatever variable you create or use inside Sqlplus will not be seen in the parent command shell. You can however create a variable by using the screen output of a command as demonstrated.
  • 11. Re: GET VALUE FROM ORACLE PROCEDURE AND DISPLAY IN UNIX VARIABLE
    Veeresh.S Pro
    Currently Being Moderated
    I took your code and changed the procedure to what i need as below if the parameter mode is OUT how do you change the unix shell to work now?
    DUDE has explained it perfectly.

    you have to display a variable inside a package or outside a package(using out parameter) but can never pass a parameter from sqlplus to unix.
    so at any cost the variable needs to be display from sqlplus and that o/p will be recorded into the unix variable. without displaying you cant pass a parameter directly to the unix shell.

    SQL> set feed off;
    SQL> set head off;
    SQL> create or replace procedure testv(params out varchar2)
    is
    begin
    select status into params from v$instance;
    end;
    / 2 3 4 5 6
    SQL>
    SQL> variable pout varchar2(20);
    SQL> exec testv(:pout);
    SQL> print pout;

    OPEN

    SQL>
    SQL>
    SQL> create or replace procedure testv2
    is
    params varchar(20);
    begin
    select status into params from v$instance;
    dbms_output.put_line(params);
    end;
    /
    2 3 4 5 6 7 8 SQL>
    SQL> exec testv2;
    SQL> set serveroutput on;
    SQL> exec testv2;
    OPEN
    SQL>

    now this sqlplus o/p "OPEN" can be passed to unix but you cant pass a variable params to unix. what you do inside sqlplus stays inside only.

    In unix shell script

    status=$(sqlplus -s /nolog <<-EOF
    connect / as sysdba
    set pages 0 feed off;
    set head off;
    set serveroutput on;

    <you can include any of one of the above 2 codes, both are just one and the same but just a different method to display a variable>

    EOF
    )
    echo $status



    hope you got what we are trying to tell...
  • 12. Re: GET VALUE FROM ORACLE PROCEDURE AND DISPLAY IN UNIX VARIABLE
    Gor_Mahia Explorer
    Currently Being Moderated
    Veeresh.S
    I see what you are saying and now i was testing just a simple procedure body like this below,
    create procedure PROC1
    IS
    BEGIN
    DBMS_OUTPUT.PUT_LINE('TEST THIS PROCEDURE');
    END;

    with my below script and this script seems to work but it doesn't display the output of the procedure body('TEST THIS PROCEDURE') , so what iam i missing here any help pls?

    *#!/bin/ksh*
    echo 'Start process'
    VAR_PROC='PROC1'
    *SQLDATA=${OUTPUT_DIR}/MYOUTPUT.log*
    *touch ${SQLDATA}*
    *$SQLPLUS << EOF > ${SQLDATA} 2>&1*
    set serveroutput on
    set echo on
    exec $VAR_PROC;
    EOF
    echo 'Done: '
    exit;

    thanks & regards.
  • 13. Re: GET VALUE FROM ORACLE PROCEDURE AND DISPLAY IN UNIX VARIABLE
    Dude! Guru
    Currently Being Moderated
    VAR_PROC='PROC1'
    You need to export the variable to be available to a child process, e.g. sqlplus
    $SQLPLUS << EOF
    Again, if want to store the output of your sql query in a variable, use: variable=$(sqlplus... as previously shown
    ) ${SQLDATA} 2>&1
    You are redirecting all output to a file. Nothing will appear on screen.

    You can pass a variable to sqlplus, for example:

    <pre>
    export query=name
    result=sqlplus /nolog <<EOM
    connect / as sysdba
    select $query from v/$instance;
    EOM
    )

    echo $result
    OPEN
    </pre>

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points