Home » SQL & PL/SQL » SQL & PL/SQL » convert long to varchar [merged 5 by jd] (10g)
| convert long to varchar [merged 5 by jd] [message #565072] |
Thu, 30 August 2012 06:42  |
 |
Oracle12Oracle
Messages: 2
Registered: August 2012
|
Junior Member |
|
|
Hi ,
Can anyone help to display or get values stored in a long column.
i tried the below code, but myvar is shown as null. am i missing something here or is there
a better apporoach than this ?. There are actually 16 rows returned for this query but with
empty values for 'high_value' column which is critical for me. How can i do this long to varchar convertion ?
SET SERVEROUTPUT ON
DECLARE
my_var long;
BEGIN
for x in ( SELECT high_value
FROM all_tab_partitions
WHERE table_name = 'SALES_DISCOUNT'
AND table_owner = 'CED12'
ORDER BY partition_position DESC )
loop
my_var := to_char(dbms_lob.substr( x.high_value, 32000, 1 ));
dbms_output.put_line('high_value >'||my_var);
end loop;
EXCEPTION
when others then
dbms_output.put_line('Error Occured '||sqlerrm);
rollback;
END;
/
another peice of sample code which i tried : (when i ran this in a oracle 10 db in aix env, 'high_value' was
getting displayed, but same script is not showing any results when ran in 0racle 10g db - Linux env)
so strange, any thoughts or some suggestions. plz..
set serveroutput on
DECLARE
I_fail_String long := lpad('1','10000',0);
o_fail_String varchar2(9000);
ret number;
cursor c1 is SELECT ap.partition_name
-- , dbms_lob.substr(ap.high_value,9000,1) abc
, ap.high_value abc
FROM all_tab_partitions ap
WHERE ap.table_name = 'SALES_DISCOUNT'
AND ap.table_owner = 'CED12'
ORDER BY ap.partition_position DESC;
BEGIN
o_fail_string := dbms_lob.substr(I_fail_string,9000,1);
dbms_output.put_line('o_fail_string >'||o_fail_string);
dbms_output.put_line('o_fail_string >'||o_fail_string);
for c_rec in c1
loop
dbms_output.put_line('abc >'||c_rec.abc);
o_fail_string := dbms_lob.substr(c_rec.abc,9000,1);
dbms_output.put_line('o_fail_string >'||o_fail_string);
end loop;
dbms_output.put_line('Return Code '||ret);
rollback;
EXCEPTION
when others then
dbms_output.put_line('Error Occured '||sqlerrm);
rollback;
END;
|
|
|
|
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Thu May 15 22:11:17 CDT 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
