Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2004
    Posts
    7

    Red face Convert LONG to VARCHAR2

    Hi folks,
    can anybody suggest me a trick to convert (usying for example a function) a LONG variable into a VARCHAR2?
    No problem in truncating it if exceed 4000 characters!
    I'm using Oracle 8.1.7; please help me!
    Thanks - Max

  2. #2
    Join Date
    Jan 2003
    Location
    Vienna, Austria
    Posts
    102
    declare
    string varchar2(4000);
    character char(1);
    begin
    select longcolumn, pk into string, character from longtable;
    insert into varchartable values (string, character);
    end;

    maybe this helps?
    ^/\x

  3. #3
    Join Date
    Apr 2004
    Posts
    7
    Sorry, when lenght of longcolumn is > than 4000 it gives me the following error:

    ORA-06502: PL/SQL: numeric or value error: character string buffer too small

    He cannot truncate the field.

    Max

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    If you have no more than 32K of data in your LONG column you can modify berxh3g's answer to:

    declare
    string long;
    character char(1);
    begin
    select longcolumn, pk into string, character from longtable;
    insert into varchartable values (substr(string,1,4000), character);
    end;

    If you have to handle more than 32K then you could use the DBMS_SQL package to get the data in pieces using the DEFINE_COLUMN_LONG and COLUMN_VALUE_LONG procedures.

  5. #5
    Join Date
    Apr 2004
    Posts
    7
    Thanks to everybody!
    It's working now!
    Max

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •