Oracle 1Z0-183 study guide
Oracle 1Z0-183 · Work through your exam, one topic at a time.
The Oracle Database 12c: Program with PL/SQL exam (1Z0-183) is the certification test for developers who write server-side PL/SQL rather than administer databases. It suits application developers, report and ETL developers, and database-side engineers who already read and write SQL comfortably and now want formal recognition for procedural work: anonymous blocks, stored procedures and functions, packages, triggers, and cursor-driven logic. The Oracle 1Z0-183 study guide has 10 sections and about 3,065 words.
01Declaring PL/SQL Variables and Writing Executable Statements
The general form is name [CONSTANT] datatype [NOT NULL] {:= DEFAULT} expression . Every variable starts life as NULL; a NOT NULL variable must be initialised in its own declaration or the block fails to compile.
331 words, about 2 min02Interacting with the Oracle Database Server: SQL Statements in PL/SQL
INTO must return exactly one row. Zero rows raises NO DATA FOUND ; two or more raises TOO MANY ROWS .
308 words, about 2 min03Writing Control Structures
END IF; - note ELSIF is one word, END IF is two. A CASE expression with no matching branch and no ELSE raises CASE NOT FOUND (ORA-06592), whereas an unmatched IF simply does nothing.
332 words, about 2 min04Working with Composite Data Types
TYPE t rec IS RECORD (field datatype, ...) groups fields of differing types. %ROWTYPE produces a record matching a table row or, with cursor%ROWTYPE , a cursor's select list.
280 words, about 1 min05Using Explicit Cursors
) makes the WHERE clause reusable - the parameter exists only inside the cursor query. %ISOPEN , %FOUND , %NOTFOUND , %ROWCOUNT .
306 words, about 2 min06Handling Exceptions
Predefined exceptions have names and numbers (NO DATA FOUND, TOO MANY ROWS, ZERO DIVIDE, DUP VAL ON INDEX, INVALID NUMBER, VALUE ERROR). Non-predefined exceptions are ORA- errors without a name; bind one with PRAGMA EXCEPTION INIT(e name, -2292) .
307 words, about 2 min07Creating Stored Procedures, Functions, and Packages
END; - a function adds RETURN datatype and must execute a RETURN on every path, otherwise ORA-06503 occurs at run time. Parameter modes are IN (default), OUT and IN OUT; OUT and IN OUT arguments must be actual variables - passing a literal or expression raises PLS-00363.
306 words, about 2 min08Using Oracle-Supplied Packages
PUT LINE writes into the session output buffer. SQL Plus displays it only after SET SERVEROUTPUT ON , and the buffer is flushed at the end of the block.
307 words, about 2 min09Creating Database Triggers
DML triggers combine a timing (BEFORE, AFTER, INSTEAD OF) with a level (row or statement). INSTEAD OF exists only on views; system triggers fire on schema or database events (CREATE, DROP, LOGON).
295 words, about 1 min10Managing PL/SQL Code, Compiler Settings, and Dependencies
PLSQL WARNINGS controls static analysis ( 'ENABLE:ALL' , 'DISABLE:PERFORMANCE' , 'ERROR:SEVERE' ). PLSQL CODE TYPE selects INTERPRETED (default) or NATIVE.
293 words, about 1 min