How to escape & character in Oracle query

Question

 SELECT DISTINCT ENTRY_GUID FROM 
       (SELECT e.ENTRY_GUID FROM alertwlnqedv3.ENTRY e,
                                 alertwlnqedv3.ENTRY_PARTNERDEFINED_INFO ep,
                                 alertwlnqedv3.PRODUCT p
        WHERE e.ENTRY_GUID = ep.ENTRY_GUID AND 
              e.PRODUCT_CODE = p.PRODUCT_CODE AND
             (XMLEXISTS('/partnerDefinedInfo/facetedIds/alertGroup[text()=
             ("Colleen&s Main Web2 QA Folder")]' PASSING PARTNERDEFINED_INFO) ) AND 
              e.STATUS_CODE != 13 AND 
              p.PRODUCT_VALUE =1)

Am trying to run the above query - where Colleen&s Main Web2 QA Folder is the text entered on a run time. Now as it allows the special character & the query is breaking. I tried to escape with \ and one more & but still it fails. Please let me know how can I handle this scenario. TIA.

Possible duplicate of [Escaping ampersand character in SQL string](http://stackoverflow.com/questions/12961215/escaping-ampersand-character-in-sql-string) — Pavel Gatnar, Oct 12 '15 at 11:48
No luck with this too.....Getting error "missing right parenthesis" — Rajesh Bhat, Oct 12 '15 at 11:58

Marcx · Answer 1 · 2015-10-12T11:58:58.233

3

Try to use chr(38) instead of &

SELECT DISTINCT ENTRY_GUID FROM 
       (SELECT e.ENTRY_GUID FROM alertwlnqedv3.ENTRY e,
                                 alertwlnqedv3.ENTRY_PARTNERDEFINED_INFO ep,
                                 alertwlnqedv3.PRODUCT p
        WHERE e.ENTRY_GUID = ep.ENTRY_GUID AND 
              e.PRODUCT_CODE = p.PRODUCT_CODE AND
             (XMLEXISTS(('/partnerDefinedInfo/facetedIds/alertGroup[text()=
             ("Colleen'||chr(38)||'s Main Web2 QA Folder")]') PASSING PARTNERDEFINED_INFO) ) AND 
              e.STATUS_CODE != 13 AND 
              p.PRODUCT_VALUE =1)

edited Oct 12 '15 at 11:58

answered Oct 12 '15 at 11:45

Marcx

6,806
5
46
69

this is also trowing error "missing right parenthesis" – Rajesh Bhat Oct 12 '15 at 11:47
try to enclose the entire string/xml with `()` – Marcx Oct 12 '15 at 11:58

Pavel Gatnar · Answer 2 · 2015-10-12T12:38:54.250

1

Simply use this pattern:

'text1&' || 'text2'

edited Oct 12 '15 at 12:38

answered Oct 12 '15 at 11:41

Pavel Gatnar

3,987
2
19
29

score 1 · Answer 3 · answered Oct 12 '15 at 14:34

1

As you are using that value inside an XPath expression, you need to escape the ampersand using an XML entity. So you need to replace every & in your input string with &

You also need to take care of other special XML characters:

& - &
< - <
> - >
" - "
' - '

answered Oct 12 '15 at 14:34

Can you please elaborate little bit? I tried replacing & by & but Oracle is prompting me to enter a value for AMP. – Rajesh Bhat Oct 13 '15 at 13:16
@RajeshBhat: it's not "Oracle" that prompts for a variable, it's SQL*Plus: http://stackoverflow.com/q/118190/330315 – Oct 13 '15 at 13:22
Oh ohk, am running the query in SQLDeveloper. So how can I stop that? I need to run the query from java application too. – Rajesh Bhat Oct 13 '15 at 13:31
2

@RajeshBhat: I don't use SQL Developer but I think `set define off`should also work. You need to read the manual. This is a client application thing, so your Java application won't be affected – Oct 13 '15 at 13:42
@RajeshBhat I can confirm `set define off` works also in Oracle SQLDeveloper (I had to use it just last week). There might also be a SQLDeveloper specific configuration option for the same thing. – user272735 Oct 14 '15 at 06:59
after running set define off it started throwing error without any prompt. Error is : ORA-19112: error raised during evaluation: ORA-06550: line 1, column 13: PLS-00201: identifier 'SYS.DBMS_XQUERYINT' must be declared ORA-06550: line 1, column 7: PL/SQL: Statement ignored 19112. 00000 - "error raised during evaluation: %s" *Cause: The error function was called during evaluation of the XQuery expression. *Action: Check the detailed error message for the possible causes. – Rajesh Bhat Oct 14 '15 at 12:51

How to escape & character in Oracle query

3 Answers3

Related