08-25-2007
Quote:
Originally Posted by
maco_home
i'm having real problems retrieving the returncode of my sqlplus-call. I found a lot of informations on the net, but havn't been able to get it running so far, so now i ask for some help
I do start the sqlplus out of my shell script with the parameters stored in the proc_clips.sql, which is perfectly working. But now i have created some real obviously mistakes like "trunate" or "comit" - but I still get the 0 as returncode. Does anybody have an idea what i'm doing wrong???
Thanks for your help, Martin
###### dbUpdate.sh ######
sqlplus -s userrob@db1/passwrob< proc_clips.sql
RETVAL=$?
echo $RETVAL
echo "SQLPUS-CLIPS"
###### proc_clips.sql ######
set echo on
whenever sqlerror exit 1
whenever oserror exit 2
exec martin_s4m_fictions;
exec MAJ_RANK;
trunate table T_DRAUT_TEMP_CLIPSFICTIONS;
comit;
exit 0
###### OUTPUT ######
SP2-0734: unknown command beginning "trunate ta..." - rest of line ignored.
SP2-0042: unknown command "comit" - rest of line ignored.
0
SQLPUS-CLIPS
It's because those are SQL*Plus errors and not SQL ,PL/SQL blocks (SQLERROR) or OS (OSERROR) errors.
Last edited by radoulov; 08-25-2007 at 01:08 PM..
Reason: corrected(problem description not clear): commands->errors
10 More Discussions You Might Find Interesting
1. Shell Programming and Scripting
Hi ,
I want to pass parameters from a shell script to a sql script and use the parameter in the sql query ..and then I want to spool a particular select query on to my unix box... for 4 different locations by writing only one sql script
Right now no file is generated on the unix box...it is a... (2 Replies)
Discussion started by: phani
2 Replies
2. Shell Programming and Scripting
I have a script which connects to different database servers using sqlplus. Is there a way by which I can run a shell command on that host from sqlplus?
I know about 'host' command but it runs script on the local machine where the original script is running. Is there a way to run command on the... (9 Replies)
Discussion started by: dkr123
9 Replies
3. Shell Programming and Scripting
Can anybody help me out in sending parameters from sql*plus script to unix shell script without using flat files..
Initially in a shell script i will call sql*plus and after getting some value from some tables, i want that variable value in unix shell script. How can i do this?
Please tell me... (2 Replies)
Discussion started by: Hara
2 Replies
4. Shell Programming and Scripting
Hi,
I am writing a script to test database connection. If the first try fails, it will wait for 1 minutes and then try again. The script is as following:
........
for i in $ORACLE_SID
do
$ORACLE_HOME/bin/sqlplus $username/$password@$i <<! >/dev/null
select * from tab;
exit
if ; then... (3 Replies)
Discussion started by: beaniebear
3 Replies
5. Shell Programming and Scripting
Hi,
I need help urgently for following issue. Pls help me to resolve this issue.
I am calling sql script file(file1.sql) from UNIX Shell Script(script1.ksh) using sql plus and trying to create flat file that contains all records returned from SQL query in SQL script(file1.sql)
I given... (6 Replies)
Discussion started by: praka
6 Replies
6. Shell Programming and Scripting
Hi
I want to connect to the Oracle database using a username/password and get back the query result(a numeric value) in a variable, which I can then compare using a conditional.
Can anybody help me with this.
Thanks
Gaurav (4 Replies)
Discussion started by: gaurav_1711
4 Replies
7. Shell Programming and Scripting
Hi All,
How i will use sqlplus in shell script?
Can any one provide sample code which explain following:
1. Connect to oracle DB
2. Exceute select * from tablename
3. Release connection to the DB
4. Append output in file everytime when query executes.
Thanks in advance (1 Reply)
Discussion started by: poweroflinux
1 Replies
8. Shell Programming and Scripting
sql_rows=`sqlplus -s / <<EOF
set heading off
set pagesize 1000
set tab off
set linesize 120 wrap off
column "Path" format a15
--column "No_Of_files" format a10
select tablespace_name, substr(file_name,1,instr(file_name,'/',1,2)) as "Path" , count(*) as "No_Of_files" from dba_data_files
... (7 Replies)
Discussion started by: desibabu
7 Replies
9. Shell Programming and Scripting
Hi
When I use sqlplus in shell script, I get sqlplus: command not found.
ORACLE_HOME is not set. How to set ORACLE_HOME in unix?
Thanks (3 Replies)
Discussion started by: vinoth_kumar
3 Replies
10. Shell Programming and Scripting
Hi All,
Please let me know what i am missing in the following code (part of my script)
Schemas=(AWQM WFCONTROLLER PROVCO PRISM)
for s in "${Schemas}"
do
sch="${s}_$tol"
if || ;then
echo "This is AD or TD region"
sqlplus -s $sch/$tpwd@$ttns <<EOF... (7 Replies)
Discussion started by: pvmanikandan
7 Replies
LEARN ABOUT PHP
sqlsrv_errors
SQLSRV_ERRORS(3) SQLSRV_ERRORS(3)
sqlsrv_errors - Returns error and warning information about the last SQLSRV operation performed
SYNOPSIS
mixed sqlsrv_errors ([int $errorsOrWarnings])
DESCRIPTION
Returns error and warning information about the last SQLSRV operation performed.
PARAMETERS
o $errorsOrWarnings
- Determines whether error information, warning information, or both are returned. If this parameter is not supplied, both error
information and warning information are returned. The following are the supported values for this parameter: SQLSRV_ERR_ALL, SQL-
SRV_ERR_ERRORS, SQLSRV_ERR_WARNINGS.
RETURN VALUES
If errors and/or warnings occurred on the last sqlsrv operation, an array of arrays containing error information is returned. If no errors
and/or warnings occurred on the last sqlsrv operation, NULL is returned. The following table describes the structure of the returned
arrays:
Array returned by sqlsrv_errors
+---------+---------------------------------------------------+
| Key | |
| | |
| | Description |
| | |
+---------+---------------------------------------------------+
|SQLSTATE | |
| | |
| | For errors that originate from the ODBC driver, |
| | the SQLSTATE returned by ODBC. For errors that |
| | originate from the Microsoft Drivers for PHP for |
| | SQL Server, a SQLSTATE of IMSSP. For warnings |
| | that originate from the Microsoft Drivers for PHP |
| | for SQL Server, a SQLSTATE of 01SSP. |
| | |
| code | |
| | |
| | For errors that originate from SQL Server, the |
| | native SQL Server error code. For errors that |
| | originate from the ODBC driver, the error code |
| | returned by ODBC. For errors that originate from |
| | the Microsoft Drivers for PHP for SQL Server, the |
| | Microsoft Drivers for PHP for SQL Server error |
| | code. |
| | |
|message | |
| | |
| | A description of the error. |
| | |
+---------+---------------------------------------------------+
EXAMPLES
Example #1
functionname(3) example
<?php
$serverName = "serverName/sqlexpress";
$connectionInfo = array( "Database"=>"dbName", "UID"=>"username", "PWD"=>"password");
$conn = sqlsrv_connect( $serverName, $connectionInfo);
if( $conn === false ) {
die( print_r( sqlsrv_errors(), true));
}
/* Set up a query to select an invalid column name. */
$sql = "SELECT BadColumnName FROM Table_1";
/* Execution of the query will fail because of the bad column name. */
$stmt = sqlsrv_query( $conn, $sql );
if( $stmt === false ) {
if( ($errors = sqlsrv_errors() ) != null) {
foreach( $errors as $error ) {
echo "SQLSTATE: ".$error[ 'SQLSTATE']."<br />";
echo "code: ".$error[ 'code']."<br />";
echo "message: ".$error[ 'message']."<br />";
}
}
}
?>
NOTES
By default, warnings generated on a call to any SQLSRV function are treated as errors. This means that if a warning occurs on a call to a
SQLSRV function, the function returns FALSE. However, warnings that correspond to SQLSTATE values 01000, 01001, 01003, and 01S02 are never
treated as errors. For information about changing this behavior, see sqlsrv_configure(3) and the WarningsReturnAsErrors setting.
SEE ALSO
sqlsrv_configure(3).
PHP Documentation Group SQLSRV_ERRORS(3)