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.
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.
answered 22 Jun '12, 15:35
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'?
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' );
answered 29 May '12, 13:44