Power By 专否社区

EXECUTE IMMEDIATE ' some commands '

Go To StackoverFlow.com

3

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;
2012-04-05 15:41
by Nikita
Why do you need to do it in a single statement - Chetter Hummin 2012-04-05 15:45
What error are you getting - Shannon Severance 2012-04-05 15:54
Executing in single statement will require less refactoring - Nikita 2012-04-05 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 2012-04-05 19:57

6

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.

2012-04-05 16:00
by Shannon Severance
Thank you!If I understand correctly, there is no such a query to comment some tables within one execute immediate blok - Nikita 2012-04-05 16:21
You understand - Shannon Severance 2012-04-05 19:06

2

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.

2012-04-11 05:28
by Jeffrey Kemp
Ads