Re: Help with the COPY command

Don't know whats so confusing or messy about my post, anyway to explain it in simple terms :-

1. I would like to create the SBL_MSGTAB table, residing in PROD database, in the DEV1 database.
2. The SBL_MSGTAB table contains LONG datatype columns among other columns. Please note, cust_code column in this table forms the unique/primary key.
3. When the table (SBL_MSGTAB) gets created in DEV1 database, I want to restrict the amount of rows inserted by joining the "CUST_CODES" table in DEV1. Please note: If no rows match the "cust_code" between SBL_MSGTAB and CUST_CODES, The table, SBL_MSGTAB needs to get created in DEV1 database (as an empty table).

Thanks,

Mahesh.

"Sybrand Bakker" <gooiditweg_at_sybrandb.demon.nl> wrote in message news:f0bsdvgpsp77347tdsfsksfqibt1esbqnh_at_4ax.com...
> 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 Thu Jun 05 2003 - 04:32:15 CDT