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.

asked 29 May '12, 13:06

Siger%20Matt's gravatar image

Siger Matt
3.2k496697
accept rate: 13%

edited 15 Mar '13, 19:39

Mark%20Culp's gravatar image

Mark Culp
23.2k9132273


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.

permanent link

answered 22 Jun '12, 15:35

Siger%20Matt's gravatar image

Siger Matt
3.2k496697
accept rate: 13%

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' );
permanent link

answered 29 May '12, 13:44

Mark%20Culp's gravatar image

Mark Culp
23.2k9132273
accept rate: 40%

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

(29 May '12, 17:33) Siger Matt
Replies hidden

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.

(29 May '12, 17:45) Mark Culp

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'
(30 May '12, 09:18) Siger Matt

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

(04 Jun '12, 09:52) Siger Matt
Replies hidden

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

(04 Jun '12, 10:22) Volker Barth

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.

(20 Sep '13, 06:23) Thomas Dueme...

@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...)

(20 Sep '13, 07:24) Volker Barth

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

(20 Sep '13, 07:31) Volker Barth
showing 2 of 8 show all flat view
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×412
×63
×27
×26

question asked: 29 May '12, 13:06

question was seen: 2,723 times

last updated: 20 Sep '13, 07:31