Skip to Main Content
  • Questions
  • Passing dbms_sql.desc_tab (or desc_tab2) as a function parameter

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Jeremy.

Asked: May 26, 2017 - 10:21 am UTC

Last updated: May 29, 2017 - 3:00 am UTC

Version: Oracle Database 11g Release 11.2.0.4.0 - 64bit Production

Viewed 1000+ times

You Asked

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 ignored


Having 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?

and Connor said...

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.

Is this answer out of date? If it is, please let us know via a Comment

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library