Oracle SELECT Statements
Version 12.1.0.1

General Information
Library Note Morgan's Library Page Footer
The Library is currently in the process of being upgraded from Oracle Database Version 11.2.0.3 to 12.1.0.1. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c.
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@orabase

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;
 
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);
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

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-2014 Daniel A. Morgan All Rights Reserved