Skip to Main Content
Forums

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Oracle database link to MySQL select only one row

moreNov 5 2014 — edited Nov 10 2014

Hello,

I have created a connection from the Oracle batabase 11.2 to a MySQL database via database link. The following statement shows, that 35 rows are in the mySQL table:

SQL>  select count(*) from "main_pages"@MOREWEB;

  COUNT(*)

----------

        35

But a normal select statement only return 1 row.

SQL> select "subject" from "main_pages"@MOREWEB;

subject

------------------------------------------------

Übersicht: Referenzen

I am using the mysql-connector-odbc-3.51.30-winx64 driver. A newer version cann't be installed because on Windows Server 2008 R2 I get an error with an missing dll-file. The DataDirect-ODBC-driver is not possible, because we like to use the free MySQL-database an DataDirect only support the enterprise edition.

I also have tried to limit the HS_OPEN_CURSORS or dont limit the HS_FDS_FETCH_ROWS, but there is no difference in the result. I always get only one row.

HS Init.ora

# This is a sample agent init file that contains the HS parameters that are

# needed for the Database Gateway for ODBC

#

# HS init parameters

#

HS_FDS_CONNECT_INFO = moreweb

HS_FDS_TRACE_LEVEL = ON

HS_FDS_FETCH_ROWS=1

#

# Environment variables required for the non-Oracle system

#

#set <envvar>=<value>

HS tracefile

Oracle Corporation --- MITTWOCH   NOV 05 2014 13:56:22.066

Heterogeneous Agent Release

11.2.0.1.0

Oracle Corporation --- MITTWOCH   NOV 05 2014 13:56:22.066

    Version 11.2.0.1.0

HOSGIP for "HS_FDS_TRACE_LEVEL" returned "ON"

HOSGIP for "HS_OPEN_CURSORS" returned "50"

HOSGIP for "HS_FDS_FETCH_ROWS" returned "1"

HOSGIP for "HS_LONG_PIECE_TRANSFER_SIZE" returned "65536"

HOSGIP for "HS_NLS_NUMERIC_CHARACTER" returned ".,"

HOSGIP for "HS_KEEP_REMOTE_COLUMN_SIZE" returned "OFF"

HOSGIP for "HS_FDS_DELAYED_OPEN" returned "TRUE"

HOSGIP for "HS_FDS_WORKAROUNDS" returned "0"

HOSGIP for "HS_FDS_MBCS_TO_GRAPHIC" returned "FALSE"

HOSGIP for "HS_FDS_GRAPHIC_TO_MBCS" returned "FALSE"

HOSGIP for "HS_FDS_RECOVERY_ACCOUNT" returned "RECOVER"

HOSGIP for "HS_FDS_TRANSACTION_LOG" returned "HS_TRANSACTION_LOG"

HOSGIP for "HS_FDS_TIMESTAMP_MAPPING" returned "DATE"

HOSGIP for "HS_FDS_DATE_MAPPING" returned "DATE"

HOSGIP for "HS_FDS_CHARACTER_SEMANTICS" returned "FALSE"

HOSGIP for "HS_FDS_MAP_NCHAR" returned "TRUE"

HOSGIP for "HS_FDS_RESULTSET_SUPPORT" returned "FALSE"

HOSGIP for "HS_FDS_RSET_RETURN_ROWCOUNT" returned "FALSE"

HOSGIP for "HS_FDS_PROC_IS_FUNC" returned "FALSE"

HOSGIP for "HS_FDS_REPORT_REAL_AS_DOUBLE" returned "FALSE"

using mpgw as default value for "HS_FDS_DEFAULT_OWNER"

HOSGIP for "HS_SQL_HANDLE_STMT_REUSE" returned "FALSE"

SQL text from hgopars, id=1, len=36 ...

     00: 53454C45 43542043 4F554E54 282A2920  [SELECT COUNT(*) ]

     10: 46524F4D 20606D61 696E5F70 61676573  [FROM `main_pages]

     20: 60204131                             [` A1]

Deferred open until first fetch.

Performing delayed open.

SQL text from hgopars, id=1, len=40 ...

     00: 53454C45 43542041 312E6073 75626A65  [SELECT A1.`subje]

     10: 63746020 46524F4D 20606D61 696E5F70  [ct` FROM `main_p]

     20: 61676573 60204131                    [ages` A1]

Deferred open until first fetch.

Performing delayed open.

Please can help me someone.

Thanks.

Bianca
This post has been answered by mxallen-Oracle on Nov 10 2014
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Dec 8 2014
Added on Nov 5 2014
#heterogeneous-connectivity
6 comments
2,390 views