Home » Other » Client Tools » Need SQL Query (9.0.4.0)
Need SQL Query [message #331591] Fri, 04 July 2008 03:02 Go to next message
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 #331593 is a reply to message #331591] Fri, 04 July 2008 03:12 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Where are the description of tables in both databases?
Where is the copy and paste of the session?

Regards
Michel
Re: Need SQL Query [message #331612 is a reply to message #331593] Fri, 04 July 2008 04:07 Go to previous messageGo to next message
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 #331615 is a reply to message #331612] Fri, 04 July 2008 04:18 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If you use SQL*Plus copy command, you are able to post SQL*Plus description and SQL*Plus session.
Do it!
With about 300 posts you should know how to post and be able to do it.
If you can't then this is just because you don't want and despise us and don't deserve to be helped.

You also should able to look at documentation, what is said about COPY command?

Regards
Michel
Re: Need SQL Query [message #331629 is a reply to message #331615] Fri, 04 July 2008 04:38 Go to previous messageGo to next message
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 #331634 is a reply to message #331629] Fri, 04 July 2008 04:46 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
user71408 wrote on Fri, 04 July 2008 11:38
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...

No comment!

Re: Need SQL Query [message #331682 is a reply to message #331634] Fri, 04 July 2008 09:38 Go to previous messageGo to next message
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 Go to previous message
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.
Previous Topic: Setup a scheduler in toad
Next Topic: Deployment Tools
Goto Forum:
  


Current Time: Fri Mar 29 02:48:31 CDT 2024