October 2005
Intermediate to advanced
454 pages
14h 44m
English
One very useful application of table functions is to use them to establish time limits for returning records from queries. This is great if you want to test an application using a subset of queried records without having to wait for the whole list. The following function pipes records back from a query for the number of seconds passed in. Once the number of seconds is reached, a value of negative 1 is piped out and the function is exited.
/* File on web: time_limit.sql */
CREATE OR REPLACE FUNCTION get_a_table
( p_limit NUMBER )
RETURN rowset_t
PIPELINED AS
CURSOR curs_get_a IS
SELECT *
FROM a_table;
v_start DATE;
BEGIN
v_start := SYSDATE;
FOR v_a_rec IN curs_get_a LOOP
PIPE ROW(rowset_o(v_a_rec.col1));
IF SYSDATE - v_start >= ( p_limit * 0.000011574 ) THEN
PIPE ROW(rowset_o(-1));
EXIT;
END IF;
END LOOP;
END;Here’s an example of selecting from a table with 1,000 records in it.
SQL>SELECT *2FROM TABLE(get_a_table(1));COL1 ---------- 661 662 663 664 -1 5 rows selected.
If the query takes more than p_limit seconds to return, the function will exceed its time limit.