1

Topic: How to obtain the data through DBLINK

Oracle Database 11g Release 11.2.0.3.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
Tom Kyte
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?

2

Re: How to obtain the data through DBLINK

Tried to create request, in which join-jatsja dblink-ovskie tables...
The request is fulfilled very long... Did not wait the performance terminations, dropped...
Well here it is clear, the plan forms cannot...  most likely all data from tables dblink... Well and accordingly hangup.

3

Re: How to obtain the data through DBLINK

Vladimir ;
Restriction on Using User-Defined Types with a Remote Database

4

Re: How to obtain the data through DBLINK

And it is possible to explain?

5

Re: How to obtain the data through DBLINK

  wrote:

it is passed...
And it is possible to explain?

There only 3 paragraphs which is not clear?

6

Re: How to obtain the data through DBLINK

It is necessary to receive a data set of the cursor or one value from it? If the first - that through XML. The second can be turned through dbms_sql@remote though the bicycle quits the notable smile

7

Re: How to obtain the data through DBLINK

AlexFF __ | wrote:

it is passed...
There only 3 paragraphs which is not clear?

wrote:

You can use the CREATE TYPE statement with the optional keyword OID to create a user-specified object identifier (OID) that allows an object type to be used in multiple databases. See the discussion on assigning an OID to an object type in the Oracle Database Data Cartridge Developer's Guide.

the ideology of understanding as it to implement Is desirable...

8

Re: How to obtain the data through DBLINK

MazoHist wrote:

It is necessary to receive a cursor data set or one value from it? If the first - that through XML . The second can be turned through dbms_sql@remote though the bicycle quits notable smile

I already thought of it.
It ... Then ...

9

Re: How to obtain the data through DBLINK

  wrote:

it is passed...
it is passed...
The ideology of understanding as it to implement is desirable...

CREATE OR REPLACE TYPE... OID ' 90A1350CAC40434EE044001CC4B76823 ' IS TABLE of...

10

Re: How to obtain the data through DBLINK

AlexFF __ | wrote:

it is passed...
CREATE OR REPLACE TYPE... OID ' 90A1350CAC40434EE044001CC4B76823 ' IS TABLE of...

I generally asked that about ideology... The result the cursor is necessary To me.
One user caused with one parameter, another with another...
As it will be coordinated with:

wrote:

CREATE OR REPLACE TYPE... OID ' 90A1350CAC40434EE044001CC4B76823 ' IS TABLE of...

?
It forms 1 time, I correctly understand?
How in it the data will register by different calls? Overlapping?

11

Re: How to obtain the data through DBLINK

  wrote:

it is passed...
I generally asked that about ideology... The result the cursor is necessary To me.
One user caused with one parameter, another with another...
As it will be coordinated with:
it is passed...
?
It forms 1 time, I correctly understand?
How in it the data will register by different calls? Overlapping?

Really, that I climb...
How many time already said to itself that clever understand, and remaining = (