Re: Working with 2 schemas

"Steve Ashmore" <sashmore_at_neonramp.com> wrote in news:ukslafb7vciveb_at_corp.supernews.com:

> If the schema's are in the same instance you can just
> do a
> insert into schema1.tab1
> select * from schema2.tab2;
>
> If they are in different instances
> you can try to use the SQLPLUS copy command.
> It does not require a database link.
>

From the Oracle 9i SQL*Plus Users Guide and Reference:

"The COPY command will be obsoleted in future releases of SQL*Plus. COPY supports the datatypes listed for the COPY command, but no new datatypes will be supported."
...
"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."

So, avoid the COPY command - for future compatibility issues if nothing else.

-- 
Jason Baugher 
Virtual Adept Professional Consulting Services
1406 Adams St.
Quincy, IL 62301
(217) 221-5406
http://baugher.pike.il.us/virtualadept
jason_at_baugher.pike.il.us