Re: how to query between 2 remote databases

From: TurkBear <john.greco_at_dot.state.mn.us>
Date: Wed, 02 Jul 2003 12:04:47 -0500
Message-ID: <un36gvcigglkf9s3f9bml9ou2kfgh2m7uv@4ax.com>

The oracle error details are:

---

ORA-00933 SQL command not properly ended

Cause: The SQL statement ends with an inappropriate clause. For example, an ORDER BY clause may have been included in a CREATE VIEW or INSERT statement. ORDER BY cannot be used to create an ordered view or to insert in a certain order. Also, an improper SQL ending occurs if IN clause is used with only one argument (IN(X), for example). An IN clause must have two or more arguments.

Action: Correct the syntax by removing the inappropriate clauses. It may be possible to duplicate the removed clause with another SQL statement. For example, to order the rows of a view, do so when querying the view and not when creating it. This error can also occur in SQL*Forms applications if a continuation line is indented. Check for indented lines and delete these spaces.

---

I suspect it is in the use of the user/pass combo with the database link name.. Try recreating the links with the usual
create database link link_name1 ( depends on global_name setting on remote database) connect to user identified by pass using 'db1.world' ( assumes tnsnames.ora entry by that name)

create database link link_name2 ( depends on global_name setting on remote database) connect to user identified by pass using 'db2.world' ( assumes tnsnames.ora entry by that name)

Then:
select * from
tbl1_at_link_name1 a,tb2_at_link_name2 b
where a.col1 = b.col1;

should work..

BTW, what Oracle version ( it can make a difference)

"kiran" <kiran.news.invalid_at_web2news.net> wrote:

>The error I get is:
>
>SQL> !oerr ora 00933
>00933, 00000, "SQL command not properly ended"
>
>The OS is HP/UX 9000
>
>
>TurkBear wrote:
>> "kiran" <kiran.news.invalid_at_web2news.net> wrote:
>>
>>>How to query on two tables that are on two different remote database
>>>with a join.
>>>
>>> s *
>>> f tb1_at_uid/pass_at_db1.world a, tb2_at_uid/pass_at_db2.world b
>>> w a.col1 = b.col2
>>>
>>>but this does not work, any idea ?
>>>
>>>Thanks
>> Does not work, how?
>>
>> Also OS, version, etc would be a nice touch..
Received on Wed Jul 02 2003 - 12:04:47 CDT