Pre-General Availability: 2017-09-04

D Supported SQL, SQL*Plus and SQLcl Statements

This appendix lists all the supported SQL, SQL*Plus and SQLcl statements for REST Enabled SQL service.

Topics

D.1 Supported SQL Statements

This section describes the SQL statements that the REST Enabled SQL service supports.

REST Enabled SQL service supports all SQL commands. If the specified Oracle database schema has the appropriate privileges, then you can run them. ORDS makes all queries into in-line views before execution to provide pagination support. Queries are made in-line irrespective of the format in which you provide the query. All the other nonquery SQL statements are executed as they are.

In-line views have the following limitations:
  • All column names in a query must be unique as the views and in-line views cannot have ambiguous column names

  • Cursor expressions are not displayed in view or in-line views

D.2 Supported PL/SQL Statements

The REST Enabled SQL service supports PL/SQL statements and blocks.

Example D-1 PL/SQL Statement

DECLARE v_message VARCHAR2(100) := 'Hello World';

BEGIN

  FOR i IN 1..3 LOOP

    DBMS_OUTPUT.PUT_LINE (v_message);

  END LOOP;

END;

/

D.3 Supported SQL*Plus Statements

This section lists all the SQL*Plus statements that the REST Enabled SQL service supports.

REST Enabled SQL service supports most of the SQL*Plus statements except those statements that are related to formatting. The specific Oracle database schema must have the appropriate privileges to run the SQL*Plus statemments.

Following is a list of supported SQL*Plus statements:
  • SET system_variable value

    Note:

    system_variable and value represent one of the clauses described in Set System Variables section.
  • / (slash)

  • COPY {FROM database | TO database | FROM database TO database} {APPEND | CREATE | INSERT | REPLACE} destination_table[(column, column, column, ...)] USING query

  • DEF[INE] [variable] | [variable = text]

  • DESC[RIBE] {[schema.]object[@connect_identifier]}

  • EXEC[UTE] statement

  • HELP | ? [topic]

  • PASSW[ORD] [username]

  • PRINT [variable ...]

  • PRO[MPT] [text]

  • REM[ARK]

  • SHO[W] [option]

  • TIMI[NG] [START text | SHOW | STOP]

  • UNDEF[INE] variable ...

  • VAR[IABLE] [variable [type][=value]]

  • WHENEVER OSERROR {EXIT [SUCCESS | FAILURE | n | variable | :BindVariable] [COMMIT | ROLLBACK] | CONTINUE[COMMIT | ROLLBACK | NONE]}

  • WHENEVER SQLERROR {EXIT [SUCCESS | FAILURE | WARNING | n | variable  | :BindVariable] [COMMIT | ROLLBACK] | CONTINUE [COMMIT | ROLLBACK | NONE]}

  • XQUERY xquery_statement

D.3.1 Set System Variables

Following is a list of possible values for system_variable and value:
  • SET APPI[NFO]{ON | OFF | text}

  • SET AUTOP[RINT] {ON | OFF}

  • SET AUTOT[RACE] {ON | OFF | TRACE[ONLY]} [EXP[LAIN]] [STAT[ISTICS]]

  • SET BLO[CKTERMINATOR] {. | c | ON | OFF}

  • SET CMDS[EP] {; | c | ON | OFF}

  • SET COLINVI[SIBLE] [ON | OFF]

  • SET CON[CAT] {. | c | ON | OFF}

  • SET COPYC[OMMIT] {0 | n}

  • SET DEF[INE] {& | c | ON | OFF}

  • SET DESCRIBE [DEPTH {1 | n | ALL}] [LINENUM {ON | OFF}] [INDENT {ON | OFF}]

  • SET ECHO {ON | OFF}

  • SET ERRORL[OGGING] {ON | OFF} [TABLE [schema.]tablename] [TRUNCATE] [IDENTIFIER identifier]

  • SET ESC[APE] {\ | c | ON | OFF}

  • SET FEED[BACK] {6 | n | ON | OFF | ONLY}]

  • SET SERVEROUT[PUT] {ON | OFF} [SIZE {n | UNL[IMITED]}] [FOR[MAT] {WRA[PPED] | WOR[D_WRAPPED] | TRU[NCATED]}]

  • SET SHOW[MODE] {ON | OFF}

  • SET SQLBL[ANKLINES] {ON | OFF}

  • SET SQLP[ROMPT] {SQL> | text}

  • SET TI[ME] {ON | OFF}

  • SET TIMI[NG] {ON | OFF}

  • SET VER[IFY] {ON | OFF}

  • SET XQUERY BASEURI {text}

  • SET XQUERY ORDERING {UNORDERED | ORDERED | DEFAULT}

  • SET XQUERY NODE {BYVALUE | BYREFERENCE | DEFAULT}

  • SET XQUERY CONTEXT {text}

D.3.2 Show System Variables

This section lists the possible values for option which is either a term or a clause used in SHO[W] option command.

Following is a list of possible values for option variable:
  • SHOW system_variable

  • SHOW EDITION

  • SHOW ERR[ORS] [ { ANALYTIC VIEW | ATTRIBUTE DIMENSION | HIERARCHY | FUNCTION | PROCEDURE | PACKAGE | PACKAGE BODY | TRIGGER | VIEW | TYPE | TYPE BODY | DIMENSION | JAVA CLASS } [schema.]name]

  • SHOW PDBS

  • SHOW SGA

  • SHOW SQLCODE

  • SHOW COLINVI[SIBLE]

  • SHOW APPIN[FO]

  • SHOW AUTOT[RACE]

  • SHOW BINDS

  • SHOW BLO[CK TERMINATOR]

  • SHOW CMDSEP

  • SHOW COPYTYPECHECK

  • SHOW COPYCOMMIT

  • SHOW DEFINE

  • SHOW DEFINES

  • SHOW DESCR[IBE]

  • SHOW ECHO

  • SHOW EDITION

  • SHOW ERRORL[OGGING]

  • SHOW ESC[APE]

  • SHOW FEEDBACK

  • SHOW CONCAT

  • SHOW SHOW[MODE]

  • SHOW RECYC[LEBIN]

  • SHOW RELEASE

  • SHOW SQLBL[ANKLINES]

  • SHOW SCAN

  • SHOW SERVEROUT[PUT]

  • SHOW SPACE

  • SHOW TABLES

  • SHOW TIMI[NG]

  • SHOW USER

  • SHOW VER[IFY]

  • SHOW XQUERY

D.4 Supported SQLcl Statements

This section lists the SQLcl statements that the REST Enabled SQL service supports.

REST Enabled SQL service supports some of the SQLcl statements. The specific Oracle database schema must have the appropriate privileges to run the SQLcl statements.

Following is a list of supported SQLcl statements:
  • CTAS

  • DDL

  • SET DDL