Gel Query special characters

[Rajani]

Hello,

I need to use the following query in a gel script and its failing due to the use of special characters. Please advice on how to reformat the query.

select regexp_substr('name1;name2;name3;name4','[^;]+', 1, level) as res from dual
connect by regexp_substr('name1;name2;name3;name4', '[^;]+', 1, level) is not null;

Thank you,
Rajani.

[Eugene Greiff]

I typically wrap my query in CDATA to prevent it from being parsed.

<sql:query dataSource="${clarityDS}" var="queryResults">
<![CDATA[
select regexp_substr('name1;name2;name3;name4','[^;]+', 1, level) as res from dual
connect by regexp_substr('name1;name2;name3;name4', '[^;]+', 1, level) is not null;
]]>
</sql:query>

V/r,
Gene

[Rajani]

I tried CDATA and got the following error. I am wondering if i need to escape the special chars.

Caught Exception was: org.apache.commons.jelly.JellyTagException: null:13:39: <sql:query> select regexp_substr('name1;name2;name3;name4','[^;]+', 1, level) as res from dual connect by regexp_substr('name1;name2;name3;name4', '[^;]+', 1, level) is not null;: [CA Clarity][Oracle JDBC Driver][Oracle]ORA-00911: invalid character

[Anon Anon]

Is that " invalid character" just the semi-colon you have at the end of your SQL string - if I remember right you don't need this when calling SQL from GEL. :unsure:

[Rajani]

CDATA worked actually. Query should not contain a semi colon.-_-