OCIStmtExecute failed to execute remote query‎ strikes again

laurenz / oracle_fdw

PostgreSQL Foreign Data Wrapper for Oracle

http://laurenz.github.io/oracle_fdw/

Other

496 stars 156 forks source link

OCIStmtExecute failed to execute remote query‎ strikes again #590

Closed Reznik070 closed 1 year ago

Reznik070 commented 1 year ago

Hi,

And thank you for your contribution. This OCIStmtExecute failed to execute remote query‎ has been giving me a headache recently. Oracle_fdw worked so well last year until I checked recently. Did you change anything recently? Is this some serialization issue? First I got this error. Then it worked but I noticed no ID (Primary key) serial was saved. Then I tried again with a new create and all I get is this error.

Thanks.

laurenz commented 1 year ago

If you want help, you have to give me details:

what version of PostgreSQL, Oracle client, Oracle server, oracle_fdw?
a complete, self contained test case for me to reproduce the problem If there are older issues that are relevant for this, please link to them.

Reznik070 commented 1 year ago

Sorry for the late response. I was trying to find out additional information as per test case.

PostgresSQL version 11 / oracle_fdw 1.1 / Oracle 11g / Oracle client 12c

laurenz commented 1 year ago

Thanks. 1.1 is the version of the extension. You can run SELECT oracle_diag(); to get the oracle_fdw version.

Now please copy and paste the error message and show me the exact sequence of statements that lead to this.

Reznik070 commented 1 year ago

Apologies. My mistake. Below:

oracle_fdw 2.1.0, PostgreSQL 11.5 (Ubuntu 11.5-1.pgdg18.04+1), Oracle client 12.2.0.1.0

Error:

Npgsql.PostgresException: 23502: error executing query: OCIStmtExecute failed to execute remote query [Details] Severity: ERROR InvariantSeverity: ERROR SqlState: 23502 MessageText: error executing query: OCIStmtExecute failed to execute remote query Detail: Detail redacted as it may contain sensitive data. Specify 'Include Error Detail' in the connection string to include this information. File: oracle_fdw.c Line: 6308 Routine: oracleError_d

laurenz commented 1 year ago

Detail redacted as it may contain sensitive data. Specify 'Include Error Detail' in the connection string to include this information.

Please make it display the interesting information.

You are using a very old version of oracle_fdw. Please try with Git HEAD.

If the error persists, and the secret detail information is not enough for me to understand what's going on, I will ask for the following:

the exact CREATE TABLE statement in Oracle
the exact CREATE FOREIGN TABLE statement in PostgreSQL
the exact query you are running

Reznik070 commented 1 year ago

Big thanks!

laurenz commented 1 year ago

You are welcome! Could you solve the problem? How?

Reznik070 commented 1 year ago

Hi, not yet. Oracle admin is on holiday. I'll post when we try your suggestions. Sorry for the late response

laurenz commented 1 year ago

I'll close this for inactivity. Feel free to reopen it if you have new information.