October 2005
Intermediate to advanced
454 pages
14h 44m
English
Oracle’s SYS_REFCURSOR function provides a way to quickly declare a weakly typed REF cursor that can process almost any cursor. The fact that SYS_REFCURSOR can be specified as the datatype means that any old SELECT statement can be passed in, as long as the cursor isn’t actually manipulated in the function. For example, this function is wide open to any SELECT.
CREATE OR REPLACE FUNCTION wide_open ( p_curs SYS_REFCURSOR )
RETURN number_t IS
v_ret_val number_t := number_t( );
BEGIN
v_ret_val.EXTEND;
v_ret_val(v_ret_val.LAST) := 99;
RETURN v_ret_val;
END;Any valid SELECT will work.
SQL>SELECT *2FROM TABLE(wide_open(CURSOR(SELECT NULL3FROM DUAL)));COLUMN_VALUE ------------ 99 SQL>SELECT *2FROM TABLE(wide_open(CURSOR(SELECT *3FROM orders)));COLUMN_VALUE ------------ 99
But things have to become more isolated because, presumably, records will be fetched within the function. Thus, local variables are required to fetch into.
CREATE OR REPLACE FUNCTION wide_open ( p_curs SYS_REFCURSOR )
RETURN number_t IS
v_ret_val number_t := number_t( );
v_order_rec orders%ROWTYPE;
BEGIN
FETCH p_curs INTO v_order_rec;
v_ret_val.EXTEND;
v_ret_val(v_ret_val.LAST) := 99;
RETURN v_ret_val;
END;Only SELECT statements getting all columns from the ORDERS table can now be passed to this function. Others will raise the ORA-01007 error .
SQL>SELECT *2FROM TABLE(wide_open(CURSOR(SELECT NULL3FROM DUAL)));FROM TABLE(wide_open(CURSOR(SELECT NULL * ERROR at line 2: ORA-01007: ...