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!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Problem with unicode DBlink to postgres

ShahmirJun 23 2022

Hello everyone,
When I am connecting to POSTGRES db by using DBLINK my select is not correctly show me output:
SQL> select * from "test"@PG;
c�mil�
I think its utf problem. but when I am edit initPG.ora file to this
HS_LANGUAGE = AMERICAN_AMERICA.AL32UTF8
HS_NLS_NCHAR = UCS2

parameter LINK is not working . error is :
SQL> select * from "test"@PG;
select * from "test"@PG
*
ERROR at line 1:
ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
ORA-02063: preceding line from PG

error log are:
Exiting hgolofns at 2022/06/23-11:24:38
Failed to load ODBC library symbol: /usr/lib64/psqlodbc.so(SQLGetDescRecW)
Exiting hgolofn, rc=28500 at 2022/06/23-11:24:38
Exiting hgoinit, rc=28500 with error ptr FILE:hgoinit.c LINE:441 ID:Loading ODBC aray of function ptrs
hostmstr: 0: HOA After hoainit
RPC Calling nscontrol(0), rc=0
HOA 06/23 11:24:38.758055000: (hotker_ExecuteRpcs) ncrorpi: status = 0
hostmstr: 0: RPC Before Exit Agent
hostmstr: 0: HOA Before hoaexit
Entered hgoexit
HS Gateway: NULL connection context at exit
Exiting hgoexit, rc=0 with error ptr FILE:hgoexit.c LINE:114 ID:Connection context
hostmstr: 0: HOA After hoaexit
hostmstr: 0: RPC After Exit Agent
====================================
odbcinst -j -q
unixODBC 2.3.1
DRIVERS............: /etc/odbcinst.ini
SYSTEM DATA SOURCES: /etc/odbc.ini
FILE DATA SOURCES..: /etc/ODBCDataSources
USER DATA SOURCES..: /home/oracle/.odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8

Comments

thatJeffSmith-Oracle
I understand what you want, however I don't think we have an answer that will give you what you want.

PL/SQL blocks aren't SQL - you're not going to get a resultset back like what if you had run a query in SQL*Plus or SQL Developer.

HOWEVER, if you have your program return the resultset as a REFCURSOR - SQL Developer will 'catch' that output and place it in a 'grid.'

You're other option is to output the format to file or to the output buffer however which way you want - but that's code you're going to have to write yourself.

Here's an example of what REFCURSOR output looks like in SQL Developer

http://www.thatjeffsmith.com/archive/2011/12/sql-developer-tip-viewing-refcursor-output/
unknown-7404
Welcome to the forum!

Whenever you post provide your full sql developer version.
>
But output via PL/SQL is usually done via dbms_output.
>
No - it isn't.

The DBMS_OUTPUT package should NOT be used at all in production code. You should only enable and use it very sparingly and only then for debugging purposes when developing and testing your code.

A line like this: 
    dbms_output.put_line(i || ' ' || i*i);
does nothing more than put text into a buffer in Oracle and uses expensive PGA memory to store it. There is a small default limit on the size of that buffer and you run the risk of an exception if you fill it up.

The client software (sql*plus, sql developer) has to actually access that buffer to get the text or it will NEVER get displayed. And it normally only gets displayed AFTER the completion of the code, not during the execution.

If you are using DBMS_OUTPUT as a general-purpose 'display' mechanism then you are misusing it and should find another alternative.
>
How to get the resulting table not shown in the "dbms output" window, but in the "query result" window?
>
There is no 'table' and there is no 'query result'. If you want a query result put the results into a table or use a REF CURSOR to return them.
thatJeffSmith-Oracle
No - it isn't.
Good catch. Although unfortunately too many folks do rely on this. And of course it's never in production. Sigh.
1 - 3

Post Details

Added on Jun 23 2022
#heterogeneous-connectivity
1 comment
621 views