-
Converting LONG value into VARCHAR value
I have a table with LONG type column. Can anyone send me code for converting LONG value into Varchar2 value in oracle8i.
Table Structure:- TEST
Column Name Data Type
--------------- ---------------
C1 NUMBER
C2 LONG RAW
Thank you in advance.
Regards
Bhaskara
-
How to Convert a long to varchar2
Please look at Metalink document Note:228532.1 - below is the note
PURPOSE
-------
The purpose of this document is to provide a sample on how to convert a long to a varchar2.
SCOPE & APPLICATION
-------------------
This document is intended for a developer that has plsql and sqlplus background.
Here is a sample on how to convert the long to a varchar2.
-----------------------------
SQL> create table long_test (field1 long, field2 varchar2(1000));
SQL> insert into long_test values ('Hello' , 'World');
SQL> set serveroutput on;
SQL> declare
2 v1 varchar2(500);
3 begin
4 select field1 into v1 from long_test;
5 dbms_output.put_line(v1);
6* end;
SQL> /
Hello
PL/SQL procedure successfully completed.
Another test I completed is the following:
SQL> insert into long_test (Field1 ) values ('Hello');
1 row created.
SQL> edit
Wrote file afiedt.buf
1 declare
2 v1 varchar2(500);
3 begin
4 select field1 into v1 from long_test;
5 dbms_output.put_line(v1);
6 update long_test set field2 = v1;
7* end;
SQL> /
Hello
PL/SQL procedure successfully completed.
SQL> select * from long_test;
FIELD1 --------------------------------------------------------------------------------
FIELD2 --------------------------------------------------------------------------------
Hello
Hello
***************************************************************************
If you have multiple rows that you need to convert from a long to varchar2, here is an example on how to do this:
create table delete_me (field1 long, field2 varchar2(100));
SQL> insert into delete_me (field1) values ('Row 1');
SQL> insert into delete_me (field1) values ('Row 2')
SQL> insert into delete_me (field1) values ('Row 3')
SQL> insert into delete_me (field1) values ('Row 4')
SQL> insert into delete_me (field1) values ('Row 5')
SQL> select Field1 from delete_me;
FIELD1 ----------------------------------------------------------------
Row 1
Row 2
Row 3
Row 4
Row 5
create table delete_me2 (NewField VARCHAR2(200));
declare
v1 varchar2(100);
CURSOR c_Select IS select Field1 from delete_me;
begin
Open c_Select;
LOOP
Fetch c_Select into v1;
Exit when c_Select%NOTFOUND;
Insert into delete_me2 values (v1);
END LOOP;
Close c_Select;
END;
SQL> select * from delete_me2;
NEWFIELD
----------------------------------------------------------------
Row 1
Row 2
Row 3
Row 4
Row 5
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|