06-25-2009
Bash executing Orcale Update statement
Hi All,
Using Solaris box
bash-3.00$ echo $BASH_VERSION
3.00.16(1)-release
I have a real bummer of a bug, basically Im running a bash script that executes a bash function "dbase_sql". The bash function accepts a parameter in the form of an Oracle update statement eg
dbase_sql "update dte_batch_details
set row_count='$row_count'
, checksum_value='$checksum_value'
, load_attempts=NVL(load_attempts,0)+1
, pre_stage_errors='$pre_stage_errors'
, message='$sql_err_msg'
where object_name='$source_tabin_name'"
The function itself is as below
dbase_sql()
{
echo "dbase_sql() 1.1 "
local sql="$1"
echo "sql="$sql
echo "showed ECHO!"
sqlplus -s $DBUSER/$DBPASS@$DBNAME<<EOF
set serveroutput on size 1000000
set verify off
set feedback off
VARIABLE vi_err NUMBER
declare
begin
${sql};
commit;
:vi_err:=0;
exception
when others
then
:vi_err:=1;
end;
/
EXIT:vi_err
EOF
}
The perplexing thing is that I cannot get the call to the function to work in my main shell script, so I created a simple script that calls the function and that does seem to work! For some reason, the bug is that the echo does not show the full statement in the function and hence it passes the malformed statement to the oracle call which then fails.
Error from the shell is as follows:
dbase_sql() 1.1
' , load_attempts=NVL(load_attempts,0)+1 , pre_stage_errors='Y' , message='/#SQL Loader Bad File[/export/home/ORACLE/product/10.2.0/TORPEDO/LOGS/SQLLDR_bad_TOS_TABIN218.bad]' where object_name='TOS_TABIN218'
showed ECHO!
Thursday, 25 June 2009 14:06:43 BST #Application Error - Abort: Call to dbase_func.dbase_sql()
The working version shows:
dbase_sql() 1.1
sql=update dte_batch_details set row_count='1' , checksum_value='1' , load_attempts=NVL(load_attempts,0)+1 , pre_stage_errors='Y' , message='None/#SQL Loader Bad File[]/#SQL Loader Bad File[]/#SQL Loader Bad File[]/#SQL Loader Bad File[]/#SQL Loader Bad File[]/#SQL Loader Bad File[]/#SQL Loader Bad File[]/#SQL Loader Bad File[]' where object_name='TOS_TABIN218'
showed ECHO!
Any ideas?!
Kind Regards
Satnam
10 More Discussions You Might Find Interesting
1. UNIX for Dummies Questions & Answers
Dear Sir,
I am working in a bank and our parent bank has developed an application package which will work with oracle 8i on unix platform. The unix on the server is unix ware 7.1.1 with pro*c compiler. My question is
1. Whether Oracle 8i or above for windows will work on any unix version... (1 Reply)
Discussion started by: Parameswaran.AR
1 Replies
2. Shell Programming and Scripting
Hi All,
I need to select one column from a table based upon the passed in parameter.
I tried this:
sqlplus -silent $MISP_USER << EOF
set feedback off;
set verify off;
set sqlprompt ""
SELECT mail_flag
FROM dailyjobs
WHERE job_name = '$1';
exit 0
EOF
exit... (1 Reply)
Discussion started by: ganga.dharan
1 Replies
3. Shell Programming and Scripting
Hi,
I have a piece of shell script which will connect to mysql database and execute a load statement(which will load datas in a file to the database table).The code is working and the data is in the tables.
Now my requirement is, i need to grab the output from the load statement... (4 Replies)
Discussion started by: DILEEP410
4 Replies
4. Programming
The problem I was working on is solved, but felt it would be worthwhile to ask for some opinions as to whether the approach can actually be improved.
I am using the following example and data taken from https://www.unix.com/high-level-programming/119134-sql-datetime-calculations.html and MySQL... (0 Replies)
Discussion started by: figaro
0 Replies
5. Shell Programming and Scripting
Hi,
How to execute sql statements from the .sh file ??
Means, when we run .sh file then the sql statements within it should be get executed one by one from the sqlplus
With Regards (3 Replies)
Discussion started by: milink
3 Replies
6. Homework & Coursework Questions
Use and complete the template provided. The entire template must be completed. If you don't, your post may be deleted!
1. The problem statement, all variables and given/known data:
Create a phonebook program.
It should use functions to perform the required tasks. It should be menu-based,... (1 Reply)
Discussion started by: Rgasin02
1 Replies
7. Red Hat
Hi
i want to update the BASH because of the "shell shock" vulnerability.
my RedHat 5 is clean install with the default mirror site.
when im running the command: yum update bash
im getting a message saying there is no update. you can see in the attach picture...
what am i doing wrong? is... (4 Replies)
Discussion started by: guy3145
4 Replies
8. Shell Programming and Scripting
Hi,
i want to do the following:
Grep the following kind of strings for the 15digit ID which is stored in filename1:
"14:06:51.396 INFO BMCREMEDYSD INPUT-ACTION Failed to retrieve Remedy Incident Modification record: 000000000039047 org.apache.axis2.AxisFault: Read timed out - complete... (9 Replies)
Discussion started by: Khushbu
9 Replies
9. Shell Programming and Scripting
Hi folks,
I have a scenario to convert the update statements into insert statements using shell script (awk, sed...) or in database using regex.
I have a bunch of update statements with all columns in a file which I need to convert into insert statements.
UPDATE TABLE_A SET COL1=1 WHERE... (0 Replies)
Discussion started by: dev123
0 Replies
10. Shell Programming and Scripting
i have a "if .. then exit end " in s shell script on remote servers.
now the connection to the remote server got killed after i run this script on the remote servers. How do i run this script on remote hosts and still keep remote connections alive after executing the script.
Thank you. (10 Replies)
Discussion started by: moonmonk
10 Replies
LEARN ABOUT POSIX
libbash
LIBBASH(7) libbash Manual LIBBASH(7)
NAME
libbash -- A bash shared libraries package.
DESCRIPTION
libbash is a package that enables bash dynamic-like shared libraries. Actually its a tool for managing bash scripts whose functions you may
want to load and use in scripts of your own.
It contains a 'dynamic loader' for the shared libraries ( ldbash(1)), a configuration tool (ldbashconfig(8)), and some libraries.
Using ldbash(1) you are able to load loadable bash libraries, such as getopts(1) and hashstash(1). A bash shared library that can be loaded
using
ldbash(1) must answer 4 requirments:
1. It must be installed in $LIBBASH_PREFIX/lib/bash (default is /usr/lib/bash).
2. It must contain a line that begins with '#EXPORT='. That line will contain (after the '=') a list of functions that the library
exports. I.e. all the function that will be usable after loading that library will be listed in that line.
3. It must contain a line that begins with '#REQUIRE='. That line will contain (after the '=') a list of bash libraries that are
required for our library. I.e. every bash library that is in use in our bash library must be listed there.
4. The library must be listed (For more information, see ldbashconfig(8)).
Basic guidelines for writing library of your own:
1. Be aware, that your library will be actually sourced. So, basically, it should contain (i.e define) only functions.
2. Try to declare all variables intended for internal use as local.
3. Global variables and functions that are intended for internal use (i.e are not defined in '#EXPORT=') should begin with:
__<library_name>_
For example, internal function myfoosort of hashstash library should be named as
__hashstash_myfoosort
This helps to avoid conflicts in global name space when using libraries that come from different vendors.
4. See html manual for full version of this guide.
AUTHORS
Hai Zaar <haizaar@haizaar.com>
Gil Ran <ril@ran4.net>
SEE ALSO
ldbash(1), ldbashconfig(8), getopts(1), hashstash(1) colors(1) messages(1) urlcoding(1) locks(1)
Linux Epoch Linux