How to use bind variables with json.

froliol
2011-10-13
2013-05-28
  • froliol
    froliol
    2011-10-13

    Greetings, I have a simple example where I want to be able to use a bind variable.  However, I can't seem to make it work.  Here is an example of what I want to do:

    cl screen;

    set serveroutput on format wrapped;
    declare
      vSourceInternalID varchar2(100);
      obj json;
      procedure p(v varchar2) as begin dbms_output.put_line(null);dbms_output.put_line(v); end;
     
    begin
     
      vSourceInternalID := '56bea44e-2ddd-4aea-a79b-0b7e7e4dc5b3';
    - p(vSourceInternalID);
     
      p('RelationshipDO');
      obj := json(); -fresh json;
      obj.put('category', json_value.makenull );
      obj.put('sourceInternalId', json('{"string": vSourceInternalID}'));
      obj.put('endDate', json('{"java.util.Date": {}}'));
      obj.put('internalId', json_value.makenull);
      obj.print;
    end;
    /

    When I run this code I don't get the value assigned to "vSourceInternalID", I get the following:

    Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.2.0
    Connected as jsonuser@MIGRATEDB_MEDVA

    RelationshipDO
    {
      "category" : null,
      "sourceInternalId" : {
        "string" : "vSourceInternalID"
      },
      "endDate" : {
        "java.util.Date" : {
        }
      },
      "internalId" : null
    }

    PL/SQL procedure successfully completed

    SQL>

    Any and all help would be greatly appreciated.

     
  • Try this:

    set serveroutput on format wrapped;
    declare
      vSourceInternalID varchar2(100);
      obj json;
      inner_json json := json();
      procedure p(v varchar2) as begin dbms_output.put_line(null);dbms_output.put_line(v); end;
      
    begin
      
      vSourceInternalID := '56bea44e-2ddd-4aea-a79b-0b7e7e4dc5b3';
     -- p(vSourceInternalID);
      
      p('RelationshipDO');
      obj := json(); --fresh json;
      obj.put('category', json_value.makenull );
      inner_json.put('string', vSourceInternalID);
      obj.put('sourceInternalId', inner_json);
      obj.put('endDate', json('{"java.util.Date": {}}'));
      obj.put('internalId', json_value.makenull);
      obj.print;
    end;
    /
    
     
  • froliol
    froliol
    2011-10-13

    Thank you so much, that solved my problem.