This discussion is archived
1 2 3 Previous Next 34 Replies Latest reply: Dec 3, 2012 2:53 AM by 936666 RSS

Dynamic table in Run time

936666 Newbie
Currently Being Moderated
Hi,
I need to create a dynamic table in run time
Input from user will be like
Param1---->'tablename'
Param2---->'col1name datatype,col2name datatype,col3name datatype,col4name datatype,col5name datatype,col6name datatype........................'
Param3---->returnCode OUT NUMBER
Param4---->errorMessage OUT VARCHAR2

how to write a script to execute the above statement.

The input will be from java page, it has connection string of the database

Thanks!
  • 1. Re: Dynamic table in Run time
    Purvesh K Guru
    Currently Being Moderated
    933663 wrote:
    Hi,
    I need to create a dynamic table in run time
    Input from user will be like
    Param1---->'tablename'
    Param2---->'col1name datatype,col2name datatype,col3name datatype,col4name datatype,col5name datatype,col6name datatype........................'
    Param3---->returnCode OUT NUMBER
    Param4---->errorMessage OUT VARCHAR2

    how to write a script to execute the above statement.

    The input will be from java page, it has connection string of the database

    Thanks!
    Wrong way to create table on the Fly!!!

    But Dynamic SQL is a way to achieve it.
    create or replace procedure Create_Table(param1 varchar2, param2 varchar2, param3 out number, param4 out varchar2)
    is
      v_sql  varchar2(2000);
    begin
      v_sql := 'CREATE TABLE ' || param1 || ' (' || param2 || ')';
      execute immediate v_sql;
    exception
      when others then
        log_error; --Your Exception Logger
    end Create_Table;
  • 2. Re: Dynamic table in Run time
    Hoek Guru
    Currently Being Moderated
    I need to create a dynamic table in run time
    Why would you need to 'do DDL on the fly'?
    Do you know what possible consequences such an approach can have (scalability, possible errors, etc.)?
  • 3. Re: Dynamic table in Run time
    936666 Newbie
    Currently Being Moderated
    Since i dont know how many columns will be needed and how many tables needed before my process starts.
    After creating tables i will be creating scripts dynamically to load/ fetch the data.
    since the schema will be defined on run time only i need to create on this bases.

    Thanks!
  • 4. Re: Dynamic table in Run time
    936666 Newbie
    Currently Being Moderated
    for param 2 i'm having the input as col1name datatype,col2name datatype,col3name datatype,col4name datatype,col5name datatype,col6name datatype

    so it is saying as in invalid no of params
  • 5. Re: Dynamic table in Run time
    Purvesh K Guru
    Currently Being Moderated
    933663 wrote:
    for param 2 i'm having the input as col1name datatype,col2name datatype,col3name datatype,col4name datatype,col5name datatype,col6name datatype

    so it is saying as in invalid no of params
    Unless you provide us with some sample data and the Script you tried with the Errors, we would not be of much help.

    Post the script you tried, with the parameters and the errors you received.

    do not forget to post the database version.
  • 6. Re: Dynamic table in Run time
    BluShadow Guru Moderator
    Currently Being Moderated
    933663 wrote:
    Since i dont know how many columns will be needed and how many tables needed before my process starts.
    After creating tables i will be creating scripts dynamically to load/ fetch the data.
    since the schema will be defined on run time only i need to create on this bases.

    Thanks!
    That sounds very much like a system that has missed a stage... the stage called "design".

    If you create tables dynamically at runtime because you don't know the structure, then all the code you write to use those tables also has to be dynamic, and then everything following has to be dynamic.
    It also negates the in built ability of the database to validate things at compilation time; it introduces hard to maintain code; it will likely introduce bugs which may not be seen until certain situations arise, but which would have been obviously avoided using a fixed design and compilation validation; and it can open up a whole can of security issues including sql injection or even pl/sql injection.

    Everything about it is wrong.

    Step back, and explain what the issue is you are trying to resolve, not how you are trying to badly resolve it.
  • 7. Re: Dynamic table in Run time
    936666 Newbie
    Currently Being Moderated
    create or replace procedure Create_Table (param1 varchar2, param2 varchar2, param3 out number, param4 out varchar2)
    is
      v_sql  varchar2(2000);
    begin
      v_sql := 'CREATE TABLE ' || param1 || ' (' || param2 || ')';
      execute immediate v_sql;
    exception when others then
    param3 :=SQLCODE;
     param4 :='ERR:Creating/Altering table '||param1 ||'-'||SQLERRM;
    end Create_Table;
    DECLARE reCode NUMBER;
    errorMsg VARCHAR2(5);
    begin 
    Exec Create_Table ('mytable','col1name varchar2 , col2name varchar2', :reCode, :errorMsg);
    end;
    Bind Variable "reCode" is NOT DECLARED
    anonymous block completed
  • 8. Re: Dynamic table in Run time
    936666 Newbie
    Currently Being Moderated
    I am doing this because from user to user it differs, say user 1 will have a table with 20 columns and user 2 will having 2 tables with 15 and 23 columns and so on.. if user 1 login's then he much be able to see only his records from his corresponding table. It can't be kept in a single table since there may be mismatch in the data type from user to user.

    Thanks!
  • 9. Re: Dynamic table in Run time
    Purvesh K Guru
    Currently Being Moderated
     create or replace procedure Create_Table (param1 varchar2, param2 varchar2, param3 out number, param4 out varchar2)
     is
       v_sql  varchar2(2000);
     begin
       v_sql := 'CREATE TABLE ' || param1 || ' (' || param2 || ')';
       execute immediate v_sql;
     exception when others then
     param3 :=SQLCODE;
      param4 :='ERR:Creating/Altering table '||param1 ||'-'||SQLERRM;
     end Create_Table;
     
     DECLARE reCode NUMBER;
     errorMsg VARCHAR2(5);
     begin 
     Exec Create_Table ('mytable','col1name varchar2(10) , col2name varchar2(10)', :reCode, :errorMsg);          --Provide Width to VARCHAR2 Datatypes.
     end;
     
  • 10. Re: Dynamic table in Run time
    BluShadow Guru Moderator
    Currently Being Moderated
    933663 wrote:
    I am doing this because from user to user it differs, say user 1 will have a table with 20 columns and user 2 will having 2 tables with 15 and 23 columns and so on.. if user 1 login's then he much be able to see only his records from his corresponding table. It can't be kept in a single table since there may be mismatch in the data type from user to user.
    That's not a requirement for a single system, that's a requirement for multiple systems. If different users require different tables with different columns for different purposes, you should not be attempting to write a single application to deal with that.... because that would be bad design.

    Please explain why different users need different tables with different columns. What are they trying to achieve?

    If users are storing varying structures of data for a singular application then a more appropriate flexible data type should be used on a known table structure, such as using object types or storing the data as XML in an XMLTYPE datatype.
  • 11. Re: Dynamic table in Run time
    936666 Newbie
    Currently Being Moderated
    Still i am getting the same problem i have used number data type for param3
    DECLARE reCode NUMBER;
     errorMsg VARCHAR2;
     begin 
     Exec Create_Table ('mytable','col1name varchar2(10) , col2name varchar2(10)', :reCode, :errorMsg);          --Provide Width to VARCHAR2 Datatypes.
     end;
    Bind Variable "reCode" is NOT DECLARED
    anonymous block completed.
  • 12. Re: Dynamic table in Run time
    936666 Newbie
    Currently Being Moderated
    Please explain why different users need different tables with different columns. What are they trying to achieve?
    Each user here is each company , each company needs to see only it own data.Data is independent to each company.

    Thanks!
  • 13. Re: Dynamic table in Run time
    Purvesh K Guru
    Currently Being Moderated
    933663 wrote:
    DECLARE reCode NUMBER;
    errorMsg VARCHAR2;
    begin
    Exec Create_Table ('mytable','col1name varchar2(10) , col2name varchar2(10)', :reCode, :errorMsg); --Provide Width to VARCHAR2 Datatypes.
    end;
    Bind Variable "reCode" is NOT DECLARED
    anonymous block completed.
    Please remove the ":"(Colon) before reCode and errorMsg; As you are declaring those in an Anonymous block and with :, SQL Plus considers it to be a Bind variable, which hasn't been done yet, I assume.
  • 14. Re: Dynamic table in Run time
    936666 Newbie
    Currently Being Moderated
    After remove *:*

    Error report:
    ORA-06550: line 4, column 6:
    PLS-00103: Encountered the symbol "CREATE_TABLE" when expecting one of the following:

    := . ( @ % ;
    The symbol ":=" was substituted for "CREATE_TABLE" to continue.
    06550. 00000 - "line %s, column %s:\n%s"
    *Cause:    Usually a PL/SQL compilation error.
    *Action:                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               
1 2 3 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points