Home » SQL & PL/SQL » SQL & PL/SQL » Remote Pipeline
Remote Pipeline [message #276636] Thu, 25 October 2007 12:55 Go to next message
PORSCHITO
Messages: 5
Registered: October 2007
Junior Member
hi all,

i have a pacakge.procedure that returns a pipeline cursor in one database, im trying to call it from different database using a link. is that even possible?

i tried cast and multiset to match the types on both sides and still no luck.

the error i get is ora-30626: function/procedure parameters of remote object types are not supported.

Re: Remote Pipeline [message #276638 is a reply to message #276636] Thu, 25 October 2007 13:02 Go to previous messageGo to next message
PORSCHITO
Messages: 5
Registered: October 2007
Junior Member
-- CAST AND MULTISET
OPEN cur_out FOR
SELECT cast (MULTISET (SELECT COLUMN_VALUE FROM table (TRR.PKG.proc@data_LINK(par1,par2)))AS retset) FROM DUAL;



-- CAST
OPEN cur_out FOR
SELECT COLUMN_VALUE FROM cast(table (TRR.PKG.proc@data_LINK(par1,par2))AS retset);

[Updated on: Thu, 25 October 2007 13:03]

Report message to a moderator

Re: Remote Pipeline [message #276663 is a reply to message #276636] Thu, 25 October 2007 22:05 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
PORSCHITO's way with refcursors will work

But it does not use pipelined table functions and it is not very portable. You can't move the function around between databases and have it point to different places without having to change other objects (e.g. synonyms/links etc.).

It may be that the problem you are experiencing is because of a mis-interpretation of object types. Pipelined fuctions as we know, return an object type, just one set of rows at a time (maybe just one row at a time?).

The called function and calling piece of code must both recognize the object type being used as the same object type. Normally, the called function and calling piece of code are on the same database instance. So, there is no problem with identifying the object type being used.

But, there are actually at least two ways of identifying an object type:

1) by its object name (obvious)
2) by its OID (an internal object id)

if you examine the create type statement you will see there is an optional phrase that allows you to specify a GUID to be used as a formal object id. For those who don't know, GUID=(Globally Unique Object ID?) (I think?). As the name suggests, Globally Unique means that no two generation events are supposed to create the same value. Thus this is a way to create an Identifier that is unique all over the world. Don't ask me about the theory, I only know what I read.

There are at least two reasons why you would want to specify an OID when creating an object type:

1) certain oracle technologies use it (SYS.ANYDATA for example)
2) to provide a way for two different databases to know for sure that a specific object type defined seperately on each of the two instances with the same name is in fact the same object, it has the same name, defintion, and identical OID.

Everytime you create an object type, oracle supplies an OID value if you do not (and most people do not). Thus the object type on instanceA and object type on instanceB even though they have the same name, have different OID values and so are not considered the same object type across the two databases.

If this is your problem, then you can solve it by recreating the object types on both instances using the same explicitly supplied OID value. I have done this in support of a fancy PL/SQL ETL job; it is possible to move data between two databases using object types, but you must supply the same OID (which if you are using the same create statement on both instances, and have included the OID in the statement, then of course you can't miss).

Read up on GUID and OID and CREATE TYPE to see an example of how to generate an GUID value to use as and OID in the CREATE TYPE statement.

Good luck, Kevin
Re: Remote Pipeline [message #276909 is a reply to message #276663] Fri, 26 October 2007 14:35 Go to previous messageGo to next message
PORSCHITO
Messages: 5
Registered: October 2007
Junior Member
do you think if i return the results as xml, i would still have an issue with unrecongnized types ?

[Updated on: Fri, 26 October 2007 14:36]

Report message to a moderator

Re: Remote Pipeline [message #276911 is a reply to message #276636] Fri, 26 October 2007 14:37 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
no, but why, xml is ugly.

Just create the types with an OID. Stop trying to avoid learning something new. This is easy.

Kevin
Re: Remote Pipeline [message #277539 is a reply to message #276911] Tue, 30 October 2007 08:33 Go to previous message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Kevin Meade wrote on Fri, 26 October 2007 21:37

no, but why, xml is ugly.
Well spoken.

MHE
Previous Topic: ORA-00942 Error
Next Topic: Try to create a Dynamic column using EXECUTE IMMEDIATE
Goto Forum:
  


Current Time: Sat Dec 10 11:06:59 CST 2016

Total time taken to generate the page: 0.25525 seconds