Oracle 11 G New Features for DBA :

Oracle has announced the new version Oracle 11 G and announced the new features in
that version. Oracle has added around 500 Oracle 11 G New Features for DBA.(exact
number is 482 features).The Most exciting features are DBA related features and simplicity
and well-structured DBA tasks. Another important Oracle 11 G New Feature is related to
Performance Tuning and optimization. Oracle has added considerable enhancements in
SQL to raise the performance of SQL Query. In this article, will explain the extra features
related to SQL. I am writing this article to give idea about the Oracle 11 G New Features
with examples. These Oracle 11 G New Features are important for programmers as well as
DBA to use it for different purpose.

1.ALTER Table with Default values:

In Oracle previous version there is no facility to alter the table with adding the default values
in it. In Oracle 11 G ,Oracle provides the facility to alter the table with adding default values
in the specified column. So developer efforts of updating the column values has been
reduced.

Scenario:
Add the New column in Employee table in which Employee_Status is ‘Working’

Query:
Alter table Employee

Add Empoyee_Status varchar2(20)  Default ‘Working’ not null;

2.New Table Datatypes added named ‘simple_integer’,’simple_float’,’simple_double’:
In Oracle 11 G new datatype is introduced which is simple_integer datatype. This simple_integer
datatype is always not null. This datatype is faster than Integer and PLS_INTEGER datatype in SQL.
These all datatypes are subtypes of NLS_INTEGER,INS_FLOAT,INS_DOUBLE datatype.
Simple_Integer is nothing but PLS Integer with not null constraint
Example:
CREATE OR REPLACE PROCEDURE P_Simple_Integer_test AS
v_start NUMBER;
v_loops NUMBER := 10000000;
v_pls_integer PLS_INTEGER := 0; —PLS integer declaration
v_pls_integer1 PLS_INTEGER := 1;
v_simple_integer SIMPLE_INTEGER := 0;
v_simple_integer1 SIMPLE_INTEGER := 1; –Simple_integer declaration
BEGIN

v_start := DBMS_UTILITY.get_time; –We are using the database utility get_time

FOR i IN 1 .. v_loops LOOP

v_pls_integer := v_pls_integer + v_pls_integer1; —Summation of PLS integers and check
the time
END LOOP;

DBMS_OUTPUT.put_line(‘PLS_INTEGER: ‘ || (DBMS_UTILITY.get_time – v_start) || ‘

hsecs’); —time for PLS integer

v_start := DBMS_UTILITY.get_time;

FOR i IN 1 .. v_loops LOOP

v_simple_integer := v_simple_integer + v_simple_integer1; —Summation of simple_integer
integers and check the time
END LOOP;

DBMS_OUTPUT.put_line(‘SIMPLE_INTEGER: ‘ || (DBMS_UTILITY.get_time – v_start)

|| ‘ hsecs’);

END P_Simple_Integer_test;
Output:
PLS_INTEGER: 19 hsecs
SIMPLE_INTEGER: 12 hsecs
3.Invisible Indexes:
Indexes can be created in Invisible mode in Oracle 11 G.The new parameter has been
introduced named OPTIMIZER_USE_INVISIBLE_INDEXES and optimizer uses the same
parameter if it sets to ‘TRUE’ or ‘FALSE’.This parameter sets to true or false at system
level.

Syntax:
Create or replace index Index_name on Table_name(Column_name) Invisible;
Alter index:
ALTER Index Indexname set Invisible=[TRUE|FALSE];
4.SQL /*result_cache*/ Hint:
The result data is cached in the data buffer using this hint.This hint improves the
performance of query drastically as the result data is stored in data buffer the data is been
fetched directly from the buffer location.The RESULT_CACHE_MODE parameter specifies
the applicability of result cache feature in SQL queries.It accepts two admissible values
‘Manual’ and ‘Force’.For MANUAL mode, RESULT_CACHE hint must be specified with the
SQL statements to use the feature. In FORCE mode, server enables the caching feature
with all the SQL statements.

Syntax:
ALTER SYSTEM SET RESULT_CACHE_MODE=’MANUAL’;

ALTER SYSTEM SET RESULT_CACHE_MODE=’FORCE’;

Example of Hint:
Select /*+result_cache*/ Department_ID,Dept_num

from Department;
5.Read Only Mode of Tables:
Oracle 11G provides the facility to change the mode of the table to Read only mode.In
read_only mode table can only be queried.The DML and DDL (Truncate and Alter) options
are restricted in read_only mode.At any point of time the table mode has been changed
from Read mode to write mode.

Syntax:
ALTER TABLE TABLE_NAME [READ ONLY|READ WRITE];
Query to Find Second highest salary of Employee with explaination..
6.Regular Expression (REGEXP_COUNT function):
REGEXP_COUNT is the new regular expression function, introduced in Oracle 11g release.
This function is introduced for language support which is used to count the character or
string appearances in a given string.

Example:
SELECT ‘Rohit Shinde’ Programmer,REGEXP_COUNT(Programmer, ‘s’, 1, ‘i’)
‘Expression_Output’ from dual;

Output:

Programmer   Expression_Output

Rohit Shinde   1

7.Virtual Columns:
Oracle 11 G provides new feature of virtual columns which allows user to provide the
column virtually where value is specified in Expression itself.Virtual columns acts like a
normal columns during indexing and partitioning.Virtual columns belongs to LOBs or
collection datatypes.

Example:

CREATE TABLE Employee(

Employee_num NUMBER,
Salary NUMBER,
Dearness_allowance NUMBER,
Total_salary NUMBER AS ((Salary+Dearness_allowance)    –this column is virtual
column
);
8.Fully Automatic Tuning:
In Previous version Oracle 10 G only advices for tuning using SQL Tuning Adviser.In Oracle
11 G provides facility of automatically applying the SQL profile to improve the performance
of the query. The performance comparisons are done by a new administrative task during a
user-specified maintenance window.

9.Partitioning:
The partitioning plays an important role in database performance and every SQL
professional wants to do the perfect partitioning to improve the performance of SQL
queries.There are following new features added in SQL partitioning:

1.System Partitioning

2.Reference Partitioning

3.Interval Partitioning

4.Extended Composite Partitioning

10.Analytical Functions-NTH,LISTAGG:
Oracle 11 G specifies two new aggregate functions:

1.LISTAGG:
LISTAGG aggregates a column values in a single row format.
2.NTH_VALUE:
NTH_VALUE is an extended format of FIRST_VALUE and LAST_VALUE functions to get a
random row from a grouped result set.

Syntax:
LISTAGG – LISTAGG (measure_expr [, ‘delimiter_expr’]) WITHIN GROUP (ORDER
BY clause) [OVER PARTITION BYclause]
Real Life Scenario:

Kindly consider following table.We need to find aggregate results departmentwise in form of
rows.

Employee table:

Employee_num Employee_name Department ID Salary

1 Amit 100 680000

2 Rohan 100 550000

3 Rohit 101 430000

Query used:
select Department_ID,listagg(Employee_name,’,’) within group(order by Employee_name)
as Employee_name from Employee group by Department_id;
Output:

Department ID Employee_name

100 Amit,Rohan

101 Rohit
So  listagg function is very useful function for converting columns in to single row format.

11.Skip locked utility:

Oracle 11 G provides the new utility where the records are directly fetched from table.The
records from running transactions are not considered.

“Skip locked utility doesnot fetches the locked records in transaction”

Example:
select * from Employee

for update

Skip locked;
12.XML SQL queries – Oracle11g will support query mechanisms for XML including XQuery
and SQL XML, emerging standards for querying XML data stored inside tables.
 

Here I have tried to cover the Newly added features in Oracle 11G. Please do let me know if
any other information needs to be added in this.Please comment below if you needed extra
information or suggestions if any..
Oracle 11g vs 12c :
In my previous article, I have given the idea about the Oracle 11g new features. There are some
newly added features in Oracle 12c over oracle 11G.In the article I will try to explain the newly
added features in oracle 12c as well as Oracle 11g vs 12c. From 12c Onwards, the instance is shared
with multiple databases. This multiple databases are self-contained and pluggable from one database
to another database. This is very useful methodology where database consolidation. In short a single
SGA and background process will be shared to multiple databases, the databases can be created on
fly and drop or attach and detach from one server to another server.This article will give you idea
about Oracle 11g vs 12c .
Oracle 12c Features:
1.Advanced Indexing Techniques:

Oracle 12c user will be able to create multiple indexes on same column.Prior to oracle 12c, user will
be able to create single index on one column. While creating multiple index-using oracle 12c the type
of index should be different and only one type of index is usable at a time. Means if user create one
index on column ‘a’ which is simple index and usable then user cannot create the index on same
column.

Only one type of index on column is usable at a same time.

To create multiple index on it column alter the session and set following attribute as true:

Optimizer_use_invisible_index=true

Then create indexes,

Create index SI_Employee on Employee (Employee_ID);

Create bitmap index BM_Employee on Employee (Employee_ID) Invisible;

2.Newly added Functions:

Oracle 12c has added new features to handle the huge data aggregation using
Approx_Count_distinct () function which handles the approximate count distinct
aggregation. Optimizing the processing time and resource consumption by orders of
magnitude while providing almost exact results speeds up any existing processing and
enables new levels of analytical insight.

3.Automatic Big table caching:

In previous versions of oracle, in-memory parallel query did not work well when multiple
scans contended for cache memory. The new cache mechanism called as big table cache
is used to improve the performance for full table scan.

4.Full Database caching:

 Oracle 12c provides the full database-caching feature, which is beneficial to improve the
performance of application especially in I/O throughput and response time.

 5.In Memory aggregation:

Oracle 12C supports In-memory aggregation, which optimizes the queries that joins the
dimension tables to fact tables and aggregate data. User needs to use KEY VECTOR and
VECTOR GROUP BY operations to achieve the In-memory aggregation. In memory,
aggregation is used in star schema, which improves the performance of Star queries and
reduces the CPU usage. The In memory aggregation eliminates the need of summary
tables in most of cases which simplifies the star schema query.

6.JSON support :
Oracle 12C supports the Java Object Notation(JSON) data to Oracle Database and allows
the database to enforce that JSON stored in the Oracle Database conforms to the JSON
rules.

7.Online migration of table partition or subpartition :

To migrate the table partition and subpartition to different tablespace can be moved to
different tablespace online or offline.

8.Invisible Columns :
Oracle 12c introduces new feature of invisible columns in the table.When specific column is
defined as invisible it will not appear in generic queries.It is pretty easy to add invisible
columns or modify invisible columns.

Example :
Create table Employee

(Employee_no Number(5),

salary number(8) invisible);

Table Visible:
Alter table Employee

Modify (salary visible);

Oracle 11g vs 12c :

Oracle 11G Oracle 12C
 Indexing: User can create more than one index
Indexing: User can create only one index on one column. The on same column. Only one type of index on
Invisible index feature has been implemented in Oracle 11G. column is usable at a same time.

Newly added Functions: The newly added function are not Newly added Functions: Oracle 12C has added
considering the performance tuning of the data. The newly added the new feature approx_count_distinct () which
functions in oracle 11G are LISTAGG and Nth_value functions. provides approximate count distinct aggregation.

Caching in Oracle 12C: The new cache

mechanism called as big table cache is used to
improve the performance for full table scan. This
new concept is known as Automatic big table
caching in Oracle 12C.Big table cache provides
significant performance improvement for full
table scan.
 

Oracle 12C provides the feature of full database

Caching in Oracle 11G: The Oracle 11G caching In memory
parallel query did not working well with multiple scans caching to improve the significant performance
benefits especially for workloads that will be
contended for cache memory.
  previously limited by I/O throughput or response
time.
Full Database caching is not implemented in Oracle 11G.
 

In Memory table :
Oracle 11G supports the concept of In-memory tables, which
will, used to improve the full table scans. The user needs to put
the table in the memory so that user can access the fast data. In Memory aggregation :
Oracle 12C supports the In memory aggregation
Oracle 11G does not support the In memory aggregation concept. concept which is very useful in star queries.

Table partition and subpartition movement : To

Table partition and subpartition movement : To move the migrate the table partition and subpartition
partition and subpartition from one tablespace to other tablespace oracle 12c uses 2 methods one is  with online
user needs to write complex procedural logic. keyword and second is offline method.

Invisible columns : In Oracle 12c R1,User can

Invisible columns : In Oracle 11g,the couple of good define the invisible columns in the table. When
enhancements introduced in form of invisible indexes and virtual column is defined as invisible column it will not
columns. The invisible column has not been introduced in oracle come in generic query. It is explicitly referred to
11g. SQL statement or condition in SQL statement.
Complex SQL Queries Examples ( 90% ASKED IN
Interviews ) 
1.Query to find Second Highest Salary of Employee?(click for explaination)
Answer:
Select distinct Salary from Employee e1 where 2=Select count(distinct Salary)
from Employee e2 where e1.salary<=e2.salary;

select min(salary)from(select distinct salary from emp order by salary desc)where

rownum<=2;
2.Query to find duplicate rows in table?(click here for explaination )
Answer :
Select * from Employee a where rowid <>( select max(rowid) from Employee b where
a.Employee_num=b.Employee_num);
3.How to fetch  monthly Salary of Employee if annual salary is given?(click here for
Explaination) 
Answer:
   Select Employee_name,Salary/12 as ‘Monthly Salary’ from employee;
Click here to get information on ROW_ID
4.What is the Query to fetch first record from Employee table? (90% asked Complex SQL
Queries Examples)
Answer:
 Select * from Employee where Rownum =1;
Click here to get What is Rownum?
5.What is the Query to fetch last record from the table?
Answer:
Select * from Employee where Rowid= select max(Rowid) from Employee;
6.What is Query to display first 5 Records from Employee table?(90% asked Complex SQL
Queries Examples)
Answer:
Select * from Employee where Rownum <= 5;

6.What is Query to display last 5 Records from Employee table?(90% asked Complex SQL
Queries Examples)
Answer:
Select * from Employee e where rownum <=5

union

select * from (Select * from Employee e order by rowid desc) where rownum <=5;
Click Here to get What is Union?
7.What is Query to display Nth Record from Employee table?
Answer :
select * from ( select a.*, rownum rnum from ( YOUR_QUERY_GOES_HERE — including
the order by ) a where rownum <= N_ROWS ) where rnum >= N_ROWS
8.How to get 3 Highest salaries records from Employee table?
Answer:
select distinct salary from employee a where 3 >= (select count(distinct salary) from
employee b where a.salary <= b.salary) order by a.salary desc;
Alternative Solution: Suggested by Ankit Srivastava

select min(salary)from(select distinct salary from emp order by salary desc)where

rownum<=3;
9.How to Display Odd  rows in Employee table?(90% asked Complex SQL Queries Examples)
Answer:
Select * from(Select rownum as rno,E.* from Employee E) where Mod(rno,2)=1;
10.How to Display Even rows in Employee table?
Answer:
Select * from(Select rownum as rno,E.* from Employee) where Mod(rno,2)=0;
11.How to fetch 3rd highest salary using Rank Function?
Answer:
select * from (Select Dense_Rank() over ( order by  salary desc) as Rnk,E.* from Employee
E) where Rnk=3;
Click Here to Get Information on Rank and Dense_Rank
12.How Can i create table with same structure of Employee table?(90% asked Complex SQL
Queries Examples)
Answer:
Create table Employee_1 as Select * from Employee where 1=2;
13.Display first 50% records from Employee table?
Answer:
select rownum, e.* from emp e where rownum<=(select count(*)/2 from emp);
14.Display last 50% records from Employee table?
Answer:
Select rownum,E.* from Employee E

minus

Select rownum,E.* from Employee E where rownum<=(Select count(*)/2) from Employee);

15.How Can i create table with same structure with data of Employee table?
Answer:
Create table Employee1 as select * from Employee;
16.How do i fetch only common records between 2 tables.
Answer:
Select * from Employee;
Intersect

Select * from Employee1;

17.Find Query to get information of Employee where Employee is not assigned to the
department
Answer:
Select * from Employee where Dept_no Not in(Select Department_no from Department);
18.How to get distinct records from the table without using distinct keyword.
Answer:
select * from Employee a where  rowid = (select max(rowid) from Employee b where 
a.Employee_no=b.Employee_no);
19.Select all records from Employee table whose name is ‘Amit’ and ‘Pradnya’
Answer:
Select * from Employee where Name in(‘Amit’,’Pradnya’);
20.Select all records from Employee table where name not in ‘Amit’ and ‘Pradnya’
Answer:
select * from Employee where name Not  in (‘Amit’,’Pradnya’);
21.how to write sql query for the below scenario
I/p:ORACLE
O/p:
O
R
A
C
L
E
i.e, splitting into multiple columns a string using sql.
Answer:
Select Substr(‘ORACLE’,Level,1) From Dual
Connect By Level<= Length(‘ORACLE’);

22.How to fetch all the records from Employee whose joining year is  2017?
Answer:
Oracle:

select * from Employee where To_char(Joining_date,’YYYY’)=’2017′;

MS SQL:

select * from Employee where substr(convert(varchar,Joining_date,103),7,4)=’2017′;

23.What is SQL Query to find maximum salary of each department?
Answer:
Select Dept_id,max(salary) from Employee group by Dept_id;
24.How Do you find all Employees with its managers?(Consider there is manager id also in
Employee table)
Answer:
Select e.employee_name,m.employee name from Employee e,Employee m where
e.Employee_id=m.Manager_id;
25.Display the name of employees who have joined in 2016 and salary is greater than 10000?
Answer:
Select name from Employee where Hire_Date like ‘2016%’ and salary>10000;
26.How to display following using query?
*
**
***
Answer:
We cannot use dual table to display output given above. To display output use any table. I
am using Student table.

SELECT lpad (‘*’, ROWNUM,’*’) FROM Student WHERE ROWNUM <4;

27.How to add the email validation using only one query?
Answer :
User needs to use REGEXP_LIKE function for email validation.
 SELECT
Email
FROM
Employee
where NOT REGEXP_LIKE(Email, ‘[A-Z0-9._%+-]+@[A-Z0-9.-]+\.[A-Z]{2,4}’, ‘i’);
28.How to display 1 to 100 Numbers with query?
Answer:
Select level from dual connect by level <=100;
Tip: User needs to know the concept of Hierarchical queries. Click here to get concept of
hierarchical queries
29.How to remove duplicate rows from table?(100% asked in Complex SQL Queries for
Interviews)
Answer:
First Step: Selecting Duplicate rows from table
Tip: Use concept of max (rowid) of table. Click here to get concept of rowid.
Select rollno FROM Student WHERE ROWID <>

(Select max (rowid) from Student b where rollno=b.rollno);

Step 2:  Delete duplicate rows
Delete FROM Student WHERE ROWID <>

(Select max (rowid) from Student b where rollno=b.rollno);

30.How to find count of duplicate rows? (95% asked in SQL queries for Interviews )
Answer:
Select rollno, count (rollno) from Student

Group by rollno

Having count (rollno)>1

Order by count (rollno) desc;

31.How to Find the Joining date of Employee in YYYY-DAY-Date format.
Select FIRST_NAME, to_char(joining_date,’YYYY’) JoinYear ,
to_char(joining_date,’Mon’), to_char(joining_date,’dd’) from EMPLOYEES;

JSON_QUERY
Note:
The JSON_QUERY function is available starting with Oracle Database 12c Release 1 (12.1.0.2).

Syntax

Description of the illustration ''json_query.gif''

JSON_path_expression::=

Description of the illustration ''json_path_expression.gif''

object_step::=
Description of the illustration ''object_step.gif''

array_step::=

Description of the illustration ''array_step.gif''

JSON_query_returning_clause::=

Description of the illustration ''json_query_returning_clause.gif''

JSON_query_return_type::=

Description of the illustration ''json_query_return_type.gif''

JSON_query_wrapper_clause::=
Description of the illustration ''json_query_wrapper_clause.gif''

JSON_query_on_error_clause::=

Description of the illustration ''json_query_on_error_clause.gif''

Purpose
JSON_QUERY finds one or more specified JSON values in JSON data and returns the values in a character
string.

expr

Use this clause to specify the JSON data to be evaluated. For expr, specify an expression that evaluates to a
text literal. If expr is a column, then the column must be of data type VARCHAR2, CLOB, or BLOB. If expr is
null, then the function returns null.

If expr is not a text literal of well-formed JSON data using strict or lax syntax, then the function returns null
by default. You can use the JSON_query_on_error_clause to override this default behavior. Refer
to JSON_query_on_error_clause.

FORMAT JSON

You must specify FORMAT JSON if expr is a column of data type BLOB.

JSON_path_expression

Use this clause to specify a JSON path expression. The function uses the path expression to evaluate expr and
find one or more JSON values that match, or satisfy, the path expression. The path expression must be a text
literal.
The path expression must begin with a dollar sign ($), which represents the context item, that is, the expression
specified by expr. The dollar sign is followed by zero or more steps, each of which can be an object step or an
array step.

The function attempts to match the first step in the path expression to the context item. If the first step results
in a match, then the function attempts to match the second step to the JSON value(s) that matched the first
step. If the second step results in a match, then the function attempts to match the third step to the JSON
values(s) that matched the second step, and so on. The function returns the value(s) matched in the final step as
a comma-separated sequence of values in a character string. The order of the sequence is nondeterministic. All
values are returned using strict JSON syntax, regardless of whether the original JSON data used strict or lax
JSON syntax. A path expression that consists of a dollar sign followed by zero steps ( '$') matches the entire
context item.

You can specify the JSON_query_returning_clause to control the data type and format of the return
character string. Refer to the JSON_query_returning_clause.

If multiple values match the path expression, or if only one scalar value matches the path expression, then you
must wrap the value(s) in an array wrapper. Refer to the JSON_query_wrapper_clause.

If any step in the path expression does not result in a match, then the function returns null by default. You can
use the JSON_query_on_error_clause to override this default behavior. Refer to
the JSON_query_on_error_clause.

object_step Use this clause to specify an object step.

 Use simple_name or complex_name to specify a property name. If a member with that property

name exists in the JSON object being evaluated, then the object step results in a match to the property
value of that member. Otherwise, the object step does not result in a match. Both types of names are
case-sensitive. Therefore, a match will result only if the alphabetic character cases match in the object
step and the JSON data.

A simple_name can contain only alphanumeric characters and must begin with an alphabetic
character. A complex_name can contain only alphanumeric characters and spaces, and must begin
with an alphanumeric character. A complex_name must be enclosed in double quotation marks.

 Use the asterisk wildcard symbol (*) to specify all property names. If the JSON object being evaluated
contains at least one member, then the object step results in a match to the values of all members.
Otherwise, the object step does not result in a match.

If you apply an object step to a JSON array, then the array is implicitly unwrapped and the elements of the
array are evaluated using the object step. This is called JSON path expression relaxation. Refer to Oracle XML
DB Developer's Guide for more information.

If the JSON data being evaluated is not a JSON object, then the object step does not result in a match.

array_step Use this clause to specify an array step.

 Use integer to specify the element at index integer in a JSON array.

Use integer TO integer to specify the range of elements between the two index integer values,
inclusive. If the specified elements exist in the JSON array being evaluated, then the array step results
 in a match to those elements. Otherwise, the array step does not result in a match. The first element in
a JSON array had index 0.

 Use the asterisk wildcard symbol (*) to specify all elements in a JSON array. If the JSON array being
evaluated contains at least one element, then the array step results in a match to all elements in the
JSON array. Otherwise, the array step does not result in a match.

If the JSON data being evaluated is not a JSON array, then the data is implicitly wrapped in an array and then
evaluated using the array step. This is called JSON path expression relaxation. Refer to Oracle XML DB
Developer's Guide for more information.

JSON_query_returning_clause

Use this clause to specify the data type and format of the character string returned by this function.

RETURNING Use the RETURNING clause to specify the data type of the character string. If you omit this
clause, then JSON_QUERY returns a character string of type VARCHAR2(4000).

You can use the JSON_return_type_clause to specify the following data type:

 VARCHAR2[(size [BYTE,CHAR])]

When specifying the VARCHAR2 data type elsewhere in SQL, you are required to specify a size.
However, in this clause you can omit the size. In this case, JSON_QUERY returns a character string of
type VARCHAR2(4000).

Refer to "VARCHAR2 Data Type" for more information.

If the data type is not large enough to hold the return character string, then this function returns null by default.
You can use the JSON_query_on_error_clause to override this default behavior. Refer to
the JSON_query_on_error_clause.

PRETTY Specify PRETTY to pretty-print the return character string by inserting newline characters and
indenting.

ASCII Specify ASCII to automatically escape any non-ASCII Unicode characters in the return character
string, using standard ASCII Unicode escape sequences.

JSON_query_wrapper_clause

Use this clause to control whether this function wraps the values matched by the path expression in an array
wrapper—that is, encloses the sequence of values in square brackets ([]).

 Specify WITHOUT WRAPPER to omit the array wrapper. You can specify this clause only if the path
expression matches a single JSON object or JSON array. This is the default.

 Specify WITH WRAPPER to include the array wrapper. You must specify this clause if the path
expression matches a single scalar value (a value that is not a JSON object or JSON array) or multiple
values of any type.
  Specifying the WITH UNCONDITIONAL WRAPPER clause is equivalent to specifying
the WITH WRAPPER clause. The UNCONDITIONAL keyword is provided for semantic clarity.

 Specify WITH CONDITIONAL WRAPPER to include the array wrapper only if the path expression

matches a single scalar value or multiple values of any type. If the path expression matches a single
JSON object or JSON array, then the array wrapper is omitted.

The ARRAY keyword is optional and is provided for semantic clarity.

If the function returns a single scalar value, or multiple values of any type, and you do not
specify WITH [UNCONDITIONAL | CONDITIONAL] WRAPPER, then the function returns null by default. You
can use the JSON_query_on_error_clause to override this default behavior. Refer to
the JSON_query_on_error_clause.

JSON_query_on_error_clause

Use this clause to specify the value returned by this function when any of the following errors occur:

 expr is not well-formed JSON data using strict or lax JSON syntax

 No match is found when the JSON data is evaluated using the JSON path expression

 The return value data type is not large enough to hold the return character string

 The function matches a single scalar value or, multiple values of any type, and
the WITH [UNCONDITIONAL | CONDITIONAL] WRAPPER clause is not specified

You can specify the following clauses:

 NULL ON ERROR - Returns null when an error occurs. This is the default.

 ERROR ON ERROR - Returns the appropriate Oracle error when an error occurs.

 EMPTY ON ERROR - Returns an empty JSON array ('[]') when an error occurs.

Examples
The following query returns the context item, or the specified string of JSON data. The path expression
matches a single JSON object, which does not require an array wrapper. Note that the JSON data is converted
to strict JSON syntax in the returned value—that is, the object property names are enclosed in double quotation
marks.

SELECT JSON_QUERY('{a:100, b:200, c:300}', '$') AS value

FROM DUAL;
VALUE

------------------------------------------------------------------------------
--

{"a":100,"b":200,"c":300}

The following query returns the value of the member with property name a. The path expression matches a
scalar value, which must be enclosed in an array wrapper. Therefore, the WITH WRAPPER clause is specified.

SELECT JSON_QUERY('{a:100, b:200, c:300}', '$.a' WITH WRAPPER) AS value

FROM DUAL;

VALUE

------------------------------------------------------------------------------
--

[100]

The following query returns the values of all object members. The path expression matches multiple values,
which together must be enclosed in an array wrapper. Therefore, the WITH WRAPPER clause is specified.

SELECT JSON_QUERY('{a:100, b:200, c:300}', '$.*' WITH WRAPPER) AS value

FROM DUAL;

VALUE

------------------------------------------------------------------------------
--

[100,200,300]

The following query returns the context item, or the specified string of JSON data. The path expression
matches a single JSON array, which does not require an array wrapper.

SELECT JSON_QUERY('[0,1,2,3,4]', '$') AS value

FROM DUAL;

VALUE
------------------------------------------------------------------------------
--

[0,1,2,3,4]

The following query is similar to the previous query, except the WITH WRAPPER clause is specified. Therefore,
the JSON array is wrapped in an array wrapper.

SELECT JSON_QUERY('[0,1,2,3,4]', '$' WITH WRAPPER) AS value

FROM DUAL;

VALUE

------------------------------------------------------------------------------
--

[[0,1,2,3,4]]

The following query returns all elements in a JSON array. The path expression matches multiple values, which
together must be enclosed in an array wrapper. Therefore, the WITH WRAPPER clause is specified.

SELECT JSON_QUERY('[0,1,2,3,4]', '$[*]' WITH WRAPPER) AS value

FROM DUAL;

VALUE

------------------------------------------------------------------------------
--

[0,1,2,3,4]

The following query returns the elements at indexes 0, 3 through 5, and 7 in a JSON array. The path expression
matches multiple values, which together must be enclosed in an array wrapper. Therefore,
the WITH WRAPPER clause is specified.

SELECT JSON_QUERY('[0,1,2,3,4,5,6,7,8]', '$[0, 3 TO 5, 7]' WITH WRAPPER) AS

value

FROM DUAL;

VALUE
------------------------------------------------------------------------------
--

[0,3,4,5,7]

The following query returns the fourth element in a JSON array. The path expression matches a scalar value,
which must be enclosed in an array wrapper. Therefore, the WITH WRAPPER clause is specified.

SELECT JSON_QUERY('[0,1,2,3,4]', '$[3]' WITH WRAPPER) AS value

FROM DUAL;

VALUE

------------------------------------------------------------------------------
--

[3]

The following query returns the first element in a JSON array. The WITH CONDITIONAL WRAPPER clause is
specified and the path expression matches a single JSON object. Therefore, the value returned is not wrapped
in an array. Note that the JSON data is converted to strict JSON syntax in the returned value—that is, the
object property name is enclosed in double quotation marks.

SELECT JSON_QUERY('[{a:100},{b:200},{c:300}]', '$[0]'

WITH CONDITIONAL WRAPPER) AS value

FROM DUAL;

VALUE

------------------------------------------------------------------------------
--

{"a":100}

The following query returns all elements in a JSON array. The WITH CONDITIONAL WRAPPER clause is

specified and the path expression matches multiple JSON objects. Therefore, the value returned is wrapped in
an array.

SELECT JSON_QUERY('[{"a":100},{"b":200},{"c":300}]', '$[*]'

WITH CONDITIONAL WRAPPER) AS value

FROM DUAL;
VALUE

------------------------------------------------------------------------------
--

[{"a":100},{"b":200},{"c":300}]

The following query is similar to the previous query, except that the value returned is of data
type VARCHAR2(100).

SELECT JSON_QUERY('[{"a":100},{"b":200},{"c":300}]', '$[*]'

RETURNING VARCHAR2(100) WITH CONDITIONAL WRAPPER) AS value

FROM DUAL;

VALUE

------------------------------------------------------------------------------
--

[{"a":100},{"b":200},{"c":300}]

The following query returns the fourth element in a JSON array. However, the supplied JSON array does not
contain a fourth element, which results in an error. The EMPTY ON ERROR clause is specified. Therefore, the
query returns an empty JSON array.

SELECT JSON_QUERY('[{"a":100},{"b":200},{"c":300}]', '$[3]'

EMPTY ON ERROR) AS value

FROM DUAL;

VALUE

------------------------------------------------------------------------------
--

[]

JSON_TABLE
Note:
The JSON_TABLE function is available starting with Oracle Database 12c Release 1 (12.1.0.2).

Syntax

Description of the illustration ''json_table.gif''

(JSON_path_expression::=, JSON_table_on_error_clause::=, JSON_columns_clause::=)

JSON_path_expression::=

Description of the illustration ''json_path_expression.gif''

object_step::=

Description of the illustration ''object_step.gif''

array_step::=
Description of the illustration ''array_step.gif''

JSON_table_on_error_clause::=

Description of the illustration ''json_table_on_error_clause.gif''

JSON_columns_clause::=

Description of the illustration ''json_columns_clause.gif''

JSON_column_definition::=

Description of the illustration ''json_column_definition.gif''

JSON_exists_column::=
Description of the illustration ''json_exists_column.gif''

(The syntax and semantics of these clauses are described in

the JSON_EXISTS and JSON_VALUE documentation: JSON_value_return_type::=, JSON_path_expression::
=, JSON_exists_on_error_clause::=)

JSON_query_column::=

Description of the illustration ''json_query_column.gif''

(The syntax and semantics of these clauses are described in

the JSON_QUERY documentation: JSON_query_return_type::=, JSON_query_wrapper_clause::=, JSON_pat
h_expression::=, JSON_query_on_error_clause::=)

JSON_value_column::=

Description of the illustration ''json_value_column.gif''

(The syntax and semantics of these clauses are described in

the JSON_VALUE documentation: JSON_value_return_type::=, JSON_path_expression::=, JSON_value_on_
error_clause::=)

JSON_nested_path::=

Description of the illustration ''json_nested_path.gif''

(JSON_path_expression::=, JSON_columns_clause::=)
ordinality_column::=

Description of the illustration ''ordinality_column.gif''

Purpose
JSON_TABLE creates a relational view of JSON data. It maps the result of a JSON data evaluation into
relational rows and columns. You can query the result returned by the function as a virtual relational table
using SQL. The main purpose of JSON_TABLE is to create a row of relational data for each object inside a
JSON array and output JSON values from within that object as individual SQL column values.

You can specify JSON_TABLE only in the FROM clause of a SELECT statement. The function first applies a

JSON path expression, called a row path expression, to the supplied JSON data. The JSON value that
matches the row path expression is called a row source in that it generates a row of relational data.
The COLUMNS clause evaluates the row source, finds specific JSON values within the row source, and returns
those JSON values as SQL values in individual columns of a row of relational data.

The COLUMNS clause enables you to search for JSON values in different ways by using the following clauses:

 JSON_exists_column - Evaluates JSON data in the same manner as the JSON_EXISTS condition,

that is, determines if a specified JSON value exists, and returns either a VARCHAR2 column of values
'true' or 'false', or a NUMBER column of values 1 or 0.
 JSON_query_column - Evaluates JSON data in the same manner as the JSON_QUERY function,
that is, finds one or more specified JSON values, and returns a column of character strings that contain
those JSON values.
 JSON_value_column - Evaluates JSON data in the same manner as the JSON_VALUE function,
that is, finds a specified scalar JSON value, and returns a column of those JSON values as SQL
values.
 JSON_nested_path - Allows you to flatten JSON values in a nested JSON object or JSON array
into individual columns in a single row along with JSON values from the parent object or array. You
can use this clause recursively to project data from multiple layers of nested objects or arrays into a
single row.
 ordinality_column - Returns a column of generated row numbers.

The column definition clauses allow you to specify a name for each column of data that they return. You can
reference these column names elsewhere in the SELECT statement, such as in the SELECT list and
the WHERE clause.

expr

Use this clause to specify the JSON data to be evaluated. For expr, specify an expression that evaluates to a
text literal. If expr is a column, then the column must be of data type VARCHAR2, CLOB, or BLOB. If expr is
null, then the function returns null.
If expr is not a text literal of well-formed JSON data using strict or lax syntax, then the function returns null
by default. You can use the JSON_table_on_error_clause to override this default behavior. Refer
to JSON_table_on_error_clause.

FORMAT JSON

You must specify FORMAT JSON if expr is a column of data type BLOB.

JSON_path_expression

Use this clause to specify the row path expression. The function uses the row path expression to
evaluate expr and find the a JSON value, called the row source, that matches, or satisfy, the path expression.
This row source is then evaluated by the COLUMNS clause. The path expression must be a text literal.

The JSON_path_expression clause has the same semantics for JSON_TABLE and JSON_QUERY. For the

full semantics of this clause, refer to JSON_path_expression in the documentation on JSON_QUERY.

JSON_table_on_error_clause

Use this clause to specify the value returned by this function when the following errors occur:

 expr is not well-formed JSON data using strict or lax JSON syntax
 No match is found when the JSON data is evaluated using the row path expression

You can specify the following clauses:

 NULL ON ERROR - Returns null when an error occurs. This is the default.

 ERROR ON ERROR - Returns the appropriate Oracle error when an error occurs.
 DEFAULT literal ON ERROR - Returns literal when an error occurs. If the data type of the value
returned by this function is VARCHAR2, then you must specify a text literal. If the data type
is NUMBER, then you must specify a numeric literal.

JSON_columns_clause

Use the COLUMNS clause to define the columns in the virtual relational table returned by
the JSON_TABLE function.

JSON_exists_column This clause evaluates JSON data in the same manner as the JSON_EXISTS condition,

that is, it determines if a specified JSON value exists. It returns either a VARCHAR2 column of values 'true' or
'false', or a NUMBER column of values 1 or 0. A value of 'true' or 1 indicates that the JSON value exists and
a value of 'false' or 0 indicates that the JSON value does not exist.

You can use the JSON_value_return_type clause to control the data type of the returned column. If you
omit this clause, then the data type is VARCHAR2(4000). Use column_name to specify the name of the
returned column. The rest of the clauses of JSON_exists_column have the same semantics here as they
have for the JSON_EXISTS condition. For full information on these clauses, refer to "JSON_EXISTS
Condition". Also see "Using JSON_exists_column: Examples" for an example.
JSON_query_column This clause evaluates JSON data in the same manner as the JSON_QUERY function, that
is, it finds one or more specified JSON values, and returns a column of character strings that contain those
JSON values.

Use column_name to specify the name of the returned column. The rest of the clauses
of JSON_query_column have the same semantics here as they have for the JSON_QUERY function. For full
information on these clauses, refer to JSON_QUERY. Also see "Using JSON_query_column: Example" for an
example.

JSON_value_column This clause evaluates JSON data in the same manner as the JSON_VALUE function, that
is, it finds a specified scalar JSON value, and returns a column of those JSON values as SQL values.

Use column_name to specify the name of the returned column. The rest of the clauses
of JSON_value_column have the same semantics here as they have for the JSON_VALUE function. For full
information on these clauses, refer to JSON_VALUE. Also see "Using JSON_value_column: Example" for an
example.

JSON_nested_path Use this clause to flatten JSON values in a nested JSON object or JSON array into
individual columns in a single row along with JSON values from the parent object or array. You can use this
clause recursively to project data from multiple layers of nested objects or arrays into a single row.

Specify the JSON_path_expression clause to match the nested object or array. This path expression is
relative to the row path expression specified in the JSON_TABLE function.

Use the COLUMNS clause to define the columns of the nested object or array to be returned. This clause is
recursive—you can specify the JSON_nested_path clause within another JSON_nested_path clause.
Also see "Using JSON_nested_path: Examples" for an example.

ordinality_column This clause returns a column of generated row numbers of data type NUMBER. You can
specify at most one ordinality_column. Also see "Using JSON_value_column: Example" for an example
of using the ordinality_column clause.

Examples
Creating a Table That Contains a JSON Document: Example This example shows how to create and
populate table j_purchaseorder, which is used in the rest of the JSON_TABLE examples in this section.

The following statement creates table j_purchaseorder. Column po_document is for storing JSON data
and, therefore, has an IS JSON check constraint to ensure that only well-formed JSON is stored in the column.

CREATE TABLE j_purchaseorder

(id RAW (16) NOT NULL,

date_loaded TIMESTAMP(6) WITH TIME ZONE,

po_document CLOB CONSTRAINT ensure_json CHECK (po_document IS JSON));

The following statement inserts one row, or one JSON document, into table j_purchaseorder:
INSERT INTO j_purchaseorder

VALUES (

SYS_GUID(),

SYSTIMESTAMP,

'{"PONumber" : 1600,

"Reference" : "ABULL-20140421",

"Requestor" : "Alexis Bull",

"User" : "ABULL",

"CostCenter" : "A50",

"ShippingInstructions" : {"name" : "Alexis Bull",

"Address": {"street" : "200 Sporting

Green",

"city" : "South San

Francisco",

"state" : "CA",

"zipCode" : 99236,

"country" : "United States of

America"},

"Phone" : [{"type" : "Office", "number" :

"909-555-7307"},

{"type" : "Mobile", "number" :

"415-555-1234"}]},

"Special Instructions" : null,

"AllowPartialShipment" : true,

"LineItems" : [{"ItemNumber" : 1,

"Part" : {"Description" : "One Magic Christmas",

"UnitPrice" : 19.95,

"UPCCode" : 13131092899},

"Quantity" : 9.0},

{"ItemNumber" : 2,
 "Part" : {"Description" : "Lethal Weapon",

"UnitPrice" : 19.95,

"UPCCode" : 85391628927},

"Quantity" : 5.0}]}');

Using JSON_query_column: Example The statement in this example queries JSON data for a specific JSON
property using the JSON_query_column clause, and returns the property value in a column.

The statement first applies a row path expression to column po_document, which results in a match to
the ShippingInstructions property. The COLUMNS clause then uses the JSON_query_column clause
to return the Phone property value in a VARCHAR2(100) column.

SELECT jt.phones

FROM j_purchaseorder,

JSON_TABLE(po_document, '$.ShippingInstructions'

COLUMNS

(phones VARCHAR2(100) FORMAT JSON PATH '$.Phone')) AS jt;

PHONES

------------------------------------------------------------------------------
-------

[{"type":"Office","number":"909-555-7307"},{"type":"Mobile","number":"415-555-
1234"}]

Using JSON_value_column: Example The statement in this example refines the statement in the previous
example by querying JSON data for specific JSON values using the JSON_value_column clause, and
returns the JSON values as SQL values in relational rows and columns.

The statement first applies a row path expression to column po_document, which results in a match to the
elements in the JSON array Phone. These elements are JSON objects that contain two members
named type and number. The statement uses the COLUMNS clause to return the type value for each object in
a VARCHAR2(10) column called phone_type, and the number value for each object in
a VARCHAR2(20) column called phone_num. The statement also returns an ordinal column
named row_number.

SELECT jt.*

FROM j_purchaseorder,

JSON_TABLE(po_document, '$.ShippingInstructions.Phone[*]'
COLUMNS (row_number FOR ORDINALITY,

phone_type VARCHAR2(10) PATH '$.type',

phone_num VARCHAR2(20) PATH '$.number'))

AS jt;

ROW_NUMBER PHONE_TYPE PHONE_NUM

---------- ---------- --------------------

1 Office 909-555-7307

2 Mobile 415-555-1234

Using JSON_exists_column: Examples The statements in this example test whether a JSON value exists in
JSON data using the JSON_exists_column clause. The first example returns the result of the test as a
'true' or 'false' value in a column. The second example uses the result of the test in the WHERE clause.

The following statement first applies a row path expression to column po_document, which results in a
match to the entire context item, or JSON document. It then uses the COLUMNS clause to return the requestor's
name and a string value of 'true' or 'false' indicating whether the JSON data for that requestor contains a
zip code. The COLUMNS clause first uses the JSON_value_column clause to return the Requestor value in
a VARCHAR2(32) column called requestor. It then uses the JSON_exists_column clause to determine
if the zipCode object exists and returns the result in a VARCHAR2(5) column called has_zip.

SELECT requestor, has_zip

FROM j_purchaseorder,

JSON_TABLE(po_document, '$'

COLUMNS

(requestor VARCHAR2(32) PATH '$.Requestor',

has_zip VARCHAR2(5) EXISTS PATH '$.ShippingInstructions.Address.zipCode'));

REQUESTOR HAS_ZIP

-------------------------------- -------

Alexis Bull true

The following statement is similar to the previous statement, except that it uses the value of has_zip in
the WHERE clause to determine whether to return the Requestor value:
SELECT requestor

FROM j_purchaseorder,

JSON_TABLE(po_document, '$'

COLUMNS

(requestor VARCHAR2(32) PATH '$.Requestor',

has_zip VARCHAR2(5) EXISTS PATH '$.ShippingInstructions.Address.zipCode'))

WHERE (has_zip = 'true');

REQUESTOR

--------------------------------

Alexis Bull

Using JSON_nested_path: Examples The following two simple statements demonstrate the functionality of

the JSON_nested_path clause. They operate on a simple JSON array that contains three elements. The first
two elements are numbers. The third element is a nested JSON array that contains two string value elements.

The following statement does not use the JSON_nested_path clause. It returns the three elements in the
array in a single row. The nested array is returned in its entirety.

SELECT *

FROM JSON_TABLE('[1,2,["a","b"]]', '$'

COLUMNS (outer_value_0 NUMBER PATH '$[0]',

outer_value_1 NUMBER PATH '$[1]',

outer_value_2 VARCHAR2(20) FORMAT JSON PATH '$[2]'));

OUTER_VALUE_0 OUTER_VALUE_1 OUTER_VALUE_2

------------- ------------- --------------------

1 2 ["a","b"]

The following statement is different from the previous statement because it uses
the JSON_nested_path clause to return the individual elements of the nested array in individual columns in
a single row along with the parent array elements.
SELECT *

FROM JSON_TABLE('[1,2,["a","b"]]', '$'

COLUMNS (outer_value_0 NUMBER PATH '$[0]',

outer_value_1 NUMBER PATH '$[1]',

NESTED PATH '$[2]'

COLUMNS (nested_value_0 VARCHAR2(1) PATH '$[0]',

nested_value_1 VARCHAR2(1) PATH '$[1]')));

OUTER_VALUE_0 OUTER_VALUE_1 NESTED_VALUE_0 NESTED_VALUE_1

------------- ------------- -------------- --------------

1 2 a b

The previous example shows how to use JSON_nested_path with a nested JSON array. The following
example shows how to use the JSON_nested_path clause with a nested JSON object by returning the
individual elements of the nested object in individual columns in a single row along with the parent object
elements.

SELECT *

FROM JSON_TABLE('{a:100, b:200, c:{d:300, e:400}}', '$'

COLUMNS (outer_value_0 NUMBER PATH '$.a',

outer_value_1 NUMBER PATH '$.b',

NESTED PATH '$.c'

COLUMNS (nested_value_0 NUMBER PATH '$.d',

nested_value_1 NUMBER PATH '$.e')));

OUTER_VALUE_0 OUTER_VALUE_1 NESTED_VALUE_0 NESTED_VALUE_1

------------- ------------- -------------- --------------

100 200 300 400

The following statement uses the JSON_nested_path clause when querying the j_purchaseorder table.

It first applies a row path expression to column po_document, which results in a match to the entire context
item, or JSON document. It then uses the COLUMNS clause to return the Requestor value in
a VARCHAR2(32) column called requestor. It then uses the JSON_nested_path clause to return the
property values of the individual objects in each member of the nested Phone array. Note that a row is
generated for each member of the nested array, and each row contains the corresponding Requestor value.

SELECT jt.*

FROM j_purchaseorder,

JSON_TABLE(po_document, '$'

COLUMNS

(requestor VARCHAR2(32) PATH '$.Requestor',

NESTED PATH '$.ShippingInstructions.Phone[*]'

COLUMNS (phone_type VARCHAR2(32) PATH '$.type',

phone_num VARCHAR2(20) PATH '$.number')))

AS jt;

REQUESTOR PHONE_TYPE PHONE_NUM

-------------------- -------------------- ---------------

Alexis Bull Office 909-555-7307

Alexis Bull Mobile 415-555-1234

JSON_VALUE
Note:
The JSON_VALUE function is available starting with Oracle Database 12c Release 1 (12.1.0.2).

Syntax

Description of the illustration ''json_value.gif''

JSON_path_expression::=

Description of the illustration ''json_path_expression.gif''

object_step::=

Description of the illustration ''object_step.gif''

array_step::=

Description of the illustration ''array_step.gif''

JSON_value_returning_clause::=

Description of the illustration ''json_value_returning_clause.gif''

JSON_value_return_type::=
Description of the illustration ''json_value_return_type.gif''

JSON_value_on_error_clause::=

Description of the illustration ''json_value_on_error_clause.gif''

Purpose
JSON_VALUE finds a specified scalar JSON value in JSON data and returns it as a SQL value.

expr

Use this clause to specify the JSON data to be evaluated. For expr, specify an expression that evaluates to a
text literal. If expr is a column, then the column must be of data type VARCHAR2, CLOB, or BLOB. If expr is
null, then the function returns null.

If expr is not a text literal of well-formed JSON data using strict or lax syntax, then the function returns null
by default. You can use the JSON_value_on_error_clause to override this default behavior. Refer to
the JSON_value_on_error_clause.

FORMAT JSON

You must specify FORMAT JSON if expr is a column of data type BLOB.

JSON_path_expression

Use this clause to specify a JSON path expression. The function uses the path expression to evaluate expr and
find a scalar JSON value that matches, or satisfies, the path expression. The path expression must be a text
literal.
The path expression must begin with a dollar sign ($), which represents the context item, that is, the expression
specified by expr. The dollar sign is followed by zero or more steps, each of which can be an object step or an
array step.

The function attempts to match the first step in the path expression to the context item. If the first step results
in a match, then the function attempts to match the second step to the JSON value(s) that matched the first
step. If the second step results in a match, then the function attempts to match the third step to the JSON
value(s) that matched the second step, and so on. If the final step matches a scalar JSON value, then the
function returns that value as a SQL value. A path expression that consists of a dollar sign followed by zero
steps ('$') matches the entire context item.

You can specify the JSON_value_returning_clause to control the data type and format of the returned
SQL value. Refer to the JSON_value_returning_clause.

If any step in the path expression does not result in a match, or if the final step matches a nonscalar value, then
the function returns null by default. You can use the JSON_value_on_error_clause to override this default
behavior. Refer to the JSON_value_on_error_clause.

object_step Use this clause to specify an object step.

 Use simple_name or complex_name to specify a property name. If a member with that property

name exists in the JSON object being evaluated, then the object step results in a match to the property
value of that member. Otherwise, the object step does not result in a match. Both types of names are
case-sensitive. Therefore, a match will result only if the alphabetic character cases match in the object
step and the JSON data.

A simple_name can contain only alphanumeric characters and must begin with an alphabetic
character. A complex_name can contain only alphanumeric characters and spaces, and must begin
with an alphanumeric character. A complex_name must be enclosed in double quotation marks.

 Use the asterisk wildcard symbol (*) to specify all property names. If the JSON object being evaluated
contains at least one member, then the object step results in a match to the values of all members.
Otherwise, the object step does not result in a match.

If you apply an object step to a JSON array, then the array is implicitly unwrapped and the elements of the
array are evaluated using the object step. This is called JSON path expression relaxation. Refer to Oracle XML
DB Developer's Guide for more information.

If the JSON value being evaluated is not a JSON object, then the object step does not result in a match.

array_step Use this clause to specify an array step.

 Use integer to specify the element at index integer in a JSON array. Use integer TO integer to

specify the range of elements between the two index integer values, inclusive. If the specified
elements exist in the JSON array being evaluated, then the array step results in a match to those
elements. Otherwise, the array step does not result in a match. The first element in a JSON array has
index 0.
 Use the asterisk wildcard symbol (*) to specify all elements in a JSON array. If the JSON array being
evaluated contains at least one element, then the array step results in a match to all elements in the
JSON array. Otherwise, the array step does not result in a match.
If the JSON data being evaluated is not a JSON array, then the data is implicitly wrapped in an array and then
evaluated using the array step. This is called JSON path expression relaxation. Refer to Oracle XML DB
Developer's Guide for more information.

JSON_value_returning_clause

Use this clause to specify the data type and format of the value returned by this function.

RETURNING Use the RETURNING clause to specify the data type of the return value. If you omit this clause,
then JSON_VALUE returns a value of type VARCHAR2(4000).

You can use JSON_value_return_type to specify the following data types:

 VARCHAR2[(size [BYTE,CHAR])]

If you specify this data type, then the scalar value returned by this function can be a character or
number value. A number value will be implicitly converted to a VARCHAR2. When specifying
the VARCHAR2 data type elsewhere in SQL, you are required to specify a size. However, in this clause
you can omit the size. In this case, JSON_VALUE returns a value of type VARCHAR2(4000).

Refer to "VARCHAR2 Data Type" for more information.

 NUMBER[(precision [, scale])]

If you specify this data type, then the scalar value returned by this function must be a number value.

Refer to "NUMBER Data Type" for more information.

If the data type is not large enough to hold the return value, then this function returns null by default. You can
use the JSON_value_on_error_clause to override this default behavior. Refer to
the JSON_value_on_error_clause.

ASCII Specify ASCII to automatically escape any non-ASCII Unicode characters in the return value, using
standard ASCII Unicode escape sequences.

JSON_value_on_error_clause

Use this clause to specify the value returned by this function when any of the following errors occur:

 expr is not well-formed JSON data using strict or lax JSON syntax
 A nonscalar value or no match is found when the JSON data is evaluated using the JSON path
expression
 The return value data type is not large enough to hold the return value

You can specify the following clauses:

 NULL ON ERROR - Returns null when an error occurs. This is the default.

 ERROR ON ERROR - Returns the appropriate Oracle error when an error occurs.
  DEFAULT literal ON ERROR - Returns literal when an error occurs. If the data type of the value
returned by this function is VARCHAR2, then you must specify a text literal. If the data type
is NUMBER, then you must specify a numeric literal.

Examples
The following query returns the value of the member with property name a. Because the RETURNING clause is
not specified, the value is returned as a VARCHAR2(4000) data type:

SELECT JSON_VALUE('{a:100}', '$.a') AS value

FROM DUAL;

VALUE

-----

100

The following query returns the value of the member with property name a. Because
the RETURNING NUMBER clause is specified, the value is returned as a NUMBER data type:

SELECT JSON_VALUE('{a:100}', '$.a' RETURNING NUMBER) AS value

FROM DUAL;

VALUE

----------

100

The following query returns the value of the member with property name b, which is in the value of the
member with property name a:

SELECT JSON_VALUE('{a:{b:100}}', '$.a.b') AS value

FROM DUAL;

VALUE

-----

100
The following query returns the value of the member with property name d in any object:

SELECT JSON_VALUE('{a:{b:100}, c:{d:200}, e:{f:300}}', '$.*.d') AS value

FROM DUAL;

VALUE

-----

200

The following query returns the value of the first element in an array:

SELECT JSON_VALUE('[0, 1, 2, 3]', '$[0]') AS value

FROM DUAL;

VALUE

-----

The following query returns the value of the third element in an array. The array is the value of the member
with property name a.

SELECT JSON_VALUE('{a:[5, 10, 15, 20]}', '$.a[2]') AS value

FROM DUAL;

VALUE

-----

15

The following query returns the value of the member with property name a in the second object in an array:

SELECT JSON_VALUE('[{a:100}, {a:200}, {a:300}]', '$[1].a') AS value

FROM DUAL;
VALUE

-----

200

The following query returns the value of the member with property name c in any object in an array:

SELECT JSON_VALUE('[{a:100}, {b:200}, {c:300}]', '$[*].c') AS value

FROM DUAL;

VALUE

-----

300

The following query attempts to return the value of the member that has property name lastname. However,
such a member does not exist in the specified JSON data, resulting in no match. Because the ON ERROR clause
is not specified, the statement uses the default NULL ON ERROR and returns null.

SELECT JSON_VALUE('{firstname:"John"}', '$.lastname') AS "Last Name"

FROM DUAL;

Last Name

---------

The following query results in an error because it attempts to return the value of the member with property
name lastname, which does not exist in the specified JSON. Because the ON ERROR clause is specified, the
statement returns the specified text literal.

SELECT JSON_VALUE('{firstname:"John"}', '$.lastname'

DEFAULT 'No last name found' ON ERROR) AS "Last Name"

FROM DUAL;

Last Name
 ---------

No last name found

LAG
Syntax

Description of the illustration ''lag.gif''

See Also:
"Analytic Functions" for information on syntax, semantics, and restrictions, including valid forms
of value_expr

Purpose
LAG is an analytic function. It provides access to more than one row of a table at the same time without a self
join. Given a series of rows returned from a query and a position of the cursor, LAG provides access to a row at
a given physical offset prior to that position.

For the optional offset argument, specify an integer that is greater than zero. If you do not specify offset,
then its default is 1. The optional default value is returned if the offset goes beyond the scope of the
window. If you do not specify default, then its default is null.

{RESPECT | IGNORE} NULLS determines whether null values of value_expr are included in or eliminated

from the calculation. The default is RESPECT NULLS.

You cannot nest analytic functions by using LAG or any other analytic function for value_expr. However,
you can use other built-in function expressions for value_expr.

See Also:
"About SQL Expressions" for information on valid forms of expr and LEAD
Examples
The following example provides, for each purchasing clerk in the employees table, the salary of the
employee hired just before:

SELECT hire_date, last_name, salary,

LAG(salary, 1, 0) OVER (ORDER BY hire_date) AS prev_sal

FROM employees

WHERE job_id = 'PU_CLERK'

ORDER BY hire_date;

HIRE_DATE LAST_NAME SALARY PREV_SAL

--------- ------------------------- ---------- ----------

18-MAY-03 Khoo 3100 0

24-JUL-05 Tobias 2800 3100

24-DEC-05 Baida 2900 2800

15-NOV-06 Himuro 2600 2900

10-AUG-07 Colmenares 2500 2600

LAST
Syntax

last::=
 Description of the illustration ''last.gif''

See Also:
"Analytic Functions" for information on syntax, semantics, and restrictions of
the query_partitioning_clause

Purpose
FIRST and LAST are very similar functions. Both are aggregate and analytic functions that operate on a set of
values from a set of rows that rank as the FIRST or LAST with respect to a given sorting specification. If only
one row ranks as FIRST or LAST, then the aggregate operates on the set with only one element.

Refer to FIRST for complete information on this function and for examples of its use.