Hi,
I've got a procedure which
has to open and return a strongly typed cursor to the client. The cursor's SQL statement uses the TABLE function on a collection (in a JOIN statement, not present in the sample script), therefore I need to add a CARDINALITY hint (i.e. collection's size), but I'm not sure how to "inject" the collection size into the hint section of the SQL statement; because of the use of strongly typed cursor I can't use NDS, and I'm not sure if DBMS_SQL can be utilized in this case.
You can find a simplified script here:
https://livesql.oracle.com/apex/livesql/file/content_CFD0CL4RRC7P5D4NZMGOS4BK5.html Regards,
Babak.
Thanks for using LiveSQL to provide a test harness! This makes it much easier for us to help you.
Starting in 11g you can convert a DBMS_SQL cursor to a ref cursor.
To do this, build your statement as you normally would using DBMS_SQL. After executing the cursor, convert it to your refcursor using dbms_sql.to_refcursor.
Here's a link to the LiveSQL version:
https://livesql.oracle.com/apex/livesql/file/content_CFFBH7F3AYR3N5XAUXR0OEGOS.html Or the updated package body is here directly:
CREATE OR REPLACE PACKAGE BODY client_utilities AS
PROCEDURE return_open_cursor
(
p_flagged_varchar OUT flagged_varchar_ref_cur,
p_unflagged_varchars IN STRINGARRAY
)
IS
l_varchar_table STRINGARRAY;
l_cursor number;
l_return number;
l_sql varchar2(1000);
BEGIN
l_sql := 'SELECT /*+ CARDINALITY(unflagged_varchars, 100) */ COLUMN_VALUE, 1
FROM TABLE(CAST(:p_unflagged_varchars AS STRINGARRAY)) unflagged_varchars';
l_cursor := dbms_sql.open_cursor;
dbms_sql.parse(l_cursor, l_sql, dbms_sql.native);
dbms_sql.bind_variable (l_cursor, 'p_unflagged_varchars', p_unflagged_varchars);
l_return := dbms_sql.execute(l_cursor);
p_flagged_varchar := dbms_sql.to_refcursor(l_cursor);
END return_open_cursor;
END client_utilities;
/
declare
ret client_utilities.flagged_varchar_ref_cur;
l STRINGARRAY := STRINGARRAY('1', '2', 'apple', 'banana');
v client_utilities.flagged_varchar;
begin
client_utilities.return_open_cursor(ret, l);
loop
fetch ret into v;
exit when ret%notfound;
dbms_output.put_line(v.value || '##' || v.flag);
end loop;
end;
/
1##1
2##1
apple##1
banana##1