How to create and drop a table inside Oracle function?

Question

I have the function in which I need to drop and create tables. In the example below I try to create the table but it fails

CREATE OR REPLACE FUNCTION DEVTEST 
RETURN  NUMBER
IS 
    COMMAND    VARCHAR2(256);
    ID         VARCHAR2(128);
    NAME       VARCHAR2(128);
    TMP_LIST   VARCHAR2(128);
BEGIN
    ID := '12345';
    NAME := 'ABCdef';
    TMP_LIST := 'tmpTest';

    command := 'create table ' || TMP_LIST || ' ( USER_ID VARCHAR2(11), USER_NAME VARCHAR2(36))';
    DBMS_OUTPUT.PUT_LINE('command = ' || command);
    EXECUTE IMMEDIATE command;
return 0;
END;

I call the function:

select NSB_DEVTEST() from dual

And get the error:

ORA-14552: cannot perform a DDL, commit or rollback inside a query or DML ORA-06512: at "DEV1_SERVER.DEVTEST", line 15

How do I correct this to create/drop a table inside a function?

My server details:

• Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
• PL/SQL Release 10.2.0.5.0 - Production
• CORE 10.2.0.5.0 Production
• TNS for Solaris: Version 10.2.0.5.0 - Production
• NLSRTL Version 10.2.0.5.0 - Production

Answer 1

The problem is not with the function but with it being called from a SQL statement rather than from a pl/sql block.

A SELECT statement in SQL is equivalent to a READ operation that comes with read consistency. It cannot make any changes to the database. The database should always be the same before and after the "READ" operation completed, otherwise it would be a WRITE operation and the entire database consistency would go havoc.

Also, like the error says, DDL operations do a COMMIT behind the scenes before they start. Any read consistent operation should never do any COMMITS and write to the database without the user knowing.

You can instead call the function from pl/sql like this -

DECLARE
l_result NUBMER;
BEGIN
l_result := DEVTEST;
DBMS_OUTPUT.PUT_LINE(l_number);
END;

Still I would prefer writing a procedure for this, so others don't get confused by why this can't be called from SQL. The general rule that I follow for myself is that - Functions "get" things and Procedures "do" things (like DML).

Answer 2

The answer to your question is: don't. Production code, on the whole, shouldn't be creating tables on the fly. If you need a table to hold data temporarily, then create a Global Temporary Table (GTT) once and have your code refer to it.

The reason why you're getting that error (apart from it being self-evident from the error message) is because you're calling the function from within a SQL statement. You can't do that; you'd have to call it directly in PL/SQL.

I'm curious as to why you think this approach is a good, feasible approach, and also what you're going to be doing with the table once you've created it.

Answer 3

Your code is perfect no problem in the code . the problem is while you try to execute

you can only execute a pure function in select statement, which means a function without ddl & dml . ( if you use pragma autonomous_transaction while performing dml inside a function then you can use it in select statement ). When function has DDL command you can never ever execute it in select statement , but instead you can only execute it in PLSQL block like this

declare 
a number;
begin
a:= devtest;
end;
/

and you can check your table

select * from tmptest;