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