Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Help with the COPY command

Re: Help with the COPY command

From: Karsten Farrell <kfarrell_at_belgariad.com>
Date: Wed, 04 Jun 2003 19:47:06 GMT
Message-ID: <MPG.1947ec931cc875e39897a3@news.la.sbcglobal.net>


HalpethM_at_Logica.com said...
> 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
>
>

I think you might have a syntax error. Hop on over to the AskTom site and search for "copy from" (including quotes). You'll get a few hits on how to do it, including this document (will wrap, so copy/paste into your browser):

http://asktom.oracle.com/pls/ask/f?p=4950:8:1090762::NO::F4950_P8 _DISPLAYID,F4950_P8_CRITERIA:1546206316314,

-- 
/Karsten
DBA > retired > DBA
Received on Wed Jun 04 2003 - 14:47:06 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US