Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Interesting Distributed UNION Question

Re: Interesting Distributed UNION Question

From: Roger Corman <rogcorpr_at_hotmail.com>
Date: 28 Oct 2004 07:11:43 -0700
Message-ID: <f8285955.0410280611.2e6b8e48@posting.google.com>


> 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! Received on Thu Oct 28 2004 - 09:11:43 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US