setup
apex 4.2.3
listener 2.0.5
oracle 11.2.0.x
apex.oracle.com
https://apex.oracle.com/pls/apex/f?p=50968
developer/trymeout
problem
I have a particular case where I need to use a dynamic SQL for populating a Collection
(I'm still working on avoiding that ....)
According to the APEX Documentation, it appears you can use just cursor number from DBMS_SQL.
However, when I try, I get ORA-29470.
ORA-20104: create_collection_from_query Error:
ORA-20104: create_collection_from_query ExecErr:
ORA-29470: Effective userid or roles are not the same as when cursor was parsed
And if I open the cursor with "security_level => 0", I get:
ORA-29474: DBMS_SQL.OPEN_CURSOR failed. security_level of 0 is not allowed.
With respect to APEX_COLLECTION using the cursor number, is this a known bug?
Any other comments/suggestions/retort?
Thanks,
MK
The Process for easier review:
declare
c int;
l_sql varchar2(32000);
l_collection_name varchar2(50) := 'DBMS_SQL_RESULTS';
begin
-- this is only an EXAMPLE sql
l_sql := 'select * from emp';
-- DBMS_SQL stuff
c := dbms_sql.open_cursor(
security_level => 1 -- default is 1. 0 === no security check
);
dbms_sql.parse( c, l_sql, DBMS_SQL.NATIVE );
-- place DBMS_SQL.BIND_VARIABLES() here
-- collections stuff
if apex_collection.collection_exists( l_collection_name )
then
apex_collection.delete_collection( l_collection_name );
end if;
-- NOTICE I am using the cursor number, not SQL statement
apex_collection.create_collection_from_query( l_collection_name, c );
end;