How to get PLJSON working with other users-CodePudding

I just installed the latest version of PLJSON version 2.5.4

I successfully installed the packages on oracle with no error under the sys user, but other users can't access PLJSON for example running the test examples sql files.

ran as sys/sysdba

SQL> connect sys@backup as sysdba
Enter password: ********
Connected.
SQL> @C:\sql\pljson-3.6.1\examples\ex12.sql
"true"
"true"
"true"

PL/SQL procedure successfully completed.

running it from a different user results

SQL> connect orders@backup
Enter password: *****
Connected.
SQL> @C:\sql\pljson-3.6.1\examples\ex12.sql
  obj pljson;
      *
ERROR at line 2:
ORA-06550: line 2, column 7:
PLS-00201: identifier 'PLJSON' must be declared
ORA-06550: line 2, column 7:
PL/SQL: Item ignored
ORA-06550: line 4, column 3:
PLS-00320: the declaration of the type of this expression is incomplete or
malformed
ORA-06550: line 4, column 3:
PL/SQL: Statement ignored
ORA-06550: line 6, column 24:
PLS-00201: identifier 'PLJSON_PRINTER.PRETTY_PRINT_ANY' must be declared
ORA-06550: line 6, column 3:
PL/SQL: Statement ignored
ORA-06550: line 8, column 24:
PLS-00201: identifier 'PLJSON_EXT.PP' must be declared
ORA-06550: line 8, column 3:
PL/SQL: Statement ignored
ORA-06550: line 10, column 17:
PLS-00320: the declaration of the type of this expression is incomplete or
malformed
ORA-06550: line 10, column 3:
PL/SQL: Statement ignored

how can i get it so all users can access it.

CodePudding user response：

I never used it, but - usually such a problem is resolved by granting privileges to other users - or even public. For example (connected as SYS and presuming package name is pljson)

grant execute on pljson to public;

create public synonym pljson for pljson;

Then you'd again connect as orders and run

@C:\sql\pljson-3.6.1\examples\ex12.sql