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
