Integrating PL/JSON with Oracle APEX 4.0

Question

I am trying to use PL/JSON in my Oracle APEX application but get the following errors when I attempt to run a Hello, world example (which works fine when I run the example from SQL*PLUS as sys):

ORA-06550: line 2, column 20:
PLS-00905: object MY_SCHEMA.JSON is invalid
ORA-06550: line 2, column 20:
PL/SQL: Item ignored
ORA-06550: line 5, column 5:
PLS-00320: the declaration of the type of this expression is incomplete or malformed
ORA-06550: line 5, column 5:
PL/SQL: Statement ignored
ORA-06550: line 7, column 26:
PLS-00201: identifier 'JSON_EXT.GET_VARCHAR2' must be declared
ORA-06550: line 7, column 5:
PL/SQL: Statement ignored

The version of Oracle database that I am using is 10g XE.

I am guessing that I haven't granted execute privileges correctly on the packages: JSON_EXT, JSON_PRINTER, and JSON_PARSER. I have tried granting execute privileges on the 3 packages to public and APEX_PUBLIC_USER but to no avail. I would really appreciate it if someone could point me in the right direction. I have Google searched for several hours and have yet to come across a post/page that answers my problem or helps explain what is wrong and how I can go about resolving my problem.

Thanks in advance!

Answer 1

If you do a DESC JSON_EXT, there isn't a GET_VARCHAR2 function (at least in version 0.9.2). There is a GET_STRING function that returns a VARCHAR2.

The page you link to uses PL/JSON version 0.8.6, and it looks like they've done some biggish changes in 0.9

  Version: 0.9.0
WARNING: You cannot do an easy upgrade because of changes in the API
    Rewrote the API to increase speed and simplicity

Try out the examples in the latest version of the package.