06-26-2006
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 from the SQL statement to the shell variable as below, but what about multiple values...
bash-2.05$ more shellsql.sh
#!/bin/ksh
#set -x
outvar=`sqlplus -s apps/apps <<EOF
set heading off feedback off verify off
select bug_number from ad_bugs where bug_number='4289310';
exit
EOF
`
echo $outvar
Thanks for your help on this.
Veera
10 More Discussions You Might Find Interesting
1. Shell Programming and Scripting
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
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
3. Shell Programming and Scripting
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
4. Shell Programming and Scripting
I tried searching the forum for similar posts but its closed now.
Would appreciate any help on this.
I am trying to capture return value from a select query into a variable.
DB is Oracle
I am able to spool it to a file but I donot intend to use it.
Here is my script that does not work ;)
I... (27 Replies)
Discussion started by: monie2717
27 Replies
5. Shell Programming and Scripting
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
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
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
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
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
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 DEBIAN
sql::reservedwords
SQL::ReservedWords(3pm) User Contributed Perl Documentation SQL::ReservedWords(3pm)
NAME
SQL::ReservedWords - Reserved SQL words by ANSI/ISO
SYNOPSIS
if ( SQL::ReservedWords->is_reserved( $word ) ) {
print "$word is a reserved SQL word!";
}
DESCRIPTION
Determine if words are reserved by ANSI/ISO SQL standard.
METHODS
is_reserved( $word )
Returns a boolean indicating if $word is reserved by either "SQL:1992", "SQL:1999" or "SQL:2003".
is_reserved_by_sql1992( $word )
Returns a boolean indicating if $word is reserved by "SQL:1992".
is_reserved_by_sql1999( $word )
Returns a boolean indicating if $word is reserved by "SQL:1999".
is_reserved_by_sql2003( $word )
Returns a boolean indicating if $word is reserved by "SQL:2003".
reserved_by( $word )
Returns a list with SQL standards that reserves $word.
words
Returns a list with all reserved words.
EXPORTS
Nothing by default. Following subroutines can be exported:
is_reserved
is_reserved_by_sql1992
is_reserved_by_sql1999
is_reserved_by_sql2003
reserved_by
words
SEE ALSO
SQL::ReservedWords::DB2
SQL::ReservedWords::MySQL
SQL::ReservedWords::ODBC
SQL::ReservedWords::Oracle
SQL::ReservedWords::PostgreSQL
SQL::ReservedWords::SQLite
SQL::ReservedWords::SQLServer
ISO/IEC 9075:1992 Database languages -- SQL
ISO/IEC 9075-2:1999 Database languages -- SQL -- Part 2: Foundation (SQL/Foundation)
ISO/IEC 9075-2:2003 Database languages -- SQL -- Part 2: Foundation (SQL/Foundation)
AUTHOR
Christian Hansen "chansen@cpan.org"
COPYRIGHT
This program is free software, you can redistribute it and/or modify it under the same terms as Perl itself.
perl v5.8.8 2008-03-28 SQL::ReservedWords(3pm)