Login or Register to Ask a Question and Join Our Community

Sponsored Content
Full Discussion: Shell script to catch PL/SQL return values
Top Forums Shell Programming and Scripting Shell script to catch PL/SQL return values Post 302407072 by monie2717 on Wednesday 24th of March 2010 12:51:43 PM
Old 03-24-2010
Code:
 
cognos@sec-dev-sun04 : cat -n ./monie_test.sh
     1   !/bin/sh
     2  ###############################################################################
     3  #set -vxf
     4  
     5  COGNOS_HOME=/opt/cognos/cer/bin
     6  PATH=/usr/bin:/usr/sbin:/usr/local/bin:/opt/oracle/product/9.2.0/bin
     7  MODEL_HOME=/opt/cognos/mis_bi/models/Shipment
     8  TNS_ADMIN=/opt/oracle/product/9.2.0/network/admin
     9  CLASS_PATH=/opt/cognos/cognos8/webapps/p2pd/WEB-INF/lib
    10  PREF=/opt/cognos/preferences
    11  LD_LIBRARY_PATH=/opt/oracle/product/9.2.0/lib:/opt/cognos/cer/bin
    12  
    13  CUBE_SRC=/opt/cognos/mis_bi/cubes/Shipment/shipment_build
    14  CUBE_TRGT=/opt/cognos/mis_bi/cubes/Shipment
    15  HOSTNAME=`hostname`
    16  
    17  export COGNOS_HOME MODEL_HOME PREF PATH TNS_ADMIN CLASS_PATH LD_LIBRARY_PATH ORACLE_HOME CUBE_SRC HOSTNAME CUBE_TRGT
    18  
    19  TODAY=$(date)
    20  
    21  ORACLE_HOME=/opt/oracle/product/9.2.0
    22  ORACLE_SID=$1
    23  echo "Date is Fridayyyyy: $TODAY"
    24  x=$($ORACLE_HOME/bin/sqlplus -s report/report@$ORACLE_SID <<EOF
    25  set heading off pages 0 feedback off
    26  var n varchar2(100)
    27  exec select mod_cd into:n from report.cube where cube_cd = 'LNR_ALL';
    28  print :n
    29  exit;
    30  EOF)
    31  echo "Count = $x"
    32  
    33  
    34

Shell script to catch PL/SQL return values-new-bitmap-imagejpg
od.txt (8.8 KB) 
Last edited by monie2717; 03-24-2010 at 02:33 PM..
 

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

passing values from sql to shell script

Hi guyz, Posting a thread after a long time. I want to pass two variables to unix shell script from sql script. Note: I am calling sql script from unix script. sql script has 2 variables one is the return code for status of program run and second one email flag. I don't know how to capture... (3 Replies)
Discussion started by: sachin.gangadha
3 Replies

2. Shell Programming and Scripting

Shell script to catch PL/SQL return values

Hello, I need some help from the experts on PL/SQL and Shell scripting. I need a shell script that runs a PL/SQL procedure and gets the values returned from the PL/SQL procedure into the shell variables. The PL/SQL procedure returns multiple values. I was able to assign a single return value... (1 Reply)
Discussion started by: Veera_Raghav
1 Replies

3. Shell Programming and Scripting

Need to return fail or pass from shell script on the basis of pl/sql code execution

Hi guys, I am quite new in shell scripting. I am tring to promote some oracle jobs into control-M. In control-M, I am calling a script which establishes a connection with database and execute some procedures. Now I want if that PL/sql Block got failed script should return failure to... (2 Replies)
Discussion started by: alok1301
2 Replies

4. Shell Programming and Scripting

how to store the return values of stored procedure in unix shell script.

hi i am calling a oracle stored procedure(in the database) from unix shell scripting (a.sh). the called stored procedure returns some values through OUT variables i want to assign the return values of stored procedure in to unix shell script variable. can you provide me the code. ... (1 Reply)
Discussion started by: barani75
1 Replies

5. Shell Programming and Scripting

calling pl/sql procedure from shell and return values

How could I call an Oracle PL/SQL procedure from any shell (bash) and catch returning value from that procedure (out param) or get a returning value if it's a function. also, I got into trouble when I tried to send a number as a param #!/bin/bash -e username=$1 pwd=$2 baza=$3... (0 Replies)
Discussion started by: bongo
0 Replies

6. Shell Programming and Scripting

Assigning return value of an embedded SQL in a shell script variable

I've a script of the following form calling a simple sql that counts the no of rows as based on some conditions. I want the count returned by the sql to get assigned to the variable sql_ret_val1. However I'm finding that this var is always getting assigned a value of 0. I have verified by executing... (1 Reply)
Discussion started by: MxC
1 Replies

7. UNIX for Advanced & Expert Users

Call parallel sql scripts from shell and return status when both sql are done

Hi Experts: I have a shell script that's kicked off by cron. Inside this shell script, I need to kick off two or more oracle sql scripts to process different groups of tables. And when both sql scripts are done, I will continue in the shell script to do other things like checking processing... (3 Replies)
Discussion started by: huasheng8
3 Replies

8. Shell Programming and Scripting

Query the table and return values to shell script and search result values from another files.

Hi, I need a shell script, which would search the result values from another files. 1)execute " select column1 from table_name" query on the table. 2)Based on the result, need to be grep from .wft files. could please explain about this.Below is the way i am using. #!/bin/sh... (4 Replies)
Discussion started by: Rami Reddy
4 Replies

9. Shell Programming and Scripting

Return value to shell script, depending on status of pl/sql udpate

Hi All, I need to return value to the main shell script, depending on whether the UPDATE command in the embedded pl/sql is successfu or not. #!bin/ksh updateStatus=`sqlplus --conn details-- << EOF DECLARE var_rows NUMBER; BEGIN update table_name set column_name =... (7 Replies)
Discussion started by: rituparna_gupta
7 Replies

10. Shell Programming and Scripting

How to catch sql error in script?

Hi Gurus, I have a script which send sql query to oracle db and return value to my script. dummy code like below: sqlplus -s user/${PASSWD}@${ORACLE_SID} @${DIR}/query.sql > outputfile using above code, when query has error, it send error to same out put file and exit code is 0, is... (6 Replies)
Discussion started by: ken6503
6 Replies

LEARN ABOUT REDHAT

lint

lint(1B)                                             SunOS/BSD Compatibility Package Commands                                             lint(1B)

NAME

       lint - C program verifier

SYNOPSIS

       /usr/ucb/lint [options]

DESCRIPTION

       /usr/ucb/lint  is  the  interface  to  the  BSD  Compatibility  Package  C  program  verifier.   It  is  a  script  that looks for the link
       /usr/ccs/bin/ucblint to the C program verifier. /usr/ccs/bin/ucblint is available only with the SPROcc package, whose default  location  is
       /opt/SUNWspro.  /usr/ucb/lint  is  identical to /usr/ccs/bin/ucblint, except that BSD headers are used and BSD  libraries are linked before
       base libraries.  The /opt/SUNWspro/man/man1/lint.1 man page is available only with the SPROcc package.

OPTIONS

       /usr/ucb/lint accepts the same options as /usr/ccs/bin/ucblint, with the following exceptions:

       -Idir           Search dir for included files whose names do not begin with a slash (/) prior  to  searching  the  usual  directories.  The
                       directories for multiple -I options are searched in the order specified. The preprocessor first searches for #include files
                       in the directory containing sourcefile, and then in directories named with -I options (if any), then  /usr/ucbinclude,  and
                       finally, in /usr/include.

       -Ldir           Add  dir  to  the  list  of  directories  searched  for  libraries  by  /usr/ccs/bin/ucblint.  This  option  is  passed  to
                       /usr/ccs/bin/ld. Directories specified with this option are searched before /usr/ucblib and /usr/lib.

       -Y P, dir       Change the default directory used for finding libraries.

EXIT STATUS

       The following exit values are returned:

       0               Successful completion.

       >0              An error occurred.

FILES

       /usr/lint/bin/ld        link editor

       /usr/lib/libc           C library

       /usr/ucbinclude         BSD Compatibility directory for header files

       /usr/ucblib             BSD Compatibility directory for libraries

       /usr/ucblib/libucb      BSD Compatibility C library

       /usr/lib/libsocket      library containing socket routines

       /usr/lib/libnsl         library containing network functions

       /usr/lib/libelf         library containing routines to process ELF object files

       /usr/lib/libaio         library containing asynchronous I/O routines

ATTRIBUTES

       See attributes(5) for descriptions of the following attributes:

       +-----------------------------+-----------------------------+
       |      ATTRIBUTE TYPE         |      ATTRIBUTE VALUE        |
       +-----------------------------+-----------------------------+
       |Availability                 |SUNWscpu                     |
       +-----------------------------+-----------------------------+

SEE ALSO

       ld(1), a.out(4), attributes(5)

SunOS 5.10                                                          1 Feb 1995                                                            lint(1B)
All times are GMT -4. The time now is 11:36 AM.
Unix & Linux Forums Content Copyright 1993-2022. All Rights Reserved.
Privacy Policy