|
|
|
|
|||||||
| Forums | Portal | Register | Forum Rules | FAQ | Contribute | Members List | Arcade | Search | Today's Posts | Mark Forums Read |
| Shell Programming and Scripting Post questions about KSH, CSH, SH, BASH, PERL, PHP, SED, AWK and OTHER shell scripts here. |
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 | 4 Weeks Ago 12:46 PM |
| Issue calling scripts through CRON. | vskr72 | Shell Programming and Scripting | 5 | 01-07-2008 04:12 PM |
| Calling SQL LDR and SQL plus scripts in a shell script | rajagavini | Shell Programming and Scripting | 5 | 11-05-2007 02:12 PM |
| Calling functions in scripts directly | LiquidChild | Shell Programming and Scripting | 12 | 04-27-2007 03:28 AM |
| Calling SQL scripts through Shell Script | madhunk | Shell Programming and Scripting | 18 | 06-14-2006 09:35 AM |
 |
|
|
Submit Tools | LinkBack | Thread Tools | Display Modes |
11-21-2007
|
||||
|
||||
|
any possible solution on sql calling scripts
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=""
}
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"
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........... Last edited by manas_ranjan; 11-22-2007 at 01:06 AM. Reason: remove login/pwd@dbname 
|
| Forum Sponsor | ||
|
|
|
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 
|
|
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
Last edited by manas_ranjan; 11-22-2007 at 01:08 AM.
|
|
11-22-2007
|
||||
|
||||
you are absolutely right......after removing sed ...i got the exact answer as expected.
thanks a lot....ranj......
|
||||
| Google The UNIX and Linux Forums |
Linear Mode
