Re: How to convert LONG to VARCHAR2

From: David Fitzjarrell <oratune_at_yahoo.com>
Date: Fri, 12 Apr 2013 11:38:23 -0700 (PDT)
Message-ID: <1365791903.51073.YahooMailNeo_at_web121601.mail.ne1.yahoo.com>

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

Received on Fri Apr 12 2013 - 20:38:23 CEST