Database link miss understanding

[stressman]

Hy,
I successfully create a public database link from my oracle database (vers. 8.1.7.0) to an SQLServer.

from server manager i succeed the command :
select * from table@SQLSERVER;

but if i try to specify the field i want to report from the SQLServer table the command failed

select field1 from table@SQLSERVER;
ORA-12663 this oracle error code is one of the different errors i had.

If someone has any experience with database link and select of informations it will be a pleasure for me to share this experience.

Thanks

 

[wellster34]

To create it, use an id that has the proper priviledges:

CREATE DATABASE LINK test_link
CONNECT TO test_id IDENTIFIED BY test_id_password
USING 'database_name';
commit;

To use it, make sure that id that created the link is the one that is running the query otherwise it will not work.

SELECT Column1,Column2
FROM TABLE_OTHER_DATABASE@test_link;

 

[stressman]

I think i was not clear enought.

I create the database link with enought priviledges.

the answer of this request :

select * from Table@link;

is :

Field1 Field2
-----------------
1 one
2 two
3 three

but the following request

select Field1, Field2 from Table@link;

ORA- ..... could not ....

Why i don't have the same rows i don't anderstand why i have an oracle error.

thanks for your help

 

[RhythmAddict112]

what happens if you do a

Desc someTable@SQLServer


what do the columns look like? If you can select *, the problem is obviously in the identification of the actual columns...I've never tried a DB link to SQL-serv so I can't say what it is conclusively. If the describe doesn't help you, try double quotes, or the schema name

ie..

select "field1" from table@sqlServer

and/or

select "schemaname.field1" from table@sqlserver

etc..

hth

Use your resources, you're on the internet!

 

[sem]

Try

select "Field1", "Field2" from Table@link;

I think that column names are capitalized so you should make Oracle to use exact column names by doublequoting them.

Regards, Dima

http://seminihin.narod.ru