Re: Interesting Distributed UNION Question

Roger Corman (rogcorpr_at_hotmail.com) wrote:
: > This smacks of homework. Apologies if it isn't, but here's a hint if it is
: > (or question to be answered if it isn't)...
: >
: > Why do you need the SOURCE column?

: Looks like I need to provide some more detail. It was a good laugh to
: read the "smacks of homework" line this morning. I wasn't sure if I
: was to be congratulated or offended by the remark! It is true that it
: smacks of University drivel. Anyway...

: The Oracle server version is 9i. The SQL Server version is 2000. I
: am somewhat new to the Oracle programming world with years of SQL
: Server experience.

: The SOURCE column would not be needed. The intent of the question was
: to keep things as simple as possible.

: I've immediately ruled out the *hint*
: "dbms_application_info.set_client_info" because, as stated in my post,
: it isn't allowed to create a view or SQL query dynamically due to the
: application that will be calling the view.

: The application is a pharma reporting package that requires a
: hardcoded view name. The pharma package is also configured with the
: WHERE clause values that will be sent to the view, such as "WHERE X =
: [VAL]". The application cannot call stored procedures, functions,
: packages, etc. Environment settings also cannot be set or retrieved
: prior to calling View. The application can't make EXEC calls.

: Conclusion: I believe I am going to have to create a different
: redundant view for each Server. But, and it is a long shot, I may be
: able to provide optimizer hints which help Oracle determine that it is
: only necessary to query one Server. Or, pipe dream now, someone might
: surprise me with a nifty workaround that I haven't considered!

Is there not a way in oracle to use package procedures to create a "pseudo view:? The package procedure would then query just the one server you need. I forget the name, but the select would be like

        select whatever from fake-table-provided-by-package

(Perhaps this isn't oracle, I saw it discussed some ago.) Received on Fri Oct 29 2004 - 23:20:24 CDT