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

Home -> Community -> Usenet -> c.d.o.tools -> Re: select from schema 1 minus select from schema 2

Re: select from schema 1 minus select from schema 2

From: Jeff Kish <jeff.kish_at_ait-mmii.com>
Date: Fri, 17 Nov 2000 12:23:27 -0500
Message-ID: <dcqa1tsll6qqpm91ljt6kuvjccfion3jk2@4ax.com>

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:23:27 CST

Original text of this message

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