Re: How to convert LONG to VARCHAR2
- From: David Fitzjarrell <oratune@xxxxxxxxx>
- To: "ecandrietta@xxxxxxxxx" <ecandrietta@xxxxxxxxx>, ORACLE-L <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 12 Apr 2013 11:38:23 -0700 (PDT)
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
Other related posts:
