EXECUTE IMMEDIATE ' some commands '

Question

Is it possible to execute some sql commands within one EXECUTE IMMEDIATE block?

What is wrong with this syntax:

declare
    pragma autonomous_transaction;
begin           
    execute immediate
    'begin
        COMMENT ON TABLE t1 IS ''description1'';
        COMMENT ON TABLE t2 IS ''description2'';            
    end;';
end;

For one SQL command it works fine:

declare
    pragma autonomous_transaction;
begin           
    execute immediate ' COMMENT ON TABLE t1 IS ''description1'' ';
end;

Executing in single statement will require less refactoring) — Nikita, Apr 05 '12 at 19:54
This is incorrect usage of "execute immediate" command. Understanding, that first statement is PL/SQL and the second is DDL helped me a lot. Thanks again, Shannon! — Nikita, Apr 05 '12 at 19:57

score 6 · Accepted Answer · answered Apr 05 '12 at 16:00

The begin end within the string to execute immediate is going to be treated as a PL/SQL anonymous block. DDL, such as COMMENT is not allowed in PL/SQL. If it were you wouldn't need to use execute immediate. Oracle essentially works with either a block of PL/SQL statement or a single SQL statement at a time. Though there are APIs to batch SQL statements too.

So, to run COMMENT within a PL/SQL block or procedure, you will need to execute immediate statements.

Without more context I can not intelligently comment on whether that is the right approach, or if just having the two comment statements stand alone would be better.

Thank you!If I understand correctly, there is no such a query to comment some tables within one execute immediate blok? — Nikita, Apr 05 '12 at 16:21

score 2 · Answer 2 · answered Apr 11 '12 at 05:28

Well, you could do this:

begin           
    execute immediate
    'begin
        execute immediate ''COMMENT ON TABLE t1 IS ''''description1'''' '';
        execute immediate ''COMMENT ON TABLE t2 IS ''''description2'''' '';
    end;';
end;

But there's not much point.

EXECUTE IMMEDIATE ' some commands '

2 Answers2