| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: [RE: COPY vs. INSERTS
This message is in MIME format. Since your mail reader does not understand
this format, some or all of this message may not be legible.
------_=_NextPart_001_01BFCA53.E4E08FA0
Content-Type: text/plain;
charset="iso-8859-1"
George, you are right with NUMBER versus NUMBER(38). Didn't think it thru. Returning to the original message - I did some experimenting with copy command. If you have original table AA with column NUMBER and you do COPY .... create AANEW using select * from AA(original table)- yes it will create DECIMAL(38) but if you precreate empty table where you want to copy and use command COPY ... INSERT AANEW using select * from AA - it will create exact copy of original table. We need to use COPY if there is a LONG datatype in the table, so the conclusion is - for tables which have columns NUMBER without precision if we want to use COPY we need to precreate empty result table.
Alex Hillman
-----Original Message-----
From: George.Brennan_at_warnermusic.com
[mailto:George.Brennan_at_warnermusic.com]
Sent: Tuesday, May 30, 2000 6:18 AM
To: Multiple recipients of list ORACLE-L
Subject: RE: [RE: COPY vs. INSERTS
There is a BIG difference.
NUMBER can store a floating point number NUMBER(38) will only store interger numbers.
i.e. it will truncate the decimals.
George.
> -----Original Message-----
> From: "Alex Hillman" <alex_hillman_at_physia.com>
> [mailto:alex_hillman_at_physia.com]
> Sent: Friday, May 26, 2000 10:40 PM
> To: smtp_at_inl001@servers["Multiple recipients of list ORACLE-L"
> <ORACLE-L_at_fatcity.com>]
> Subject: RE: [RE: COPY vs. INSERTS
>
>
> What do you think is the difference between Oracle types NUMBER and
> DECIMAL(38) - answer is NONE.
>
> Alex Hillman
>
> -----Original Message-----
> Sent: Friday, May 26, 2000 3:08 PM
> To: Multiple recipients of list ORACLE-L
>
>
> One important note regarding SQLPLUS COPY utility that can
> make this less
> utilitarian is as follows from SQL*Plus 3.1 User Guide and Reference:
> "Note: To enable the copying of data between ORACLE and non-ORACLE
> databases,
> NUMBER columns are changed to DECIMAL columns in the
> destination table.
> Hence, if you are copying between ORACLE databases, a NUMBER
> column with no
> precision will be changed to a DECIMAL(38) column. When
> copying between
> ORACLE databases, you should use SQL commands (CREATE TABLE
> AS and INSERT)
> or
> you should ensure that your columns have a precision specified."
>
> Milan Rahman
> Senior Oracle DBA
> Oracle Applications DBA
> SageLogix, Inc.
> Greenwood Village, Colorado
>
> http://www.sagelogix.com
>
> --
> Author: Milan Rahman
> INET: milan_rahman_at_MailAndNews.com
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>
>
-- Author: INET: George.Brennan_at_warnermusic.com Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). ------_=_NextPart_001_01BFCA53.E4E08FA0 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN"> <HTML> <HEAD> <META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; = charset=3Diso-8859-1"> <META NAME=3D"Generator" CONTENT=3D"MS Exchange Server version = 5.5.2448.0"> <TITLE>RE: [RE: COPY vs. INSERTS</TITLE> </HEAD> <BODY> <P><FONT SIZE=3D2>George, you are right with NUMBER versus NUMBER(38). = Didn't think it thru. Returning to the original message - I did some = experimenting with copy command. If you have original table AA with = column NUMBER and you do COPY .... create AANEW using select * from = AA(original table)- yes it will create DECIMAL(38) but if you precreate = empty table where you want to copy and use command COPY ... INSERT = AANEW using select * from AA - it will create exact copy of original = table. We need to use COPY if there is a LONG datatype in the table, so = the conclusion is - for tables which have columns NUMBER without = precision if we want to use COPY we need to precreate empty result = table.</FONT></P> <P><FONT SIZE=3D2>Alex Hillman</FONT> </P> <P><FONT SIZE=3D2>-----Original Message-----</FONT> <BR><FONT SIZE=3D2>From: George.Brennan_at_warnermusic.com</FONT> <BR><FONT SIZE=3D2>[<A = HREF=3D"mailto:George.Brennan_at_warnermusic.com">mailto:George.Brennan_at_war= nermusic.com</A>]</FONT> <BR><FONT SIZE=3D2>Sent: Tuesday, May 30, 2000 6:18 AM</FONT> <BR><FONT SIZE=3D2>To: Multiple recipients of list ORACLE-L</FONT> <BR><FONT SIZE=3D2>Subject: RE: [RE: COPY vs. INSERTS</FONT> </P> <BR> <P><FONT SIZE=3D2>There is a BIG difference.</FONT> </P> <P><FONT SIZE=3D2>NUMBER can store a floating point number </FONT> <BR><FONT SIZE=3D2>NUMBER(38) will only store interger numbers.</FONT> </P> <P><FONT SIZE=3D2>i.e. it will truncate the decimals.</FONT> </P> <P><FONT SIZE=3D2>George.</FONT> </P> <P><FONT SIZE=3D2>> -----Original Message-----</FONT> <BR><FONT SIZE=3D2>> From: "Alex Hillman" = <alex_hillman_at_physia.com></FONT> <BR><FONT SIZE=3D2>> [<A = HREF=3D"mailto:alex_hillman_at_physia.com">mailto:alex_hillman_at_physia.com</= A>]</FONT> <BR><FONT SIZE=3D2>> Sent: Friday, May 26, 2000 10:40 PM</FONT> <BR><FONT SIZE=3D2>> To: smtp_at_inl001@servers["Multiple = recipients of list ORACLE-L"</FONT> <BR><FONT SIZE=3D2>> <ORACLE-L_at_fatcity.com>]</FONT> <BR><FONT SIZE=3D2>> Subject: RE: [RE: COPY vs. INSERTS</FONT> <BR><FONT SIZE=3D2>> </FONT> <BR><FONT SIZE=3D2>> </FONT> <BR><FONT SIZE=3D2>> What do you think is the difference between = Oracle types NUMBER and</FONT> <BR><FONT SIZE=3D2>> DECIMAL(38) - answer is NONE.</FONT> <BR><FONT SIZE=3D2>> </FONT> <BR><FONT SIZE=3D2>> Alex Hillman</FONT> <BR><FONT SIZE=3D2>> </FONT> <BR><FONT SIZE=3D2>> -----Original Message-----</FONT> <BR><FONT SIZE=3D2>> Sent: Friday, May 26, 2000 3:08 PM</FONT> <BR><FONT SIZE=3D2>> To: Multiple recipients of list ORACLE-L</FONT> <BR><FONT SIZE=3D2>> </FONT> <BR><FONT SIZE=3D2>> </FONT> <BR><FONT SIZE=3D2>> One important note regarding SQLPLUS COPY = utility that can </FONT> <BR><FONT SIZE=3D2>> make this less </FONT> <BR><FONT SIZE=3D2>> utilitarian is as follows from SQL*Plus 3.1 = User Guide and Reference:</FONT> <BR><FONT SIZE=3D2>> "Note: To enable the copying of data = between ORACLE and non-ORACLE</FONT> <BR><FONT SIZE=3D2>> databases, </FONT> <BR><FONT SIZE=3D2>> NUMBER columns are changed to DECIMAL columns = in the </FONT> <BR><FONT SIZE=3D2>> destination table. </FONT> <BR><FONT SIZE=3D2>> Hence, if you are copying between ORACLE = databases, a NUMBER </FONT> <BR><FONT SIZE=3D2>> column with no </FONT> <BR><FONT SIZE=3D2>> precision will be changed to a DECIMAL(38) = column. When </FONT> <BR><FONT SIZE=3D2>> copying between </FONT> <BR><FONT SIZE=3D2>> ORACLE databases, you should use SQL commands = (CREATE TABLE </FONT> <BR><FONT SIZE=3D2>> AS and INSERT)</FONT> <BR><FONT SIZE=3D2>> or </FONT> <BR><FONT SIZE=3D2>> you should ensure that your columns have a = precision specified."</FONT> <BR><FONT SIZE=3D2>> </FONT> <BR><FONT SIZE=3D2>> Milan Rahman</FONT> <BR><FONT SIZE=3D2>> Senior Oracle DBA</FONT> <BR><FONT SIZE=3D2>> Oracle Applications DBA</FONT> <BR><FONT SIZE=3D2>> SageLogix, Inc.</FONT> <BR><FONT SIZE=3D2>> Greenwood Village, Colorado</FONT> <BR><FONT SIZE=3D2>> </FONT> <BR><FONT SIZE=3D2>> <A HREF=3D"http://www.sagelogix.com" = TARGET=3D"_blank">http://www.sagelogix.com</A></FONT> <BR><FONT SIZE=3D2>> </FONT> <BR><FONT SIZE=3D2>> -- </FONT> <BR><FONT SIZE=3D2>> Author: Milan Rahman</FONT> <BR><FONT SIZE=3D2>> INET: = milan_rahman_at_MailAndNews.com</FONT> <BR><FONT SIZE=3D2>> </FONT> <BR><FONT SIZE=3D2>> Fat City Network Services -- = (858) 538-5051 FAX: (858) 538-5051</FONT> <BR><FONT SIZE=3D2>> San Diego, = California -- Public Internet = access / Mailing Lists</FONT> <BR><FONT SIZE=3D2>> = --------------------------------------------------------------------</FO= NT> <BR><FONT SIZE=3D2>> To REMOVE yourself from this mailing list, send = an E-Mail message</FONT> <BR><FONT SIZE=3D2>> to: ListGuru_at_fatcity.com (note EXACT spelling = of 'ListGuru') and in</FONT> <BR><FONT SIZE=3D2>> the message BODY, include a line containing: = UNSUB ORACLE-L</FONT> <BR><FONT SIZE=3D2>> (or the name of mailing list you want to be = removed from). You may</FONT> <BR><FONT SIZE=3D2>> also send the HELP command for other = information (like subscribing).</FONT> <BR><FONT SIZE=3D2>> </FONT> <BR><FONT SIZE=3D2>> </FONT> </P> <BR> <P><FONT SIZE=3D2>-- </FONT> <BR><FONT SIZE=3D2>Author: </FONT> <BR><FONT SIZE=3D2> INET: George.Brennan_at_warnermusic.com</FONT> </P> <P><FONT SIZE=3D2>Fat City Network Services -- (858) = 538-5051 FAX: (858) 538-5051</FONT> <BR><FONT SIZE=3D2>San Diego, = California -- Public Internet = access / Mailing Lists</FONT> <BR><FONT = SIZE=3D2>---------------------------------------------------------------= -----</FONT> <BR><FONT SIZE=3D2>To REMOVE yourself from this mailing list, send an = E-Mail message</FONT> <BR><FONT SIZE=3D2>to: ListGuru_at_fatcity.com (note EXACT spelling of = 'ListGuru') and in</FONT> <BR><FONT SIZE=3D2>the message BODY, include a line containing: UNSUB = ORACLE-L</FONT>Received on Tue May 30 2000 - 11:27:05 CDT
 |
 |