Here's a very short testcase that demonstrates a technique of passing shell variables to Oracle SQL -
I'll let you figure out the details of tailoring it for your scripts.
A few observations about your script:
(a) The Oracle documentation recommends the use VARCHAR2 datatype instead of VARCHAR. Check the Concepts Guide or the SQL Reference guide in the documentation set.
(b) Your "if" condition is redundant. The record count of a table is always 0 or something greater than 0. It can *never* be negative. So your "if" condition will always be true. In fact, I am not sure why you'd want to run a count(*) query before inserting a record into a table!!
(c) The exception handler "when others then null;" is a classic logical bug. That's because "when others" is a catch-all for all exceptions that you either don't know about or don't care about.
These exceptions could be something completely unrelated to your code; serious things like:
ORA-01000: maximum open cursors exceeded due to cursor leakage that could bring your Oracle database to a grinding halt,
or
ORA-1652: unable to extend temp segment by 1024 in tablespace <blah>
which means your temp tablespace is full and requires DBA intervention
or
the dreaded ORA-00600 internal kernel errors that might require you to contact Oracle support
etc. You don't want to "swallow" such exceptions (NULL; statement does exactly that). You want to raise such exceptions to your client program as soon as possible so you or your DBA could do something about it.
Instead of a NULL; there should be a RAISE; statement in "when others then" exception handler. Or better yet, leave it out altogether, and Oracle will do the escalation for you.
It's something like this - if you are on a vacation, and your house catches fire, you'd want to know that as soon as possible! You'd want to let go of your vacation and get back as soon as possible. The NULL statement gives you the impression that everything is fine and dandy, whereas you'd come back to a charred house!
Finally, Neo's suggestion is noteworthy. Small Oracle projects (such as yours apparently) that deal mainly with strings or "string-like" data may be fine with Unix shells. But as the project increases in scope and volume, and as you keep on adding more and more complex datatypes - LOBs, XML, timestamps, Collections - nested tables, varrays etc. the use of a wrapper API like Perl DBI or PHP or JDBC will make your life easier. They are very robust, well tested and bind quite tightly with complex Oracle types.
tyler_durden
These 4 Users Gave Thanks to durden_tyler For This Post:
Hi All,
I am using Unix ksh script.
I need to insert values to a table using the o/p from a slelect statement.
Can anybody Help!
My script looks like tihs.
---`sqlplus -s username/password@SID << EOF
set heading off
set feedback off
set pages 0
insert into ${TB_NAME}_D... (2 Replies)
hi,
When i try to run the code below, i get the following error
"ksh: syntax error: `(' unexpected"
i am not able to figure it out. Can anyone help me?
Code: (2 Replies)
I have a form , where i will put the values to a table.
I wrote a insert statement for the same.
Table structure is
ename | character varying(30) |
eadd | character varying(30) |
eid | integer |
sal | integer
In the statements, i don't... (1 Reply)
I am trying to assign an select statement into a variable. Can someone hel me with this.
example :
a='select * from dual'
echo $a should give me select * from dual
But this is not working. I trying with \ before * and quotes too. (1 Reply)
Dear All,
we have a command output which looks like :
Total 200 queues in 30000 Kbytes
and we're going to get "200" and "30000" for further process. currently, i'm using :
numA=echo $OUTPUT | awk '{print $2}'
numB=echo $OUTPUT | awk '{print $5}'
my question is : can I use just one... (4 Replies)
Hi Guys,
I want to create a shell script that will give me the output below. I want to insert the numbers from the input file to my url addresses below. And from the numbers below, I want to separate the last digit with a period (i.e. from 222222222222 to 22222222222.2). Appreciate any help.... (14 Replies)
Hi Guys,
Can somebody help me in inserting today's DATE format (20110709) in my awk statement. I have a script but its not working.
inputfile.txt:
269,1,0,AAA,430
231,2,0,BBB,430
252,3,0,CCC,430
214,4,0,DDD,430
script.sh
#!/bin/bash
DATE="`date +%Y%m%d`"
cd /var/opt/ (8 Replies)
Hello,
I need to insert a space between 2 strings. I used many techniques and all of them worked but when I assign the value to another variable then the inserted space vanishes, strange! Please advise.
# dat=`date |awk '{print $2,$3}'`
# echo $dat
Nov 3
The above is perfectly fine. Now... (4 Replies)
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)
I have the following script, and I want to assign the output ($10 and $5) from awk to N and L:
grdinfo data.grd | awk '{print $10,$5}'| read N L
output from gridinfo data.grd is: data.grd 50 100 41 82 -2796 6944 0.016 0.016 3001 2461. where N and L is suppose to be 3001 and 100. I use... (8 Replies)