Home » SQL & PL/SQL » SQL & PL/SQL » Escape Single Codes in Dynamic Script (Oracle 11g)
Escape Single Codes in Dynamic Script [message #433684] |
Thu, 03 December 2009 12:07  |
faiz_hyd
Messages: 294 Registered: February 2006
|
Senior Member |
|
|
I have this script to generate some insert statements for table
to send across over other DB.
SET HEAD OFF
SET FEEDBACK OFF
SET PAGES 0
SET ECHO OFF
SET SHOW OFF
SET VERIFY OFF
SET SCAN ON
SET LINES 500
SET CONCAT +
Spool &3
SELECT 'Insert into &1.PROD_PREF@&2
(PREF_CD,
PROD_DIM_NB,
PROD_PREF_VAL_TX,
DFLT_IN,
STS_CD,STS_MOD_DT,CRE_USR_ID,CRE_DT,UPDT_USR_ID,UPDT_DT
) values ('''
|| PPREF.PREF_CD ||''','
|| PPREF.PROD_DIM_NB || ','''
|| PPREF.PROD_PREF_VAL_TX || ''','''
|| PPREF.DFLT_IN ||''','''
|| PPREF.STS_Cd ||''','''
|| to_Char(PPREF.STS_MOD_DT,'YYYY-MM-DD') || ''','''
|| PPREF.CRE_USR_ID ||''','''
|| to_Char(PPREF.CRE_DT,'YYYY-MM-DD') ||''','''
|| PPREf.UPDT_USR_ID ||''','''
|| to_Char(PPREF.UPDT_DT,'YYYY-MM-DD') || '''' || ');'
From hds01.PROD_PREF PPREF
WHERE prod_dim_nb <> 0 and
NOT EXISTS (SELECT 1 FROM &1.PROD_PREF@&2 iris
where
PPREF.PREF_CD=trim(iris.pref_Cd)
and PPREF.PROD_DIM_NB=iris.prod_dim_nb);
SELECT 'commit;' FROM DUAL;
spool off
And this gives me , insert statment like this, all insert went fine, but some has a Additional Single Codes , coming from source for this column PROD_PREF_VAL_TX ( varchar2 Datatype ).
I tired with SET DEFINE OFF also, it doesn't work,
I am generating this in Oracle and running from oracle Db, but inserts will happen in remote DB2 Database.
Insert into IRIDBHC5.PROD_PREF@DRDA
(PREF_CD,
PROD_DIM_NB,PROD_PREF_VAL_TX,
DFLT_IN,STS_CD,STS_MOD_DT,CRE_USR_ID,
CRE_DT,UPDT_USR_ID,UPDT_DT ) values ('numberFormat',523,'''pref.global.numberFormat.9'999.99.value','N','ACTIVE','2009-09-21','PRELOAD','2009-09-1',
'PRELOAD','2009-09-21');
If i know this 1 record i can manually add additional codes and run it, but in a dynamic script how can i use Escape here,
I know data is wrong there but this is coming from source, so no question of removing it from tables first before running the script.And dates in Single Codes, that we are sending to DB2 so don't worry about Dates that's the way it stores in DB2.
Please let me know what can be done here, if Escape can be used here how,
Thanks
|
|
|
|
|
Re: Escape Single Codes in Dynamic Script [message #433725 is a reply to message #433691] |
Thu, 03 December 2009 18:40  |
andrew again
Messages: 2577 Registered: March 2000
|
Senior Member |
|
|
Well - you need to be sure what characters to expect in your strings first. Single quotes, doulbe quotes, $, %, _, CR, LF, & etc can all cause issues. If it was Oracle to Oracle inserts, you could encoding & decoding. Maybe DB2 has functions to decode base64 strings too?
SQL> column encoded format a30
SQL> column decoded format a30
SQL> select sys.utl_encode.base64_encode(sys.utl_raw.cast_to_raw('''$%"abc')) encoded,
2 sys.utl_raw.cast_to_varchar2(sys.utl_encode.base64_decode(
3 sys.utl_encode.base64_encode(sys.utl_raw.cast_to_raw('''$%"abc'))
4 )) decoded
5 from dual;
ENCODED DECODED
------------------------------ ------------------------------
4A79516C496D466959773D3D '$%"abc
SQL>
|
|
|
Goto Forum:
Current Time: Tue Feb 18 14:24:10 CST 2025
|