13 Replies Latest reply: Oct 15, 2012 5:41 PM by Catch_22 RSS

    GET VALUE FROM ORACLE PROCEDURE AND DISPLAY IN UNIX VARIABLE

    Gor_Mahia
      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
          Catch_22
          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
            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
              Catch_22
              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
                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
                  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
                    Catch_22
                    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
                      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
                        Catch_22
                        [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
                          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
                            Catch_22
                            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
                              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
                                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
                                  Catch_22
                                  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>