This article is mainly focused for developers
- Row
Limiting Clause for Top-N Queries
- Top-N queries provide a method for
limiting the number of rows returned from ordered sets of data. They are
extremely useful when you want to return the top or bottom "N"
number of rows from a set or when you are paging through data
SELECT *
FROM table_name
ORDER BY column_name
LIMIT 0 , 50
[ OFFSET offset { ROW | ROWS }
]
[ FETCH { FIRST | NEXT } [ {
rowcount | percent PERCENT } ]
{ ROW | ROWS } { ONLY | WITH
TIES } ]
SELECT column1
FROM table_name
ORDER BY column1 DESC
FETCH FIRST 20 ROWS ONLY
- WITH Clause Enhancements
- We can declare PL/SQL functions and procedures in the
WITH Clause of a select statement
WITH
PROCEDURE with_procedure(p_id IN NUMBER) IS
BEGIN
DBMS_OUTPUT.put_line('p_id=' || p_id);
END;
FUNCTION with_function(p_id IN NUMBER) RETURN
NUMBER IS
BEGIN
with_procedure(p_id);
RETURN p_id;
END;
SELECT
with_function(id)
FROM table_name
WHERE rownum = 1
- Duplicate Indexes
- Can have both B-tree and bitmap index on same
column(s)
- VARCHAR2(32K)
- Currently max is 4000 bytes
- Auto-populate column from a sequence
- Using the DEFAULT clause for a column in create
table
CREATE TABLE table_name (
col1 NUMBER DEFAULT
default_seq.NEXTVAL,
col2 NUMBER DEFAULT ON NULL
default_on_null_seq.NEXTVAL,
description VARCHAR2(30)
);
- Implicit Result Sets
- DBMS_SQL.RETURN_RESULT and DBMS_SQL.GET_NEXT_RESULT
- Result sets that can be returned to a client, but do
not necessitate a formal named parameter. The client code to process such
a result set is identical now - the client code needs not change, the
stored procedure signature need not change
- PL/SQL Unit Security
- Grant access to a PL/SQL unit by granting a role to a PL/SQL unit, where the role contains the required rights for accessing the tables - so a function can only select, update etc from a table where it has been granted access.
- Invisible Columns
- Hide columns form the table in select and describe
- SET COLINVISIBLE ON / OFF to display the invisible
columns.
CREATE TABLE table_name (
col1 NUMBER,
col2 NUMBER INVISIBLE,
description VARCHAR2(30)
);
- UTL_CALL_STACK for programmatically inspecting the
PL/SQL Call Stack
- UTL_CALL_STACK package provides an API for inspecting
the PL/SQL Callstack. The package complements the DBMS_
UTILITY.FORMAT_CALL_STACK that returns a pretty print human readable
overview of the callstack.
- Pluggable Databases (Multitenant)
- Create a container database (CDB) that contains all of
the oracle level data and data dictionary. Create pluggable
databases (PDB) that contain user data and the user portion of the data
dictionary. Since the PDB files contain everything about the user data,
you can unplug a PDB from a CDB and plug it into a different CDB and be
up in seconds. All that needs to happen is a quick data dictionary update
in the CDB.
