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 [email protected]
To unsubscribe from this group, send email to
[email protected]
For more options, visit this group at
http://groups.google.com/group/Oracle-PLSQL?hl=en
