Category: Premium Content

ORA-00932: inconsistent datatypes from Heterogeneous Service Database Link

There is an Oracle database link between Oracle and PostgreSQL database through Heterogeneous Service ( HS ).

The sql query is working fine in PostgreSQL database:

testdb=> \d test_tbl
Table "testuser.test_tbl"
Column  | Type                    | Collation | Nullable | Default
--------+-------------------------+-----------+----------+---------
id      | double precision        |           |          |
s10     | character varying(10)   |           |          |
s20     | character varying(20)   |           |          |
s30     | character varying(2048) |           |          |


testdb=> select * from test_tbl;
id  | s10    | s20    | s30
----+--------+--------+---------------
1   | tets1 | test2 | Test Record 1

(1 row)

testdb=>select "id", substr("s10",1,5),
                substr("s20",1,5),substr("s30",1,10) 
         from test_tbl;

id | substr | substr | substr
---+--------+--------+------------
1  | test1  | test2  | Test Recor

(1 row)

While running the same query in Oracle database through Heterogeneous Service ( HS ) database link, get “ORA-00932: inconsistent datatypes” error. Sometimes you might get “ORA-02070: database does not support TO_CHAR in this context”.

SQL> select "id", substr("s10",1,5),substr("s20",1,5),substr("s30",1,10) 
      from "test_tbl"@PGLINK;
select "id", substr("s10",1,5),substr("s20",1,5), substr("s30",1,10) 
from "test_tbl"@PGLINK
*
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected CHAR got LONG

Subscribe to get access

Read more of this content when you subscribe today.

Subscribe

ORA-16665: timeout waiting for the result from a database

$ oerr ora 16665
16665, 0000, "timeout waiting for the result from a database"
// *Cause: The Oracle Data Guard broker was forced to time out a network
// connection to a remote database because:
// - The network call to the remote database did not complete in
// a timely manner.
// - The remote database was unable to execute the command due to
// an instance failure.
// *Action: Check Data Guard broker log files for the details of the failure.
// If the network call did not complete in a timely manner, increase
// the CommunicationTimeout configuration property value and reissue
// the command.

Subscribe to get access

Read more of this content when you subscribe today.

Subscribe

AWS DMS Validation Pending for Some Tables

While running an AWS DMS task for migrating on-premise Oracle to AWS PostgreSQL RDS, some tables records are fully loaded into target database. From Table Statistics, it shows some tables validations are pending forever without progressing at all.

Check “Migration task logs”, it just says :

[TASK_MANAGER]I:All tables are loaded.waiting for validation to finish... 
                                     (replicationtask.c:1234)

RESOLUTION

Subscribe to get access

Read more of this content when you subscribe today.

Subscribe

ORA-29283: invalid file operation ORA-06512: at “SYS.UTL_FILE”, line 536 ORA-29283: invalid file operation

Connected to Oracle database through service name, then get  errors by running the below sample code.  There is no problem to run the sample code while connecting to database through IPC.

$sqlplus testuser/****@DBTEST

SQL>DECLARE
    l_output utl_file.file_type;
BEGIN
    l_output := utl_file.fopen( 'MY_DIR', 'test.txt', 'w' );
    utl_file.fclose( l_output ); 
END;
/

*
ERROR at line 1:
ORA-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE", line 536
ORA-29283: invalid file operation
ORA-06512: at line 4
$sqlplus  / as sysdba
SQL> DECLARE
     l_output utl_file.file_type;
BEGIN
     l_output := utl_file.fopen( 'MY_DIR', 'test.txt', 'w' );
     utl_file.fclose( l_output );
END;

 /

PL/SQL procedure successfully completed.

CAUSES

Subscribe to get access

Read more of this content when you subscribe today.

Subscribe

Heterogeneous Database Connections – Oracle to PostgreSQL

There are two ways to use Heterogeneous Services,  one is via ODBC which does not require a licence,  or you can use the licensed Gateways.

This article describes how to set up the Oracle configurations using ODBC , and then you can create database link between Oracle database and AWS PostgreSQL RDS.

Subscribe to get access

Read more of this content when you subscribe today.

Subscribe