Wednesday, January 19, 2011

How to find column count of a query.

This is based on one of my post in OTN. Where an user want to count the columns of a dynamic SQL query.
SQL> CREATE OR REPLACE FUNCTION count_column(pi_sql_statement IN VARCHAR2)
  2    RETURN INTEGER IS
  3    cur_handle NUMBER;
  4    col_count  INTEGER;
  5    rec_tab    DBMS_SQL.DESC_TAB;
  6    var1           NUMBER;
  7  BEGIN
  8    cur_handle := DBMS_SQL.OPEN_CURSOR;
  9    DBMS_SQL.PARSE(cur_handle, pi_sql_statement, DBMS_SQL.NATIVE);
 10    var1 := DBMS_SQL.EXECUTE(cur_handle);
 11    DBMS_SQL.DESCRIBE_COLUMNS(cur_handle, col_count, rec_tab);
 12    DBMS_SQL.close_cursor(cur_handle);
 13    RETURN col_count;
 14  END count_column;
 15  /

Function created.

SQL> SELECT count_column('SELECT ename,sal FROM emp') AS cnt_col FROM dual;

   CNT_COL
----------
         2

SQL> SELECT count_column('SELECT ename,sal,deptno FROM emp') AS cnt_col FROM dual;

   CNT_COL
----------
         3

SQL> SELECT count_column('SELECT * FROM emp') AS cnt_col FROM dual;

   CNT_COL
----------
         8

SQL>

No comments :

Post a Comment