Hi,
I always wondered what exatcly happens when DBMS_SQL.DEFINE_COLUMN operation is performed.
My research tells me that it is mainly used in the *output*
Source: https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:9526411800346939330
But, DEFINING a Column as DATE of a VARCHAR2 column would anyway throw an error.
Converting a NUMBER to VARCHAR2 anyway happens internally, no need of explicit DEFINE_COLUMN
Then I wondered where exactly DEFINE_COLUMN makes it difference.
Is it only required syntactically (we have to this while fetching rows using COLUMN_VALUE)?
What exactly happens internally when DEFINE_COLUMN is performed.
Just I wrote below code for understanding it.
Code: [
Select all] [
Show/ hide]
CREATE TABLE t_emp ( empno NUMBER, ename VARCHAR2 (20) ) insert into t_emp values (1,'s'); DECLARE cid NUMBER; v_sql VARCHAR2 (2000) := 'SELECT empno, ename from t_EMP'; fdb NUMBER; l_varchar2 VARCHAR2 (200); l_number NUMBER; l_cnt NUMBER; l_date DATE; l_tab DBMS_SQL.desc_tab; BEGIN cid := DBMS_SQL.open_cursor; DBMS_SQL.parse (cid, v_sql, DBMS_SQL.native); DBMS_SQL.describe_columns (cid, l_cnt, l_tab); -- fetch rows FOR i IN 1 .. l_cnt LOOP IF (l_tab (i).col_type = 1) -- data type VARCHAR2 THEN DBMS_SQL.define_column (cid, i, l_varchar2, 200); DBMS_OUTPUT.put_line ('inside define1:' || l_varchar2); ELSIF (l_tab (i).col_type = 2) -- data type NUMBER THEN DBMS_SQL.define_column (cid, i, l_number); DBMS_OUTPUT.put_line ('inside define2:' || l_varchar2); END IF; END LOOP; fdb := DBMS_SQL.execute (cid); WHILE (DBMS_SQL.fetch_rows (cid) > 0) LOOP DBMS_SQL.COLUMN_VALUE (cid, 1, l_varchar2); END LOOP; END; / Thank you in advance.
Regards,
Pointers