How to convert Long data to Varchar in Oracle

Search this site

Email us on admin 'at' kaali.co.uk if you need hosting space or a website at a very reasonable price. Kaali Web Engine is a web based content management system to manage website content, images, look & feel and more.


	Authentic Silver Jewellery

How to convert Long data to Varchar in Oracle

This article provides PL/SQL code example of how to display Oracle Long data from a table. Also it is a great help if you plan to migrate/convert your data from a Long type to varchar2.

SQL/Plus is unable to display Oracle Long types. We need to use PL/SQL to achieve this. The PL/SQL code can be used either on the SQL prompt or in a packaged form (Function or Procedure).

Let's get started.

Assume that you have a table called MY_DATA defined as:

MD_SEQNO NUMBER(6);
MD_TEXT LONG;
Our aim is to read the MD_TEXT column from this table and display the result on to the screen in chunks of 200 characters each.

declare
l_var_text varchar2(2000);
l_counter number(5) := 0;
i number(5) := 1;
l_rec_length number(9) := 0;
l_size number(3) := 200;
cursor c1 is
select MD_TEXT from MY_DATA where MD_SEQNO=29';
begin
for c1_rec in c1 loop
l_rec_length := length(c1_rec.text);
dbms_output.put_line('Record length is: '||l_rec_length);
while l_rec_length > 0 loop
l_counter := l_counter + 1;
l_var_text := substr(c1_rec.text,i,l_size);
DBMS_OUTPUT.PUT_LINE('Line '||l_counter||': '||l_var_text);
i := i + l_size;
l_rec_length := l_rec_length - l_size;
end loop;
i := 1;
end loop;
end;
/

Hope this helps.

Was this article helpful? Please leave your feedback. It will help us improve and serve our customers better.

Hands on...

Author: Kaali, last update: 25th Jul 2005

Hands on |

Site map |