asked on

Initialization of an oracle type

I have created an oracle type like this:

Create or replace Rec_Comm as object
(
field_1 varchar2(10),
field_2 varchar2(10),
.....
field_50 varchar2(10),
)
/

in a PL/SQL I then want to initliaze all fields of this type to NULL without specify 50 fields to null, how do i do this ?

ASKER CERTIFIED SOLUTION

Sean Stuber

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Sean Stuber

if you're trying to instantiate with the default constructor method of an object then you can't do it.
You'll have to do...

declare
v_your_object rec_comm := rec_comm(null,null,null,null.....null,null); -- 50 nulls

Sean Stuber

you could create your own constructor function for the object type that does the mass initialization for you.

CREATE OR REPLACE TYPE rec_comm AS OBJECT
(
    field1 VARCHAR2(10),
    field2 VARCHAR2(10),
    field3 VARCHAR2(10),
    field4 VARCHAR2(10),
    field5 VARCHAR2(10),
    field6 VARCHAR2(10),
    field7 VARCHAR2(10),
    field8 VARCHAR2(10),
    field9 VARCHAR2(10),
    field10 VARCHAR2(10),
    field11 VARCHAR2(10),
    field12 VARCHAR2(10),
    field13 VARCHAR2(10),
    field14 VARCHAR2(10),
    field15 VARCHAR2(10),
    field16 VARCHAR2(10),
    field17 VARCHAR2(10),
    field18 VARCHAR2(10),
    field19 VARCHAR2(10),
    field20 VARCHAR2(10),
    field21 VARCHAR2(10),
    field22 VARCHAR2(10),
    field23 VARCHAR2(10),
    field24 VARCHAR2(10),
    field25 VARCHAR2(10),
    field26 VARCHAR2(10),
    field27 VARCHAR2(10),
    field28 VARCHAR2(10),
    field29 VARCHAR2(10),
    field30 VARCHAR2(10),
    field31 VARCHAR2(10),
    field32 VARCHAR2(10),
    field33 VARCHAR2(10),
    field34 VARCHAR2(10),
    field35 VARCHAR2(10),
    field36 VARCHAR2(10),
    field37 VARCHAR2(10),
    field38 VARCHAR2(10),
    field39 VARCHAR2(10),
    field40 VARCHAR2(10),
    field41 VARCHAR2(10),
    field42 VARCHAR2(10),
    field43 VARCHAR2(10),
    field44 VARCHAR2(10),
    field45 VARCHAR2(10),
    field46 VARCHAR2(10),
    field47 VARCHAR2(10),
    field48 VARCHAR2(10),
    field49 VARCHAR2(10),
    field50 VARCHAR2(10),
    CONSTRUCTOR FUNCTION rec_comm(self IN OUT NOCOPY rec_comm)
        RETURN SELF AS RESULT
)

Open in new window

CREATE OR REPLACE TYPE BODY rec_comm
AS
    CONSTRUCTOR FUNCTION rec_comm(self IN OUT NOCOPY rec_comm)
        RETURN SELF AS RESULT
    IS
    BEGIN
        field1   := NULL;
        field2   := NULL;
        field3   := NULL;
        field4   := NULL;
        field5   := NULL;
        field6   := NULL;
        field7   := NULL;
        field8   := NULL;
        field9   := NULL;
        field10  := NULL;
        field11  := NULL;
        field12  := NULL;
        field13  := NULL;
        field14  := NULL;
        field15  := NULL;
        field16  := NULL;
        field17  := NULL;
        field18  := NULL;
        field19  := NULL;
        field20  := NULL;
        field21  := NULL;
        field22  := NULL;
        field23  := NULL;
        field24  := NULL;
        field25  := NULL;
        field26  := NULL;
        field27  := NULL;
        field28  := NULL;
        field29  := NULL;
        field30  := NULL;
        field31  := NULL;
        field32  := NULL;
        field33  := NULL;
        field34  := NULL;
        field35  := NULL;
        field36  := NULL;
        field37  := NULL;
        field38  := NULL;
        field39  := NULL;
        field40  := NULL;
        field41  := NULL;
        field42  := NULL;
        field43  := NULL;
        field44  := NULL;
        field45  := NULL;
        field46  := NULL;
        field47  := NULL;
        field48  := NULL;
        field49  := NULL;
        field50  := NULL;
        RETURN;
    END;
END;

Open in new window

DECLARE
    v_my_object   rec_comm := rec_comm();
BEGIN
    IF v_my_object.field30 IS NULL
    THEN
        v_my_object.field30 := 'Thirty';
        DBMS_OUTPUT.put_line('It worked!');
    END IF;
END;

Open in new window

Sean Stuber

actually your user-defined constructor doesn't even need to specify the assignments.
All member attributes are NULLed as soon as the constructor is called
So, this is sufficient...

CREATE OR REPLACE TYPE BODY rec_comm
AS
CONSTRUCTOR FUNCTION rec_comm(self IN OUT NOCOPY rec_comm)
RETURN SELF AS RESULT
IS
BEGIN
RETURN;
END;
END;