[MDEV-15081] Connect Engine : Got error 122 'SQLExecDirect: [Oracle][ODBC][Ora]ORA-01000 from Engine Created: 2018-01-26  Updated: 2018-01-26

Status: Open
Project: MariaDB Server
Component/s: Storage Engine - Connect
Affects Version/s: 10.2.12
Fix Version/s: 10.2

Type: Bug Priority: Major
Reporter: Nicola Assignee: Olivier Bertrand
Resolution: Unresolved Votes: 0
Labels: None
Environment:

Vmware with Oracle Linux 7


 Description   

Hi all,
I've created two table with engine connect from source Oracle database 12c, but with this simple query the connect engine return the following error:
Connect Engine : Got error 122 'SQLExecDirect: [Oracle][ODBC][Ora]ORA-01000 from Engine

I do not see this error in the Oracle Database logs.

this is the query I launch: 

select count(*) from  `AG_UBICAZIONE_SLOT` US
 
inner join `AG_ATTORE_UBICAZIONE` AUTIR on (AUTIR.PROG_ESERCIZIO_ID=US.PROG_ESERCIZIO_ID OR AUTIR.PROG_MAGAZZINO_ID=US.PROG_MAGAZZINO_ID)

Queries on individual tables work without problems.

Thanks.
Regards.

 Comments   
Comment by Olivier Bertrand [ 2018-01-26 ]

I am currently re-installing my computer and Oracle is not yet available. However, to try reproducing this case, please publish your table create statements and possibly the Oracle tables definition and data sample.

Meanwhile, a possible turnover is to define an ODBC table based on a view specified in the SRCDEF option. Something such as:

CREATE TABLE whatever ENGINE=CONNECT TABLE_TYPE=ODBC BLOCK_SIZE=10
 
CONNECTION='your oracle data source'
 
SRCDEF='select count(*) from  `AG_UBICAZIONE_SLOT` US
 
inner join `AG_ATTORE_UBICAZIONE` AUTIR on (AUTIR.PROG_ESERCIZIO_ID=US.PROG_ESERCIZIO_ID OR AUTIR.PROG_MAGAZZINO_ID=US.PROG_MAGAZZINO_ID)';

Normally CONNECT should be able to make the colum definition.

The advantage of doing so is to let Oracle do the join and to retrieve only the query result. Sometimes it can be much faster than when MariaDB does the join itself. See Random Access of ODBC Tables for information on what to do when an ODBC table is randomly used, like when joining tables.

Generated at Thu Feb 08 08:18:32 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.