This discussion is archived
9 Replies Latest reply: Nov 20, 2012 4:48 AM by EdStevens RSS

create table based on previous desc

975169 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    Post your codes in between {noformat}
    {noformat} tags, so that the formatting will prevail..                                                                                                                                                                                                    
  • 7. Re: create table based on previous desc
    975169 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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.

Legend

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