Oracle Database SQL Language Reference

https://docs.oracle.com/en/database/oracle/oracle-database/26/sqlrf/
Admin
Oracle Database SQL Language Reference is the official documentation for SQL syntax, statements,...
Tokens:921,763
Snippets:11,360
Trust Score:9.7
Update:2 months ago
Show doc for...
Tokens:
Raw
### Create and Populate Table for Deletion Example

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/sqlrf/DELETE.html

Creates a partitioned table 'product_price_history' and inserts sample data for subsequent deletion examples.

```SQL
CREATE TABLE product_price_history (
  product_id          INTEGER NOT NULL,
  price               INTEGER NOT NULL,
  currency_code       VARCHAR2(3 CHAR) NOT NULL,
  effective_from_date DATE NOT NULL,
  effective_to_date   DATE,
  CONSTRAINT product_price_history_pk 
    PRIMARY KEY (product_id, currency_code, effective_from_date)
) PARTITION BY RANGE (effective_from_date) (
    PARTITION p0 VALUES less than (DATE'2015-01-02'),
    PARTITION p1 VALUES less than (DATE'2015-01-03'),
    PARTITION p2 VALUES less than (DATE'2015-01-04')
);

INSERT INTO product_price_history 
  WITH prices AS ( 
    SELECT 1, 100, 'USD', DATE'2015-01-01', DATE'2015-01-02' 
    FROM   dual UNION ALL 
    SELECT 1, 60, 'GBP', DATE'2015-01-01', DATE'2015-01-02' 
    FROM   dual UNION ALL 
    SELECT 1, 110, 'EUR', DATE'2015-01-01', DATE'2015-01-02' 
    FROM   dual UNION ALL 
    SELECT 1, 101, 'USD', DATE'2015-01-02', DATE'2015-01-03' 
    FROM   dual UNION ALL 
    SELECT 1, 62, 'GBP', DATE'2015-01-02', DATE'2015-01-03' 
    FROM   dual UNION ALL 
    SELECT 1, 109, 'EUR', DATE'2015-01-02', DATE'2015-01-03' 
    FROM   dual UNION ALL 
    SELECT 1, 105, 'USD', DATE'2015-01-03', NULL 
    FROM   dual UNION ALL 
    SELECT 1, 61, 'GBP', DATE'2015-01-03', NULL 
    FROM   dual UNION ALL 
    SELECT 1, 107, 'EUR', DATE'2015-01-03', NULL 
    FROM   dual UNION ALL 
    SELECT 2, 30, 'USD', DATE'2015-01-01', DATE'2015-01-03' 
    FROM   dual UNION ALL 
    SELECT 2, 33, 'USD', DATE'2015-01-03', NULL 
    FROM   dual UNION ALL 
    SELECT 3, 100, 'GBP', DATE'2015-01-03', NULL 
    FROM   dual 
  ) 
SELECT * 
FROM   prices;
```

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

### Create Tables for Data Insertion Examples

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/sqlrf/INSERT.html

Sets up the schema for the subsequent insertion examples, including primary and foreign key constraints.

```sql
CREATE TABLE people ( 
  person_id   INTEGER NOT NULL PRIMARY KEY, 
  given_name  VARCHAR2(100) NOT NULL, 
  family_name VARCHAR2(100) NOT NULL, 
  title       VARCHAR2(20), 
  birth_date  DATE 
);

CREATE TABLE patients ( 
  patient_id          INTEGER NOT NULL PRIMARY KEY REFERENCES people (person_id), 
  last_admission_date DATE 
);

CREATE TABLE staff ( 
  staff_id   INTEGER NOT NULL PRIMARY KEY REFERENCES people (person_id), 
  hired_date DATE 
);
```

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

### Start Database with Default SPFILE

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/sqlrf/CREATE-SPFILE.html

Starts the database using the default server parameter file. No PFILE parameter is needed in the STARTUP command.

```sql
STARTUP
```

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

### Create Tables for Property Graph Examples

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/sqlrf/create-property-graph.html

Sets up sample tables (EMP, DPT, FOF) with data and constraints, intended for use in property graph creation examples.

```SQL
CREATE TABLE EMP(
  ID NUMBER PRIMARY KEY,
  NAME VARCHAR2(10),
  AGE NUMBER,
  DEPARTMENT NUMBER,
  MANAGER NUMBER,
  CONSTRAINT emp_fk_mgr FOREIGN KEY(MANAGER) REFERENCES EMP(id)
);
INSERT INTO EMP VALUES(1, 'Alex', 25, 1, NULL);
INSERT INTO EMP VALUES(2, 'Bob', 27, 1, 1);
INSERT INTO EMP VALUES(3, 'Craig', 26, 2, NULL);
INSERT INTO EMP VALUES(4, 'Dan', 24, 2, 3);
INSERT INTO EMP VALUES(5, 'Erin', 25, 2, 3);
commit;

CREATE TABLE DPT(
  ID NUMBER PRIMARY KEY,
  NAME VARCHAR2(10)
);
INSERT INTO DPT VALUES(1, 'IT');
INSERT INTO DPT VALUES(2, 'Sales');
commit;

CREATE TABLE FOF(
  ID NUMBER PRIMARY KEY,
  SRC NUMBER,  
  DST NUMBER,
  E_COST NUMBER,
  CONSTRAINT fof_fk_src FOREIGN KEY(SRC) REFERENCES EMP(ID),
  CONSTRAINT fof_fk_dst FOREIGN KEY(DST) REFERENCES EMP(ID)
);
INSERT INTO FOF VALUES(1, 1, 2, 10);
INSERT INTO FOF VALUES(2, 2, 3, 20);
INSERT INTO FOF VALUES(3, 3, 4, 30);
INSERT INTO FOF VALUES(4, 4, 5, 40);
commit;
```

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

### Create a Basic Sequence

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/sqlrf/CREATE-SEQUENCE.html

This example creates a sequence named 'customers_seq' starting at 1000, incrementing by 1, without caching or cycling. The first call to customers_seq.nextval will return 1000.

```sql
CREATE SEQUENCE customers_seq
 START WITH     1000
 INCREMENT BY   1
 NOCACHE
 NOCYCLE;
```

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

### Create a Directory Object (Example)

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/sqlrf/CREATE-DIRECTORY.html

This is a concrete example of creating a directory database object. The path specified must exist on the server.

```SQL
CREATE DIRECTORY admin AS '/disk1/oracle/admin';
```

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

### USE_NL Hint Examples

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/sqlrf/Comments.html

Instructs the optimizer to use a nested loops join. These examples demonstrate basic usage and combination with LEADING hints.

```sql
SELECT /*+ USE_NL(l h) */ h.customer_id, l.unit_price * l.quantity
  FROM orders h, order_items l
  WHERE l.order_id = h.order_id;
```

```sql
select /*+ LEADING(t2) USE_NL(t1) */ sum(t1.a),sum(t2.a)
from   t1 , t2
where   t1.b = t2.b;
select * from table(dbms_xplan.display_cursor()) ;
```

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

### Create Table, Insert Data, and Get ETAG

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/sqlrf/sys_row_etag.html

This example demonstrates creating a table, inserting a row, and then using SYS_ROW_ETAG to generate an etag based on specific columns (c2 and c1). This is useful for optimistic concurrency control.

```SQL
CREATE TABLE foo (c1 NUMBER, c2 NUMBER, c3 NUMBER);

Table created.

INSERT INTO foo VALUES (1, 2, 3);

1 row created.

SELECT SYS_ROW_ETAG(c2, c1) FROM foo;

SYS_ROW_ETAG(C2,C1)
--------------------------------
3B978191AD0C828DA0E6A53EDF0B278A

---------------
```

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

### Start Database with Non-Default SPFILE

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/sqlrf/CREATE-SPFILE.html

Starts the database using a non-default server parameter file. A temporary PFILE containing the SPFILE path must be created and specified in the STARTUP command.

```sql
STARTUP PFILE=new_param.ora
```

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

### Example Execution Plan Output

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/sqlrf/EXPLAIN-PLAN.html

This is an example of the output generated by querying the plan_table, showing the hierarchical execution plan with operations, options, object names, and positions.

```text
 ID OPERATION            OPTIONS              OBJECT_NAME          OBJECT_ALIAS         POSITION
--- -------------------- -------------------- -------------------- -------------------- --------
  0 UPDATE STATEMENT                                                                           4
  1   UPDATE                                  EMPLOYEES                                        1
  2     INDEX            RANGE SCAN           EMP_DEPARTMENT_IX    EMPLOYEES@UPD$1             1
  3       TABLE ACCESS   BY INDEX ROWID       DEPARTMENTS          DEPARTMENTS@SEL$1           1
  4         INDEX        RANGE SCAN           DEPT_LOCATION_IX     DEPARTMENTS@SEL$1           1
```

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

### START LOGICAL STANDBY APPLY

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/sqlrf/img_text/start_standby_clause.html

Syntax definition for starting the logical standby apply process.

```APIDOC
## START LOGICAL STANDBY APPLY

### Description
Starts the application of redo logs on a logical standby database.

### Syntax
START LOGICAL STANDBY APPLY [ IMMEDIATE ] [ NODELAY ] [ NEW PRIMARY dblink | INITIAL [ scn_value ] | { SKIP FAILED TRANSACTION | FINISH } ]
```

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

### REGEXP_COUNT with Specified Start Position

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/sqlrf/REGEXP_COUNT.html

This example shows how to specify a starting position for the function to evaluate the source string, skipping initial occurrences of the pattern.

```SQL
SELECT REGEXP_COUNT('123123123123', '123', 3, 'i') COUNT FROM DUAL;

     COUNT
----------
         3


```

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

### Create and Populate Table for Demo

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/sqlrf/TO_BINARY_DOUBLE.html

Sets up a sample table with different numeric data types for demonstrating floating-point conversions.

```SQL
CREATE TABLE float_point_demo
  (dec_num NUMBER(10,2), bin_double BINARY_DOUBLE, bin_float BINARY_FLOAT);

INSERT INTO float_point_demo
  VALUES (1234.56,1234.56,1234.56);

SELECT * FROM float_point_demo;

   DEC_NUM BIN_DOUBLE  BIN_FLOAT
---------- ---------- ----------
   1234.56 1.235E+003 1.235E+003

```

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

### Get Container GUID using CON_ID_TO_GUID

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/sqlrf/con_id_to_guid.html

This statement demonstrates how to use the CON_ID_TO_GUID function to retrieve the GUID for a specific container, identified by its CON_ID (e.g., 4).

```sql
SELECT CON_ID_TO_GUID(4) "CON_GUID" FROM DUAL;
```

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

### GraphQL Duality View with Comments

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/sqlrf/graphql-table-function.html

Example of creating a JSON relational duality view with both GraphQL and SQL comments. GraphQL comments start with '#', and SQL comments start with '--' or are enclosed in '/* */'.

```SQL
CREATE OR REPLACE JSON RELATIONAL DUALITY VIEW student_ov AS 
    student { # this is a valid single line GraphQL comment
    _id: stuid
    Name: name
    -- SQL comments can be placed within GraphQL expression as well
};

```

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

### Create View and Table for Hint Testing

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/sqlrf/Comments.html

Setup script to create a view and a table used for demonstrating hint behavior.

```sql
CREATE VIEW v AS
  SELECT e.last_name, e.department_id, d.location_id
  FROM employees e, departments d
  WHERE e.department_id = d.department_id;
 
CREATE TABLE t AS
  SELECT * from employees
  WHERE employee_id < 200;
```

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

### Get Short Form of UCA Collation Name

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/sqlrf/NLS_COLLATION_NAME.html

This example demonstrates how to get the short form of a UCA collation name by providing the collation ID and the 'S' flag. This is useful for concise representation of UCA collations.

```sql
SELECT NLS_COLLATION_NAME(208897,'S')
  FROM DUAL;
```

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

### Get Current Session Timezone

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/sqlrf/SESSIONTIMEZONE.html

This example demonstrates how to retrieve the current session's time zone using the SESSIONTIMEZONE function.

```APIDOC
## SELECT SESSIONTIMEZONE FROM DUAL

### Description
Retrieves the time zone of the current session.

### Method
SELECT

### Endpoint
DUAL

### Response
#### Success Response (200)
- **SESSION** (VARCHAR2) - The time zone offset or region name of the current session.
```

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

### Create customers_demo table

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/sqlrf/Multiset-Operators.html

Initializes the demo table by copying data from the oe.customers table.

```sql
CREATE TABLE customers_demo AS
  SELECT * FROM customers;
```

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

### REGEXP_COUNT for Alphabetic Letter Followed by Digit at String Start

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/sqlrf/REGEXP_COUNT.html

This example counts occurrences of an alphabetic letter followed by a single digit, but only at the beginning of the string.

```SQL
select regexp_count('ABC123', '^[A-Z][0-9]'), regexp_count('A1B2C3', '^[A-Z][0-9]') from dual;

REGEXP_COUNT('ABC123','^[A-Z][0-9]') REGEXP_COUNT('A1B2C3','^[A-Z][0-9]')
------------------------------------ ------------------------------------
			   0				    1


```

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

### Get INT8 Vector Dimension Format

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/sqlrf/vector_dimension_format.html

Retrieves the INT8 storage format for a vector. This example shows the function usage with a vector and its specified dimensions.

```SQL
SELECT VECTOR_DIMENSION_FORMAT(TO_VECTOR('[34.6, 77.8, 9.10]', 3, INT8));

VECTOR_DIMENSION_FORMAT(TO_VECTOR('[34.6,77.8,9.10
--------------------------------------------------
INT8
```

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

### Setup Test Table and Data

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/sqlrf/is-json-condition.html

Create a table and insert various JSON-formatted strings for testing syntax and key uniqueness.

```sql
CREATE TABLE t (col1 VARCHAR2(100));
```

```sql
INSERT INTO t VALUES ( '[ "LIT192", "CS141", "HIS160" ]' );
INSERT INTO t VALUES ( '{ "Name": "John" }' );
INSERT INTO t VALUES ( '{ "Grade Values" : { A : 4.0, B : 3.0, C : 2.0 } }');
INSERT INTO t VALUES ( '{ "isEnrolled" : true }' );
INSERT INTO t VALUES ( '{ "isMatriculated" : False }' );
INSERT INTO t VALUES (NULL);
INSERT INTO t VALUES ('This is not well-formed JSON data');
```

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

### Get Session User UID

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/sqlrf/UID.html

Returns the integer that uniquely identifies the session user. This function requires no setup and can be used directly in SQL queries.

```SQL
SELECT UID FROM DUAL;
```

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

### Get Container Name by ID

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/sqlrf/con_id_to_con_name.html

This example demonstrates how to use the CON_ID_TO_CON_NAME function to retrieve the name of a container given its numeric ID. The result is aliased as 'CON_NAME'.

```sql
SELECT CON_ID_TO_CON_NAME(4) "CON_NAME" FROM DUAL;
     CON_NAME
     –-------
     SALESDB

```

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

### Create and Populate Table for REGEXP_REPLACE Examples

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/sqlrf/REGEXP_REPLACE.html

Sets up a temporary table and inserts sample data for demonstrating REGEXP_REPLACE string manipulation.

```SQL
CREATE TABLE regexp_temp(empName varchar2(20), emailID varchar2(20));

INSERT INTO regexp_temp (empName, emailID) VALUES ('John Doe', 'johndoe@example.com');
INSERT INTO regexp_temp (empName, emailID) VALUES ('Jane Doe', 'janedoe@example.com');
```

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

### Get Time Zone Offset for US/Eastern

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/sqlrf/TZ_OFFSET.html

This example demonstrates how to retrieve the time zone offset for the 'US/Eastern' time zone from UTC using the TZ_OFFSET function.

```sql
SELECT TZ_OFFSET('US/Eastern') FROM DUAL;
```

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

### STARTUP Command Syntax

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/sqlrf/img_text/startup_clauses.html

Defines the available clauses for the STARTUP command including MOUNT and OPEN options.

```APIDOC
## STARTUP Command Syntax

### Description
The STARTUP command is used to start an Oracle instance. This documentation covers the specific clauses for mounting and opening the database.

### Syntax
{ MOUNT [ { STANDBY | CLONE } DATABASE ] | OPEN { [ READ WRITE ] [ RESETLOGS | NORESETLOGS ] [ UPGRADE | DOWNGRADE ] | READ ONLY } }
```

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

### Aggregate MAX Function Example

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/sqlrf/MAX.html

Use this snippet to find the highest salary in the employees table. It requires no special setup beyond having the 'employees' table available.

```SQL
SELECT MAX(salary) "Maximum"
  FROM employees;
 
   Maximum
----------
     24000

```

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

### Create Table for Datetime Examples

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/sqlrf/TO_CHAR-datetime.html

Sets up a table with various timestamp data types to demonstrate formatting and time zone sensitivity.

```sql
CREATE TABLE date_tab (
   ts_col      TIMESTAMP,
   tsltz_col   TIMESTAMP WITH LOCAL TIME ZONE,
   tstz_col    TIMESTAMP WITH TIME ZONE);
```

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

### Create Object Types and Tables for REF Columns

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/sqlrf/ALTER-TABLE.html

Initial setup for object types and tables to demonstrate REF column constraints.

```sql
CREATE TYPE dept_t AS OBJECT 
   (deptno NUMBER, dname VARCHAR2(20));
/

CREATE TABLE staff 
   (name   VARCHAR2(100), 
    salary NUMBER,
    dept   REF dept_t); 
```

```sql
CREATE TABLE offices OF dept_t; 
```

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

### UPDATE statement with subquery

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/sqlrf/Automatic-Locks-in-DML-Operations.html

This example shows an UPDATE statement that includes a subquery. The subquery is guaranteed to be consistent as of the start of the query and does not see the effects of the DML statement it is part of.

```sql
UPDATE t SET x = ( SELECT y FROM t2 WHERE t2.z = t.z ) WHERE a > 5;
```

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

### Get FLOAT32 Vector Dimension Format

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/sqlrf/vector_dimension_format.html

This example demonstrates retrieving the FLOAT32 storage format for a vector using VECTOR_DIMENSION_FORMAT. The input vector and dimension count are specified.

```SQL
SELECT VECTOR_DIMENSION_FORMAT(TO_VECTOR('[34.6, 77.8, 9]', 3, FLOAT32));

VECTOR_DIMENSION_FORMAT(TO_VECTOR('[34.6,77.8,9]',
--------------------------------------------------
FLOAT32
```

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

### Create a Staging Table With Partitions

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/sqlrf/CREATE-TABLE.html

Syntax for creating a staging table that includes range partitioning.

```sql
CREATE TABLE part_staging_table (col1 number, col2 varchar2(100))
PARTITION BY RANGE (col1) (PARTITION p1 VALUES LESS THAN (100), PARTITION pmax VALUES LESS THAN (MAXVALUE)) 
FOR STAGING;
```

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

### Create Schema with Table, View, and Grant

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/sqlrf/CREATE-SCHEMA.html

This example demonstrates creating a schema named 'oe', defining a 'new_product' table, creating a 'new_product_view' based on that table, and granting SELECT privileges on the view to the 'hr' user. Each included statement (CREATE TABLE, CREATE VIEW, GRANT) is executed as part of a single transaction.

```sql
CREATE SCHEMA AUTHORIZATION oe
   CREATE TABLE new_product 
      (color VARCHAR2(10)  PRIMARY KEY, quantity NUMBER) 
   CREATE VIEW new_product_view 
      AS SELECT color, quantity FROM new_product WHERE color = 'RED' 
   GRANT select ON new_product_view TO hr; 

```

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

### Get Current Session Language with USERENV

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/sqlrf/USERENV.html

This example demonstrates how to retrieve the language setting for the current session using the USERENV function with the 'LANGUAGE' parameter. The output format is language_territory.characterset.

```SQL
SELECT USERENV('LANGUAGE') "Language" FROM DUAL;
```

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

### Get Character using National Character Set

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/sqlrf/CHR.html

Retrieves a character using the CHR function with the `USING NCHAR_CS` clause, specifying the national character set (UTF16 in this example).

```SQL
SELECT CHR (196 USING NCHAR_CS)
  FROM DUAL;
```

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

### Create List-Partitioned Table

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/sqlrf/CREATE-TABLE.html

Demonstrates creating a table partitioned by a list of values, including a default partition for unspecified values.

```sql
CREATE TABLE list_customers 
   ( customer_id             NUMBER(6)
   , cust_first_name         VARCHAR2(20) 
   , cust_last_name          VARCHAR2(20)
   , cust_address            CUST_ADDRESS_TYP
   , nls_territory           VARCHAR2(30)
   , cust_email              VARCHAR2(40))
   PARTITION BY LIST (nls_territory) (
   PARTITION asia VALUES ('CHINA', 'THAILAND'),
   PARTITION europe VALUES ('GERMANY', 'ITALY', 'SWITZERLAND'),
   PARTITION west VALUES ('AMERICA'),
   PARTITION east VALUES ('INDIA'),
   PARTITION rest VALUES (DEFAULT));
```

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

### Aggregate MIN Function Example

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/sqlrf/MIN.html

Use the MIN aggregate function to find the earliest hire date in the employees table. This requires no special setup beyond having the 'employees' table.

```SQL
SELECT MIN(hire_date) "Earliest"
  FROM employees;
```

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

### STARTUP Clause Syntax Diagram

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/sqlrf/img_text/startup_clauses.html

Use this syntax to define the state of the database during startup, including mount and open modes.

```sql
{ MOUNT [ { STANDBY | CLONE } DATABASE ]
| OPEN
  { [ READ WRITE ]
      [ RESETLOGS | NORESETLOGS ]
        [ UPGRADE | DOWNGRADE ]
  | READ ONLY
  }
}
```

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

### Hierarchical Path with SYS_CONNECT_BY_PATH

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/sqlrf/SYS_CONNECT_BY_PATH.html

This example demonstrates how to use SYS_CONNECT_BY_PATH to display the hierarchical path of employee names, starting from 'Kochhar'. The LPAD function is used for visual indentation based on the level in the hierarchy.

```SQL
SELECT LPAD(' ', 2*level-1)||SYS_CONNECT_BY_PATH(last_name, '/') "Path"
   FROM employees
   START WITH last_name = 'Kochhar'
   CONNECT BY PRIOR employee_id = manager_id;
```

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

### Get National Character using CHR with NCHAR_CS

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/sqlrf/NCHR.html

This example demonstrates the equivalent functionality of NCHR using the CHR function with the USING NCHAR_CS clause. It returns the character for binary value 187.

```SQL
SELECT CHR(187 USING NCHAR_CS)
  FROM DUAL;
```

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

### Insert and Get SCN using TIMESTAMP_TO_SCN

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/sqlrf/TIMESTAMP_TO_SCN.html

This example demonstrates how to insert a row with the current timestamp and then use TIMESTAMP_TO_SCN to retrieve the SCN associated with that insert operation. The returned SCN will vary per system.

```SQL
INSERT INTO orders (order_id, order_date, customer_id, order_total)
   VALUES (5000, SYSTIMESTAMP, 188, 2345);
1 row created.

COMMIT;
Commit complete.

SELECT TIMESTAMP_TO_SCN(order_date) FROM orders
   WHERE order_id = 5000;

TIMESTAMP_TO_SCN(ORDER_DATE)
----------------------------
                      574100

```

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

### Setup Employee Table

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/sqlrf/TO_CHAR-datetime.html

Creates and populates a temporary table for testing date formatting functions.

```sql
CREATE TABLE empl_temp 
  ( 
     employee_id NUMBER(6), 
     first_name  VARCHAR2(20), 
     last_name   VARCHAR2(25), 
     email       VARCHAR2(25), 
     hire_date   DATE DEFAULT SYSDATE, 
     job_id      VARCHAR2(10), 
     clob_column CLOB 
  );
INSERT INTO empl_temp
VALUES(111,'John','Doe','example.com','10-JAN-2015','1001','Experienced Employee');
INSERT INTO empl_temp
VALUES(112,'John','Smith','example.com','12-JAN-2015','1002','Junior Employee');
INSERT INTO empl_temp
VALUES(113,'Johnnie','Smith','example.com','12-JAN-2014','1002','Mid-Career Employee');
INSERT INTO empl_temp
VALUES(115,'Jane','Doe','example.com','15-JAN-2015','1005','Executive Employee');
```

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

### Find Employees by First Name Pattern

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/sqlrf/Pattern-matching-Conditions.html

Use REGEXP_LIKE to find employees whose first names start with 'Ste', followed by 'v' or 'ph', and end with 'en'. This example demonstrates basic string matching with alternatives.

```SQL
SELECT first_name, last_name
FROM employees
WHERE REGEXP_LIKE (first_name, '^Ste(v|ph)en$')
ORDER BY first_name, last_name;
```

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

### Create PFILE for Non-Default SPFILE Startup

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/sqlrf/CREATE-SPFILE.html

Creates a traditional parameter file containing a single line to specify the non-default SPFILE. This file is used to start the database.

```plaintext
spfile = 's_params.ora'
```

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

### Create Sequence and Table for Default Column

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/sqlrf/ALTER-TABLE.html

Sets up a sequence and a table to demonstrate DEFAULT ON NULL behavior.

```sql
CREATE SEQUENCE s1 START WITH 1;

CREATE TABLE t1 (name VARCHAR2(10));
INSERT INTO t1 VALUES('Kevin');
INSERT INTO t1 VALUES('Julia');
INSERT INTO t1 VALUES('Ryan');
```

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

### Querying a View on an XMLType Table

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/sqlrf/CREATE-VIEW.html

Demonstrates how to query a view created on an XMLType table, using XML functions to extract and filter data. This example shows how to get CLOB values and check for specific XML nodes.

```SQL
SELECT e.warehouse_xml.getclobval()
   FROM warehouse_view e
   WHERE EXISTSNODE(warehouse_xml, '//Docks') =1;
```

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

### Create a Domain and Add a Column

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/sqlrf/ALTER-TABLE.html

Example of creating a domain and adding a new column to a table with that domain.

```sql
CREATE DOMAIN phone_number as VARCHAR2(12)  
  CONSTRAINT CHECK (phone_number not like '%[0-9]%')
  NOT NULL;
```

```sql
ALTER TABLE customers ADD (cust_cell_phone_number Varchar2(12) DOMAIN phone_number);
```

```sql
ALTER TABLE customers ADD (cust_cell_phone_number Varchar2(12) DOMAIN phone_number DEFAULT ON NULL '650-000-0000');
```

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

### Get Previous Row Salary using LAG

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/sqlrf/LAG.html

This example demonstrates how to retrieve the salary of the previously hired employee for each purchasing clerk. The `LAG` function is used with an offset of 1 and a default value of 0. The `OVER` clause specifies the ordering by `hire_date`.

```SQL
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;
```

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

### Row Pattern Matching Setup

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/sqlrf/SELECT.html

Creates and populates the Ticker table for demonstrating row pattern matching.

```sql
CREATE TABLE Ticker (SYMBOL VARCHAR2(10), tstamp DATE, price NUMBER);

INSERT INTO Ticker VALUES('ACME', '01-Apr-11', 12);
INSERT INTO Ticker VALUES('ACME', '02-Apr-11', 17);
INSERT INTO Ticker VALUES('ACME', '03-Apr-11', 19);
INSERT INTO Ticker VALUES('ACME', '04-Apr-11', 21);
INSERT INTO Ticker VALUES('ACME', '05-Apr-11', 25);
INSERT INTO Ticker VALUES('ACME', '06-Apr-11', 12);
INSERT INTO Ticker VALUES('ACME', '07-Apr-11', 15);
INSERT INTO Ticker VALUES('ACME', '08-Apr-11', 20);
INSERT INTO Ticker VALUES('ACME', '09-Apr-11', 24);
INSERT INTO Ticker VALUES('ACME', '10-Apr-11', 25);
INSERT INTO Ticker VALUES('ACME', '11-Apr-11', 19);
INSERT INTO Ticker VALUES('ACME', '12-Apr-11', 15);
INSERT INTO Ticker VALUES('ACME', '13-Apr-11', 25);
INSERT INTO Ticker VALUES('ACME', '14-Apr-11', 25);
INSERT INTO Ticker VALUES('ACME', '15-Apr-11', 14);
INSERT INTO Ticker VALUES('ACME', '16-Apr-11', 12);
INSERT INTO Ticker VALUES('ACME', '17-Apr-11', 14);
INSERT INTO Ticker VALUES('ACME', '18-Apr-11', 24);
INSERT INTO Ticker VALUES('ACME', '19-Apr-11', 23);
INSERT INTO Ticker VALUES('ACME', '20-Apr-11', 22);
```

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

### Create Software Keystore

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/sqlrf/ADMINISTER-KEY-MANAGEMENT.html

Creates a password-protected software keystore in a specified directory. Requires a password for identification.

```SQL
ADMINISTER KEY MANAGEMENT
  CREATE KEYSTORE '/etc/ORACLE/WALLETS/orcl'
  IDENTIFIED BY password;

```

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

### Get Hire Date of Next Hired Employee

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/sqlrf/LEAD.html

This example demonstrates how to use the LEAD function to retrieve the hire date of the employee hired immediately after the current employee within Department 30. The default offset of 1 is used, and the results are ordered by hire date.

```sql
SELECT hire_date, last_name,
       LEAD(hire_date, 1) OVER (ORDER BY hire_date) AS "NextHired" 
  FROM employees
  WHERE department_id = 30
  ORDER BY hire_date;
```

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

### Create and Populate Temporary Table

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/sqlrf/REGEXP_COUNT.html

Sets up a temporary table and inserts sample data for demonstrating case-sensitive and case-insensitive searches with REGEXP_COUNT.

```SQL
CREATE TABLE regexp_temp(empName varchar2(20));

INSERT INTO regexp_temp (empName) VALUES ('John Doe');
INSERT INTO regexp_temp (empName) VALUES ('Jane Doe');
```

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

### Find Employees by ASCII Value of First Letter

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/sqlrf/ASCII.html

Use the ASCII function to filter records based on the numerical value of the first character of a string. This example finds employees whose last names start with 'L' by checking if the ASCII value of the first letter is 76.

```SQL
SELECT last_name
  FROM employees
  WHERE ASCII(SUBSTR(last_name, 1, 1)) = 76
  ORDER BY last_name;
```

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

### SQL LIKE Condition Examples

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/sqlrf/CREATE-MATERIALIZED-ZONEMAP.html

Use the LIKE condition to perform pattern matching on text data. The left side must be a column name, and the right side must be a text literal containing at least one pattern matching character ('_' for a single character, '%' for zero or more characters). The pattern cannot start with a pattern matching character.

```sql
WHERE prod_name LIKE 'DVD%'
```

```sql
WHERE prod_name LIKE 'Model%Cordless%Battery'
```

```sql
WHERE prod_name LIKE 'CD%Pack of _'
```

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

### Domain Creation Syntax

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/sqlrf/img_text/create_domain.html

This snippet shows the general syntax for creating various types of domains. Use the appropriate command based on the domain's structure.

```sql
( create_single_column_domain 
  | create_multi_column_domain 
  | create_flexible_domain )
```

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

### Create Directory Object and Insert BFILE Locator

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/sqlrf/BFILENAME.html

This example demonstrates how to create a database directory object and then use the BFILENAME function within an INSERT statement to associate a BFILE locator with a BFILE column in the 'print_media' table. Ensure the directory object name matches the case sensitivity in your data dictionary.

```sql
CREATE DIRECTORY media_dir AS '/demo/schema/product_media';

INSERT INTO print_media (product_id, ad_id, ad_graphic)
  VALUES (3000, 31001, BFILENAME('MEDIA_DIR', 'modem_comp_ad.gif'));

```

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

### TIME_BUCKET with YEAR Interval and START

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/sqlrf/time_bucket-datetime.html

Calculates the start of a 5-year time bucket containing '2022-06-29'. The 'START' argument specifies returning the beginning of the bucket.

```SQL
SELECT TIME_BUCKET ( DATE ‘2022-06-29’, INTERVAL ‘5’ YEAR, DATE ‘2000-01-01’, START);
```

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

### Retrieve Container IDs and GUIDs

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/sqlrf/CON_GUID_TO_ID.html

This query retrieves the container ID and GUID for all containers in a CDB from the V$CONTAINERS view. The GUID is displayed as its 32-character hexadecimal representation.

```sql
SELECT CON_ID, GUID
  FROM V$CONTAINERS;
```

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

### Create a Staging Table

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/sqlrf/CREATE-TABLE.html

Basic syntax for creating a staging table without partitions.

```sql
CREATE TABLE staging_table (col1 number, col2 varchar2(100)) FOR STAGING;
```

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

### Creating and Using a Restore Point

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/sqlrf/CREATE-RESTORE-POINT.html

Demonstrates creating a normal restore point, modifying data, and flashing back to the original state.

```sql
CREATE RESTORE POINT good_data;

SELECT salary FROM employees WHERE employee_id = 108;

    SALARY
----------
     12000

UPDATE employees SET salary = salary*10
   WHERE employee_id = 108;

SELECT salary FROM employees
   WHERE employee_id = 108;

    SALARY
----------
    120000

COMMIT;

FLASHBACK TABLE employees TO RESTORE POINT good_data;

SELECT salary FROM employees
   WHERE employee_id = 108;

    SALARY
----------
     12000
```

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

### Rebuilding an Outline Example

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/sqlrf/ALTER-OUTLINE.html

An example demonstrating how to regenerate a stored outline using the REBUILD clause.

```APIDOC
## Rebuilding an Outline: Example

The following statement regenerates a stored outline called `salaries` by compiling the text of the outline and replacing the old outline data with the outline created under current conditions.

```sql
ALTER OUTLINE salaries REBUILD;
```
```

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

### Configure ANYDATA Unpacked Storage

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/sqlrf/ALTER-TABLE.html

Demonstrates creating a table with an ANYDATA column and enabling unpacked storage for specific opaque types.

```sql
CREATE TABLE t1 (n NUMBER, x ANYDATA);
```

```sql
CREATE OR REPLACE TYPE clob_typ AS OBJECT (c clob);
/
```

```sql
ALTER TABLE t1 MODIFY OPAQUE TYPE x STORE (XMLType, clob_typ) UNPACKED;
```

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

### Extract Substring from Start

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/sqlrf/SUBSTR.html

Extracts a substring starting from a positive position. Counts characters from the beginning of the string.

```SQL
SELECT SUBSTR('ABCDEFG',3,4) "Substring"
     FROM DUAL;
 
Substring
---------
CDEF
```

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

### XDBURIType Examples

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/sqlrf/Data-Types.html

Examples of XDBURIType strings representing paths to XML documents within the database hierarchy.

```text
/home/oe/doc1.xml
/home/oe/doc1.xml#/orders/order_item
```

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

### Create a Table with Parallelism

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/sqlrf/CREATE-TABLE.html

Demonstrates creating a table using parallel execution servers versus serial execution.

```sql
CREATE TABLE dept_80
   PARALLEL
   AS SELECT * FROM employees
   WHERE department_id = 80;
```

```sql
CREATE TABLE dept_80
   AS SELECT * FROM employees
   WHERE department_id = 80;
```

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

### Create File Group or Tablespace from Template

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/sqlrf/ALTER-DISKGROUP.html

This example demonstrates creating a file group from a template and then creating a tablespace using that file group, inheriting properties from the template.

```SQL
ALTER DISKGROUP hmdg ADD FILEGROUP fgtem2 TEMPLATE 
    CREATE TABLESPACE tbs1 datafile '+hmdg(fg$fgtem2)/dbs/tbs1.f' size 1M
```

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

### Common SQL Condition Examples

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/sqlrf/About-SQL-Conditions.html

Various examples of valid SQL conditions including comparisons, IN clauses, and BETWEEN ranges.

```sql
name = 'SMITH' 
employees.department_id = departments.department_id 
hire_date > '01-JAN-08' 
job_id IN ('SA_MAN', 'SA_REP') 
salary BETWEEN 5000 AND 10000
commission_pct IS NULL AND salary = 2100
```

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

### ALTER ROLE Statement Examples

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/sqlrf/ALTER-ROLE.html

Examples demonstrating how to use the ALTER ROLE statement to modify existing roles.

```APIDOC
## ALTER ROLE Statement Examples

### Changing Role Identification

This example changes the role `warehouse_user` to `NOT IDENTIFIED`.

```sql
ALTER ROLE warehouse_user NOT IDENTIFIED;
```

### Changing a Role Password

This example changes the password for the `dw_manager` role to `data`.

```sql
ALTER ROLE dw_manager 
   IDENTIFIED BY data;
```

### Application Roles

This example changes the `dw_manager` role to an application role using the `hr.admin` package.

```sql
ALTER ROLE dw_manager IDENTIFIED USING hr.admin;
```
```

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

### Query and Populate Data

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/sqlrf/CREATE-TABLE.html

Examples of querying existing NULL values and populating the new table using a subquery.

```sql
SELECT employee_id, last_name, department_id
  FROM employees
  WHERE department_id IS NULL;
```

```sql
INSERT INTO myemp (employee_id, last_name, department_id)
  (SELECT employee_id, last_name, department_id from employees);
```

```sql
SELECT employee_id, last_name, department_id
  FROM myemp
  WHERE employee_id = 178;
```

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

### Create and Populate Source and Target Tables

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/sqlrf/MERGE.html

Creates two tables, 'people_source' and 'people_target', and populates them with sample data. This setup is used to demonstrate conditional inserts and updates using the MERGE statement.

```sql
CREATE TABLE people_source ( 
  person_id  INTEGER NOT NULL PRIMARY KEY, 
  first_name VARCHAR2(20) NOT NULL, 
  last_name  VARCHAR2(20) NOT NULL, 
  title      VARCHAR2(10) NOT NULL 
);

CREATE TABLE people_target ( 
  person_id  INTEGER NOT NULL PRIMARY KEY, 
  first_name VARCHAR2(20) NOT NULL, 
  last_name  VARCHAR2(20) NOT NULL, 
  title      VARCHAR2(10) NOT NULL 
);

INSERT INTO people_target VALUES (1, 'John', 'Smith', 'Mr');
INSERT INTO people_target VALUES (2, 'alice', 'jones', 'Mrs');
INSERT INTO people_source VALUES (2, 'Alice', 'Jones', 'Mrs.');
INSERT INTO people_source VALUES (3, 'Jane', 'Doe', 'Miss');
INSERT INTO people_source VALUES (4, 'Dave', 'Brown', 'Mr');

COMMIT;
```

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

### Calculate Week Difference with Custom Start Day

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/sqlrf/datediff.html

Calculates the difference in weeks using a custom start of the week parameter.

```sql
SELECT DATEDIFF (WEEK, TIMESTAMP '2005-12-31 23:59:59.9999999', TIMESTAMP '2006-01-01 00:00:00.0000000', 7);
```

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

### TO_DSINTERVAL - ISO Format Example

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/sqlrf/TO_DSINTERVAL.html

This example shows how to use TO_DSINTERVAL with the ISO duration format to calculate a future timestamp.

```APIDOC
## TO_DSINTERVAL - ISO Format Example

### Description
This example shows how to use TO_DSINTERVAL with the ISO duration format to calculate a future timestamp.

### Method
SQL Function Call

### Endpoint
N/A (SQL Function)

### Parameters
#### Function Arguments
- **argument** (character string) - The expression to convert to an INTERVAL DAY TO SECOND data type. Must be in ISO duration format.

### Request Example
```sql
SELECT TO_CHAR(TIMESTAMP '2009-01-01 00:00:00' + TO_DSINTERVAL('P100DT05H'),
   'YYYY-MM-DD HH24:MI:SS') "Time Stamp"
     FROM DUAL;
```

### Response
#### Success Response
Returns the formatted timestamp.

#### Response Example
```
Time Stamp
-------------------
2009-04-11 05:00:00
```
```
Tokens:
Raw