9 Replies Latest reply: Nov 20, 2012 6:48 AM by EdStevens RSS

    create table based on previous desc

    975169
      hey,

      i'm trying to create a few tables based on a desc i recieved
      Name Type Nullable Default Comments
      ----------------------------- ------------ -------- ----------- --------
      SUBSCR_NO NUMBER(10)
      SUBSCR_NO_RESETS NUMBER(6)
      ....
      is there an automatic way to do it or iether i have to use the create table command and modfy it according to specifications in the desc?
        • 1. Re: create table based on previous desc
          jeneesh
          create table new_table as
          select *
          from old_table
          where 1 = 2
          OR check the package dbms_metadata.get_ddl
          • 2. Re: create table based on previous desc
            975169
            maybe i didnt explain myself
            i dont have the actual tables
            i need to create tables according the specifications i got drom desc commant
            i got the desc command and the result in TXT file
            • 3. Re: create table based on previous desc
              908002
              not sure in which format you have your text file..

              mean

              in this fformat??

              a number(10), b varchar2(200)...?


              If yes you can read the text file using utl_file and build sql create statment and use execute immediate.
              • 4. Re: create table based on previous desc
                Manik
                Hi,

                Take test schema, run the description file in the test schema
                Compare the test schema tables data with original table schema and generate synch script.

                http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:941629680330

                Check the above link.

                Cheers,
                Manik.
                • 5. Re: create table based on previous desc
                  975169
                  no its not in that format
                  i pasted the format in the question i guess it messed up..
                  it looks kined of like a table:
                  i'll type here manually how it looks like with a few rows:
                  Name Type Nullable Default Comments
                  ----------------------------- -------------- ---------- --------- ---------------
                  SUBSCR_NO NUMBER(10) Y 1
                  SUBSCR_NO_RESETS NUMBER(6)
                  and so on..
                  • 6. Re: create table based on previous desc
                    jeneesh
                    Post your codes in between {noformat}
                    {noformat} tags, so that the formatting will prevail..                                                                                                                                                                                                    
                    • 7. Re: create table based on previous desc
                      975169
                      Name                                      Type          Nullable    Default    Comments 
                      ------------------------------------- ----------------- --------- ----------- -------------
                      SUBSCR_NO                             NUMBER(10)                                 
                      SUBSCR_NO_RESETS                      NUMBER(6)                                  
                      ACCOUNT_NO                            NUMBER(10)                     1            
                      RANGE_MAP_EXTERNAL_ID                 VARCHAR2(30)   Y                             
                      PARENT_ACCOUNT_NO                     NUMBER(10)      Y                             
                      CURRENCY_CODE                         NUMBER(6)                      0
                      Edited by: BluShadow on 20-Nov-2012 11:11
                      added {noformat}
                      {noformat} tags to help formatting.  Please read {message:id=9360002} and learn to do this yourself.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
                      • 8. Re: create table based on previous desc
                        975169
                        the problem is you cant see the spaces..
                        anyway, under type of course is the data type, under Nullabe its Y stands for nullable or blank for not null constraint, under default of course the default value
                        • 9. Re: create table based on previous desc
                          EdStevens
                          972166 wrote:
                          hey,

                          i'm trying to create a few tables based on a desc i recieved
                          Name Type Nullable Default Comments
                          ----------------------------- ------------ -------- ----------- --------
                          SUBSCR_NO NUMBER(10)
                          SUBSCR_NO_RESETS NUMBER(6)
                          ....
                          is there an automatic way to do it or iether i have to use the create table command and modfy it according to specifications in the desc?
                          Given what you've shown, there is not automated way. Sometimes you just have to do the work yourself.

                          However, if I had that file, I could convert it to the necessary SQL statements by using the features of a real editor, like vi - not like notepad.