This content has been marked as final. Show 4 replies
Can you give us an example of the code that causes the ORA-942 error ? SQL*Server can be case sensitive for object names so it depends how they are given in the procedure.
Also, any privileges need to be granted directly in PL/SQL and not via a role so make sure that isn't a possible cause.
I tried the case sensitivity also, but no luck there.
PROCEDURE prcJentest IS
CURSOR c1 IS
FROM dbo.customer_order@wce06 co;
I've also tried the above with "dbo"."customer_order"@wce06 and that doesn't work either.
984196 wrote:Are you saying you can issue in Oracle SQL*Plus:
I have a database link created from Oracle (version 10.2.0) to SQL Server. The database link works. I can query the SQL Server database directly, ex SELECT columna FROM databasename@LINK;
And it works. Then it should not give you ORA-00942. Keep in mind, Oracle names are case sensitive, so you must use Oracle quoted names. Table and column names must be types in EXACT case they show up in SQL Server enclosed in double quotes. So post SQL*PLus execution (along with results) snippet of:
SELECT co.id FROM dbo.customer_order@wce06 co;
SELECT co.id FROM dbo.customer_order@wce06 co WHERE rownum = 1;
Please pay attention that you also have to surround column names by double quotes as they are case sensitive as well.
So just to clarify when you use SQL*Plus then this select works:
SELECT co."id" FROM "dbo"."customer_order"@wce06 co;
but when you use it in a cursor then it fails with ORA-942 error. Could you please provide the source table definition and a gateway trace level DEBUG. Gateway tracing is enabled in the gateway init file by setting HS_FDS_TRACE_LEVEL=DEBUG - then starting a new SQL*Plus session and executing the code. Once finished, exit from the SQL*Plus session and disable tracing again. The trace file is now located in the <gateway>/log or <gateway>/trace directory depending on the platform.