object-oriented PL/SQL for situations where PL/SQL's own object system isn't accessible (Forms)

oo-plsql.sql

set serveroutput on

-- create a table to test against
CREATE TABLE objects(
  id NUMBER,
  text VARCHAR2(255)
);

INSERT INTO objects VALUES(1, 'a');
INSERT INTO objects VALUES(2, 'b');

-- define a package spec with a subtype for the instance
-- and any functions; all functions working on instances
-- should take an instance as first argument
CREATE OR REPLACE PACKAGE SKObject IS
  SUBTYPE instance IS objects%rowtype;
  FUNCTION fetchFromDatabase(param_id NUMBER) RETURN SKObject.instance;
END SKObject;
/

-- define a package body with functions/procedures
CREATE OR REPLACE PACKAGE BODY SKObject IS
  FUNCTION fetchFromDatabase(param_id NUMBER) RETURN SKObject.instance IS
    instanceVar instance;
  BEGIN
    SELECT * INTO instanceVar FROM objects WHERE id=param_id;
    RETURN instanceVar;
  END;
END SKObject;
/

-- test it out
DECLARE
  obj_one SKObject.instance;
  obj_two SKObject.instance;
BEGIN
  obj_one := SKObject.fetchFromDatabase(1);
  DBMS_OUTPUT.PUT_LINE('obj_one val:' || obj_one.text);
  obj_two := SKObject.fetchFromDatabase(2);
  DBMS_OUTPUT.PUT_LINE('obj_two val:' || obj_two.text);
END;
/

-- RESULTS:
-- obj_one val:a
-- obj_two val:b