Using DDL in PL/SQL

Question:  I'm trying to issue a "create index" statement in PL/SQL and I get the error:

DECLARE
c_Count NUMBER;
BEGIN
SELECT COUNT(*) INTO c_Count
FROM DBA_INDEXES
WHERE TABLE_NAME = 'SOME_TABLE_NAME'
AND INDEX_NAME = 'SOME_INDEX_NAME'
AND OWNER = 'SOME_OWNER'
AND TABLE_OWNER = 'SOME_OWNER';
IF c_Count = 0 THEN
CREATE INDEX SOME_OWNER.SOME_INDEX ON SOME_OWNER.SOME_TABLE (SOME_KEY);
END IF;
END;

PLS-00103: Encountered the symbol "CREATE" when expecting one of the following: begin case declare exit for goto if loop...

Can you show an example of doing DDL in PL/SQL?

 

Answer:  You can issue DDL for PL/SQL

The Oracle docs note these examples of issuing DDL in PL/SQL:

Executing DDL Statements in PL/SQL

In PL/SQL, you can only execute the following types of statements using dynamic SQL, rather than static SQL:

Data definition language (DDL) statements, such as CREATE, DROP, GRANT, and REVOKE

 

CREATE OR REPLACE PROCEDURE salary_raise (raise_percent NUMBER, job VARCHAR2) IS
    TYPE loc_array_type IS TABLE OF VARCHAR2(40)
        INDEX BY binary_integer;
    dml_str VARCHAR2              (200);
    loc_array      loc_array_type;
BEGIN
    -- bulk fetch the list of office locations
    SELECT location BULK COLLECT INTO loc_array
        FROM offices;
    -- for each location, give a raise to employees with the given 'job'
    FOR i IN loc_array.first..loc_array.last LOOP
        dml_str := 'UPDATE emp_' || loc_array(i)
        || ' SET sal = sal * (1+(:raise_percent/100))'
        || ' WHERE job = :job_title';
    EXECUTE IMMEDIATE dml_str USING raise_percent, job;
    END LOOP;
END;
/
SHOW ERRORS;


CREATE OR REPLACE PROCEDURE add_location (loc VARCHAR2) IS
BEGIN
    -- insert new location in master table
    INSERT INTO offices VALUES (loc);
    -- create an employee information table
    EXECUTE IMMEDIATE  
    'CREATE TABLE ' || 'emp_' || loc ||
    '(
        empno   NUMBER(4) NOT NULL,
        ename   VARCHAR2(10),
        job     VARCHAR2(9),
        sal     NUMBER(7,2),
        deptno  NUMBER(2)
    )';
END;
/
SHOW ERRORS;

 

---