CODEWITHSUNDEEP
oracle-databaseplsqlconstructoralter
Andrew M.
Andrew M.

Reputation: 101

Alter/Evolve Type User Defined Constructor Function Signature (PL/SQL)

How does one evolve a user defined constructor function signature?

Given an Oracle PL/SQL user defined type that has table dependents, instead of replacing them with create/replace statements I need to evolve these types. I found Oracle documentation on how to drop/add attributes, and drop/add member functions via alter statements, but I do not see information on how to evolve a constructor function signature. I need to do this without creating a new table/type and migrating data over to the new updated type. In my case, using FORCE does not work either.

For example, given the type below, how could I update the user defined constructor signature to include a new parameter to use during initialization?

-- Create new type
CREATE OR REPLACE TYPE test_type AS OBJECT (
    test_attribute NUMBER(1, 0),

    CONSTRUCTOR FUNCTION test_type(
        p_test_attribute NUMBER DEFAULT NULL
    )
    RETURN SELF AS RESULT
);

-- Make this new type have table dependents
CREATE OR REPLACE TYPE test_type_table 
    AS TABLE OF test_type;

CREATE TYPE test_child_obj AS OBJECT (
    test_type_field test_type_table
);

-- Add new attribute via alter statement
ALTER TYPE test_type
    ADD ATTRIBUTE (new_attribute NUMBER)
    CASCADE NOT INCLUDING TABLE DATA;

I would like to update the constructor signature to the following:

CONSTRUCTOR FUNCTION test_type(
    p_test_attribute NUMBER DEFAULT NULL,
    p_new_attribute NUMBER DEFAULT NULL
)
RETURN SELF AS RESULT

I was hoping there would be an alter statement like the following, but I cannot find a proper alter statement. Please help.

ALTER TYPE test_type
    ADD CONSTRUCTOR FUNCTION test_type(
        p_test_attribute NUMBER DEFAULT NULL,
        p_new_attribute NUMBER DEFAULT NULL
    )
    RETURN SELF AS RESULT
    CASCADE NOT INCLUDING TABLE DATA;

Upvotes: 1

Views: 562

Answers (1)

Andrew M.
Andrew M.

Reputation: 101

It turns out that the alter statement is in fact the one stated above and shown below. However, you can run into "too many declarations of 'test_type' match this call". In my real problem, the issue was all my constructor parameters have default NULL values and conflicted with my original constructor that all had default NULL parameters. If I take away the DEFAULT NULL, I can add new constructors.

ALTER TYPE test_type
    ADD CONSTRUCTOR FUNCTION test_type(
        p_test_attribute NUMBER DEFAULT NULL,
        p_new_attribute NUMBER DEFAULT NULL
    )
    RETURN SELF AS RESULT
    CASCADE NOT INCLUDING TABLE DATA;

Upvotes: 1

Related Questions