Oracle 1Z0-183 case studies
Oracle 1Z0-183 · Put your knowledge to work in context.
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 case studies are 5 cases and about 3,435 words.
1The Aggregate That Always Returns a Row
The order-entry team owns p dept bonus(p dept NUMBER) in the ORDER MGMT schema. Its core is SELECT SUM(salary) INTO v total FROM employees WHERE dept id = p dept; , followed by INSERT INTO bonus audit(dept id, amount) VALUES (p dept, v total 0.1); .
667 words, about 3 min2Five Hundred Orders, Zero Errors, Empty Log
A batch package processes 500 orders in a cursor loop. Each iteration body is a nested block whose exception section reads WHEN OTHERS THEN INSERT INTO err log(run id, msg) VALUES (v run, SQLERRM); with no re-raise.
593 words, about 3 min3Salary Cap Trigger That Only Works One Row at a Time
HR has a row-level trigger trg sal cap on EMPLOYEES, BEFORE INSERT OR UPDATE FOR EACH ROW , enforcing a department salary budget. Its body runs SELECT SUM(salary) INTO v sum FROM employees WHERE dept id = :NEW.dept id; and raises ORA-20001 if v sum + :NEW.salary exceeds the budget for that department.
727 words, about 4 min4Thursday Release, ORA-04068 in the Pool
The platform team ships PL/SQL every Thursday with CREATE OR REPLACE scripts. Last Thursday they added an OUT parameter to p calc tax in the specification of TAX PKG , then recompiled spec and body successfully.
708 words, about 4 min5Sparse Collection, Missing Shipment
A warehouse procedure loads 10,000 order ids into a nested table using BULK COLLECT INTO l ids , then calls a cleanup routine that removes cancelled orders with l ids.DELETE(i) - roughly 30 elements per run - and finally iterates the survivors with FOR i IN 1 .. l ids.COUNT LOOP ship api.send(l ids(i)); END LOOP; .
740 words, about 4 min