Pre-General Availability: 2017-09-04
This appendix lists all the supported SQL, SQL*Plus and SQLcl statements for REST Enabled SQL service.
Topics
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.
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
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; /
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.
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
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}
This section lists the possible values for option
which is either a term or a clause used in SHO[W] option
command.
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
Related Topics
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.
CTAS
DDL
SET DDL