2 Replies Latest reply: Feb 17, 2009 3:40 PM by 142970 RSS

    Pipelined Table Function across DBLink fails - Java workaround also fails

    142970
      U.C. Davis is evolving an approach to serving data to campus information workers from the campus Data Warehouse that is built around the power of pipelined table functions. This approach enables us to implement strong credentials validation inside the query package. It also makes it possible to record usage data for audit and planning purposes.

      A typical test query looks like this: SELECT * FROM TABLE(LINKTEST.TENROWS('SAMPLE DATA));

      The output look like this:
      1     SAMPLE DATA WAS INPUT.
      2     SAMPLE DATA WAS INPUT.
      3     SAMPLE DATA WAS INPUT.
      4     SAMPLE DATA WAS INPUT.
      5     SAMPLE DATA WAS INPUT.
      6     SAMPLE DATA WAS INPUT.
      7     SAMPLE DATA WAS INPUT.
      8     SAMPLE DATA WAS INPUT.
      9     SAMPLE DATA WAS INPUT.
      10     SAMPLE DATA WAS INPUT.

      We have used this approach for the past 2 years to develop web applications. It gives us the ability to separate interface development from database development.

      We also want to use this approach to fetch data from a source system into our data warehouse, however when we try to perform this query across a database link it fails!

      SELECT * FROM TABLE(LINKTEST.TENROWS@RJWARGCVT('SAMPLE DATA')) produces an ORA-06553: PLS-752 inconsistent state error. According to Oracle Tech Support, Oracle does not allow/enable/implement table functions across database links.
      Bug 5959438 - ORA-7445 OCCURS WHEN SELECTING PIPELINED FUNCTION OVER DATABASE LINK
      Bug 6919270 - GUIDES SHOULD MENTION THE PIPELINED FUNCTION OVER DBLINK IS NOT SUPPORTED


      Now that's odd because I can execute the same function using ODBC and MS Access or MS SQLServer. What do they know that Oracle doesn't?

      One workaround would be to route the query through a Microsoft SQLServer, and while that seems to work in our prototype I'm not sure the performance hit of the extra jump would be acceptable. Besides, we'd need to dedicate a machine to it.

      Another workaround might be to write a Java JDBC query inside PLSQL and return the result from the Table Function as a REF CURSOR, then LOOP through the REF CURSOR and return a PIPELINED rowset locally. But there's another problem. The built in SERVER SIDE JDBC driver will return a REF CURSOR but it won't connect to a remote system. The JDBC Thin driver will connect to a remote system but won't return a REF CURSOR.

      Another very expensive solution would be to re-implement the Campus Data Warehouse using MSSQLServer (or perhaps MySQL which also handles the functions without error)

      What to do, what to do?

      What we're left with, if we want to use a purely Oracle solution, is write the entire transfer in Java and call it from PLSQL. Granted that's only a few hundred lines of code but it's not nearly as elegant, simple or easy to support as 'INSERT INTO TABLEA (SELECT * FROM TABLE(LINKTEST.TENROWS('SAMPLE DATA')));'

      Any suggestions (other than switch to another database vendor)?
        • 1. Re: Pipelined Table Function across DBLink fails - Java workaround also fails
          thomaso
          SELECT * FROM TABLE(LINKTEST.TENROWS@RJWARGCVT('SAMPLE DATA')) <<
          Can you create a view on "RJWARGCVT" database:
          CREATE VIEW MyView AS SELECT * FROM TABLE(LINKTEST.TENROWS('SAMPLE DATA'));
          and then from main database call:
          SELECT * FROM MyView@RJWARGCVT;
          That way you let TABLE and your package to be resolved on one database level.


          BTW
          This approach enables us to implement strong credentials validation inside the query package. It also makes it possible to record usage data for audit and planning purposes. <<
          I have a feeling that you are reinventing a wheel.
          Look at Oracle terms:
          Fine grained security and Fine grained audit.

          HTH
          Thomas
          • 2. Re: Pipelined Table Function across DBLink fails - Java workaround also fai
            142970
            Thanks for the reply.

            We are implementing centralized authentication system (CAS) single sign on security for our web based Student Information System. The web servers connect to the database using their own WEBSERVER account. The individual users will no longer have direct connection rights to the database. Rather in the same way that I can record this message in some Oracle database without being able to connect to it by having the web service use its connection. I think this limits the effective use of Oracle 'Fine Grained Security'. I can't find any Oracle 'Audit' product that will enable us to record the function name (query), the parameters used, the user ID of the ultimate user (not the connection ID), and the number of rows retrieved (or the cause of failure).

            Our actual function call includes a proxy ticket parameter which a Java function inside PLSQL uses to to return the unique campus ID of the person who made the request from CAS. This ID is used to control data access in certain cases and to record usage in all cases. Because of the encrypted nature of the proxy ticket, the ID is very difficult to spoof.


            Creating a remote view on the fly requires CREATE rights on the remote system. That isn't feasible for many reasons. It's also a 2 step function and we already have a 2 step view workaround that uses a set of global variables to control the table function. It uses a separate procedure in the package to set the variables. This works well enough for simple cases, but isn't sufficiently dynamic to be a permanent solution.

            It still may turn out that a SQLServer bridge is the simplest solution. I'm may propose that we do a cost/benefit analysis for a switch to SQLServer. Many of our campus client units already use SQLServer and they would probably prefer the upgrade to a more modern database.