Sponsored Content
Full Discussion: Shell Script And SQLPLUS
Top Forums UNIX for Dummies Questions & Answers Shell Script And SQLPLUS Post 302133746 by radoulov on Saturday 25th of August 2007 05:39:38 AM
Old 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 Smilie

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

passing parameters from a shell script to sqlplus

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

running shell script from sqlplus

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

help me in sending parameters from sqlplus script to unix shell script

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

Call sqlplus in the shell script

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

(Urgent):Creating flat file using sql script and sqlplus from UNIX Shell Script

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

SQLPLUS within shell script

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

Want to learn/use SQLPLUS in shell script

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

SQLplus and Shell script problem

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

sqlplus in shell script

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

Sqlplus in shell script

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
MDBTools(1)															       MDBTools(1)

NAME
mdb-sql - SQL interface to MDB Tools SYNOPSIS
mdb-sql [-HFp] [-d delimiter] [-i file] [-o file] [database] DESCRIPTION
mdb-sql is a utility program distributed with MDB Tools. mdb-sql allows querying of an MDB database using a limited SQL subset language. OPTIONS
-H Supress header row. -F Supress footer row. -p Turn off pretty printing. By default results are printed in an ascii table format which looks nice but is not conducive to manipu- lating the output with unix tools. This option prints output plainly in a tab separated format. -d Specify an alternative column delimiter. If no delimiter is specified, columns will be delimited by a tab character if pretty print- ing (-p) is turned off. If pretty printing is enabled this option is meaningless. -i Specify an input file. This option allows an input file containing the SQL to be passed to mdb-sql. See Notes. -o Specify an output file. This option allows the name of an output file to be used instead of stdout. COMMANDS
mdb-sql in interactive mode takes some special commands. connect to <database> If no database was specified on the command line this command is necessary before any querys are issued. It also allows the switch- ing of databases once in the tool. disconnect Will disconnect from the current database. go Each batch is sent to the parser using the 'go' command. reset A batch can be cleared using the 'reset' command. list tables The list tables command will display a list of available tables in this database, similar to the mdb-tables utility on the command line. describe table <table> Will display the column information for the specified table. quit Will exit the tool. SQL LANGUAGE
The currently implemented SQL subset is quite small, supporting only single table queries, no aggregates, and limited support for WHERE clauses. Here is a brief synopsis of the supported language. select: SELECT [* | <column list>] FROM <table> WHERE <where clause> column list: <column> [, <column list>] where clause: <column> <operator> <literal> [AND <where clause>] operator: =, =>, =<, <>, like, <, > literal: integers, floating point numbers, or string literal in single quotes NOTES
When passing a file (-i) or piping output to mdb-sql the final 'go' is optional. This allow constructs like echo "Select * from Table1" | mdb-sql mydb.mdb to work correctly. The -i command can be passed the string 'stdin' to test entering text as if using a pipe. ENVIRONMENT
MDB_JET3_CHARSET Defines the charset of the input JET3 (access 97) file. Default is CP1252. See iconv(1). MDBICONV Defines the output charset. Default is UTF-8. mdbtools must have been compiled with iconv. MDBOPTS semi-column separated list of options: o use_index o no_memo o debug_like o debug_write o debug_usage o debug_ole o debug_row o debug_props o debug_all is a shortcut for all debug_* options HISTORY
mdb-sql first appeared in MDB Tools 0.3. SEE ALSO
gmdb2(1) mdb-export(1) mdb-hexdump(1) mdb-prop(1) mdb-ver(1) mdb-array(1) mdb-header(1) mdb-parsecsv(1) mdb-schema(1) mdb-tables(1) AUTHORS
The mdb-sql utility was written by Brian Bruns. BUGS
The supported SQL syntax is a very limited subset and deficient in several ways. 0.7 13 July 2013 MDBTools(1)
All times are GMT -4. The time now is 09:34 AM.
Unix & Linux Forums Content Copyright 1993-2022. All Rights Reserved.
Privacy Policy