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