Skip to main content

How to return json with null value using apex_json.write(ref_cursor)


Few days ago I had to develop dynamic query json export module.
As a sandbox I prepared PLSQL block:


DECLARE
  c sys_refcursor;
  l_sql varchar2(4000) := 'select null as "col1", 100 as "col2" from dual
						   union all
						   select 200 as "col1", null as "col2" from dual';
  l_file clob;
BEGIN
  open c for l_sql;
    apex_json.initialize_clob_output;
      apex_json.open_object;
      apex_json.write('rowset', c);
      apex_json.close_object;      
    l_file := apex_json.get_clob_output;
  apex_json.free_output;

  DBMS_OUTPUT.PUT_line(l_file);
END;


{
    "rowset": [{
            "col2": 100
        }, {
            "col1": 200
        }
    ]
}


As you can see above PLSQL code doesn't return null value pairs : (
apex_json.write(p_name IN VARCHAR2, p_cursor IN OUT NOCOPY sys_refcursor) procedure Signature 14 doesn't support null. How to fix it... ? APEX API provides apex_json.write Signature 15 where you can pass 3 parameters:
p_name IN VARCHAR2 - The attribute name.
p_value IN sys.xmltype - The value to be written. The XML is converted to JSON
p_write_null IN BOOLEAN DEFAULT FALSE - If true, write NULL values. If false (the default), do not write NULLs.

According description p_value must be xmltype. So we can prepare xml object from ref_cursor with null nodes and then we can use apex_json.write Signature 15. Let's do it !


DECLARE
  c sys_refcursor;
  l_sql varchar2(4000) := 'select null as "col1", 100 as col2 from dual
                        union all
                        select null as "col1", null as col2 from dual';
   l_xml xmltype;
   l_ctx dbms_xmlgen.ctxHandle;
BEGIN
  open c for  l_sql;
	/* converts the results of a SQL query to a canonical XML format */
    l_ctx := dbms_xmlgen.newContext(c); 
	/* Sets NULL handling options */
    dbms_xmlgen.setNullHandling(l_ctx, dbms_xmlgen.EMPTY_TAG);
    l_xml := dbms_xmlgen.getxmltype(l_ctx); 
    dbms_xmlgen.closecontext(l_ctx); 
  close c; 
  
  apex_json.initialize_clob_output;      
  apex_json.open_object;
  
  /* I don't know why but 3-th parameter doesn't work for me,
     always returns null which is ok in my case */
  apex_json.write('rowset', l_xml, true);
  
  apex_json.close_all;

  DBMS_OUTPUT.PUT_line(apex_json.get_clob_output);
END;


{
    "rowset": [{
            "col1": null,
            "col2": 100
        }, {
            "col1": null,
            "col2": null
        }
    ]
}

Probably you can reach the same result using other way but if you need to use apex_json.write its one of possible solution.

Comments

Popular posts from this blog

TWO-Factor Authentication with APEX application.

Two-factor authentication means that you must have two factors, to access your account. The first factor is something you know, like your login and password combination. The second is something you have, like a mobile device with a security app installed. That second factor — the mobile device with a security app — adds an extra layer of protection to your account. Even if hackers steal your password, they can’t log in, because they don't have your mobile device. Try 2FA on our SANDBOX environment! It is the most popular 2FA option based on Times-based One-Time Password (TOTP) alghoritm described in RFC 6238 and RFC 4226. TOTP is supported by Google Authenticator, Microsoft Authenticator, Authy 2-Factor Authentication and many other mobile apps. The most popular online services like Facebook, Google, Twitter use TOTP to protect user's accounts. TOTP is a standard algorithm based on cryptographic hash method SHA-1. SHA-1 algorithm's implementation is available sin

ORACLE APEX with MySQL !

About 10 years ago I tried to build APEX application on MS SQLServer. It was possible but performance was not satisfactory. (You can read this post here) ... Today I will describe how to use APEX with MySQL. Obviusly I don't think it is good idea to build APEX app only on MySQL. It can be used as part of Hybryd solution (Oracle + MySQL or Postrgres). My description covers integration with MySQL. As a result will be APEX application with Interactive Report and CRUD form based on MySQL data. Description contains two parts: First part is a MySQL site, Second part is a APEX application part Prerequisites: Oracle 11g+ APEX 18.1+ MySQL/Postgres RestSQL I. MySQL Part 1. MySQL contains simple  apexdb database with one apexutil table. 2. To use this database install RestSQL. What is RestSQL? RestSQL is an open-source, ultra-lightweight data access layer for HTTP clients. RestSQL is a persistence framework or engine in the middle tier of a classic three tier archi

New plugins to store files outside your database!

The plugins we release helps you easly upload files to Amazon AWS S3. Below we have simple installation tutorial and for more you can always visit our DEMO application. Actually, this plugin is in 2 parts: - "FMcomponent" part is APEX item. Something like native filebrowser item but fully configurable. You can always customize how it with a sparkle of CSS. - "FMproviders" part serves as connection to one of the file storage service (eg. Amazon S3, Dropbox, etc) To use it, you have to grand DBMS_CRYPTO privilege to your Oracle schema: grant execute on sys.dbms_crypto to <schema>; Next, install both plugins into your APEX application: FMcomponent and specific FMprovider (currently we provide AWS S3 FMprovider). At the end you have to install additional  package . As a schema owner open your favorite SQL editor and run as first apexutil_fm_aws.pls and second apexutil_fm_aws.plb. Now you can try to add plugins in your application. Go to Apex Ap