Create Proxy Table from Oracle/SAP to SQLA

[former_SQLA_member1694868]

We are running SQLA v 12 and we are trying to connect to an SAP/Oracle database to create a proxy table from which we can select.

Replacing the names, this is the syntax we are trying to use:

CREATE SERVER "ML1" CLASS 'ORAODBC' USING 'ML1' READ ONLY;
CREATE EXTERNLOGIN moo to ML1 remote login memo identified by 'password';
create existing table info_details2 at 'ML1..SAPSV3./BIC/INFO_DETAILS';

The error comes back: Server 'ML1': [Sybase][iAnywhere Solutions - Oracle][Oracle]ORA-00903: invalid table name SQLCODE=-660, ODBC 3 State="HY000"

I have found some Oracle documentation that says only alphanumeric characters and _, $, and # are allowed in table names, so I am not sure about the /BIC/ part of the table name. That part seems to be an SAP addition when I google it alone.

The same tablename will connect correctly and show data results if used in MS Access.

Any help or direction for the correct construction of a proxy table creation statement from SAP/Oracle to SQLA would be greatly appreciated.

[former_SQLA_member1694868 · Accepted solution]

I do not have much explanation for why, but after the oracle people gave us aliases for their table names that did not include the slashes it worked correctly.

[MarkCulp]

I would try calling sp_remote_tables() to get the list of tables within the Oracle database and then go from there. E.g. perhaps you just need to specify 'INFO_DETAILS' as the remote table and not '/BIC/INFO_DETAILS'?

Example:

CREATE SERVER "ML1SERVER" CLASS 'ORAODBC' USING 'ML1DSN' READ ONLY;
CREATE EXTERNLOGIN moo to ML1 remote login memo identified by 'password';
select * from sp_remote_tables( 'ML1SERVER' );

[former_SQLA_member1694868]

I received this error message: sp_remote_tables returned a result set with a different schema than expected SQLCODE=-866, ODBC 3 State="HY000"

[MarkCulp]

Do you get the same error if you execute

 call sp_remote_tables('ML1SERVER')?

I expect you will... but thought I should ask.

Try turning on cis_option = 7 (i.e. 'set temporary option cis_option = 7') and then try the select/call statement, then look at the console output to see what it reports. You likely will want to send the console log to a file (using -o option on the SA server command line) so you can browser the verbose output.

[former_SQLA_member1694868]

I do get the same error with the sp_remote_tables call

With that option on this is the output:

I. 05/29 17:33:11. Connect to 'ML1' class 'oraodbc' using 'ML1'
I. 05/29 17:33:11. ODBC error (01000). [Microsoft][ODBC Driver Manager] The driver doesnt support the version of ODBC behavior that the application requested (see SQLSetEnvAttr).
I. 05/29 17:33:11. Connected to 'msorcl32.dll' version '02.575.1117' odbc version '02.50'
I. 05/29 17:33:11. Server name = ''
I. 05/29 17:33:11. DBMS name = 'Oracle'
I. 05/29 17:33:11. DBMS version = '00.00.0000 Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Prod'
I. 05/29 17:33:11. ODBC conformance = level 1
I. 05/29 17:33:11. ODBC SQL conformance = Core grammar
I. 05/29 17:33:11. SQL identifier case = Upper case
I. 05/29 17:33:11. Maximum connections = 0
I. 05/29 17:33:11. Maximum statements = 0
I. 05/29 17:33:11. SQLGetData extensions = any row, 
I. 05/29 17:33:11. Search pattern escape = '\\'
I. 05/29 17:33:11. Concatenation null behavior = SQL_CB_NON_NULL
I. 05/29 17:33:11. Cursor commit behavior = SQL_CC_CLOSE
I. 05/29 17:33:11. Cursor rollback behavior = SQL_CR_CLOSE
I. 05/29 17:33:11. Cursor types = value concurrency, locking concurrency, read only 
I. 05/29 17:33:11. Read only datasource = 'N'
I. 05/29 17:33:11. Correlation names are supported
I. 05/29 17:33:11. Transactions that contain DDL are automatically committed
I. 05/29 17:33:11. Default isolation = read committed 
I. 05/29 17:33:11. Multiple result sets = 'N'
I. 05/29 17:33:11. Multiple transactions = 'Y'
I. 05/29 17:33:11. Isolation options = read committed, 
I. 05/29 17:33:11. Outer join capabilities = right, left 
I. 05/29 17:33:11. Maximum owner name length = 30
I. 05/29 17:33:11. Maximum qualifier name length = 0
I. 05/29 17:33:11. Maximum table name length = 30
I. 05/29 17:33:11. Executing SQLTables(NULL, *, *)
I. 05/29 17:33:40. Disconnect from server 'ML1'

[former_SQLA_member1694868]

Still stuck on this one. Any ideas on the driver doesn't support the version of ODBC behavior or is that even the problem?

[VolkerBarth]

Just a very very very wild guess: May you try with the iAnywhere Oracle ODBC driver (as recommended for MobiLink) - cf. the v12 docs?

[thomas_duemesnil]

Volker do you know how the ODBC Driver is distributed. I have done a v12.0.1 install and I don't get a iAnywhere ODBC driver on my machine. The last Version was 9 that installed the driver automatically as far as I know. I try to move vom v10 to 12 and the server crashes when I select from the Oracle Proxy tables.

[VolkerBarth]

@Thomas: Hm, I don't use Oracle at all, so I've not used that driver myself. Additionally, there is no Oracle client installed on my box.

Nevertheless, my installation does contain the dboraodbc12.dll file, and the ODBC admin lists the according driver "iAnywhere Solutions 12 - Oracle", and I have used the normal setup. So I would think it's there by default.

The same is true for my v10 and v11 setups.

(These are 32-bit setups, in case that might matter...)

[VolkerBarth]

BTW: That might make a good question on its own, methinks:)