Re: Help with the COPY command

Mahesh,
Your post is fine. Bakker has his panties in a knot as usual.

Mahesh wrote:

> 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 - 22:55:18 CDT