Re: select from schema 1 minus select from schema 2

wait, i figured it out (dork factor X2 here, sorry for bandwidth waste).

select grammer was wrong.
now If I enter these commands, things work fine:

connect jeff/jeff_at_xxorcl;

create database link db_link_name
connect to username
identified by userpassword using 'xxclib';

select alt_num from altpart minus
select itemnum from inventory_at_db_link_name;

Thanks for your patience.

Jeff

On Fri, 17 Nov 2000 12:23:27 -0500, Jeff Kish <jeff.kish_at_ait-mmii.com> wrote:

>Thanks.
>
>Can someone help me out a bit more? I am looking in the manual
>on creating a database link. I can't seem to do it.
>
>Things seem to be a bit.. well.. confusing.
>
>Oracle docs for 7.x say:
>
>"Before Oracle7, a database administrator could specify any linkname
>for a database link. However, with Oracle7 and
>later releases, a database link must have the same name as the global
>database name of the database. Remember that the
>service name is also the same as the global database name. Therefore,
>the linkname and service name are now the same.
>Although this may seem to make the USING clause redundant, it is still
>a necessary part of the syntax. "
>
>So I looked up what a service name is and it says:
>
>"service_name
>
>Specifies the service name defined in the TNSNAMES.ORA file or stored
>in Oracle Names associated with the connect
>descriptor for the desired database. If the remote server is in the
>local server's default domain, the service name does not
>need to include the domain name. However, if the server is in another
>domain, the service name must include the domain.
>(The default domain is determined by a parameter in the server's
>SQLNET.ORA file. "
>
>So, since I can log in using (say) scott/tiger_at_xxorcl because I have
>defined xxorcl as a connect string using sql easy config, I figured
>that xxorcl must be a service name, right?
>
>but trying this:
>
>create database link xxorcl connect to scott identified by tiger using
>'xxorcl'
>
>does not throw any errors (I get database link created), but I can't
>do:
>
>select * from xxorcl.part where part is a name of a table in the
>scott schema.
>
>If I log in as scott, password tiger, I can see the part table.
>
>
>Bottom line.. I'm lost.. Can someone drag me out of the pit here?
>
>Plus, I need to be able to code it up to work on customers machines.
>(I'll have user names, passwords, and connect strings available).
>
>Thanks
>
>Jeff
>
>On Thu, 16 Nov 2000 23:20:06 GMT, Haniff_at_cyberdude.com wrote:
>
>>In article <1lh81to1c8gr1dvlei3m03cqgk1nqhi15o_at_4ax.com>,
>> Jeff Kish <jeff.kish_at_ait-mmii.com> wrote:
>>> Almost forgot.. the schemas may be in separate instances (I knew I
>>> forgot something that made this non trivail).
>>>
>>> On Thu, 16 Nov 2000 10:38:10 -0500, Jeff Kish
 <jeff.kish_at_ait-mmii.com>
>>> wrote:
>>>
>>> >I am using Pro-c (and need to know if this is possible), but this
>>> >could be extended to a general question I guess.
>>> >
>>> >Is it possible to create a result set from selecting from a table in
>>> >one schema and minus'ing a select of identical column types from a
>>> >table in another schema?
>>> >
>>> >Thanks
>>> >Jeff
>>> >e-mail replies are appreciated.
>>>
>>
>>Brian Peasland's answer still holds, but you'd have to create a
>>database link between the two instances. So:
>>
>>select * from tableA_at_schema1
>>Minus
>>select * from tableA_at_schema2
>>
>>Neef
>>
>>
>>Sent via Deja.com http://www.deja.com/
>>Before you buy.
Received on Fri Nov 17 2000 - 11:36:15 CST