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

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 04: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 07:28 AM
Calling SQL scripts through Shell Script	madhunk	Shell Programming and Scripting	18	06-14-2006 01:35 PM

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


	Powered by