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 run 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.

SEQUENCE in PostgreSQL

1)Create Sequence:

CREATE [ TEMPORARY | TEMP ] SEQUENCE [ IF NOT EXISTS ] name 
[ AS data_type ] 
[ INCREMENT [ BY ] increment ] [ MINVALUE minvalue | NO MINVALUE ] 
[ MAXVALUE maxvalue | NO MAXVALUE ] 
[ START [ WITH ] start ] [ CACHE cache ] [ [ NO ] CYCLE ] 
[ OWNED BY { table_name.column_name | NONE } ]


CREATE SEQUENCE serial START 101;

2) Select the next number from this sequence:

SELECT nextval('serial');

 nextval
---------
     101

SELECT nextval('serial');

 nextval
---------
     102

3)Sequence Manipulation Functions:

-- Return value most recently obtained with nextval 
select currval('serial') ;
-----
102
(1 row)

--Return value most recently obtained with nextval for any sequence
select lastval();
lastval
---------
102
(1 row)

-- Advance sequence and return new value
select nextval('serial') ;
-----
103
(1 row)

-- Set sequence's current value
select setval('serial', 1);
setval
--------
1
(1 row)

--setval
SELECT setval('serial', 42); Next nextval will return 43
SELECT setval('serial', 42, true); Same as above
SELECT setval('serial', 42, false); Next nextval will return 42

4) Sequence naming:

nextval('serial')   operates on sequence serial
nextval('SERIAL')   operates on sequence serial
nextval('"Serial"') operates on sequence Serial

5) Alter sequence:

ALTER SEQUENCE [ IF EXISTS ] name 
[ AS data_type ] 
[ INCREMENT [ BY ] increment ] 
[ MINVALUE minvalue | NO MINVALUE ] [ MAXVALUE maxvalue | NO MAXVALUE ] 
[ START [ WITH ] start ] 
[ RESTART [ [ WITH ] restart ] ] 
[ CACHE cache ] [ [ NO ] CYCLE ] 
[ OWNED BY { table_name.column_name | NONE } ] 

ALTER SEQUENCE [ IF EXISTS ] name OWNER TO { new_owner | CURRENT_USER 
| SESSION_USER } 
ALTER SEQUENCE [ IF EXISTS ] name RENAME TO new_name 
ALTER SEQUENCE [ IF EXISTS ] name SET SCHEMA new_schema

6) Grant Privileges On Sequence:

GRANT { { USAGE | SELECT | UPDATE }
    [, ...] | ALL [ PRIVILEGES ] }
    ON { SEQUENCE sequence_name [, ...] | ALL SEQUENCES IN SCHEMA schema_name [, ...] } 
TO role_specification [, ...] [ WITH GRANT OPTION ]

where role_specification can be: 
[ GROUP ] role_name | PUBLIC | CURRENT_USER | SESSION_USER

7)Drop sequence:

DROP SEQUENCE [ IF EXISTS ] name [, ...] [ CASCADE | RESTRICT ]

DROP SEQUENCE serial;

8) Views about sequences:

PG_SEQUENCES:

NameTypeReferencesDescription
schemanamenamepg_namespace.nspnameName of schema containing sequence
sequencenamenamepg_class.relnameName of sequence
sequenceownernamepg_authid.rolnameName of sequence’s owner
data_typeregtypepg_type.oidData type of the sequence
start_valuebigintStart value of the sequence
min_valuebigintMinimum value of the sequence
max_valuebigintMaximum value of the sequence
increment_bybigintIncrement value of the sequence
cyclebooleanWhether the sequence cycles
cache_sizebigintCache size of the sequence
last_valuebigintThe last sequence value written to disk. If caching is used, this value can be greater than the last value handed out from the sequence. Null if the sequence has not been read from yet. Also, if the current user does not have USAGE or SELECT privilege on the sequence, the value is null.

PG_SEQUENCE:

pg_sequence Columns

Name Type References Description
seqrelid oid pg_class.oid The OID of the pg_class entry for this sequence
seqtypid oid pg_type.oid Data type of the sequence
seqstart int8 Start value of the sequence
seqincrement int8 Increment value of the sequence
seqmax int8 Maximum value of the sequence
seqmin int8 Minimum value of the sequence
seqcache int8 Cache size of the sequence
seqcycle bool Whether the sequence cycles