Results 1 to 5 of 5

Thread: scale problem

  1. #1
    Join Date
    Feb 2003
    Posts
    17

    Unanswered: scale problem

    Hi folks,

    I have to clone a table but I only have to take out the ddl.

    Actually I do it using 'Create table tablename as select * from othertable' and then 'truncate tablename'.

    The problem is when I do that, the scale factor for the NUMBER fields is set to 0 so when I have to insert values with precision, it won't show because the scale factor is set to 0. Why is that? There is no scale factor in the table to be cloned, and when the table is cloned, it is set. Is there a way to tell it not to set any scale?

    Also, if you know of a better way to 'clone' a table ddl, your suggestions are appreciated.

    Thanks in advance,

    Best regards,

  2. #2
    Join Date
    Nov 2002
    Posts
    833
    if you only want the DDL why not make use of the dbms_metadata package ?
    retrieve the DDL; rename the table using your favourite editor and then apply to your schema using sqlplus or sqlworksheet

  3. #3
    Join Date
    Feb 2003
    Posts
    17
    i'm sorry i'm new to oracle, can you guide me to a doc describing functions under that package?

    Thanks again,

    Regards,

  4. #4
    Join Date
    Jul 2003
    Posts
    2,296
    or, you can export the table with ROWS=n

    import the table with indexfile=tablescript.sql (will create a script and will
    not import anything)

    open the tablescript.sql file and see your DDL
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  5. #5
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    A much easier way to create an empty table from another one is to do the following

    Create table tablename as select * from othertable where 1=2;

    This will create the table, but do not insertion because 1=2 will never be true.

    After you are done, the precision should be fine (see below)


    Code:
    SQL> desc trk2so
     Name                                      Null?    Type
     ----------------------------------------- -------- -----------------------
     DEL_DOC_NUM                                        VARCHAR2(14)
     TRK_NUM                                            VARCHAR2(5)
     DELIVERY_DATE                                      DATE
     RETAIL_TOTAL                                       NUMBER(10,2)
     DEL_CHARGE                                         NUMBER(7,2)
     DOD_ADJUST                                         NUMBER(10,2)
     DOD_REASON                                         VARCHAR2(255)
     ORD_TP_CD                                          VARCHAR2(3)
     ORD_SRT_CD                                         VARCHAR2(3)
     CDXCDA_PAY_RETAIL                                  NUMBER(10,2)
     CDI_PAY_RETAIL                                     NUMBER(10,2)
    
    SQL> create table whb as select * from trk2so where 1=2;
    
    Table created.
    
    SQL> desc whb
     Name                                      Null?    Type
     ----------------------------------------- -------- -----------------------
     DEL_DOC_NUM                                        VARCHAR2(14)
     TRK_NUM                                            VARCHAR2(5)
     DELIVERY_DATE                                      DATE
     RETAIL_TOTAL                                       NUMBER(10,2)
     DEL_CHARGE                                         NUMBER(7,2)
     DOD_ADJUST                                         NUMBER(10,2)
     DOD_REASON                                         VARCHAR2(255)
     ORD_TP_CD                                          VARCHAR2(3)
     ORD_SRT_CD                                         VARCHAR2(3)
     CDXCDA_PAY_RETAIL                                  NUMBER(10,2)
     CDI_PAY_RETAIL                                     NUMBER(10,2)
    
    SQL> drop table whb;
    
    Table dropped.
    Last edited by beilstwh; 11-08-04 at 15:10.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

Posting Permissions

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