Oracle 1Z0-183 cheat sheet

Declaration syntax: name [CONSTANT] datatype [NOT NULL] [:= value | DEFAULT expression]; a NOT NULL variable must be initialized in the same declaration or compilation fails with PLS-00218.

%TYPE anchors a variable to a column or to another variable's datatype; %ROWTYPE declares a record matching a table, view or cursor.

BOOLEAN holds TRUE, FALSE or NULL and cannot be stored in a table column or referenced directly in a SQL statement.

Identifiers must begin with a letter and may contain letters, digits, underscore, dollar sign and hash mark; the limit is 30 characters in 12c Release 1.

Use := for PL/SQL assignment and SELECT ... INTO to assign query results; a colon prefixes host (bind) variables referenced from SQL*Plus.

String literals are enclosed in single quotes with embedded quotes doubled; q'[...]' delimits strings that themselves contain quotes.

A variable declared in an inner block shadows an identically named variable of an outer block; qualify with a block label to reach the outer variable.

PLS_INTEGER and BINARY_INTEGER perform integer arithmetic faster than NUMBER; NUMBER(p,s), BINARY_FLOAT and BINARY_DOUBLE are the other numeric types.

DBMS_OUTPUT.PUT_LINE output is displayed only after SET SERVEROUTPUT ON in SQL*Plus.

Static SQL allowed in PL/SQL: SELECT INTO, INSERT, UPDATE, DELETE, MERGE, COMMIT, ROLLBACK, SAVEPOINT and LOCK TABLE; DDL requires dynamic SQL.

SELECT INTO must return exactly one row: zero rows raises NO_DATA_FOUND, two or more rows raise TOO_MANY_ROWS.

Implicit cursor attributes SQL%FOUND, SQL%NOTFOUND, SQL%ROWCOUNT and SQL%ISOPEN describe the most recently executed DML statement.

SQL%ROWCOUNT is NULL until a DML statement runs; after a SELECT INTO that returns a row it evaluates to 1.

DML statements may include a RETURNING ... INTO clause to retrieve the modified column values into variables.

Statement-level atomicity is automatic: a failing statement is rolled back to its implicit savepoint without explicit transaction control.

Queries selecting only expressions or functions need FROM dual, for example SELECT SYSDATE INTO v_date FROM dual.

SQL statements inside PL/SQL cannot reference BOOLEAN variables, PL/SQL records or associative arrays unless the values are converted first.

IF ... THEN ... ELSIF ... ELSE ... END IF: the keyword is ELSIF, every branch ends with THEN and one END IF closes the whole statement.

A simple CASE compares one selector to WHEN values; a searched CASE evaluates Boolean conditions; no matching branch without ELSE raises CASE_NOT_FOUND.

WHILE tests its condition before each iteration; a basic LOOP executes at least once and needs EXIT or EXIT WHEN to terminate.

FOR i IN [REVERSE] low..high LOOP declares i implicitly as PLS_INTEGER, read-only and scoped to the loop; the bounds are evaluated once.

EXIT [label] [WHEN condition] leaves a loop; CONTINUE [label] [WHEN condition] skips to the next iteration in Oracle 11g and later.

Labels <> qualify EXIT and CONTINUE so that an outer loop can be targeted from an inner loop.

GOTO may jump only to a label in the same or an enclosing block, never into an IF, a loop or an inner block, and never out of an exception handler into executable code.

A condition that evaluates to NULL is treated as false by IF and WHILE.

TYPE rec IS RECORD (field datatype [NOT NULL] [:= init]); fields may use %TYPE and %ROWTYPE, and a whole record may be declared as table%ROWTYPE.

Associative arrays (INDEX BY) need no constructor and may be sparse; nested tables and VARRAYs require a constructor call and can be stored in table columns.

Collection methods: COUNT, DELETE, EXISTS, FIRST, LAST, NEXT, PRIOR, EXTEND, TRIM; EXTEND is invalid for associative arrays and DELETE is invalid for VARRAYs.

EXISTS(n) returns BOOLEAN and is the only method that is safe to apply to an element that does not exist.

FIRST and LAST return NULL for an empty collection; NEXT and PRIOR return NULL at the respective end of the collection.

A PL/SQL record cannot be supplied to INSERT ... VALUES as a single value; list the fields, or use a %ROWTYPE record with a matching column list.

Records of identical structure can be assigned to one another; assigning a %ROWTYPE record requires matching field names and compatible datatypes.

FORALL and BULK COLLECT reduce context switches between the PL/SQL engine and the SQL engine when processing collections.

Explicit cursor lifecycle: DECLARE the query, OPEN (executes it and sets %ROWCOUNT to 0), FETCH one row at a time, CLOSE to release the active set.

Attributes: %FOUND and %NOTFOUND reflect the last FETCH, %ROWCOUNT counts rows fetched so far, %ISOPEN is TRUE between OPEN and CLOSE.

After the last row, a fetch returns no data, %FOUND becomes FALSE and %NOTFOUND TRUE, and fetched variables keep their previous values.

A cursor FOR loop declares the cursor and its record implicitly, opens, fetches and closes automatically, and does not raise NO_DATA_FOUND.

Parameterized cursors declare formal parameters and are opened with actual values, for example CURSOR c(p NUMBER) IS ...; OPEN c(10);

FOR UPDATE locks the fetched rows; NOWAIT, WAIT n and SKIP LOCKED modify locking behavior, and WHERE CURRENT OF updates the row last fetched.

A cursor%ROWTYPE variable holds a complete row fetched from that cursor.

Structure: EXCEPTION WHEN name1 OR name2 THEN ... WHEN OTHERS THEN ... END; WHEN OTHERS must be the last handler.

Predefined exceptions include NO_DATA_FOUND, TOO_MANY_ROWS, ZERO_DIVIDE, VALUE_ERROR, INVALID_NUMBER, DUP_VAL_ON_INDEX, CURSOR_ALREADY_OPEN and INVALID_CURSOR.

PRAGMA EXCEPTION_INIT(exception_name, error_number) binds a declared exception name to a specific Oracle error number.

RAISE_APPLICATION_ERROR with a code between -20000 and -20999 raises a user-defined error; the optional third parameter TRUE replaces the error stack.

RAISE without an argument re-raises the current exception and is valid only inside an exception handler.

SQLCODE returns 0 in a WHEN OTHERS handler for a user-defined exception raised without a code; SQLERRM returns the associated message text.

An exception not handled in a block propagates to the enclosing block, and if never handled it propagates to the calling environment.

An exception raised inside a handler is not caught by that same block's handlers; it moves to the enclosing block.

CREATE OR REPLACE PROCEDURE name (params) IS|AS ... BEGIN ... END; a function adds a RETURN datatype and a RETURN statement on every execution path.

Parameter modes: IN (default, read-only), OUT (write-only, no default value), IN OUT (read-write, no default value); formal parameters cannot carry constraints.

A function's RETURN datatype cannot include a constraint such as NUMBER(10,2) or VARCHAR2(10).

The package specification declares public items; the body holds implementation and private items and may be omitted only if the specification declares no cursors or subprograms.

Package variables persist for the whole session, are initialized on first reference and reset on recompile or when the session ends.

Overloading requires parameter lists that differ in number, datatype or order; return type, parameter names or modes alone cannot distinguish overloads.

AUTHID DEFINER (the default) checks privileges as the owner; AUTHID CURRENT_USER uses the invoker's privileges and name resolution.

Only functions without OUT parameters and with suitable purity restrictions can be invoked from SQL statements.

EXECUTE IMMEDIATE 'sql' [INTO ...] [USING ...] handles single-row dynamic SQL, DDL and DML; DBMS_SQL handles multi-row and unknown-shape dynamic SQL.

DBMS_SQL sequence: OPEN_CURSOR, PARSE, BIND_VARIABLE, DEFINE_COLUMN, EXECUTE, FETCH_ROWS, COLUMN_VALUE, CLOSE_CURSOR.

DBMS_OUTPUT.PUT_LINE needs SET SERVEROUTPUT ON; PUT buffers without a newline; the default buffer is 20000 bytes and ENABLE(NULL) removes the limit.

UTL_FILE requires a directory object or UTL_FILE_DIR configuration; FOPEN, PUT_LINE/GET_LINE, FFLUSH and FCLOSE manage operating system files.

DBMS_UTILITY.FORMAT_ERROR_STACK, FORMAT_ERROR_BACKTRACE and FORMAT_CALL_STACK return diagnostic text suitable for error logging.

DBMS_RANDOM.VALUE returns a random number, DBMS_RANDOM.STRING returns random characters, and SEED makes a sequence repeatable.

DBMS_SCHEDULER, which supersedes DBMS_JOB, submits and manages scheduled jobs from PL/SQL.

CREATE TRIGGER ... BEFORE|AFTER|INSTEAD OF event ON object [FOR EACH ROW] [WHEN (condition)]; without FOR EACH ROW the trigger is statement-level.

Firing order for a row trigger: BEFORE statement, BEFORE row, AFTER row, AFTER statement; row triggers fire once per affected row.

:NEW and :OLD pseudo-records exist only in row-level and INSTEAD OF triggers; :OLD is undefined for INSERT and :NEW is undefined for DELETE.

In the WHEN clause reference NEW.column and OLD.column without the colon, and the condition cannot contain a subquery.

A BEFORE row trigger may assign values to :NEW fields; :OLD fields are read-only in every trigger type.

A compound trigger contains BEFORE STATEMENT, BEFORE EACH ROW, AFTER EACH ROW and AFTER STATEMENT sections plus shared global variables in one object.

A row-level trigger that queries or modifies its own table raises ORA-04091 (mutating table); statement-level or compound triggers avoid the error.

INSTEAD OF triggers are row-level only and are defined on views, including object views; DDL and system triggers fire on schema or database events.

PLSQL_WARNINGS takes ENABLE|DISABLE plus SEVERE, PERFORMANCE, INFORMATIONAL, ALL or a specific warning number, and can be set with ALTER SESSION or ALTER COMPILE.

Conditional compilation uses $IF ... $THEN ... $ELSE ... $END with inquiry directives $$PLSQL_UNIT, $$PLSQL_LINE, $$PLSQL_CCFLAGS and DBMS_DB_VERSION.

PLSQL_OPTIMIZE_LEVEL ranges from 0 to 3 (default 2) and PLSQL_CODE_TYPE is INTERPRETED or NATIVE; stored settings appear in ALL_PLSQL_OBJECT_SETTINGS.

Compilation errors are recorded in USER_ERRORS, and SQL*Plus SHOW ERRORS displays them after a failed CREATE OR REPLACE.

ALTER PROCEDURE|FUNCTION|PACKAGE name COMPILE [REUSE SETTINGS] recompiles an object; REUSE SETTINGS keeps the stored compiler parameters.

Dependencies are recorded in USER_DEPENDENCIES; a change to a referenced object marks dependents INVALID and they recompile on next invocation.

DBMS_UTILITY.COMPILE_SCHEMA or UTL_RECOMP recompiles invalid objects in a schema after mass changes.

DBMS_DDL.WRAP and the WRAP utility obfuscate source code so that stored wrapped units decompile only to unreadable text.