Oracle PL/SQL

You are an Oracle PL/SQL developer. The user wants to write PL/SQL procedures, functions, packages, and triggers.

What to check first

• Verify Oracle database version with SELECT * FROM v$version;
• Confirm your schema privileges: SELECT * FROM user_sys_privs; or SELECT * FROM role_sys_privs WHERE role IN (SELECT granted_role FROM user_role_privs);
• Check existing objects in your schema: SELECT object_name, object_type FROM user_objects WHERE object_type IN ('PROCEDURE', 'FUNCTION', 'PACKAGE', 'TRIGGER');

Steps

1. Open SQL*Plus, SQL Developer, or connect via sqlplus username/password@database
2. Create a procedure using CREATE OR REPLACE PROCEDURE proc_name (params) AS BEGIN...END; — use IS instead of AS in packages
3. Create a function with CREATE OR REPLACE FUNCTION func_name (params) RETURN datatype AS BEGIN...RETURN value;END;
4. Bundle related procedures and functions in a package with CREATE OR REPLACE PACKAGE package_name AS (spec) then CREATE OR REPLACE PACKAGE BODY package_name AS (body)
5. Create a trigger using CREATE OR REPLACE TRIGGER trigger_name BEFORE/AFTER INSERT/UPDATE/DELETE ON table_name FOR EACH ROW BEGIN...END;
6. Use :NEW and :OLD pseudo-records in triggers to access changed column values
7. Compile with ALTER PROCEDURE/FUNCTION/PACKAGE proc_name COMPILE; and check errors with SHOW ERRORS
8. Test execution: call procedures with EXECUTE proc_name(args); or BEGIN proc_name(args); END;, functions in SELECT or assign to variables

Code

-- Package Specification
CREATE OR REPLACE PACKAGE emp_pkg AS
  PROCEDURE add_employee (
    p_emp_id   IN employees.employee_id%TYPE,
    p_name     IN employees.employee_name%TYPE,
    p_salary   IN employees.salary%TYPE
  );
  
  FUNCTION get_salary (p_emp_id IN employees.employee_id%TYPE) 
    RETURN employees.salary%TYPE;
  
  PROCEDURE update_bonus (p_emp_id IN employees.employee_id%TYPE, p_bonus IN NUMBER);
END emp_pkg;
/

-- Package Body
CREATE OR REPLACE PACKAGE BODY emp_pkg AS
  PROCEDURE add_employee (
    p_emp_id   IN employees.employee_id%TYPE,
    p_name     IN employees.employee_name%TYPE,
    p_salary   IN employees.salary%TYPE
  ) IS
  BEGIN
    INSERT INTO employees (employee_id, employee_name, salary)
    VALUES (p_emp_id, p_name, p_salary);
    COMMIT;
    DBMS_OUTPUT.PUT_LINE

Note: this example was truncated in the source. See the GitHub repo for the latest full version.

Common Pitfalls

• Treating this skill as a one-shot solution — most workflows need iteration and verification
• Skipping the verification steps — you don't know it worked until you measure
• Applying this skill without understanding the underlying problem — read the related docs first

When NOT to Use This Skill

• When a simpler manual approach would take less than 10 minutes
• On critical production systems without testing in staging first
• When you don't have permission or authorization to make these changes

How to Verify It Worked

• Run the verification steps documented above
• Compare the output against your expected baseline
• Check logs for any warnings or errors — silent failures are the worst kind

Production Considerations

• Test in staging before deploying to production
• Have a rollback plan — every change should be reversible
• Monitor the affected systems for at least 24 hours after the change