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...........
11-21-2007
