This discussion is archived
1 2 Previous Next 22 Replies Latest reply: Sep 20, 2007 1:02 PM by BillyVerreynne RSS

Calling a remote pipelined table function

BillyVerreynne Oracle ACE
Currently Being Moderated
Oracle 10.x

Have not seen any explicit answers to how this can be done from googling, and searching here and on asktom.

The basic SQL construct is as follows:
SELECT * FROM TABLE( function )

Obviously a remote reference requires a db link added, thus:
SELECT * FROM TABLE( function@dblink )

And this is where the error occurs. The TABLE() function is executed locally and it cannot deal with remote objects. If that can be executed remotely then problem solved. right?

This seems to be confirmed as one can create a view on the remote database where this view runs the SELECT. Then one can select from that view remotely. The local SQL is now a select on the above select - and that above select runs remotely.. including the TABLE() function that is also now executed remotely.

So in theory, if the following was valid/support SQL syntax, it would work:
SELECT * FROM TABLE@dblink( function@dblink )

I.e. forcing the TABLE() function to be executed remotely.

The current work around I have is a bit of a hack. I create a namespace (context) on the remote db and a view that is parameter driven using the name-values in this namespace.

From the local db then, calls are made to set name-values in that remote namespace and then a select is done against that remote view.

It works.. but it is a clumsy and potentially error-prone method.

Any ideas or suggestions?


Thanks.
  • 1. Re: Calling a remote pipelined table function
    NicloeiW Journeyer
    Currently Being Moderated
    well a naive suggestion,
    at remove site create a view v as select * from table(fun);

    then at your site call view via db link ?

    select * from view@remote site,

    now i am also sure you to must have got this idea , ;-)
    Message was edited by:
    Nicloei W
  • 2. Re: Calling a remote pipelined table function
    Satyaki_De Guru
    Currently Being Moderated
    Lucky Fellow...

    At first i thought whether am i reading it properly or not. I mean Billy posted any problem. It's really rare to view such thread. It's really rare.

    ;->)

    Regards.

    Satyaki De.
  • 3. Re: Calling a remote pipelined table function
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    Parameters for the pipelined function.

    When it is defined as a view, it needs to have parameters, e.g.
    create or replace view foo_view as select * from TABLE(foopipe(param1,param2))

    But param1 and param2 needs to be supplied by the caller. If you hardcode that into a view, it makes the view useful for a single query only.

    So thus the workaround. The view is defined as follows:
    create or replace view foo_view as
    select * from TABLE(foopipe( SYS_CONTEXT('foospace','param1'), SYS_CONTEXT('foospace','param2') ))


    The caller now needs to set the parameter values in the namespace and then run the view.. which is a clunky method.

    PS. Such a view is called a parameterised/parameter-driven view.. but requires additional calls to set the parameters before using the view and runs the risk of getting incorrect results as the caller can set the wrong parameter names and then accidentally use the old/previous parameter values.I personally do not like this approach at all.
  • 4. Re: Calling a remote pipelined table function
    NicloeiW Journeyer
    Currently Being Moderated
    well i knew this sys_contx thing only ;-(;

    you can put checks in parameters to be passed in the function itself,
  • 5. Re: Calling a remote pipelined table function
    436063 Newbie
    Currently Being Moderated
    Is the context defined in one database visible to the remote database?

    The view in question is defined in the remote database with the context defined, but is this context visible to the calling Database?
  • 6. Re: Calling a remote pipelined table function
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    The context needs to be local to the parameterised view. It does not need to be visible to the remote (calling) database - that database does a remote call to the context trusted procedure to set name-values in it. For example:

    SQL> -- set remote (context) paramters
    SQL> exec SetNameValue@dblink( 'param1', 'value1' )

    .. and then select from the remote view where it will use the name-value in the context
    SQL> select * from foo_view@dblink;
    The question I raise is however not about parameterising views - this is merely a work around for the problem raised.

    The problem is how do you get the TABLE() function to be executed remotely against a pipelined function?

    Simplistically put, how do you get the part in bold to be shipped by the local database to the remote database in order to be executed by the remote database?

    select * from (
    select * from TABLE(foo_pipeline('abc','123')
    )@remotedb
  • 7. Re: Calling a remote pipelined table function
    Alessandro Rossi Journeyer
    Currently Being Moderated
    There is a possible workaround to your problem but it costs something and I don't know if you have restrictions that don't make this possible as well.

    The solutions is based on a package like this to create on the remote database.
    CREATE OR REPLACE 
    PACKAGE sample_package as
         type parameter_table is
              table of number index by varchar2(30);
         my_parameters parameter_table;
         procedure set_parameter(
              name in varchar2,
              value in varchar2
         );
         procedure reset_parameters;
    end;
    /
    CREATE OR REPLACE
    PACKAGE BODY sample_package as
         procedure set_parameter(
              name in varchar2,
              value in varchar2
         )
         as
         begin
              my_parameters(name) := value;
         end;
         procedure reset_parameters
         as
         begin
              my_parameters.delete;
         end;
    end;
    /
    This package is used for the purpose to hold the parameters to use in your function. But this implies one thing.

    You must write a function like the original with no parameters that fetches its arguments from the table in the package.

    If your original function would be like this.
    create or replace 
    function get_numbers_pipe (
         n in integer
    ) return int_tab
    pipelined
    as
    begin
         for i in 1 .. n loop
              pipe row (i);
         end loop;
    end;
    /

    select *
    from table (get_numbers_pipe(3))
    Query finished, retrieving results...
                 COLUMN_VALUE             
    --------------------------------------
                                         1
                                         2
                                         3

    3 row(s) retrieved
    You could do this.
    create or replace function get_numbers_pipe_2
    return int_tab
    pipelined
    is
    begin
         
         for i in 1 .. sample_package.my_parameters('n') loop
              pipe row (i);
         end loop;
    end;
    /
    and then
    Processing ...
    create or replace view get_numbers_pipe_view as (
         select *
         from table (get_numbers_pipe_2)
    )
    Processing ...
    begin
         sample_package.reset_parameters;
         sample_package.set_parameter('n',5);
    end;
    Processing ...
    select *
    from get_numbers_pipe_view

    Query finished, retrieving results...
                 COLUMN_VALUE             
    --------------------------------------
                                         1
                                         2
                                         3
                                         4
                                         5

    5 row(s) retrieved

    Processing ...
    Executing...
    sample_package.reset_parameters;
    exec sample_package.reset_parameters;
    PL/SQL completed.
    Once you have this objects in you remote database you can create a local function that does that job.

    Like here
    Processing ...
    create database link otherdb using 'otherdb'
    Processing ...
    create or replace type int_tab as
         table of int
    TYPE INT_TAB compiled successfully
    Processing ...
    create or replace FUNCTION GET_NUMBERS (
         n in integer
    ) return int_tab
    pipelined
    as
         cursor c is
              select *
              from get_numbers_pipe_view@otherdb;
         outval int;
    begin
         sample_package.reset_parameters@otherdb;
         sample_package.set_parameter@otherdb('n',n);
         for rec in c loop
              pipe row (rec.column_value);
         end loop;
         sample_package.reset_parameters@otherdb;
    end;
    FUNCTION GET_NUMBERS compiled successfully
    Processing ...
    select *
    from table (get_numbers(5))

    Query finished, retrieving results...
                 COLUMN_VALUE             
    --------------------------------------
                                         1
                                         2
                                         3
                                         4
                                         5

    5 row(s) retrieved
    I know you have some overhead but probably there are no many more things to do.

    Bye Alessandro
  • 8. Re: Calling a remote pipelined table function
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    Thanks. Have seen similar solutions on asktom - but this make use of PL/SQL structures and not native SQL structures, as PL/SQL structures can be remotely "mapped".

    I'm hoping for a non-PL/SQL solution - I simply want that entire in-line SQL to be processed on the remote database.

    But I'm not sure that it can be done.. the DRIVING_SITE hint is also very limited in scope.
  • 9. Re: Calling a remote pipelined table function
    Alessandro Rossi Journeyer
    Currently Being Moderated
    The thing is that only native datatypes except longs can be exchanged over database links and collections datatype are not native datatypes.

    The proposed solution works only because there was an implicit conversion with the use of a view that permits to exchange data of native number datatype.

    The entire query can't be executed on the remote db if some of its parameters come from your local db.

    So probably there is no way to do that on oracle 10g.

    Bye Alessandro
  • 10. Re: Calling a remote pipelined table function
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    > The thing is that only native datatypes except longs can be exchanged over
    database links and collections datatype are not native datatypes.

    Yeah, but the issue is not about mapping object types across databases as the local SELECT does not "use" the remote object type as a PL/SQL variable (for example) would.

    After all, you can SELECT against a remote table where that table's structure is unknown to the local database. SELECT against a remote pipeline is no different. The structure (i.e. object class/sql user data type) does not need to be known to the local database.

    In both cases, a cursor is created on the remote database. That contains a describe interface that tells the local database just what the returned projection is of that cursor.

    And this is proved by being able to run a SELECT against a remote view that returns a SQL collection of SQL user defined types.

    > The entire query can't be executed on the remote db if some of its
    parameters come from your local db.

    In fact it can.. for the same reason the SQL engine can be called to execute a SQL using local PL/SQL parameter values.

    That is the purpose of bind variables. The SQL is send to the remote db using bind variables to substitute local parameter values into that SQL.

    However, how well this is implemented in Oracle I'm not sure..

    > So probably there is no way to do that on oracle 10g.

    The problem is having to use the TABLE() function in order to access the pipeline.. and that function expects and support only local pipelines.

    So yeah, you are likely right that this is not possible.. unless there is some not very well known (or even undocumented) method to specify scope between a local database and a remote database in Oracle SQL.
  • 11. Re: Calling a remote pipelined table function
    60660 Journeyer
    Currently Being Moderated
    One alternative that came to my mind: Write a local wrapper function for your remote function - never tried that with pipelined functions though.

    Thinking about it, I'm not sure that this is even possible.

    C.

    Message was edited by:
    cd
  • 12. Re: Calling a remote pipelined table function
    436423 Explorer
    Currently Being Moderated
    Hi Billy,

    I think cd is on the right track. What if you create a local pipelined function that first set the sys_contexts remotely from the local parameters, then simply selected from the view you have defined, piping each row back?

    Something like (excuse the hacky code):
    create or replace function clunky return (parm1 in number) return sometype pipelined
    as
    begin

      set_remote_context@thelink(parm1);
     
      for each_row in (select cols from remote_view) loop
        /* possibly some conversion to local type here */
        pipe row();
      end loop;
     
      return;

    end;
    HTH

    Chris
  • 13. Re: Calling a remote pipelined table function
    NicloeiW Journeyer
    Currently Being Moderated
    Hi,

    yes this is good work around,

    regards
    nic
  • 14. Re: Calling a remote pipelined table function
    Satyaki_De Guru
    Currently Being Moderated
    While testing Billy's problem, i found one problem. Can anyone pls clarify it --
    satyaki>
    satyaki>sho user;
    USER is "ODSUSER1"
    satyaki>
    satyaki>
    satyaki>create or replace type pl_hok as object
      2    (
      3      GrA         varchar2(5),
      4      GrB         varchar2(5)
      5    );
      6  /
    
    Type created.
    
    satyaki>
    satyaki>
    satyaki>create or replace type pl_hok_rec as table of pl_hok;
      2  /
    
    Type created.
    
    satyaki>
    satyaki>
    satyaki>create or replace function pipe_sel(
      2                                       st_dt in date,
      3                                       en_dt in date
      4                                     )
      5  return pl_hok_rec pipelined
      6  is
      7     cursor c1
      8     is
      9       select distinct empno
     10       from emp
     11       where hiredate between st_dt and en_dt;
     12       
     13     r1 c1%rowtype;
     14       
     15     cursor c2
     16     is
     17       select distinct mgr
     18       from emp
     19       where hiredate between st_dt and en_dt;
     20       
     21     r2 c2%rowtype;
     22     pragma autonomous_transaction;
     23  begin
     24        open c1;
     25        open c2;
     26        
     27        loop
     28          fetch c1 into r1;
     29          fetch c2 into r2;
     30          
     31            exit when c1%notfound and c2%notfound;
     32            pipe row(pl_hok(to_char(r1.empno),to_char(r2.mgr)));
     33        end loop;
     34        
     35        close c2;
     36        close c1;
     37    
     38      return;
     39  exception
     40     when others then
     41       dbms_output.put_line(sqlerrm);
     42  end;
     43  /
    
    Function created.
    
    satyaki>
    
    
    satyaki>select GrA,GrB from table(pipe_sel(to_date('01-jan-1980','dd-mon-yyyy'),to_date('20-aug-2007','dd-mon-yyyy')));
    
    GRA   GRB
    ----- -----
    7006  7369
    7369  7566
    7499  7698
    7521  7782
    7566  7788
    7654  7839
    7698  7902
    7782
    7788
    7839
    7844
    
    GRA   GRB
    ----- -----
    7876
    7900
    7902
    7934
    9898
    
    16 rows selected.
    
    satyaki>
    satyaki>
    
    satyaki>
    satyaki>sho user;
    USER is "TRG1"
    satyaki>
    satyaki>
    
    satyaki>create or replace synonym sy_pipe_sel for odsuser1.pipe_sel@AIX9I.SKYTECHSOLUTIONS.INTRA;
    
    Synonym created.
    
    satyaki>
    satyaki>
    satyaki>select distinct empno,mgr
      2     from emp;
    
    no rows selected
    
    
    satyaki>
    satyaki>select GrA,GrB from table(sy_pipe_sel(to_date('01-jan-1980','dd-mon-yyyy'),to_date('20-aug-2007','dd-mon-yyyy')));
    select GrA,GrB from table(sy_pipe_sel(to_date('01-jan-1980','dd-mon-yyyy'),to_date('20-aug-2007','dd-mon-yyyy')))
                              *
    ERROR at line 1:
    ORA-04052: error occurred when looking up remote object
    ODSUSER1.PIPE_SEL@AIX9I.SKYTECHSOLUTIONS.INTRA
    ORA-00604: error occurred at recursive SQL level 1
    ORA-02019: connection description for remote database not found
    
    
    satyaki>
    satyaki>conn odsuser1/user1@AIX9I.SKYTECHSOLUTIONS.INTRA;
    Connected.
    satyaki>
    satyaki>
    
    satyaki>
    satyaki>select GrA,GrB from table(pipe_sel(to_date('01-jan-1980','dd-mon-yyyy'),to_date('20-aug-2007','dd-mon-yyyy')));
    
    GRA   GRB
    ----- -----
    7006  7369
    7369  7566
    7499  7698
    7521  7782
    7566  7788
    7654  7839
    7698  7902
    7782
    7788
    7839
    7844
    
    GRA   GRB
    ----- -----
    7876
    7900
    7902
    7934
    9898
    
    16 rows selected.
    
    satyaki>
    Why is it throwing this error while it is connecting with the same information? Please explain my doubt.

    Regards.

    Satyaki De.
1 2 Previous Next