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 -> Dynamic function call used in SELECT

Dynamic function call used in SELECT

From: <Jiri.Felcman_at_noofs.abb.no>
Date: Sat, 18 Sep 1999 17:20:19 GMT
Message-ID: <7s0hka$9vm$1@nnrp1.deja.com>


Hi,

I have following problem or maybe a challenge. To explain it I must begin from the basics: There is a table consisting of cca 30 columns in our database where we store data about cables (routed, installed ...) and a lot of other tables (cables library ...). Then we have about five main complicated views (up till now we have been using so called "pyramid views structure" to get all summaries. For example in a view we should report let's say three cumulative calculations - SELECT from different tables grouping by different columns. I do this callculations in so called "sub-views" and then connect them in a view that is the source for the report. Up to now everything was OK. But - we have been asked to introduce some report criterias. In a standard view it works OK - SELECT ... WHERE - condition. But I need to pass the criterias to the views processing the cumulative calculations. So, I decided instead of using views to use ORACLE functions. I managed a first test function with the set of criterias required at the being. I implemented it into the basic view and it works. Now I am to create other functions and implement them. BUT !!!! As you all know when a users get something they want more and more. So, I suppose that within few days after we distribute this "new" version, customers will require reports with possibilities to use more "WHERE" criterias. And here is the problem. Let's say I will have to create about 20 new functions, very very similar where the only one difference is in the cursor. Now users require two criterias (function parameters). But what to do when they want to introduce five new? Do I have to rewrite all 20 functions (headers, cursors ...)? No - I do not want. So, as the criterias are connected only to one table, a common one for all cursors I wanted to create a general ! ONE ! function where I would use the dynamic SQL via DBMS_SQL package. One parameter would say what kind of calculations to return. Then in a array I would have different SELECTS and the WHERE clause would be added to the end of the actual SQL statement dynamicly. When a new parameter is needed it would mean to change only one function definition and one code line. BUT - it works well but the problem is when I want to use such function in a view definition. Of course - PRAGMA violation - SQL_DBMS package can't have any WNDS pragma assigned if it's possible to use there DML statements. So - does anybody have any idea how to solve this? Maybe there is a SQL_DBMS like package that enables only SELECTS and could be used here OR ????

Any response is appreciated. Sorry for so long message - but maybe somebody can propose another solution of this complex problem.

With regards Jiri

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't. Received on Sat Sep 18 1999 - 12:20:19 CDT

Original text of this message

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