The SQL directory object ORDER_ENTRY points to the folder containing the dump file, and the SQL directory object JSON_LOADER_OUTPUT points to the database’s trace folder which will contain any ‘log’ or ‘bad’ files generated when the table is processed. In this case, the documents are contained in the file purchaseOrders.json. This statement creates a simple external table that can read JSON documents from a dump file generated by a typical No-SQL style database. JSON documents contained in files can be loaded directly into the database using External Tables. Since Oracle stores JSON data using standard SQL data types, all of the popular Oracle APIs can be used to load JSON documents into the database.
JSON documents can come from a number of different sources. The IS JSON constraint is applied to the column PO_DOCUMENT, ensuring that the column can store only well formed JSON documents. The table has a column PO_DOCUMENT of type CLOB. This statement creates a very simple table, J_PURCHASEORDER. PO_DOCUMENT CLOB CHECK (PO_DOCUMENT IS JSON) This first statement in this module creates a table which will be used to contain JSON documents. This constraint returns TRUE if the content of the column is well-formed, valid JSON and FALSE otherwise. In order to ensure that the content of the column is valid JSON data, a new constraint IS JSON, is provided that can be applied to a column. VARCHAR2 can be used where the size of the JSON document will never exceed 4K (32K in database where when extended VARCHAR support has been enabled.) Larger documents are stored using CLOB or BLOB data types. JSON documents are stored in the database using standard Oracle data types such as VARCHAR2, CLOB and BLOB. In Oracle there is no dedicated JSON data type. Module 2 Storing JSON Documents in Oracle Database 12c Create a simple table to store JSON documents Where TABLE_NAME in ( 'J_PURCHASEORDER', 'JSON_DUMP_CONTENTS','CITY_LOT_FEATURES')Īnd OWNER = SYS_CONTEXT('USERENV','CURRENT_USER') Įxecute immediate 'DROP TABLE "' || t.TABLE_NAME || '" PURGE' Select EMPLOYEE_ID as ID, 'DepartmentId' as KEY, to_char(DEPARTMENT_ID) as VALUE Select EMPLOYEE_ID as ID, 'ManagerId' as KEY, to_char(MANAGER_ID) as VALUE Select EMPLOYEE_ID as ID, 'Commision' as KEY, to_char(COMMISSION_PCT) as VALUE Select EMPLOYEE_ID as ID, 'Salary' as KEY, to_char(SALARY) as VALUE Select EMPLOYEE_ID as ID, 'JobId' as KEY, JOB_ID as VALUE Select EMPLOYEE_ID as ID, 'HireDate' as KEY, to_char(HIRE_DATE) as VALUE Select EMPLOYEE_ID as ID, 'TelephoneNumber' as KEY, PHONE_NUMBER as VALUE Select EMPLOYEE_ID as ID, 'EmailAddress' as KEY, EMAIL as VALUE Select EMPLOYEE_ID as ID, 'LastName' as KEY, LAST_NAME as VALUE Select EMPLOYEE_ID as ID, 'FirstName' as KEY, FIRST_NAME as VALUE Select EMPLOYEE_ID as ID, 'EmployeeId' as KEY, to_char(EMPLOYEE_ID) as VALUE They can also be used to reset the tutorial if you want to start again at any point create or replace view EMPLOYEE_KEY_VALUE
Please run the following statements to set up the Tutorial.
0 kommentar(er)
