1 2 3 Previous Next 34 Replies Latest reply: Dec 3, 2012 4:53 AM by 936666 RSS

    Dynamic table in Run time

    936666
      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
          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
            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
              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
                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
                  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
                    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
                      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
                        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
                           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
                            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
                              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
                                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
                                  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
                                    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