Using Oracle Database 12.2.0.1.0, created a file in the db server location /u01/userhome/oracle/json_files/purchaseOrders.json
Then create an external table using the command below. The script runs successfully, however the JSON document is not in the view/table
create or replace view JSON_DUMP_CONTENTS (
PO_DOCUMENT
)
as
select JSON_DOCUMENT
from JSON_TABLE(
xdburitype('/u01/userhome/oracle/json_files/purchaseOrders.json').getClob(),
'$[*]'
columns (
JSON_DOCUMENT VARCHAR2(32000) FORMAT JSON PATH '$'
)
)
On running just the select statement in SQL Developer get the below error.
select JSON_DOCUMENT
from JSON_TABLE(
xdburitype('/u01/userhome/oracle/json_files/purchaseOrders.json').getClob(),
'$[*]'
columns (
JSON_DOCUMENT VARCHAR2(32000) FORMAT JSON PATH '$'
)
)
ORA-31001: Invalid resource handle or path name "/u01/userhome/oracle/json_files/purchaseOrders.json"
31001. 00000 - "Invalid resource handle or path name \"%s\""
*Cause: An invalid resource handle or path name was passed to
Using tutorial from below link
Loading JSON Documents into the database
https://livesql.oracle.com/apex/livesql/file/tutorial_EDVE861H6UF4Z20EV0RM4DK2G.html
Please advise.