CODEWITHSUNDEEP
sqloracle-databaseplsqloracle11g
Greg Burghardt
Greg Burghardt

Reputation: 18868

How to create a new OBJECT TYPE in Oracle PL/SQL if it doesn't already exist

I need to declare a new OBJECT TYPE in PL/SQL to hold rows in a join query that only selects a few columns. I can create the TYPE just fine, but it becomes part of the schema.

Something like:

IF (SELECT COUNT(*) FROM user_objects WHERE LOWER(object_name) = 'my_custom_type') = 0 THEN
    CREATE TYPE my_custom_type AS OBJECT
    (
        ...
    );
    /
END IF;

DECLARE
    v_foo my_custom_type%TYPE;
BEGIN
    -- Do stuff with v_foo...
END;
/

How can I check to see if the TYPE exists before creating it?

Upvotes: 2

Views: 1488

Answers (1)

Michael Piankov
Michael Piankov

Reputation: 1997

If you need new SQL-type. You may create it using dynamic SQL. 

DECLARE
    v_count NUMBER(10);
BEGIN
    SELECT COUNT(*) INTO v_count
    FROM user_objects
    WHERE LOWER(object_name) = 'my_custom_type';

    IF v_count = 0 THEN
        EXECUTE IMMEDIATE 'CREATE TYPE my_custom_type AS OBJECT (
            id      NUMBER(10),
            name    VARCHAR2(100)
        )';
    END IF;
END;
/

Upvotes: 4

Related Questions