any possible solution on sql calling scripts

manas_ranjan · #1 (**permalink**) 11-21-2007

hi all,

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

HTML Code:

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,

HTML Code:

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...........

Cameron · #2 (**permalink**) 11-21-2007

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.

How to check sqlplus command is successfull or not in HP UX?

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

manas_ranjan · #3 (**permalink**) 11-22-2007

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,

HTML Code:

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.

ranj@chn · #4 (**permalink**) 11-22-2007

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

manas_ranjan · #5 (**permalink**) 11-22-2007

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

thanks a lot....ranj......

More UNIX and Linux Forum Topics You Might Find Helpful
Thread	Thread Starter	Forum	Replies	Last Post
difference in calling shell scripts	arpit_narula	SUN Solaris	6	09-07-2008 03:46 PM
Issue calling scripts through CRON.	vskr72	Shell Programming and Scripting	5	01-07-2008 08:12 PM
Calling SQL LDR and SQL plus scripts in a shell script	rajagavini	Shell Programming and Scripting	5	11-05-2007 06:12 PM
Calling functions in scripts directly	LiquidChild	Shell Programming and Scripting	12	04-27-2007 06:28 AM
Calling SQL scripts through Shell Script	madhunk	Shell Programming and Scripting	18	06-14-2006 12:35 PM

Thread Tools	Search this Thread
Show Printable Version Email this Page	Search this Thread: Advanced Search
Display Modes	Rate This Thread
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode	Rate This Thread: