Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2003
    Posts
    41

    Unanswered: 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?

    Ondrej

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: function to create table

    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.

  3. #3
    Join Date
    Jan 2003
    Posts
    41

    Unhappy Re: function to create table

    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.

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: function to create table

    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.

  5. #5
    Join Date
    Jan 2003
    Posts
    41

    Re: function to create table

    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.

  6. #6
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: function to create table

    Very nearly: just use || instead of +

  7. #7
    Join Date
    Jan 2003
    Posts
    41

    Re: function to create table

    Yeaaa, that's it!!!

    Thank you very much...

    Ondrej

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •