function to create table

Hello,

how can I create table with pl/sql? I want to create function do this but error occured.
I used this for create function
CREATE OR REPLACE FUNCTION CREATE_OBJ
RETURN VARCHAR
IS
num NUMBER;
BEGIN
CREATE TABLE WST.test (
id NUMBER
);
commit;

END;
/

Any idea?

OndrejYou can't perform DDL (like CREATE TABLE) directly from within PL/SQL. You would have to use dynamic SQL:

CREATE OR REPLACE FUNCTION CREATE_OBJ
RETURN VARCHAR
IS
BEGIN
EXECUTE IMMEDIATE 'CREATE TABLE WST.test (id NUMBER)';
RETURN 'something';
END;
/

There is no need to COMMIT the DDL, that happens automatically. And a function has to RETURN something.|||Well, I try you write... compilation is ok but when I use the function in sql I get error:
select CREATE_OBJ from dual;

ORA-14552 cannot perform a DDL, commit or rollback inside a query or DML
ORA-06512: "WST.CREATE_OBJ_1", line 3

Originally posted by andrewst
You can't perform DDL (like CREATE TABLE) directly from within PL/SQL. You would have to use dynamic SQL:

CREATE OR REPLACE FUNCTION CREATE_OBJ
RETURN VARCHAR
IS
BEGIN
EXECUTE IMMEDIATE 'CREATE TABLE WST.test (id NUMBER)';
RETURN 'something';
END;
/

There is no need to COMMIT the DDL, that happens automatically. And a function has to RETURN something.|||That's correct, as the error says, you can't perform DDL (like CREATE TABLE) inside a query (like SELECT).

But you can do this:

SQL> VAR x VARCHAR2(100)
SQL> EXEC :x := CREATE_OBJ;

But really, I wouldn't create a FUNCTION to perform DDL like CREATE TABLE, I would just use a SQL Plus script.|||Great!!

It works fine after setting all needed privileges... thank you very much.

But I have another question. I want modified function CREATE_OBJ, I need give table name like parameter. So the function should be like this

CREATE OR REPLACE FUNCTION CREATE_OBJ (tName VARCHAR) RETURN VARCHAR IS
BEGIN
EXECUTE IMMEDIATE 'CREATE TABLE '+tName+' (id NUMBER)';
RETURN 'something';
END;

I'm sure this is not correct syntax.

Originally posted by andrewst
That's correct, as the error says, you can't perform DDL (like CREATE TABLE) inside a query (like SELECT).

But you can do this:

SQL> VAR x VARCHAR2(100)
SQL> EXEC :x := CREATE_OBJ;

But really, I wouldn't create a FUNCTION to perform DDL like CREATE TABLE, I would just use a SQL Plus script.|||Very nearly: just use || instead of +|||Yeaaa, that's it!!!

Thank you very much...

Ondrej

Originally posted by andrewst
Very nearly: just use || instead of +