Greg Burghardt Greg Burghardt - 1 month ago 5
SQL Question

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?

Answer

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;
/
Comments