Thanks Tom. Your approach of using sys_context to pass parameters worked fine. I'm sharing my 'kludgy approach', but in retrospect it may be no more kludgy than yours. Each achieve my objective. Rather than pass parameters in an application context, I elected to push them to the remote db via a global temp table. The working example is shown below. (I repeat a portion of the above post in the interest of completeness of the script.)

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> -- Connect to remote DB to create pipeline function
SQL> --  and supporting objects for test.
SQL> CONNECT /@DB_B
Connected.

SQL> CREATE OR REPLACE TYPE MyObject AS OBJECT(a_day DATE);
  2  /

Type created.

SQL> CREATE OR REPLACE TYPE MyObjType AS TABLE OF MyObject;
  2  /

Type created.

SQL> CREATE OR REPLACE
  2     FUNCTION MyPipelineFunc( FromDate IN DATE, ToDate IN DATE)
  3         RETURN MyObjType
  4         PIPELINED IS
  5
  6     BEGIN
  7        FOR Test IN (
  8            SELECT FromDate + LEVEL Day
  9               FROM DUAL
 10               CONNECT BY LEVEL < (ToDate - FromDate)
 11            )
 12        LOOP
 13           PIPE ROW( MyObject(Test.Day));
 14        END LOOP;
 15
 16     END MyPipelineFunc;
 17  /

Function created.

SQL> -- I can invoke this in a query when logged on to remote database DB_B:

SQL> SELECT * FROM TABLE( MyPipelineFunc( SYSDATE, SYSDATE+10));

A_DAY
---------
08-FEB-13
09-FEB-13
10-FEB-13
11-FEB-13
12-FEB-13
13-FEB-13
14-FEB-13
15-FEB-13
16-FEB-13

9 rows selected.

SQL> -- Remotely querying this from DB_A throws an exception:
SQL> CONNECT /@DB_A
Connected.

SQL>    SELECT * FROM TABLE( MyPipelineFunc@LANDSAT5( SYSDATE, SYSDATE+10));
   SELECT * FROM TABLE( MyPipelineFunc@LANDSAT5( SYSDATE, SYSDATE+10))
*
ERROR at line 1:
ORA-30626: function/procedure parameters of remote object types are not
supported


SQL> -- Here is Tom's solution in my environment using sys_context
SQL> CONNECT /@DB_B
Connected.

SQL> create or replace view vv
  2    as
  3    select *
  4      from table( mypipelineFunc(
  5                   to_date( substr( sys_context('userenv', 'client_info'), 1, 8 ),'yyyymmdd' ),
  6                   to_date( substr( sys_context('userenv', 'client_info'), 9, 8 ),'yyyymmdd' ) ) );

View created.

SQL> CONNECT /@DB_A
Connected.

SQL> exec dbms_application_info.set_client_info@DB_B( to_char(sysdate,'yyyymmdd')|| to_char(sysdate+10, 'yyyymmdd') );

PL/SQL procedure successfully completed.

SQL> select * from vv@Landsat5;

A_DAY
---------
08-FEB-13
09-FEB-13
10-FEB-13
11-FEB-13
12-FEB-13
13-FEB-13
14-FEB-13
15-FEB-13
16-FEB-13

9 rows selected.

SQL> -- OK - That works fine!

SQL> --------- Here is my solution using GTT -----------
SQL>
SQL> CONNECT /@DB_B
Connected.

SQL> -- I create a temp table on the remote database to serve as a
SQL> --  placeholder for the pipeline function parameters.

SQL> CREATE GLOBAL TEMPORARY
  2     TABLE MyPipeFunc_Parameters (
  3           FromDate DATE,
  4           ToDate DATE
  5     ) ON COMMIT PRESERVE ROWS;

Table created.

SQL> -- And now the magic. I found that I can query the remote pipeline function
SQL> --  in a view by passing its parameters thru a joined table. But the parameter
SQL> --  table must precede the pipeline function in the join list (due to scoping
SQL> --  rules???). I am curious whether I may be exploiting an unintended "feature"
SQL> --  that some day might be "fixed", thus breaking this approach.

SQL> CREATE OR REPLACE VIEW vv
  2    AS
  3    SELECT A_Day
  4      FROM MyPipeFunc_Parameters param,
  5           TABLE( MyPipeLinefunc( param.FromDate, param.ToDate));

View created.

SQL> -- Now, return to the local database to test the new remote view.
SQL> CONNECT /@DB_A
Connected.

SQL> -- Load the parameters into a record in the temp parameter table.
SQL> --  This assumes that one and only one record is loaded into the GTT param table,
SQL> --  So I first empty it for good measure.

SQL> DELETE FROM MyPipeFunc_Parameters@DB_B;

0 rows deleted.

SQL> INSERT INTO MyPipeFunc_Parameters@DB_B VALUES (SYSDATE, SYSDATE+10);

1 row created.

SQL> -- Then I can remotely query the pipeline function via the remote view.
SQL>
SQL> SELECT * FROM vv@DB_B;

A_DAY
---------
08-FEB-13
09-FEB-13
10-FEB-13
11-FEB-13
12-FEB-13
13-FEB-13
14-FEB-13
15-FEB-13
16-FEB-13

9 rows selected.

SQL> -- I'm happy with that. It works and accomplishes the objective.i
SQL> -- Now I can query that remote view in a local SQL statement.

Hi Tom,

regarding the above poster's workaround: it works fine but the documentation about temporary tables states: Distributed transactions are not supported for temporary tables.
http://docs.oracle.com/cd/E11882_01/server.112/e26088/statements_7002.htm#SQLRF54447

I've always been intrigued by this sentence, does it apply in this case, when can we use remote temporary tables?

Regards,

the dblink use of doubt, the same SQL with a remote DB with
Following practice database influential not of DB1 under two user permissions is not merger, there is no reason, so I'm going to do so now, do not know will give two DB bring bad impact, such as abnormal DB1 in the Session


- Company Database1 DB1,

Establish UserA and userB, DB under user-A select table1 permissions, user-b select Table2 permissions

The department Databse2 referred DB2

- DB DB1 is created using the user-a dblink, named db1-usera
- DB with the user-b the DB1 the dblink, named db1-userb

Then written in DB2 sql run, DB1 select data from the remote, table1 and table2 user is a different

select row 1, column 2, column 3 from table@db1-usera union all select row 1, column 2, column 3 from table2@db1-userb

Hi Tom,

If a query is used to retrieve data.Which one is the best option to implement using a view or a pipelined function from performance point of view

We can't use single query as it is because query is very big( 2 pages ) and many applications will be using result of this query

Thanks,
Girish

Subject: Accessing Pipelined table function over db link is not working in Apex

Dear All,

Sorry for posting on old thread but this is most relevant post for my query.

On remote DB, below activities done:

1.

CREATE OR REPLACE CONTEXT apex_sc_ctx USING schema_a.pkg_4_context_mgmt ACCESSED GLOBALLY;

2. Created schema_a.pkg_4_context_mgmt

This package has below stored programs:

Procedure: set_context_values : Takes parameters as input and set up application context like ..

DBMS_SESSION.SET_CONTEXT('apex_sc_ctx', 'lc_machine', p_machine);

Function: GET_DATA_FNC

Get parameters and returns SQL_REFCURSOR.

Function: GET_DATA_PIPE_FNC

which utilizes object type like TYPE DATA_TAB_TY IS TABLE OF sometable%ROWTYPE; Takes input SQL_REFCURSOR from GET_DATA_FNC & form pipelined output.

3. Created view over above pipelined function:

CREATE OR REPLACE VIEW V_DATA
AS
SELECT *
  FROM TABLE(schema_a.pkg_4_context_mgmt .GET_DATA_PIPE_FNC (pkg_4_context_mgmt .GET_DATA_FNC(sys_context('apex_scheduler_ctx','lc_machine') ) ) );

At local database, I have a public db link connecting to schema_a of remote database.

1. I fire schema_a.pkg_4_context_mgmt .set_context_values @db_lnk(:P7_MACHINE); to set the application context. There are many parameters, hence separate application context was created.

2. When I run above view [V_DATA] in sql developer. It takes in to account the parameter passed and provide filtered output. Eg. SELECT * FROM V_DATA@db_lnk;

But when I run the same sql statement in SQL Workshop => SQL Command, it doesn't take in to account the application context set up on remote db and entire dataset is returned.

I also created a classic report based on above view, still the outcome remains same.

Versions:

Remote DB: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

Local DB : Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production Version 18.4.0.0.0

Oracle Apex Build: 20.1.0.00.13

Can you please suggest if I am missing anything?

Thank you so much Connor for your time & reply.

What I failed to understand is ..

Through Oracle Apex, "After Submit" I am firing the procedure which sets application context on remote DB.

Once I do it, If I query this view from any PL/SQL Developer session [either local i.e. schema_a or remote i.e. APEX_PUBLIC_USER ] result would be filtered according to the application context.
Certainly, here we must be creating new sessions .. but still it works.

But it's only with Oracle Apex that application context is not getting considered. Ultimate aim is to create a report based on the view.