SQL in shell script (How to trap ORA error)

[nitincm]

In shell script I am inserting the record in to the table.After excuation the script output is commit complete which is fine but I need my echo should be display like "Account created"
#!/bin/ksh
.
.
.
inst dev000
sqlplus -s username/password EOF

Insert into table (column1,column2,column3) values ($gblid,$sysid,$crdate);
commit;
exit
EOF
=============================
Script Output #1:
commit complete.
Instead of above line o/p should be "Account created".
How I can do this?
============================

============================
Script Output#2:
ORA-00001: unique constraint violated
If output is like this then o/p should be "Account can't created"
How I can do this?
============================

How I can trap oracle error?

 

[fredericofonseca]

redirect the output to a file, then grep the file for the strings.

something on the lines of

sqlblabla >> outsql 2>>outsql

if [grep -i commit outsql]
do
blabla
done

where the blabla is whatver output you wish to give the user
(not sure about the syntax though




Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[LKBrwnDBA]

Try this:

sqlplus -s username/password <<EOF >SQL_Exec.log

set serveroutput on size 1000000;
Begin
Insert into table (column1,column2,column3) values ($gblid,$sysid,$crdate);
commit;
dbms_output.put_line('Account Created.');
Exception
When others Then
dbms_output.put_line('Account cannot be Created.');
end;
/
exit
EOF

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[LKBrwnDBA]

NOTE: if these values &quot;$gblid,$sysid,$crdate&quot; represent Unix
environment variables, then you will need to pass
them to sqlplus as parameters.

Something like this:

sqlplus -s /nolog &quot;$gblid&quot; &quot;$sysid&quot; &quot;$crdate&quot; <<EOF >SQL_Exec.log
conn username/password

set serveroutput on size 1000000;
Begin
Insert into table (column1,column2,column3) values ('&1','&2','&3');
commit;
dbms_output.put_line('Account Created.');
Exception
When others Then
dbms_output.put_line('Account cannot be Created.');
end;
/
exit
EOF

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[Annihilannic]

Not necessarily, LK.

[tt]$ TESTVAR=blah
$ cat << HERE
> The variable is $TESTVAR
> HERE
The variable is blah
$[/tt]

Annihilannic.

 

[LKBrwnDBA]

anni,

Before you comment you should read the complete requirements.

In the example from nitincm, UNIX environment variables are used within the execution of the oracle 'sqlplus' program, and which causes an execution error.


----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[nitincm]

Hi Guys, Thank u very much for ur support....Nitin

 

[Annihilannic]

LK,

I did read the complete requirements, and I realise they are within the SQL programme.

The script is supplied to sqlplus using a &quot;here&quot; document, upon which the shell performs variable substitution before passing it to the standard input of sqlplus. Provided that the shell environment variables contain valid values for insertion in the table I see no reason why it shouldn't work.

In any case your method is probably neater and less error prone.

Annihilannic.

 

[MikeLacey]

There's a FAQ on this actually

Mike

Want to get great answers to your Tek-Tips questions? Have a look at faq219-2884

It's like this; even samurai have teddy bears, and even teddy bears get drunk.