Oracle/SAP Proxy Table Error - Invalid Table Name

[former_SQLA_member1694868]

The following code to create a proxy table to an Oracle database works just fine:

CREATE EXISTING TABLE dsown.xTESTTEST AT 'DWar1..SAPUSR3.TEST';

select * from dsown.xTESTTEST;

The following code to create a proxy table to a different table on the same Oracle database does not:

CREATE EXISTING TABLE dsown.SAP_TEST AT 'DWar1..SAPUSR3./BIC/OHZOH_BP_AT';

select * from dsown.SAP_TEST;

The error gives the driver information and states "invalid table name."

I am guessing that the forward slashes in the table name are the source of the problem, but double quoting them does not help either.

CREATE EXISTING TABLE dsown.SAP_TEST AT 'DWar1..SAPUSR3."/BIC/OHZOH_BP_AT"';

gives an error message that the table cannot be found.

Had this question a few years back but the data department gave us aliases without slashes. That solution has not been offered this time.

http://sqlanywhere-forum.sap.com/questions/11882/create-proxy-table-from-oraclesap-to-sqla

[Former Member]

* * Status update for this. * * A couple of notes to tie up loose ends.

Use the Synonyms would have helped here but they won't work with the (recommended) SQL Anywhere ODBC driver for Oracle. Another workaround would be to create safely named views.

There was a bug exposed when using the SQL Anywhere ODBC driver for Oracle that can cause CIS to not delimit table names (as orignally reported here). This has been fixed. See Note 2228314 for details. The fix was made for all currently maintained branches; 12.0.1, 16.0.0 and 17+.

[VolkerBarth]

FWIW, the link to the note seems broken...

[Former Member]

That link is an SAP Note, and it may not work if you are not validated as a SAP user. Just in case you don't have that set up the engineering change number for this is 790670 which will show up in the ReadMe files for the EBFs when they become available.

[VolkerBarth]

Hm, the link does not ask for credentials, so it's difficult to validate as a SAP user, even if I have done so in a different tab in the same browser... - I get a "server not found" response.

[Former Member]

Not much about this makes a lot of sense unless the issue resides on the Oracle side (and to a lesser degree upon the ODBC driver you are currently using).

Since you no longer can rely upon synonyms to resolve this, you will likely need to get your Oracle DBAs fully engaged in identifying how this is going wrong on their end. Without their buy-in into your project (or product installation) they could present unecessary barriers to the success of your project and since this seems to have been an issue for a few years (going by other posting) you may need your or their senior management involvement to get the necessary resource to resolve this.

I can only offer the follow 'technical' observations:

The case where you are seeing "invalid table name" that should not be possible with our 'ORAODBC' server type since (with that) we should be delimiting all of the identifiers and that would avoid all concerns about the '/' characters in a way that is compatable with Oracle standards. The use of that character should not be an issue in SQL Anywhere or CIS/OMNI (verified here in a quick test without Oracle to be fully functional and compatable with 16.0.0#2127) and should arrive on the Oracle end fully delimited.

Also we do not report an error with that exact text but an Oracle error does: "ORA-00903 invalid table name"; so it seems the SQL is not arriving there delimited. From this I can only conclude you are not connected as the Oracle Remote Server Type (required) but the generic ODBC one and/or not connected with a tested and supported ODBC driver (the one included with the SQL Anywhere software is the only one tested) that may be stripping those delimiters off.

Since your use of delimiters seems to side step the (above) error due to undelimited identifiers issue but now that exposes the 'next' layer and the 'next' Oracle error (which I am assuming is ORA-00942). This, along with your prior requirement of using Synonyms, would indicate to me that the current database context is not the one where that database objects actually lives and you will need to find out how to actually map to that (ie. back to your Oracle DBAs for that I beleive).

I don't see any of this to be an issue with any SQL Anywhere component but do let us know what you find out.

With the correct server type, a tested ODBC driver (the SAP supplied on), a verifed Oracle Client (OCI) installation, and a proxy definition more like

CREATE EXISTING TABLE dsown.SAP_TEST AT 'DWar1.<db_context>.SAPUSR3./BIC/OHZOH_BP_AT';

with <db_context> putting you into the correct context to find this table, that should all just work. The last piece still seems to be an unknown to yourself and will need to be fleshed out some more. You may find you might not require an explict db_context but that needs to be resolved by user mapping or via some other Oracle facility.

If the above does not resolve this a more detailed tracing of the activity arriving on the Oracle side should tell you more.

HTH