| Need SQL Query [message #331591] |
Fri, 04 July 2008 03:02  |
user71408
Messages: 585
Registered: November 2007
Location: NE
|
Senior Member |
|
|
Hi ,
I have 2 schemas.one is PRODUCTION and one is DEVELOPMENT.
I am trying to copy one table from Production to development. It contains 4 columns. Two columns are having datatype as "varchar2" and one column datatype is Number and 4th column is "LOB" type.When I am trying to copy from Production to development the three columns were copied But the 4th column was not copied.Please find the following query have used.
COPY <prodcution> Schemaname to <Development> Schemaname
Replace tablename using
select * from tablename.
can you pelase let me know hpw to copy the 4th column into the Development also.
Please let me know for more information.
Thank you.
[Updated on: Fri, 04 July 2008 03:06] Report message to a moderator |
|
|
|
|
|
| Re: Need SQL Query [message #331612 is a reply to message #331593] |
Fri, 04 July 2008 04:07  |
user71408
Messages: 585
Registered: November 2007
Location: NE
|
Senior Member |
|
|
Michel,
Production session : system/manager@<connection >
development : scott/tiger@<connection> (assume like this)
The table contains : columns as
Production:
Item Supplier Message Descrption
------------------------------------------
Development
Item Supplier Message Message_rec
--------------------------------------
I want to copy all those data in the table in Productio to Development table.Both table names are same.every thing is copied except Description because it's data type is CLOB
please look into this.
Thank you.
|
|
|
|
|
|
| Re: Need SQL Query [message #331629 is a reply to message #331615] |
Fri, 04 July 2008 04:38 |
user71408
Messages: 585
Registered: November 2007
Location: NE
|
Senior Member |
|
|
Michel I didn't get the data for 4t column that time it shows the error as " excxeed the limit. But I have given 256 bytes. It was not yet copied. So please let me know to copy...
| Quote: |
If you can't then this is just because you don't want and despise us and don't deserve to be helped.
|
I am not getting this that's why I posted what I have tried and what I am getting as per your suggestions...
|
|
|
|
|
|
| Re: Need SQL Query [message #331682 is a reply to message #331634] |
Fri, 04 July 2008 09:38 |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
Here's the documentation for the COPY command
Some key sections you should take note of are:| Quote: | Copies data from a query to a table in the same or another database. COPY supports the following datatypes:
CHAR
DATE
LONG
NUMBER
VARCHAR2
|
Can you see a potential cause of your problem yet?
| Quote: | Note:
In general, the COPY command was designed to be used for copying data between Oracle and non-Oracle databases. You should use SQL commands (CREATE TABLE AS and INSERT) to copy data between Oracle databases.
|
What you need to do is CREATE TABLE <schema>.<table> AS SELECT <column_list> FROM <other schema>.<Other Table>
This still doesn't address the issue of why, having been asked explicitly to do so, you haven't provided us with a cut/paste of the SQL*Plus session that generated this problem.
[Updated on: Fri, 04 July 2008 09:38] Report message to a moderator |
|
|
|
| Re: Need SQL Query [message #331704 is a reply to message #331591] |
Fri, 04 July 2008 13:19 |
 |
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
|
Senior Member |
|
|
>This still doesn't address the issue of why, having been asked explicitly to do so, you haven't provided us with a cut/paste of the SQL*Plus session that generated this problem.
In My Opinion:
Some folks are clue magnets & are a pleasure to assist.
Some folks are clue resistant & require some patience.
While other folks are clue repellent & should be avoided.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
