Oracle 1Z0-183 flashcards

32 cards. Read the term and the definition here. The deck at the top of this page is optional.

%TYPE attribute

Anchors a variable's datatype to a column or to another variable, so it inherits the same datatype and size and adjusts automatically when the referenced declaration changes.

BOOLEAN variables in PL/SQL

They can hold only TRUE, FALSE or NULL and cannot be used in SQL statements, stored in database columns, or printed directly by DBMS_OUTPUT.PUT_LINE.

NOT NULL variable declaration

A variable declared NOT NULL must be assigned a value in the same declaration, otherwise compilation fails with PLS-00218; the constraint cannot be added later.

SELECT INTO returning no rows

Raises NO_DATA_FOUND when the query returns zero rows; if it returns two or more rows it raises TOO_MANY_ROWS, so it must return exactly one row.

SQL%ROWCOUNT attribute

Returns the number of rows affected by the most recent INSERT, UPDATE, DELETE or MERGE, and is NULL before any SQL statement executes in the block.

DDL inside PL/SQL

Data definition statements cannot appear as static SQL in a PL/SQL block; they must be issued through EXECUTE IMMEDIATE or the DBMS_SQL package.

CASE_NOT_FOUND exception

Raised by a CASE statement when no WHEN branch matches and no ELSE clause exists; the equivalent CASE expression in SQL returns NULL instead.

FOR loop counter scope

The counter is implicitly declared as PLS_INTEGER, exists only inside the loop, cannot be assigned to, and increases by one on each iteration.

GOTO statement restrictions

GOTO cannot jump into an IF statement, a loop or an inner block, and cannot jump from an exception handler back into the executable section of the block.

Associative array characteristics

A PL/SQL-only collection indexed by PLS_INTEGER or VARCHAR2 that needs no constructor, has no maximum size and may have gaps between index values.

Collection EXISTS method

EXISTS(n) returns BOOLEAN and is the only collection method that can safely be applied to a nonexistent element; other methods raise errors such as SUBSCRIPT_BEYOND_COUNT.

VARRAY versus nested table

A VARRAY has a fixed maximum size, is always dense and supports only TRIM; a nested table has no declared size, may become sparse and supports DELETE.

Initializing nested tables and VARRAYs

Both must be initialized with a constructor call before use, otherwise referencing them raises ORA-06531 for an uninitialized collection; associative arrays need no initialization.

Cursor %ISOPEN attribute

Returns TRUE while an explicit cursor is open, allowing code to test the state and avoid the CURSOR_ALREADY_OPEN exception before executing an OPEN statement.

Cursor FOR loop behavior

Implicitly opens the cursor, fetches each row into an implicitly declared record, closes the cursor after the last row and handles NO_DATA_FOUND automatically.

FOR UPDATE locking options

Locks rows when the cursor opens; NOWAIT fails immediately with ORA-00054, WAIT n waits n seconds, and SKIP LOCKED ignores rows already locked.

PRAGMA EXCEPTION_INIT

Associates a user-declared exception name with a specific Oracle error number so a handler can trap that error by name instead of relying on WHEN OTHERS.

RAISE_APPLICATION_ERROR usage

Raises a user-defined error with a number from -20000 to -20999 and a message of up to 2048 characters; the third parameter TRUE replaces the error stack.

SQLCODE in WHEN OTHERS

Returns 0 when the handler catches a user-defined exception raised without an error code, and a negative Oracle error number for internal exceptions.

Exception propagation to caller

An exception not handled in the current block propagates to the enclosing block; if it remains unhandled in the outermost block it returns to the calling environment.

Package state per session

Package variables and constants keep their values for the whole session, are initialized on first reference and reset when the package is recompiled or the session ends.

AUTHID CURRENT_USER clause

Causes name resolution and privilege checks to use the invoker's schema and rights instead of the owner's; the default is AUTHID DEFINER.

Overloading rules in packages

Subprograms can share a name only when their formal parameters differ in number, datatype or order; differing return type, parameter names or modes alone is insufficient.

OUT parameter defaults

OUT and IN OUT parameters cannot have default values and formal parameters cannot carry constraints; only IN parameters may be given a default expression.

EXECUTE IMMEDIATE USING clause

Passes bind variables to a dynamic SQL string in positional or named order; INTO captures a single returned row and RETURNING INTO captures DML-returned values.

DBMS_OUTPUT buffer limit

The buffer holds 20000 bytes by default; DBMS_OUTPUT.ENABLE(NULL) removes the limit, and SET SERVEROUTPUT ON in SQL*Plus displays buffered text.

UTL_FILE prerequisites

Requires a directory object or a UTL_FILE_DIR configuration entry, and the file must be opened with FOPEN before PUT_LINE or GET_LINE can be used.

Compound trigger structure

A single trigger with a global declaration section plus BEFORE STATEMENT, BEFORE EACH ROW, AFTER EACH ROW and AFTER STATEMENT sections that can share state.

Mutating table error

ORA-04091 is raised when a row-level trigger queries or modifies the table that fired it; a statement-level trigger or a compound trigger avoids the problem.

INSTEAD OF trigger restriction

It can be defined only on views, must be row-level, and fires in place of the triggering DML so that otherwise non-updatable views become modifiable.

PLSQL_WARNINGS parameter

Enables compiler warnings by category such as SEVERE, PERFORMANCE or INFORMATIONAL, or by warning number, through ALTER SESSION or per-object compilation settings.

Dependent object invalidation

When a referenced object changes, dependent PL/SQL units are marked INVALID in USER_OBJECTS and are recompiled automatically on their next invocation.