Update statement in Oracle excute for JSON format

[pranil2k2]

Hi,

I have an requirement to save the JSON column value in an Oracle DB. I am trying to use Oracle execute to design Update statement. I am using below syntax, but it is throwing error.
I think did we need to extra cautious to handle single quote and double quote, which is part of syntax.
Please someone can advice on this one.

“UPDATE SMRT_TEST SET xx_DOCUMENT = “'”{“TOTAL_CR_WRNO” :'” + $TOTAL_CREATED_WRNO + “'}”'“)”

Uploading: image.png…

[pranil2k2]

Or is there any another way to format three columns into JSON format and store the output into Oracle column.

[tstack]

pranil2k2:

I am trying to use Oracle execute to design Update statement. I am using below syntax, but it is throwing error.
I think did we need to extra cautious to handle single quote and double quote, which is part of syntax.
Please someone can advice on this one.

“UPDATE SMRT_TEST SET xx_DOCUMENT = “’”{“TOTAL_CR_WRNO” :’” + $TOTAL_CREATED_WRNO + “’}”’“)”

First, can I ask if there is a reason you are not using the Oracle Update snap?

If you do need to do this in the Execute snap, I would suggest that you not make the Statement property an expression since it is difficult to construct the statement correctly (as you have found out). You can do substitutions of values from the input document when the Statement is not a property by using a JSON-Path. So, you could put a Mapper before the Execute snap to generate the JSON string using an expression like the following:

JSON.stringify({TOTAL_CR_WRNO: $TOTAL_CREATED_WRNO})

If that expression was mapped to the output document as $xx_DOCUMENT, you can then reference it in the Execute snap’s Statement property like so:

UPDATE SMRT_TEST SET xx_DOCUMENT = $xx_DOCUMENT

[pranil2k2]

Hi Stack,

Thanks for the Input. i will try the option as said above. if the JSON.stringify populate the JSON string then i can use Oracle update snap to update in DB.

Thanks