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