Hello,
I would like to use a dbms_sql cursor when i create an apex collection.
Oracle apex documentation says that it is possible "p_query: Query to execute to populate the members of the collection. If p_query is numeric, it is assumed to be a DBMS_SQL cursor."
I want to use the p_generate_md5 parameter, and avoid sql concatenation using bind variables at the same time, it is because i want to maintain the sql performance (bind variables) and include de md5 generation. The method apex_collection.create_collection_from_query_b that supports bind variables, but it doesn't support the md5 generation.
Testing environment:
Apex 20.1
Oracle DB 18c XE
But I have errors such as:
1) the next code raises ExecErr:ORA-29470: Effective userid or roles are not the same as when cursor was parsed
procedure pr_creaCollEmp(p_deptno dept.deptno%type) is
l_query clob;
cu_sql integer;
sql_rows integer;
begin
l_query := '
select d.deptno, d.dname, d.loc
from dept d where d.deptno < :p_deptno
';
cu_sql := dbms_sql.open_cursor;
dbms_sql.parse(cu_sql, l_query, dbms_sql.native);
dbms_sql.bind_variable(cu_sql, ':p_deptno', p_deptno);
apex_collection.create_collection_from_query(p_collection_name => 'COL_DEPT',
p_query => cu_sql,
p_generate_md5 => 'YES',
p_truncate_if_exists => 'YES');
dbms_sql.close_cursor(cu_sql);
end pr_creaCollEmp;
2) When i execute the query, raises: Error:ORA-20104: Cursor is not yet open
procedure pr_creaCollEmp(p_deptno dept.deptno%type) is
l_query clob;
cu_sql integer;
sql_rows integer;
begin
l_query := '
select d.deptno, d.dname, d.loc
from dept d where d.deptno < :p_deptno
';
cu_sql := dbms_sql.open_cursor;
dbms_sql.parse(cu_sql, l_query, dbms_sql.native);
dbms_sql.bind_variable(cu_sql, ':p_deptno', p_deptno);
sql_rows := dbms_sql.execute(cu_sql);
apex_collection.create_collection_from_query(p_collection_name => 'COL_DEPT',
p_query => sql_rows,
p_generate_md5 => 'YES',
p_truncate_if_exists => 'YES');
dbms_sql.close_cursor(cu_sql);
end pr_creaCollEmp;
3) Raises: Error:ORA-20104: create_collection_from_query ExecErr:ORA-01001: invalid cursor
procedure pr_creaCollEmp(p_deptno dept.deptno%type) is
l_cursor sys_refcursor;
l_cursor_id number;
begin
open l_cursor for
select d.deptno, d.dname, d.loc
from dept d
where d.deptno < p_deptno;
l_cursor_id := dbms_sql.to_cursor_number(l_cursor);
apex_collection.create_collection_from_query(p_collection_name => 'COL_DEPT',
p_query => l_cursor_id,
p_generate_md5 => 'YES',
p_truncate_if_exists => 'YES');
end pr_creaCollEmp;
Maybe someone could help me.
Fernando.