CODEWITHSUNDEEP
oracle-databaseplsqldynamic-sql
dang
dang

Reputation: 2412

Index not getting created using stored procedure - Oracle

I have this stored procedure to create index on table:

CREATE OR REPLACE PROCEDURE create_index (
    in_tb        VARCHAR2,
    in_index     VARCHAR2,
    in_columns   VARCHAR2,
    lc_status    OUT          NUMBER
) AS
    lc_affected   NUMBER;
    lc_stmt       VARCHAR2(1500);
BEGIN
    lc_stmt := 'BEGIN EXECUTE IMMEDIATE ''CREATE INDEX '
               || in_index
               || ' ON '
               || in_tb
               || ' ('
               || in_columns
               || ')''; END;';

    dbms_output.put_line(lc_stmt);
    dbms_utility.exec_ddl_statement(lc_stmt);
    lc_affected := SQL%rowcount;
    dbms_output.put_line('AFFECTED -->' || lc_affected);
    IF ( lc_affected > 0 ) THEN
        lc_status := 1;
    ELSE
        lc_status := 1;
    END IF;

END create_index;
/

I execute the stored procedure using:

SET SERVEROUTPUT ON;
DECLARE
    lc_status   NUMBER;
BEGIN
    create_index('TABLE_1_LOAD', 'ON_RUN_INDEX', 'MY_ID', lc_status);
END;

However, the index is not getting created in table TABLE_1_LOAD.

The output is:

BEGIN EXECUTE IMMEDIATE 'CREATE INDEX ON_RUN_INDEX ON TABLE_1_LOAD (MY_ID)'; END;
AFFECTED -->

PL/SQL procedure successfully completed.

I am not able to understand why the stored procedure is not creating indexes. Can you please help?

Upvotes: 1

Views: 222

Answers (1)

Alex Poole
Alex Poole

Reputation: 191455

The dynamic statement you are trying to run via exec_ddl_statement is not DDL. It contains DDL, but embedded in an anonymous PL/SQL block, which is not the same thing. It looks like the dbms_utility procedure is just silently ignoring it for that reason.

If you simplify your statement to remove the unnecessary block then it will work:

...
BEGIN
    lc_stmt := 'CREATE INDEX '
               || in_index
               || ' ON '
               || in_tb
               || ' ('
               || in_columns
               || ')';

...

Demo:

create table table_1_load (my_id number);

Table TABLE_1_LOAD created.

CREATE OR REPLACE PROCEDURE create_index (
    in_tb        VARCHAR2,
    in_index     VARCHAR2,
    in_columns   VARCHAR2,
    lc_status    OUT          NUMBER
) AS
    lc_affected   NUMBER;
    lc_stmt       VARCHAR2(1500);
BEGIN
    lc_stmt := 'CREATE INDEX '
               || in_index
               || ' ON '
               || in_tb
               || ' ('
               || in_columns
               || ')';

    dbms_output.put_line(lc_stmt);
    dbms_utility.exec_ddl_statement(lc_stmt);
    lc_affected := SQL%rowcount;
    dbms_output.put_line('AFFECTED -->' || lc_affected);
    IF ( lc_affected > 0 ) THEN
        lc_status := 1;
    ELSE
        lc_status := 1;
    END IF;

END create_index;
/

Procedure CREATE_INDEX compiled

SET SERVEROUTPUT ON;
DECLARE
    lc_status   NUMBER;
BEGIN
    create_index('TABLE_1_LOAD', 'ON_RUN_INDEX', 'MY_ID', lc_status);
END;
/

CREATE INDEX ON_RUN_INDEX ON TABLE_1_LOAD (MY_ID)
AFFECTED -->


PL/SQL procedure successfully completed.

The 'affected' number is still null, because execute_ddl_statement doesn't cause SQL%rowcount to be set, so you can't rely on that to tell you anything. But the index has been created:

select object_type, object_name from user_objects where created > trunc(sysdate);

OBJECT_TYPE         OBJECT_NAME                   
------------------- ------------------------------
TABLE               TABLE_1_LOAD                  
PROCEDURE           CREATE_INDEX                  
INDEX               ON_RUN_INDEX

You could run your original statement with execute immediate, and that would actually set SQL%rowcount, but as you still haven't run any DML it's meaningless really. To show that, with the (still unnecessary) anonymous block you get 1; without the block, using the same simplified statement as above, you get 0.

Upvotes: 4

Related Questions