Thanks, I am still very much a beginner with PL/SQL so I will see what
I can make of this. Unfortunately it looks like your solution uses
dynamic SQL which is how my current solution works and I am trying to
avoid this as the select and from sections have grown to be very
complex which makes maintenance harder in the long run!
Jeremy.
On Apr 25, 12:26 pm, Andrej Hopko <ado.ho...@gmail.com> wrote:
> Hi,
> well I did this in school team project after cca 20hrs of thinking
> and coding (through some stages)
> by "this" I mean I created fully dynamic system of querying to
> different groups of columns from different groups of tables (and
> sometimes association tables)
>
> I won't be able (nor willing) to give you full code just like that,
> but I may give you some directions
>
> first I divided generic select to four parts that in the end will
> go to execute immediate
>
> EXECUTE IMMEDIATE 'BEGIN
> SELECT DISTINCT (' || *selected_columns* || ' )
> BULK COLLECT INTO :result
> FROM ' || *from_part* || '
> WHERE ' || *where_join* || 'AND (' || *where_criteria* || ');
> END;' USING OUT result;
>
> and by numeric character value as paramter I specified which
> columns should go there (in your case best choice is VARRAY of columns
> or charakteristics)
> and I created set of "translations" for those characteristics (more
> columns may be from one table)
>
> so by this way into *selected_columns* I added all selected columns
> into *from_par* I added tables (careful not to repeat them)
> into *where_join* I written a join conditions ( (+) notation
> for left or right outer joins)
> and into *where_criteria* I added conditions on specific
> columns (whereas I created the stuff for dynamic quering by
> characteristic conditions)
>
> there are two ways a tried to gain strings from character - first
> is by function, second is by writing them into table and then querying
> them (more dynamic)
>
> so this is way to do this dynamicaly, for me it works quite freshly
> (enough to expected user load)
>
> I hope this will help you, if you choose this way (of course you
> need to do some accomodations to your problem)
>
> Andrej Hopko
>
> On 23. 4. 2010 22:38, Jer wrote:
>
>
>
>
>
> > Hi,
> > I am trying to rewrite an ugly and hard to maintain piece of dymanic
> > sql with a function. Basically the function takes as input the list
> > of columns to return/populate.
>
> > This function retrieves a lot of data from the tables and due to the
> > complexity of the joins and outer joins I pass in a list of optional
> > columns which are retrieved from the DB and placed into the out row
> > only if the user asks for them. If they are not in the field list
> > then they are ignored and the joins are avoided, the tables being
> > joined across are also very large!
>
> > So right now I have something that looks like - this is just a
> > simplified example:
>
> > create or replace
> > FUNCTION "F_GET_DETAILS"
> > (
> > in_input_value IN NUMBER,
> > in_field_list IN VARCHAR2
> > )
> > RETURN T_DETAILS_TABLE
> > PIPELINED PARALLEL_ENABLE
> > AS
> > security_cur sys_refcursor;
> > out_rec T_DETAIL_RECORD := T_DETAIL_RECORD (
> > NULL,NULL, NULL,
> > NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL);
> > v_fields VARCHAR2(5000);
> > BEGIN
> > v_fields := UPPER(in_field_list);
>
> > OPEN detail_cur FOR
> > SELECT x,
> > y,
> > z
> > FROM A_TABLE
> > WHERE A_COLUMN = in_input_value;
>
> > LOOP
> > FETCH security_cur INTO
> > out_rec.a, out_rec.b, out_rec.c;
> > EXIT WHEN security_cur%NOTFOUND;
>
> > IF (instr(v_fields, 'LHCLASS1')> 0) THEN
> > BEGIN
> > SELECT d
> > INTO out_rec.d
> > FROM ANOTHER_TABLE
> > WHERE TABLEID = out_rec.a
> > AND ANOTHERKEY = 'KEY1';
> > EXCEPTION
> > WHEN no_data_found THEN
> > out_rec.d := null;
> > END;
> > end if;
>
> > /* lots more optional columns here controlled by the
> > in_field_list
> > */
>
> > PIPE ROW(out_rec);
> > END LOOP;
>
> > CLOSE security_cur;
>
> > RETURN;
> > END F_GET_DETAILS;
>
> > The problem here is that this is also somewhat inefficient as if the
> > initial select statement in the function returns a large rowset then
> > the subsequent optional joins happen once per row retrieved which can
> > add up to a lot of joins! While this does not take a lot of time it
> > still ends up being considerably slower than the monsterous dynamic
> > procedure...
>
> > Is there any way to optionally update all the rows in the cursor at
> > once instead of the row by row approach here as I am guessing one join
> > into the Example ANOTHER_TABLE above would be considerably faster for
> > all data retrieved initially instead of many joins...
>
> > Thanks in advance
>
> > Jeremy.
>
> --
> You received this message because you are subscribed to the Google
> Groups "Oracle PL/SQL" group.
> To post to this group, send email to Oracle-PLSQL@googlegroups.com
> To unsubscribe from this group, send email to
> oracle-plsql-unsubscr...@googlegroups.com
> For more options, visit this group
> athttp://groups.google.com/group/Oracle-PLSQL?hl=en- Hide quoted text -
>
> - Show quoted text -
--
You received this message because you are subscribed to the Google
Groups "Oracle PL/SQL" group.
To post to this group, send email to Oracle-PLSQL@googlegroups.com
To unsubscribe from this group, send email to
oracle-plsql-unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/Oracle-PLSQL?hl=en
