Re: Interesting Distributed UNION Question

Malcolm Dew-Jones wrote:

> 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.)

You can not query a package though you could use piplelined table functions. But still the question remains ... if you logically know where to query ... why aren't you doing it and if you don't ... you can't.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace 'x' with 'u' to respond)