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 Go to next message
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
icon10.gif  Re: Escape Single Codes in Dynamic Script [message #433686 is a reply to message #433684] Thu, 03 December 2009 12:15 Go to previous messageGo to next message
LKBrwn_DBA
Messages: 487
Registered: July 2003
Location: WPB, FL
Senior Member
Try this:
-- etc --
|| REPLACE(PPREF.PROD_PREF_VAL_TX,'''','''''') || ''','''
-- etc --


Shocked
Re: Escape Single Codes in Dynamic Script [message #433691 is a reply to message #433684] Thu, 03 December 2009 12:25 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
The little ticks you are talking about are called quotes, not codes.
I wonder what combination of values can cause this outcome..
Re: Escape Single Codes in Dynamic Script [message #433725 is a reply to message #433691] Thu, 03 December 2009 18:40 Go to previous message
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>

Previous Topic: REGEXP_LIKE for ?
Next Topic: question regarding the data type
Goto Forum:
  


Current Time: Sun Sep 25 06:07:52 CDT 2016

Total time taken to generate the page: 0.05433 seconds