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.