Converting LONG value into VARCHAR value
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: Converting LONG value into VARCHAR value

Hybrid View

  1. #1
    Join Date
    May 2000
    Location
    India
    Posts
    18

    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

  2. #2
    Join Date
    Nov 2003
    Posts
    1

    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
  •  



Click Here to Expand Forum to Full Width