1 2 Previous Next 22 Replies Latest reply: Sep 20, 2007 3:02 PM by Billy~Verreynne RSS

    Calling a remote pipelined table function

    Billy~Verreynne
      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
          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
            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
              Billy~Verreynne
              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
                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
                  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
                    Billy~Verreynne
                    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
                      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
                        Billy~Verreynne
                        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
                          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
                            Billy~Verreynne
                            > 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
                              cd_2
                              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
                                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
                                  Hi,

                                  yes this is good work around,

                                  regards
                                  nic
                                  • 14. Re: Calling a remote pipelined table function
                                    Satyaki_De
                                    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