Oracle PL/SQL Pass row type as constructor parameter

Question

Is it possible to pass table%rowtype as parameter in a constructor ?

I have something like this and this works

CREATE OR REPLACE TYPE shape AS OBJECT (
    name VARCHAR2(30),
    area NUMBER,
    CONSTRUCTOR FUNCTION shape(SELF IN OUT NOCOPY shape, name VARCHAR2)
                               RETURN SELF AS RESULT,
    CONSTRUCTOR FUNCTION shape(SELF IN OUT NOCOPY shape, name VARCHAR2, 
                               area NUMBER) RETURN SELF AS RESULT
) NOT FINAL;
/

CREATE OR REPLACE TYPE BODY shape AS
    CONSTRUCTOR FUNCTION shape(SELF IN OUT NOCOPY shape, name VARCHAR2) 
                               RETURN SELF AS RESULT IS
    BEGIN
        SELF.name := name;
        SELF.area := 0;
        RETURN;
    END;
    CONSTRUCTOR FUNCTION shape(SELF IN OUT NOCOPY shape, name VARCHAR2, 
                                area NUMBER) RETURN SELF AS RESULT IS
    BEGIN
        SELF.name := name;
        SELF.area := area;
        RETURN;
    END;
END;
/

However i need to pass a table%row as parameter as well

CREATE OR REPLACE TYPE shape AS OBJECT (
    name VARCHAR2(30),
    area NUMBER,
    CONSTRUCTOR FUNCTION shape(SELF IN OUT NOCOPY shape, name VARCHAR2)
                               RETURN SELF AS RESULT,
    CONSTRUCTOR FUNCTION shape(SELF IN OUT NOCOPY shape, name VARCHAR2, 
                               area NUMBER) RETURN SELF AS RESULT,
    CONSTRUCTOR FUNCTION shape(SELF IN OUT NOCOPY shape, ctrl CONTROL%ROWTYPE) RETURN SELF AS RESULT
) NOT FINAL;
/

CREATE OR REPLACE TYPE BODY shape AS
    CONSTRUCTOR FUNCTION shape(SELF IN OUT NOCOPY shape, name VARCHAR2) 
                               RETURN SELF AS RESULT IS
    BEGIN
        SELF.name := name;
        SELF.area := 0;
        RETURN;
    END;
    CONSTRUCTOR FUNCTION shape(SELF IN OUT NOCOPY shape, name VARCHAR2, 
                                area NUMBER) RETURN SELF AS RESULT IS
    BEGIN
        SELF.name := name;
        SELF.area := area;
        RETURN;
    END;
    CONSTRUCTOR FUNCTION shape(SELF IN OUT NOCOPY shape, ctrl CONTROL%ROWTYPE) RETURN SELF AS RESULT IS
    BEGIN
        SELF.area := NULL;
        RETURN;
    END;
END;
/

LINE/COL ERROR

---

0/0 PL/SQL: Compilation unit analysis terminated 8/63 PLS-00329: schema-level type has illegal reference to SCHEMA_A.CONTROL Errors: check compiler log

Type Body SHAPE compiled

LINE/COL ERROR

---

0/0 PL/SQL: Compilation unit analysis terminated 1/11 PLS-00905: object SCHEMA_A.SHAPE is invalid 1/11 PLS-00304: cannot compile body of 'SHAPE' without its specification Errors: check compiler log

Is something like this possible ?

Answer 1

If you want to take a slightly different method, you can create an object table:

CREATE TYPE control_type AS OBJECT (
  id NUMBER(8,0),
  name VARCHAR2(30),
  area NUMBER
);

CREATE TABLE control OF control_type(
  CONSTRAINT control__id__pk PRIMARY KEY (id)
);

INSERT INTO control (id, name, area)
SELECT 1, 'square', 42 FROM DUAL;

Then instead of expecting a %ROWTYPE in the cursor, you could pass the underlying object type for that table:

CREATE OR REPLACE TYPE shape AS OBJECT (
    name VARCHAR2(30),
    area NUMBER,
    CONSTRUCTOR FUNCTION shape(SELF IN OUT NOCOPY shape, name VARCHAR2)
                               RETURN SELF AS RESULT,
    CONSTRUCTOR FUNCTION shape(SELF IN OUT NOCOPY shape, name VARCHAR2, 
                               area NUMBER) RETURN SELF AS RESULT,
    CONSTRUCTOR FUNCTION shape(SELF IN OUT NOCOPY shape, ctrl control_type) RETURN SELF AS RESULT
) NOT FINAL;
/

CREATE OR REPLACE TYPE BODY shape AS
    CONSTRUCTOR FUNCTION shape(SELF IN OUT NOCOPY shape, name VARCHAR2) 
                               RETURN SELF AS RESULT IS
    BEGIN
        SELF.name := name;
        SELF.area := 0;
        RETURN;
    END;
    CONSTRUCTOR FUNCTION shape(SELF IN OUT NOCOPY shape, name VARCHAR2, 
                                area NUMBER) RETURN SELF AS RESULT IS
    BEGIN
        SELF.name := name;
        SELF.area := area;
        RETURN;
    END;
    CONSTRUCTOR FUNCTION shape(SELF IN OUT NOCOPY shape, ctrl control_type) RETURN SELF AS RESULT IS
    BEGIN
        SELF.name := ctrl.name;
        SELF.area := ctrl.area;
        RETURN;
    END;
END;
/

Then you can use VALUE(column_alias) in a SELECT from an object table to pass the table's underlying object type into your constructor:

SELECT s.shape.area,
       s.shape.name
FROM   (
  SELECT SHAPE(VALUE(c)) AS shape
  FROM   control c
) s;

Outputs:

SHAPE.AREA | SHAPE.NAME
---------: | :---------
        42 | square    

db<>fiddle here