I need to write a stored procedure that retrieves a set of rows through a remote procedure contained in a remote Oracle instance. I created a DB Link to the remote service, but I am not supposed to refer to any of the tables/views of the remote database directly from my procedure.

For instance, I am not supposed to retrieve the rowset by just using a select query on the remote table through the DB-link syntax; or, use the %ROWTYPE datatype , since that requires me to have the knowledge of the remote table's name.

I tried defining a "Table Type" in the remote instance, with the structure of rowset that I want to retrieve, and declared the OUT variable of the remote proceedure as that type.

Calling this remote procedure through my procedure requires me to declare a variable of the same 'type' so that I can pass this as an argument when I call the remote procedure. I tried to define a similar looking table type in my schema and declared the variable of that type and passed it as an argument to the remote procedure call. I get an error "wrong type of arguments.." when I compile this.

I tried to package the remote procedure in a Oracle Package but even that cannot be referred to from my instance.

Please help me if there is any way I can achieve my objective.

By the way, my version of oracle is 8.1.6

Thanks
Sastry

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width