Re: How to convert LONG to VARCHAR2
Date: Fri, 12 Apr 2013 23:46:40 +0400
Message-ID: <CAOVevU4DMykSSDQE1Oa9qxk--juj3Cus1eJLUkKgz_mjv_wFYw_at_mail.gmail.com>
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_at_yahoo.com>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_at_gmail.com>
> ________________________________
> To: ORACLE-L <oracle-l_at_freelists.org>
> 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.
> >
> --
> http://www.freelists.org/webpage/oracle-l
> --
> http://www.freelists.org/webpage/oracle-l
> > >
-- Best regards, Sayan Malakshinov Senior performance tuning engineer PSBank http://orasql.org -- http://www.freelists.org/webpage/oracle-lReceived on Fri Apr 12 2013 - 21:46:40 CEST