DEFINE_COLUMN in DBMS_SQL [message #662220] |
Thu, 20 April 2017 21:56 |
pointers
Messages: 451 Registered: May 2008
|
Senior Member |
|
|
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.
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
[Updated on: Thu, 20 April 2017 21:58] Report message to a moderator
|
|
|
|
|