Results 1 to 7 of 7

Thread: CREATE Table in a stored procedure

  1. #1
    Join Date
    Jun 2004
    Posts
    14

    CREATE Table in a stored procedure

    Hi,

    Excuse me but my English is quite bad, I wil try to explain my problem.

    I want to create a table in a stored procedure, apparantly it is impossible :/

    Anybody would have a solution for me ?
    Thanks
    juvamine

  2. #2
    Join Date
    Mar 2003
    Posts
    468
    Yes, you can create a table through a procedure. Here is some quick output to show.
    1. describe that the table does not exist.
    2. create a procedure
    3. execute the procedure
    4. describe that the table was created.


    25-FEB-05 : SYS@k10gutf8 AS SYSDBA > desc testtable
    ERROR:
    ORA-04043: object testtable does not exist


    25-FEB-05 : SYS@k10gutf8 AS SYSDBA > create PROCEDURE createta
    2
    3 BEGIN
    4 EXECUTE IMMEDIATE
    5 'CREATE TABLE testtable '||
    6 ' (mycolumn NUMBER, '||
    7 ' CONSTRAINT pk_testtable PRIMARY KEY (mycolumn
    8
    9 END createtable;
    10 /

    Procedure created.

    25-FEB-05 : SYS@k10gutf8 AS SYSDBA > exec createtable

    PL/SQL procedure successfully completed.

    25-FEB-05 : SYS@k10gutf8 AS SYSDBA > desc testtable
    Name Null? Type
    ----------------------------------------- -------- ----------
    MYCOLUMN NOT NULL NUMBER

  3. #3
    Join Date
    Jun 2004
    Posts
    14
    Thanks a lot !!!

    execute command, is able to execute all sql query ?

    EXECUTE COMMAND 'my sql query';

    yes ?

    thx again

    juvamine

  4. #4
    Join Date
    Nov 2004
    Posts
    3
    The "execute immediate" call is able to execute a string of SQL with a maximum of 2000 chars if i remember correctly. When you use ;'s in the statement its considered by the engine to be a block of PL/SQL instead of a mere query so it then needs to have a begin end; stucture.

  5. #5
    Join Date
    Mar 2003
    Posts
    468
    i have used EXECUTE IMMEIDATE command with a LONG data type to create not only tables but procedures, packages, and package bodies. this data type stores up to 2GB and the last package i created was over 13,000 characters without spaces and over 17,000 characters with spaces.

    so i think it just depends on the data type being used. then again i have concatenated many LONG data types together without any problems.

  6. #6
    Join Date
    Nov 2004
    Posts
    3
    Hmmm... you could be right then. Always use varchar2 to store the SQL string and most of the time the string stays small. I use it a lot in normal procedures with the conveniance of binding the vars. I used to concatenate everything and got sometimes an error saying SQL string to large. But then again could be just me selecting a to small datatype.

  7. #7
    Join Date
    Jun 2004
    Posts
    14
    maybe your 2 versions of Oracle ar different...that explain the difference in the weight of the varchar.
    In my case, it's OK )

Posting Permissions

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