Hi,
I'm having issues with the use of certain parts of the dbms_sql package. What I am trying to do, in brief, is:
* take an existing sys_refcursor and convert it for use with the dbms_sql package using
app_curs := dbms_sql.to_cursor_number(v_app_cursor);
* obtain a desc_tab instance that describes the cursor, using:
dbms_sql.describe_columns(app_curs, v_app_curs_cols, v_app_curs_dets);
* pass the cursor id variable, details and column count into subsidiary functions to generate xml strings.
I have defined the functions like this in a package:
FUNCTION excel_xml_row_headers(p_details IN dbms_sql.desc_tab, p_columns IN INTEGER) RETURN excel_xml_file PIPELINED;
FUNCTION excel_xml_rows(p_cursor IN NUMBER, p_details IN dbms_sql.desc_tab, p_columns IN INTEGER) RETURN excel_xml_file PIPELINED;
FUNCTION excel_xml_end_worksheet(p_columns IN INTEGER) RETURN excel_xml_file PIPELINED;
But when I try and use the functions:
PROCEDURE excel_xml_test (p_result OUT SYS_REFCURSOR)
v_app_cursor SYS_REFCURSOR;
app_curs NUMBER := NULL;
v_app_curs_dets dbms_sql.desc_tab;
v_app_curs_cols INTEGER := NULL;
BEGIN
open v_app_cursor FOR SELECT app.LAST_NAME || ', ' || app.FIRST_NAME || nullif(' ' || app.MIDDLE_NAMES, ' ') "Appointee Name", apr.INVITATION_DATE, app.IDENTIFIER FROM APPOINTEE app inner join APPOINTEE_ROLE apr ON app.ID = apr.APPOINTEE_ID;
app_curs := dbms_sql.to_cursor_number(v_app_cursor);
dbms_sql.describe_columns(app_curs, v_app_curs_cols, v_app_curs_dets);
OPEN p_result FOR
select * from TABLE(excel_xml_utils.excel_xml_row_headers(v_app_curs_dets, v_app_curs_cols))
union all
select * from TABLE(excel_xml_utils.excel_xml_rows(app_curs, v_app_curs_dets, v_app_curs_cols))
union all
select * from TABLE(excel_xml_utils.excel_xml_end_worksheet(v_app_curs_cols));
END;
I get the following compilation error:
PLS-00382: expression is of wrong type
PLS-00306: wrong number or types of arguments in call to 'EXCEL_XML_ROW_HEADERS'
PL/SQL: ORA-00904: "EXCEL_XML_UTILS"."EXCEL_XML_ROW_HEADERS": invalid identifier
PL/SQL: SQL Statement ignoredHaving read the dbms_sql guidance here
https://docs.oracle.com/cd/B28359_01/appdev.111/b28419/d_sql.htm#BABEDAHF, I also tried replacing the desc_tab with desc_tab2 (also replacing describe_columns with describe_columns2) but the same issue arises.
Is there therefore a restriction on the use of dbms_sql.desc_tab as a parameter to a function or is there an alternative invocation method I could use?
You'll need to be on 12 for that, eg
--
-- v11
--
SQL> CREATE OR REPLACE FUNCTION f(x dbms_sql.desc_tab) return sys.odcinumberlist pipelined is
2 BEGIN
3 pipe row (1);
4 pipe row (2);
5 pipe row (3);
6 return;
7 END;
8 /
Function created.
SQL> declare
2 d1 dbms_sql.desc_tab;
3 rc sys_refcursor;
4 begin
5 open rc for 'select * from table(f(:1))' using d1;
6 end;
7 /
open rc for 'select * from table(f(:1))' using d1;
ERROR at line 5:
ORA-06550: line 5, column 50:
PLS-00457: expressions have to be of SQL types
ORA-06550: line 5, column 3:
PL/SQL: Statement ignored
--
-- 12
--
SQL> declare
2 d1 dbms_sql.desc_tab;
3 rc sys_refcursor;
4 begin
5 open rc for 'select * from table(f(:1))' using d1;
6 end;
7 /
PL/SQL procedure successfully completed.
As a workaround, create your own "describe_columns" routine which does:
- dbms_sql.describe_columns
- loops through the associative array and builds an equivalent nested table of objects, and returns that
- you can then run table functions on that *sql* type.