APEX

I had a chat about this with the Apex product manager at ODTUG, but it's still not on the Statement of Direction, so here is a small reminder... :-)

One thing I don't like about Apex is scattering (and repeating) SQL strings "all over the place". Using REF CURSORS (or to be more precise, a pl/sql function returning a ref cursor) as the source of report regions (both regular reports and interactive reports, if possible) has several benefits, including

a) encapsulation (put all queries behind a package interface, GUI developers can just call a function and let more experienced SQL developers write the queries)

b) reuse (don't have to duplicate the same SQL multiple places in the same application, and the packages exposing the ref cursors can be used/called by others clients too, such as ADF/Java, .NET, php, Delphi, and most other dev tools)

c) maintainability (easier to use source control on pl/sql packages than on Apex export file)

d) security (do not need to grant table privs to parsing schema, just execute on selected packages, and perhaps easier to avoid SQL injection)

For example, let's assume we have the following function in a package:

function get_employees (p_deptno in number) return sys_refcursor
as
l_returnvalue sys_refcursor;
begin

open l_returnvalue
for
select empno, ename, job, hiredate
from emp
where deptno = p_deptno;

return l_returnvalue;

end get_employees;




It should then be possible to create a report region in Apex, set its type to "Ref Cursor (PL/SQL Function Returning Ref Cursor)" and in the region source just enter the name of the function (for example, "my_package.get_employees(:p1_deptno);"). Note the passing of a parameter based on session value.

From my understanding, Apex uses dbms_sql.describe to discover and define the report columns of user-entered SQL. From Oracle 11g, a new function, dbms_sql.to_cursor_number, allows for the conversion of a refcursor to a regular dbms_sql cursor, which can then be "described" and processed just as is done with other queries.

Now, since this function is only available in 11g, this new region source type should probably not display in Apex unless it's the correct db version. Or perhaps just throw a runtime exception if it is not the proper db version.

There may be an issue with pagination. I guess that for regular SQL reports, the Apex framework just wraps the entered query in an outer query that performs pagination. For ref cursors returned from functions, this is not possible and I believe the Apex team will have to define some sort of (optional or mandatory?) parameter list (starting position and number of rows to fetch), and it would be up to the developer of the function to implement paging on his own based on these parameters supplied by the Apex engine.

I believe this functionality could be implemented rather easily in Apex, and would add much value to the product, as an even cleaner separation of database and GUI could be achieved.

Others who are interested in seeing the same functionality should reply to this message, so the good folks on the Apex team can gauge interest in the feature.

- Morten