Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2002
    Location
    Sunny Florida
    Posts
    121

    Unanswered: Help a dummy out. Long Raw issue.

    See below:

    create table gg (xx long raw);

    create table gg (x1 varchar2(4000), x2 varchar2(4000), x3 varchar2(4000), x4 varchar2(4000));

    insert into gg select rpad('a', 4000, 'a'), rpad('b', 4000, 'b'), rpad('c', 4000, 'c'), rpad('D', 4000, 'D');

    insert into g select x1 || x2 || x3 || x4 from gg;
    *
    ERROR at line 1:
    ORA-01489: result of string concatenation is too long




    SQL> select x1 || x2 from gg;
    select x1 || x2 from gg
    *
    ERROR at line 1:
    ORA-01489: result of string concatenation is too long


    insert into g
    select UTL_RAW.CAST_TO_RAW(x1 || x2) from GG
    ERROR at line 1:
    ORA-01489: result of string concatenation is too long

    Up to now I've always made the programmers write info to these data types. I'm trying to write up to four 4000 character varchar values to a Long Raw. Am I doing something wrong or can this not be done the way I think it can?

  2. #2
    Join Date
    Nov 2006
    Location
    Indianapolis
    Posts
    90
    You should use Clobs instead of long raw

    All of the ora-01489 errors are because in each case, you are trying to concatenate varchars (they are still varchars before you convert them)

    Take a look at dbms_lob package

  3. #3
    Join Date
    Dec 2002
    Location
    Sunny Florida
    Posts
    121
    I'll look in to that package.

    If I try
    insert into g
    select UTL_RAW.CAST_TO_RAW(UTL_RAW.CAST_TO_RAW(x1) || UTL_RAW.CAST_TO_RAW(x2)) from GG;

    I get

    ERROR at line 1:
    ORA-06502: PL/SQL: numeric or value error: raw variable length too long
    ORA-06512: at line 1

    Any clue?

  4. #4
    Join Date
    Nov 2006
    Location
    Indianapolis
    Posts
    90
    I'm just guessing that cast_to_raw is going to raw, and not long raw

    Raw is limited to 2000

  5. #5
    Join Date
    Nov 2006
    Location
    Indianapolis
    Posts
    90
    There's probably lots of ways to do this, this is one. It works because the concats at end are all CLOBs

    PHP Code:
    --Ora10r2create table gg (x1 varchar2(4000), x2 varchar2(4000), x3 varchar2(4000), x4 varchar2(4000));

    Table created.

    --
    Ora10r2insert into gg select rpad('a'4000'a'), rpad('b'4000'b'), rpad('c'4000'c'), rpad('D'4000'D'from dual;

    1 row created.

    --
    Ora10r2create table gg_clob (c1 clobc2 clobc3 clobc4 clob);

    Table created.

    --
    Ora10r2insert into gg_clob (select from gg);

    1 row created.

    --
    Ora10r2create table one_clob (bigclob clob);

    Table created.

    --
    Ora10r2insert into one_clob (select c1 || c2 || c3 || c4 from gg_clob);

    1 row created

Posting Permissions

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