Re: Help with the COPY command

On Wed, 4 Jun 2003 17:54:31 +0100, "Mahesh" <HalpethM_at_Logica.com> wrote:

>Hello SQL*Plus Guru's,
>
>I have a query regarding the COPY command, please help !!
>
>Oracle version - Oracle8i Enterprise Edition Release 8.1.7.4.50
>============================================
>
>I have a table, SBL_MSGTAB, with a LONG column in Production database, and
>would like to create this table in a dev/test database. Now, because I can't
>create this table using "CREATE TABLE .... AS SELECT * FROM...." command, I
>have to use the COPY command, right ?
>
>So, here goes :-
>
>COPY from scott/tiger_at_PROD to scott/tiger_at_DEV1 create scott.SBL_MSGTAB
>using -
>SELECT a.* from scott.cust_codes_at_DEV1 b, scott.SBL_MSGTAB_at_PROD a -
>WHERE a.cust_code = b.cust_code ;
>
>Which, upon attempting gave me the following error :-
>
>SELECT a.* from scott.cust_codes_at_DEV1 b, scott.SBL_MSGTAB_at_PROD a WHERE
>a.cust_code = b.cust_code
>*
>Error in SELECT statement: ORA-00942: table or view does not exist
>
>The tables exist at both ends (ie. cust_codes_at_DEV1 and SBL_MSGTAB_at_PROD ),
>this error occurs because there is no data in scott.SQL_MSGTAB_at_PROD (If the
>table at PROD database has rows, then everything is hunky-dory)
>
>Well, my problem is , How to copy that table over to dev1 database ,
>considering the table has LONG columns in it ... I have also tried replacing
>the "create" in COPY command with "replace" ... still the same problem... Is
>it not possible to create a table using the COPY command even though the
>table at the remote-end is empty ?? The "create table ... as select " does
>create empty tables if the remote-end table or the select command returns
>no_data ... whats different with COPY ??
>
>Your help would be appreciated.
>
>Thanks,
>
>Mahesh.
>A/E: mash1830_at_yahoo.com
>

You basically have everything messed up. In your COPY command the source table is a *join*. This won't work.
The ora-942 error has *NOTHING* to do with the table being empty or not being empty.
Can you explain without the amount of rambling you've done above, including a lot of assertions about COPY and CTAS which are simply incorrect, *EXACTLY* in clear and plain text WHICH source table needs to get to WHICH target table.
Can you also please reread the documentation on COPY in the sql*plus manual.
I would have tried to reconstruct the correct COPY command, weren't you post so messy.

Sybrand Bakker, Senior Oracle DBA

To reply remove -verwijderdit from my e-mail address Received on Wed Jun 04 2003 - 12:32:01 CDT