Clarity PPM

Expand all | Collapse all

Gel Query special characters

Jump to Best Answer
  • 1.  Gel Query special characters

    Posted 10-17-2013 02:33 PM
    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.


  • 2.  RE: Gel Query special characters
    Best Answer

    Posted 10-17-2013 05:08 PM
    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


  • 3.  RE: Gel Query special characters

    Posted 10-18-2013 10:44 AM
    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


  • 4.  RE: Gel Query special characters

    Posted 10-18-2013 11:02 AM
    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:


  • 5.  RE: Gel Query special characters

    Posted 10-18-2013 11:12 AM
    CDATA worked actually. Query should not contain a semi colon.-_-