topic using execute immediate in toraclerow with single quotes in query in Talend Studio

using execute immediate in toraclerow with single quotes in query

_AnonymousUser — Wed, 21 Aug 2013 11:18:14 GMT

i am trying to execute create table statement in toraclerow component using execute immediate statement. create table statement contains code for creating partitions in which partition is done by date
(PARTITION M201002 VALUES LESS THAN (TO_DATE(' 2010-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI S', 'NLS_CALENDAR=GREGORIAN')) and this code has single quotes.
when we use execute immediate we elclose the code in single quotes but the code which i am executing has single quotes so can any body suggest a way to do this?
declare
v_cnt number :=0;
begin
select count(*) into v_cnt from user_tables where table_name='tab1';
if v_cnt > 0 then
execute immediate 'truncate table tab1';
else
execute immediate 'create table statement with partition';
end if;
end;

Re: using execute immediate in toraclerow with single quotes in query

Anonymous — Thu, 22 Aug 2013 15:06:59 GMT

Hi
I did't test, but you can try to add to add \ to escape the single quote in the partition if you get problem to execute this query with tOracleRow, for example
(PARTITION M201002 VALUES LESS THAN (TO_DATE(\' 2010-03-01 00:00:00\', \'SYYYY-MM-DD HH24:MI S\', \'NLS_CALENDAR=GREGORIAN\'))
An alternative way is to create a store procedure and use tOracleSP to call this store procedure.
Shong

Re: using execute immediate in toraclerow with single quotes in query

Anonymous — Thu, 22 Aug 2013 16:11:32 GMT

Hello,
Not being sure if this is what you are looking for from your original post - but just in case: at Oracle level, simple quotes contained in string constants should be Oracle-style escaped (by doubling the simple quote), or the string should be specified with the quote operator.
e.g.: (within Oracle, so for example whilst entering a statement in sqlplus or creating a pl/sql procedure in the database)
execute immediate 'update mytab set mycol=select 'a' '; => error (parsed as two strings separated by the letter "a")
execute immediate 'update mytab set mycol=select ''a'' '; => ok (each simple quote inside the string is escaped by being doubled)
execute immediate q'$update mytab set mycol=select 'a'$' => ok in recent versions of Oracle (q is the quote operator)
About the latter method : q'$ starting a string (instead of just a simple quote) means that:
* the string end is now expected to be $' instead of just a simple quote
* any simple quote in between loses its usual end-of-string delimiter role.
Note that there are additional possibilities (other characters than $ can be used,...) - see Oracle documentation for full details.
And actually, combining this with the previous comment, in tOracleRow you may end up having to do something like: (I won't be able to test this myself for the moment, sorry!)

execute immediate \' ....
(PARTITION M201002 VALUES LESS THAN (TO_DATE(\'\' 2010-03-01 00:00:00\'\', \'\'SYYYY-MM-DD HH24:MIS\'\', \'\'NLS_CALENDAR=GREGORIAN\'\')) .... \'

My apologies if this was obvious to you already !
Didier