Topic: How to obtain the data through DBLINK
Oracle Database 11g Release 22.214.171.124.0 - 64bit Production
How to obtain the data through DBLINK?
On one server there is a procedure in a packet which returns the cursor:
... CREATE OR REPLACE package body package1 is procedure test_proc (p_inn varchar2; p_cursor out sys_refcursor) is ...
If it to cause through dblink on other server the error is produced:
DECLARE p_inn varchar2 (50): = ' 771603983483 '; BEGIN package1.test_proc@dblink (p_inn:p_cursor); END; ORA-24338: the pointer of the operator is not fulfilled
Looked similar in search
refcursors may not be returned over a dblink
Decided to try to create a table valued function and to pull out it too through dblink
(In hope that into a table valued function I will push results of the cursor)
CREATE OR REPLACE package EGRIP_IMPORT.p_test is type test_rowGet is record (fnum numeric, fvar varchar2 (100 char), fdate date); type test_table is table of test_rowGet; function get_test_table RETURN test_table pipelined; end; CREATE OR REPLACE PACKAGE BODY EGRIP_IMPORT.p_test AS function get_test_table RETURN test_table pipelined is begin for curr in ( ---- fnum numeric, fvar varchar2 (100 char), fdate date select 12 as fnum, ' qwerqwerqwer ' as fvar, to_date (' 1/12/2017 ', ' dd.mm.yyyy ') as fdate from dual union all select 4 as fnum, ' zxcvzxvzxv ' as fvar, to_date (' 1/5/2014 ', ' dd.mm.yyyy ') as fdate from dual ) loop pipe row (curr); end loop; end; end;
If it to cause through dblink
SELECT * FROM TABLE (P_TEST.get_test_table@dblink); ORA-06553: PLS-752: Table valued function GET_TEST_TABLE is in a contradictory state.
How to pull out the data?