any possible solution on sql calling scripts

Shell Programming and Scripting
1

hi all,

i have a function which will take i/p as a ddl sctipt as i/p and execute it,
let

function execute_sql
{
db_var="$1"
v_cnt=`sqlplus -s XXXXX/XXXXX@aXXX << ENDSQL | sed -e "s/Connected\.//" -e "/^$/d"
set pagesize 0 feedback off verify off heading off echo off  serveroutput on size 10000
whenever sqlerror exit SQL.SQLCODE;
$db_var
commit;
exit ;
ENDSQL`
db_var=""
}

if i am executing a wrong ddl script, then i am not able to catch the retrun code as 1 rather than it throws me 0 if i am executing the ddl thru the script function.

let my ddl which is wrong is ,
INSERT INTO CBF_CONFIG ( CS_ID, CS_NAME, FIELD_SEP, REC_SEP, FIELDS_NUM, FTPS_FILE, DECSN_FMT_TYPE, DECSN_FIELDS, DECSN_SCHDL_TYPE, DECSN_SCHDL, DECSN_TIME, DECSN_UNITWS, UNIQ_FILE_CONVNS, OUTBOX_RETN, UPDATE_TIME ) VALUES
('MANASMANAS1','SCORENETHERLANDS','|','NULL','15','N','STANDARD','NULL','DAILY','NULL','14:00','N','Y','1',SYSDATE) ;
the o/p will as follows if i am executing it thru simple sqlplus environment,
('MANASMANAS1','SCORENETHERLANDS','|','NULL','15','N','STANDARD','NULL','DAILY','NULL','14:00','N','Y','1',SYSDATE)
*
ERROR at line 2:
ORA-12899: value too large for column "XXXXX"."CBF_CONFIG"."CS_ID"
(actual: 11, maximum: 10)

but if i am going to call it thru the above shell functions,

insert_sql="INSERT INTO CBF_CONFIG ( CS_ID, CS_NAME, FIELD_SEP, REC_SEP, FIELDS_NUM, FTPS_FILE, DECSN_FMT_TYPE, DECSN_FIELDS, DECSN_SCHDL_TYPE, DECSN_SCHDL, DECSN_TIME, DECSN_UNITWS, UNIQ_FILE_CONVNS, OUTBOX_RETN, UPDATE_TIME ) VALUES
('MANASMANAS1','SCORENETHERLANDS','|','NULL','15','N','STANDARD','NULL','DAILY','NULL','14:00','N','Y','1',SYSDATE) ; "
echo $insert_sql

execute_sql "$insert_sql"
rc=$?
echo "exit code $rc"

then rc is 0, but v_cnt is "ERROR at line 2:
ORA-12899: value too large for column "CLIFBACONFIG"."CBF_CONFIG"."CS_ID"
(actual: 11, maximum: 10)"

my question is why i am not able to catch SQL.SQLCODE even though in the function there is a line whenever sqlerror exit SQL.SQLCODE...
any suggestion to improve the error catching session...........

2

It's highly likely that you didn't search the forums for 'sqlplus' .

Whilst this may not answer all your questions, it might put you on the right path.

http://www.unix.com/hp-ux/45416-how-check-sqlplus-command-successfull-not-hp-ux.html

Trying to perform SQL in the way you've shown is really hard to manage over time.

In future, make use of the search feature in these forums.
You questions is more than likely to have been asked before in the past.

Cheers,
Cameron :wink:

3

thankx Cameron.

I have gone thru the all the search...but not reffered anyone......as any of them not met my requirements
since i post it with confusing statements...sorry for that.

actually i should ask, even though whenever sqlerror exit SQL.SQLCODE is there why i am not able to catch that SQL.SQLCODE after executing the function ???? why rc is showing 0 as we all know i am executing wrong ddl(s) , why not SQL.SQLCODE as rc ????? i will be thankful if you can explain me....

NOTE : i have not used any spool file, o/p logging file..rather than i checked the ERROR/output by passing the o/p to v_cnt as follows,

if [ `echo "$v_cnt" | grep "ORA-" | wc -l` -ne 0 ]
then
echo "`basename $0` wrong execution possible reason is :-`echo "$v_cnt" | grep "ORA-" | awk -F":" '{print $2}'`"
exit 1
fi

again sorry for misleading/confusing statements......next time i will try to take care of this.

4

The problem is with your sed. It runs fine and thats why the return code is zero. Remove the sed and check your results.

5

you are absolutely right......after removing sed ...i got the exact answer as expected.

thanks a lot....ranj......:wink: