Is it possible to do DDL statement in a dynamic pl/sql block-CodePudding

I know that I can't make a DDL in a pl/sql block. I wanted to know if I could do that with dynamic sql.

I have tried to drop an object. I don't get a message error like not possible. But I can continue to use the same object after that.

CREATE TYPE arguments_r IS  OBJECT
(
    q    integer,
    b            INTEGER
);
/

declare
    a arguments_r;
begin
   execute immediate 'drop type arguments_r';
   a:=arguments_r(1,1);
end;
/

I wonder if have made an error that cause that my dynamic statement is not executed.

code

CodePudding user response：

Your DDL is executed - you can see that if you try to use it again.

After your original block is compiled it doesn't matter that the type is dropped within it during execution, because (I believe) you're are only dealing with data held in memory - and the block already has all the information it needs to create the instance of that type. Dropping the type doesn't seem to affect you. That doesn't mean it's a good idea though.

You'd see a different result with other types of object:

create table t (id number);

begin
   execute immediate 'drop table t';
   insert into t (id) values (42);
end;
/

ORA-00942: table or view does not exist
ORA-06512: at line 3

That's a run-time error, not a compilation error. The table object still has to exist to be able to insert into it, because it isn't memory-only. (If the table doesn't exist when the block is parsed then you get a compilation error - ORA-06550 as well as ORA-00942.)

db<>fiddle