- Community Home
- >
- Servers and Operating Systems
- >
- Operating Systems
- >
- Operating System - HP-UX
- >
- Test return code from sql in script
Categories
Company
Local Language
Forums
Discussions
Forums
- Data Protection and Retention
- Entry Storage Systems
- Legacy
- Midrange and Enterprise Storage
- Storage Networking
- HPE Nimble Storage
Discussions
Discussions
Discussions
Forums
Forums
Discussions
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
- BladeSystem Infrastructure and Application Solutions
- Appliance Servers
- Alpha Servers
- BackOffice Products
- Internet Products
- HPE 9000 and HPE e3000 Servers
- Networking
- Netservers
- Secure OS Software for Linux
- Server Management (Insight Manager 7)
- Windows Server 2003
- Operating System - Tru64 Unix
- ProLiant Deployment and Provisioning
- Linux-Based Community / Regional
- Microsoft System Center Integration
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Community
Resources
Forums
Blogs
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО03-08-2004 10:31 AM
тАО03-08-2004 10:31 AM
I want to run an Oracle (9i) SQL script from a ksh script. Can I set a return code within the SQL script that can be tested within the ksh script?
Thanks!
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО03-08-2004 10:59 AM
тАО03-08-2004 10:59 AM
Solutionhttp://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_database_id=NOT&p_id=73788.1
Example PL/SQL: How to Pass Status from PL/SQL Script to Calling Shell Script
Overview
--------
This article contains an example of returning the status from a PL/SQL
program to a shell script.
It is intended for use by PL/SQL and shell script programmers. In
principle, this approach is transferable to all operating systems capable
of receiving a return value from a called program in a script. The example
given is (in part) UNIX specific.
SQL*Plus is commonly run interactively. It is also possible to pass
SQL*Plus the name of a SQL script file and have it run the script file
non-interactively and pass the result status back to the caller. In
this manner, SQL scripts may be executed via SQL*Plus from UNIX shell
scripts and, based upon the return status from SQL*Plus, the shell script
may make decisions about how to proceed.
Sample Code
-----------
ret.sql:
prompt Look in your SQL*Plus User's guide for other datatypes
prompt that EXIT can pass to a calling script via a bind variable
prompt
set serveroutput on
variable ret_val number
declare
dstr varchar2(40);
thedate date;
begin
select sysdate into thedate from dual;
dstr := to_char(thedate, 'MON');
dbms_output.put_line('Month: '||dstr);
if(dstr = 'JUL') then
:ret_val := 100;
else
:ret_val := 200;
end if;
end;
/
exit :ret_val;
The key is the SQL*Plus bind variable.
You must first declare a bind variable. Second, specify the anonymous
PL/SQL block which must set the bind variable to some meaningful value.
Third, run the anonymous PL/SQL block via the '/'. Then exit the SQL
script specifying the bind variable.
The exit command tells SQL*Plus to exit with the value specified. This
value is passed back to the calling UNIX script, where the command
completion status may be examined.
Here is the korn shell script:
#!/bin/ksh
sqlplus scott/tiger @ret
if [ $? -eq 100 ]
then
echo "The month is July"
else
echo "The month is not July"
fi
This script simply calls SQL*Plus using the user name and password of scott
and tiger and tells it to execute the ret.sql PL/SQL script. Subsequently,
the script checks the return status and prints out an appropriate message.
Other platforms may have their own script language into which you must embed
the SQL*Plus call.
Test Platforms:
--------------
The above scripts were tested succesfully on:
SunSolaris with rdbms 8.1.6.2.0
IBM RS/6000 AIX with rdbms 8.1
References
----------
"SQL*Plus User's Guide and Reference, Release 8.1.5", (A66736-01)
Note:2177.1 Pass a parameter with spaces to SQL*Plus
Note:6841.1 Using a PL/SQL Varianle to set the EXIT code of a SQL script
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО03-08-2004 11:00 AM
тАО03-08-2004 11:00 AM
Re: Test return code from sql in script
#sqlscript
sqlplus internal << EOF
command
command
command
rc=$?
EOF
return $rc
The stub above is called by another script which will get the return code from here with the following code:
sqlscript
return_code=$?
SEP
Owner of ISN Corporation
http://isnamerica.com
http://hpuxconsulting.com
Sponsor: http://hpux.ws
Twitter: http://twitter.com/hpuxlinux
Founder http://newdatacloud.com
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО03-08-2004 11:21 AM
тАО03-08-2004 11:21 AM
Re: Test return code from sql in script
Scott
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО03-09-2004 03:12 AM
тАО03-09-2004 03:12 AM
Re: Test return code from sql in script
#!/bin/sh
#typeset -i rc=0
sqlplus /nolog <
whenever sqlerror exit 9
set echo on;
SELECT tablespace_name
FROM sys.dba_xablespaces
ORDER BY 1;
EOD
rc=$?
print "returning: \"$rc\""
return $rc
=====================================
Running this script gives the following output:
SQL> Connected.
SQL> SQL> buffer cleared
SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> 2 3 FROM sys.dba_xablespaces
*
ERROR at line 2:
ORA-00942: table or view does not exist
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production
returning: "9"
Thanks again,
Scott