Kayshav.com
About Developer Oracle 19c Oracle 12c Technology Information Sitemap

Oracle Query using WITH clause based function
-- Oracle 19c: Function to manage JSON data using WITH clause 
-- Convert some country amount to US dollar based on exchange rate 

WITH
 FUNCTION convertCurrency(jsonStr IN VARCHAR2, 
       i_conv_rate  IN NUMBER)
 RETURN VARCHAR2
 IS
   jobj        JSON_OBJECT_T;
   amount_ww   NUMBER;

 BEGIN
   jobj := new JSON_OBJECT_T(jsonStr);
   amount_ww := jobj.get_number('value_ww'); 
   jobj.put('value_us', amount_ww * i_conv_rate);
  
   RETURN  jobj.to_string();
 END convertCurrency;
SELECT
  convertCurrency(qt.json_str, qt.conv_rate)
FROM 
  (SELECT
    '{"value_us": 0.0,
      "value_ww": 5.0}' json_str 
    ,0.205  conv_rate
   FROM DUAL) qt
;

JSON_OBJECT_T


  Oracle 19c Index

   Parse String for JSON

  Lower Versions   11g Index   12c Index

Oracle registered trademark of Oracle Corporation.

Last Revised On: January 31th, 2021

  4328