Help with the COPY command

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 Received on Wed Jun 04 2003 - 11:54:31 CDT