Home » SQL & PL/SQL » SQL & PL/SQL » DEFINE_COLUMN in DBMS_SQL (Oracle 11.2.0.3)
DEFINE_COLUMN in DBMS_SQL [message #662220] Thu, 20 April 2017 21:56 Go to next message
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

Re: DEFINE_COLUMN in DBMS_SQL [message #662221 is a reply to message #662220] Thu, 20 April 2017 22:04 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
https://www.google.com/webhp?hl=en&tab=ww#hl=en&q=dbms_sql.define_column+example
Re: DEFINE_COLUMN in DBMS_SQL [message #662232 is a reply to message #662221] Fri, 21 April 2017 02:09 Go to previous message
pointers
Messages: 451
Registered: May 2008
Senior Member
@Blackswan:

Thank you for your response.

I hope your response would not stop someone else to respond.

Regards,
Pointers
Previous Topic: Need help to form Query to split count into multiple rows
Next Topic: LIKE vs NOT LIKE operator
Goto Forum:
  


Current Time: Fri Mar 29 08:02:27 CDT 2024