Oracle SELECT Statements
Version 21c

General Information
Library Note Morgan's Library Page Header
ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling complimentary technical Workshops on Database Security for the first 30 Oracle Database customers located anywhere in North America, EMEA, LATAM, or APAC that send an email to asra_us@oracle.com. Request a Workshop for your organization today.
 
Data Dictionary Objects
ALL TABLES USER_TABLES V$SQL_BIND_CAPTURE
ALL_TAB_COLS USER_TAB_COLS V$SQL_BIND_DATA
ALL_VIEWS USER_VIEWS V$SQL_BIND_METADATA
CDB_TABLES V$SQL V$SQL_PLAN
CDB_TAB_COLS V$SQLAREA V$SQL_PLAN_STATISTICS
CDB_VIEWS V$SQLAREA_PLAN_HASH V$SQL_PLAN_STATISTICS_ALL
DBA_TABLES V$SQLTEXT V$SQL_REDIRECTION
DBA_TAB_COLS V$SQLTEXT_WITH_NEWLINES V$SQL_WORKAREA
DBA_VIEWS    
Object Privileges -- privileges to tables and views granted through roles may not be valid within procedural code

GRANT select ON <object_name> TO <user_name>;
conn scott/tiger@pdbdev

GRANT select ON emp TO uwclass;
System Privileges
SELECT ANY DICTIONARY SELECT ANY TABLE  
 
Basic Select Statements
Select All Columns and All Records in a Single Table or View SELECT *
FROM <table_name>;
SELECT *
FROM all_tables;
Select Named Columns SELECT <column_name, column_name, ..., <column_name>
FROM <table_name>;
SELECT table_name, tablespace_name, num_rows
FROM all_tables;
Create Table As  (CTAS)

Note: Redo only created when in ARCHIVE LOG mode
CREATE TABLE <table_name> AS
SELECT <column_name, column_name, ..., <column_name>
FROM <table_name>;
CREATE TABLE t AS
SELECT *
FROM all_tables;

SELECT * FROM t;
SELECTs can go anywhere SELECT DECODE((SELECT 'x' FROM DUAL), (SELECT 'x' FROM DUAL), (SELECT 'Morgan' FROM dual)) AS RESULT
FROM (SELECT 'm' FROM dual)
WHERE (SELECT 1 FROM dual) = (SELECT 1 FROM dual)
AND (SELECT 2 FROM dual) BETWEEN (SELECT 1 FROM dual) AND (SELECT 3 FROM dual)
AND NVL((SELECT NULL FROM dual ), (SELECT 'z' FROM dual)) = (SELECT 'z' FROM dual)
ORDER BY (SELECT 1 FROM dual);
 
Select Unique Values
All

"All" is the default: Specifying it is optional
SELECT [ALL | DISTINCT | UNIQUE] <column_name_list>
FROM <table_name>;
SELECT ALL object_type
FROM all_objects;
Distinct SELECT DISTINCT <column_name_list>
FROM <table_name>;
SELECT DISTINCT object_type
FROM all_objects;
Unique SELECT UNIQUE <column_name_list>
FROM <table_name>;
SELECT UNIQUE object_type
FROM all_objects;
 
Allows limiting the rows returned by a query by specifying an offset and/or the number or percentage of rows to return SELECT <column_name_list>
FROM <schema_name.object_name>
ORDER BY <query_column_list>
[OFFSET <INTEGER> ROWS]
[FETCH <FIRST | NEXT>  <INTEGER> [PERCENT] ROWS ONLY]
The following statement returns the 5 employees with the lowest employee_id values:

SELECT employee_id, last_name
FROM employees
ORDER BY employee_id
FETCH FIRST 5 ROWS ONLY;

EMPLOYEE_ID LAST_NAME
----------- -------------------------
        100 King
        101 Kochhar
        102 De Haan
        103 Hunold
        104 Ernst


The following statement returns the next 5 employees with the lowest employee_id values:

SELECT employee_id, last_name
FROM employees
ORDER BY employee_id
OFFSET 5 ROWS FETCH NEXT 5 ROWS ONLY;

EMPLOYEE_ID LAST_NAME
----------- -------------------------
        105 Austin
        106 Pataballa
        107 Lorentz
        108 Greenberg
        109 Faviet


The following statement returns the 5 percent of employees with the lowest salaries:

SELECT employee_id, last_name, salary
FROM employees
ORDER BY salary
FETCH FIRST 5 PERCENT ROWS ONLY;

EMPLOYEE_ID LAST_NAME                     SALARY
----------- ------------------------- ----------
        132 Olson                           2100
        128 Markle                          2200
        136 Philtanker                      2200
        127 Landry                          2400
        135 Gee                             2400
        119 Colmenares                      2500


Because WITH TIES is specified, the following statement returns the 5 percent of employees with the lowest salaries, plus all additional employees with the same salary as the last row fetched in the previous example:

SELECT employee_id, last_name, salary
FROM employees
ORDER BY salary
FETCH FIRST 5 PERCENT ROWS WITH TIES;

EMPLOYEE_ID LAST_NAME                  SALARY
----------- ------------------------- -------
        132 Olson                        2100
        128 Markle                       2200
        136 Philtanker                   2200
        127 Landry                       2400
        135 Gee                          2400
        119 Colmenares                   2500
        131 Marlow                       2500
        140 Patel                        2500
        144 Vargas                       2500
        182 Sullivan                     2500
        191 Perkins                      2500
 
Select Statement With ROW_PATTERN Clause
Match Recognize table_reference ::=
  {only (query_table_expression) | query_table_expression }[flashback_query_clause]
  [pivot_clause|unpivot_clause|row_pattern_recognition_clause] [t_alias]

row_pattern_recognition_clause ::=
  MATCH_RECOGNIZE (
  [row_pattern_partition_by ]
  [row_pattern_order_by ]
  [row_pattern_measures ]
  [row_pattern_rows_per_match ]
  [row_pattern_skip_to ]
  PATTERN (row_pattern)
  [ row_pattern_subset_clause]
  DEFINE row_pattern_definition_list
  )

row_pattern_partition_by ::=
  PARTITION BY column[, column]...

row_pattern_order_by ::=
  ORDER BY column[, column]...

row_pattern_measures ::=
  MEASURES row_pattern_measure_column[, row_pattern_measure_column]...

row_pattern_measure_column ::=
  expression AS c_alias

row_pattern_rows_per_match ::=
  ONE ROW PER MATCH
  | ALL ROWS PER MATCH

row_pattern_skip_to ::=
  AFTER MATCH {
  SKIP TO NEXT ROW
  | SKIP PAST LAST ROW
  | SKIP TO FIRST variable_name
  | SKIP TO LAST variable_name
  | SKIP TO variable_name}

row_pattern ::=
  row_pattern_term
  | row_pattern "|" row_pattern_term

row_pattern_term ::=
  row_pattern_factor
  | row_pattern_term row_pattern_factor

row_pattern_factor ::=
  row_pattern_primary [row_pattern_quantifier]

row_pattern_quantifier ::=
  *[?]
  |+[?]
  |?[?]
  |"{"[unsigned_integer ],[unsigned_integer]"}"[?]
  |"{"unsigned_integer "}"

row_pattern_primary ::=
  variable_name
  |$
  |^
  |([row_pattern])
  |"{-" row_pattern"-}"
  | row_pattern_permute

row_pattern_permute ::=
  PERMUTE (row_pattern [, row_pattern] ...)

row_pattern_subset_clause ::=
  SUBSET row_pattern_subset_item [, row_pattern_subset_item] ...

row_pattern_subset_item ::=
  variable_name = (variable_name[ , variable_name]...)

row_pattern_definition_list ::=
  row_pattern_definition[, row_pattern_definition]...

row_pattern_definition ::=
  variable_name AS condition
TBD
 
Select Statement With SAMPLE Clause
Sample Clause Returning 1% Of Records and the BLOCK option.

Block sampling is possible only during full table scans or index fast full scans. If a more efficient execution path exists, then Oracle Database does not perform block sampling. If you want to guarantee block sampling for a particular table or index, then use the FULL or INDEX_FFS hint.
SELECT *
FROM <table_name>
SAMPLE [BLOCK] (percentage_of_rows)
WHERE <where_clause>;
CREATE TABLE t AS
SELECT object_name
FROM all_objects;

SELECT COUNT(*)
FROM t;

SELECT COUNT(*) * 0.01
FROM t;

SELECT *
FROM t
SAMPLE(1);

/
/
/

EXPLAIN PLAN FOR
SELECT *
FROM t
SAMPLE(1);

SELECT * FROM TABLE(dbms_xplan.display);

------------------------------------------------------------------------
| Id | Operation         | Name | Rows | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------
| 0 | SELECT STATEMENT   |      |  714 | 17136 |    60   (7)| 00:00:01 |
| 1 | TABLE ACCESS SAMPLE| T    |  714 | 17136 |    60   (7)| 00:00:01 |
------------------------------------------------------------------------


SELECT /*+ FULL */ COUNT(*)
FROM t
SAMPLE BLOCK(1);

/
/
/
/
/
/
/
/

EXPLAIN PLAN FOR
SELECT /*+ FULL */ COUNT(*)
FROM t
SAMPLE BLOCK (1);

SELECT * FROM TABLE(dbms_xplan.display);

------------------------------------------------------------------------
| Id | Operation         | Name | Rows | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------
| 0 | SELECT STATEMENT   |      |    1 |    12 |     2   (0)| 00:00:01 |
| 1 | SORT AGGREGATE     |      |    1 |    12 |            |          |
| 2 | TABLE ACCESS SAMPLE| T    |  714 |  8568 |     2   (0)| 00:00:01 |
------------------------------------------------------------------------
 
Select Statement With SEED Clause
The database always returns the same number of records given the same seed value SELECT *
FROM <table_name>
SAMPLE (percentage_of_rows)
SEED (<integer>);
SELECT *
FROM t
SAMPLE(1) SEED (1);

/
/
/

SELECT *
FROM t
SAMPLE(1) SEED(5);

/
/
/
 
Sample Clause Statement with a WHERE Clause
Sample Clause Returning 35% Of Records After Filtering With A WHERE Clause

For a full page of WHERE clause examples see the link at page bottom
SELECT *
FROM <table_name>
SAMPLE (<percentage_of_rows>)
WHERE ....
SELECT COUNT(*)
FROM t
WHERE object_name LIKE '%J%';

SELECT COUNT(*) * 0.35
FROM t
WHERE object_name LIKE '%J%';

SELECT *
FROM t
SAMPLE(35)
WHERE object_name LIKE '%J%';

SELECT *
FROM t
SAMPLE(35)
WHERE object_name LIKE '%J%';

SELECT *
FROM t
SAMPLE(35)
WHERE object_name LIKE '%J%';
 
Scalar Select
Select In Select Clause SELECT (
  SELECT <single_value
  FROM <table_name>
FROM <table_name>;
SELECT (SELECT 1 FROM dual) FROM dual;
 
Select Statement Using Functions
Date Function Example

For more examples with DATE Functions see link at page bottom
SELECT <date_function(<column_name>))
FROM <table_name>;
desc all_objects

SELECT object_name, TO_DATE(timestamp, 'YYYY-MM-DD:HH24:MI:SS')
FROM all_objects
WHERE rownum < 11;
Numeric Function Example

For more examples with NUMERIC Functions see link at page bottom
SELECT <numeric_function(<column_name>))
FROM <table_name>;
desc user_extents

SELECT SUM(bytes)/1024/1024 USED_MB
FROM user_extents;

SELECT segment_type, SUM(bytes)/1024/1024 USED_MB
FROM user_extents
GROUP BY segment_type;
String Function Example

For more examples with STRING Functions see link at page bottom
SELECT <string_function(<column_name>))
FROM <table_name>;
desc all_objects

SELECT object_name, LOWER(object_name) LOWER_ONAME
FROM all_objects
WHERE rownum < 11;
 
Select For Update
Lock Record(s) SELECT <column_name_list)
FROM <table_name_list>
FOR UPDATE;
CREATE TABLE parents (
pid  NUMBER(10),
cash NUMBER(10,2));

CREATE TABLE children
(cid NUMBER(10),
fid  NUMBER(10),
fin_level VARCHAR2(35));


DECLARE
 CURSOR x_cur IS
 SELECT pid
 FROM parents;

 x_rec x_cur%ROWTYPE;

 x NUMBER(10,2) := 18000.64;
 y NUMBER(10,2) := 100;
BEGIN
  DELETE FROM parents;
  DELETE FROM children;

  FOR i IN 1..25
  LOOP
    INSERT INTO parents
    VALUES (y, x);

    x := x+1235.31;
    y := y-1;
  END LOOP;

  y := 0;

  OPEN x_cur;
  LOOP
    FETCH x_cur INTO x_rec;
    EXIT WHEN x_cur%NOTFOUND;

    y := y+1;

    INSERT INTO children (cid, fid)
    VALUES (y, x_rec.pid);

    y := y+1;

    INSERT INTO children (cid, fid)
    VALUES (y, x_rec.pid);
  END LOOP;
  CLOSE x_cur;
  COMMIT;
END;
/


CREATE OR REPLACE PROCEDURE cursor_loop3 IS
 CURSOR x_cur IS
 SELECT pid, cash
 FROM parents
 WHERE cash < 35000
 FOR UPDATE;
BEGIN
   FOR x_rec IN x_cur
   LOOP
      UPDATE parents
      SET cash = FLOOR(cash)
      WHERE CURRENT OF x_cur;
   END LOOP;
   COMMIT;
END cursor_loop3;
/
FOR UPDATE with NOWAIT See Deadlocks Demo page
FOR UPDATE with WAIT See Deadlocks Demo page
FOR UPDATE with SKIP LOCKED See Deadlocks Demo page
 
Partition Table Select
Select From Named Partition SELECT DISTINCT <column_name_list>
FROM <table_name> PARTITION (<partition_name>);
CREATE TABLE pt (
deptno NUMBER(10),
state  VARCHAR2(2))
PARTITION BY LIST (state) (
PARTITION nw VALUES ('OR', 'WA'),
PARTITION sw VALUES ('AZ', 'CA', 'NM'));

INSERT INTO pt VALUES (1, 'WA');
INSERT INTO pt VALUES (1, 'OR');
INSERT INTO pt VALUES (1, 'CA');

SELECT COUNT(*) FROM pt;

SELECT COUNT(*) FROM pt PARTITION(nw);
SELECT COUNT(*) FROM pt PARTITION(sw);
 
Case Insensitive Select
Select that ignores upper/lower case characters conn sys@pdbdev as sysdba

GRANT select ON v_$nls_parameters TO uwclass;

SELECT * FROM sys.v_$nls_parameters WHERE parameter LIKE '%SORT%';

conn uwclass/uwclass

CREATE TABLE cis_test (
col1 VARCHAR2(10));

INSERT INTO cis_test VALUES ('one');
INSERT INTO cis_test VALUES ('TWO');
INSERT INTO cis_test VALUES ('thRee');
INSERT INTO cis_test VALUES ('FouR');
INSERT INTO cis_test VALUES ('fiVE');

SELECT * FROM cis_test;

SELECT col1 FROM cis_test ORDER BY 1;

ALTER SESSION SET nls_sort=binary_ci;

SELECT * FROM sys.v_$nls_parameters WHERE parameter LIKE '%SORT%';

SELECT col1 FROM cis_test ORDER BY 1;
 
PL/SQL SELECT INTO
SELECTing INTO a Scalar Variable conn uwclass/uwclass@pdbdev

CREATE TABLE t (
testcol NUMBER(3));

CREATE SEQUENCE seq;

SELECT seq.NEXTVAL FROM dual;

/
/

INSERT INTO t
(testcol)
VALUES
(seq.NEXTVAL);

/
/

SELECT * FROM t;

-- this will fail
BEGIN
  SELECT seq.NEXTVAL FROM dual;
END;
/

set serveroutput on

-- this will succeed
DECLARE
 x INTEGER;
BEGIN
  SELECT seq.NEXTVAL
  INTO x
  FROM dual;

  dbms_output.put_line(x);
END;
/

/
/
SELECTing INTO a ROWTYPE Variable conn uwclass/uwclass@pdbdev

CREATE TABLE t AS
SELECT table_name, tablespace_name
FROM all_tables;

set serveroutput on

DECLARE
 trec t%ROWTYPE;
BEGIN
  SELECT *
  INTO trec
  FROM t
  WHERE rownum = 1;

  dbms_output.put_line(trec.table_name);
END;
/

Related Topics
Analytic Functions
Conditions
Conversion Functions
Date Functions
Deadlocks
Delete Statements
Explain Plan
Group By & Having Clauses
Hints
Insert Statements
Joins
Nested Tables
Numeric Functions
Object Privileges
Operators
Oracle Built-in Functions
Order By Clause
Partitioned Tables
Pivot
Pseudocolumns
Roles
String Functions
System Privileges
Tuning
Types
Unpivot
Update
Where Clause
Wildcard Characters
With Clause
What's New In 21c
What's New In 23c

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2023 Daniel A. Morgan All Rights Reserved
  DBSecWorx