CODEWITHSUNDEEP
jsonoracle-databaseescaping
MB34
MB34

Reputation: 4404

Escaping ampersands in JSON for Oracle

We are sending JSON to an oracle function and sometimes it contains ampersands. I'd like to know if there is any other way to prevent the "variable substitution" problem because of the ampersand without having to modify the string to 

'{"this is the JSON &'||' it contains an ampersand"}'

I've tried these and they DO NOT WORK.

'{"this is the JSON && it contains an ampersand"}'

'{"this is the JSON /& it contains an ampersand"}'

'{"this is the JSON \\& it contains an ampersand"}'

Edit:

This is how we manually import in Toad:

declare
    vOut varchar2(400);
begin
    vOut:=CartJSON.RequestEntry('JSON HERE'); -- function to parse JSON
    dbms_output.put_line('Here:'||vOut);
end;

Upvotes: 1

Views: 1096

Answers (2)

Lalit Kumar B
Lalit Kumar B

Reputation: 49082

Update

OP is using TOAD and not SQL*Plus.

TOAD

In TOAD, there are three ways to execute statements without substituting a value for the ampersand(&):

  • Menu Level

View -> TOAD Options: go to the "execute/compile" node/item and uncheck the "Prompt for substitution variables" option.

  • Editor Level

Right click in the editor and uncheck the "Prompt for substitution variables" option.

  • Execute as script using set define off

Most of the GUI based tools like SQL Developer, TOAD etc. now support a lot of SQL*Plus commands and executing as script seems quite similar to that in SQL*Plus. However, it is very much possible that older versions of the GUI tool might not support the SQL*Plus commands.

SQL*Plus

The use of ampersand as a substitution variable is an Oracle SQL*Plus client feature.

In SQL*Plus you could do

  • SET DEFINE OFF

For example,

SQL> SET DEFINE OFF
SQL> SELECT '{"this is the JSON && it contains an ampersand"}' str FROM dual;

STR
------------------------------------------------
{"this is the JSON && it contains an ampersand"}

Or,

  • SET SCAN OFF

For example,

SQL> SET SCAN OFF
SQL> SELECT '{"this is the JSON && it contains an ampersand"}' str FROM dual;

STR
------------------------------------------------
{"this is the JSON && it contains an ampersand"}

SQL>

Or,

  • Alternatively, you could use CHR(38) for the ampersand.

For example,

SQL> SELECT '{"this is the JSON '|| chr(38)||chr(38) ||' it contains an ampersand"}' str FROM dual;

STR
------------------------------------------------
{"this is the JSON && it contains an ampersand"}

SQL>

Upvotes: 1

Boneist
Boneist

Reputation: 23588

Ok, so now we know you're using Toad, the issue is that you're trying to run the set define off as if it's a sql statement (Execute statement at caret / F9) and not as a SQLPlus statement (ie. as part of a script - Execute as script / F5). You have two options:

1: Run both statements as a script (F5):

set define off;

declare
    vOut varchar2(400);
begin
    vOut:=CartJSON.RequestEntry('JSON HERE'); -- function to parse JSON
    dbms_output.put_line('Here:'||vOut);
end;
/
  1. Click with the right mouse button on the editor window and turn off the Substition Variable Prompting, and then run your anonymous block as a single statement (F9).

Upvotes: 0

Related Questions