It also can be done throw xml functions without creating any own functions. Simple example: select * from xmltable( '/ROWSET/ROW' passing dbms_xmlgen.getXMLType(' select table_name ,column_name ,data_type ,data_default from user_tab_columns where data_default is not null ' ) columns table_name varchar2(30) ,column_name varchar2(30) ,data_type varchar2(106) ,data_default varchar2(4000) ) On Fri, Apr 12, 2013 at 10:38 PM, David Fitzjarrell <oratune@xxxxxxxxx>wrote: > I wonder if DATA_DEFAULT was NULL -- if it is no text will be returned. I > got it to work: > SQL> CREATE OR REPLACE FUNCTION LONG_TO_CHAR > 2 ( in_table_name varchar, > 3 in_column varchar2, > 4 in_column_name varchar2, > 5 in_tab_name varchar2) > 6 RETURN varchar AS > 7 text_c1 varchar2(32767); > 8 sql_cur varchar2(2000); > 9 begin > 10 sql_cur := 'select '||in_column||' from > 11 '||in_table_name||' where column_name = ' || > 12 chr(39)||in_column_name||chr(39) ||' AND TABLE_NAME=' || > 13 chr(39)||in_tab_name||chr(39); --1 AND ROWNUM = 1'; > 14 dbms_output.put_line (sql_cur); > 15 execute immediate sql_cur into text_c1; > 16 text_c1 := substr(text_c1, 1, 4000); > 17 RETURN TEXT_C1; > 18 END; > 19 / > > Function created. > > SQL> > SQL> select long_to_char('USER_TAB_COLUMNS', 'DATA_DEFAULT', 'EMPNO', > 'EMP') from dual; > > LONG_TO_CHAR('USER_TAB_COLUMNS','DATA_DEFAULT','EMPNO','EMP') > > -------------------------------------------------------------------------------- > 9999 > select DATA_DEFAULT from > USER_TAB_COLUMNS where column_name = 'EMPNO' AND > TABLE_NAME='EMP' > SQL> > > I modified the EMPNO column to have a default (it doesn't have one when > the table is created). Not doing that returned a NULL string. > > David Fitzjarrell > > > > ________________________________ > From: Eriovaldo Andrietta <ecandrietta@xxxxxxxxx> > To: ORACLE-L <oracle-l@xxxxxxxxxxxxx> > Sent: Friday, April 12, 2013 10:22 AM > Subject: How to convert LONG to VARCHAR2 > > > Hi Friends, > How can I convert the column USER_TAB_COLUMNS.DATA_DEFAULT to VARCHAR2 in > order to compare the content with a VARCHAR2 column, using SQL or PL/SQL. > The column DATA_DEFAULT has LONG datatype. > > I am using Oracle 11g database. > > I used this function, but it did not work: > > CREATE OR REPLACE FUNCTION LONG_TO_CHAR > ( in_table_name varchar, > in_column varchar2, > in_column_name varchar2, > in_tab_name varchar2) > RETURN varchar AS > text_c1 varchar2(32767); > sql_cur varchar2(2000); > begin > sql_cur := 'select '||in_column||' from > '||in_table_name||' where column_name = ' || > chr(39)||in_column_name||chr(39) ||' AND TABLE_NAME=' || > chr(39)||in_tab_name||chr(39); --1 AND ROWNUM = 1'; > dbms_output.put_line (sql_cur); > execute immediate sql_cur into text_c1; > text_c1 := substr(text_c1, 1, 4000); > RETURN TEXT_C1; > END; > / > > Best regards > Eriovaldo. > > > -- > //www.freelists.org/webpage/oracle-l > -- > //www.freelists.org/webpage/oracle-l > > > -- Best regards, Sayan Malakshinov Senior performance tuning engineer PSBank http://orasql.org -- //www.freelists.org/webpage/oracle-l