Results 1 to 7 of 7
  1. #1
    Join Date
    Jun 2003

    Unanswered: How can I get the size of a long raw

    Does anyone knows how can I get the current size of a long raw?

    I need to know this data in order to avoid my information get truncated while inserting into the table. I've heard that the use of LOB is preferred but right know I cant afford the data migration to use that type and thus I have to investigate how to do it.

    I've read in this same forum the suggestion to use dbms_lob.getlength() with this note:

    You may try oracle package function dbms_lob.getlength(). It should work with long raw data type by converting the type carefully considering the character set that you are using.
    My question regarding this answer is ... how can I convert the type carefully, what does this answer means.

    Thank you in advance.

    The message

  2. #2
    Join Date
    Apr 2003
    Greenville, SC (USA)
    Get the size of a LONG RAW by using the function



  3. #3
    Join Date
    Jun 2003
    I follow your instructions but I get this error

    ORA-00997: illegal use of LONG datatype

    I have a very short experience with oracle, but as I see it appears that the column is not allowed with this function.

    Could you explain further ?


  4. #4
    Join Date
    Apr 2003
    Greenville, SC (USA)
    Here's an example using PL/SQL (run from SQLPlus)....
    This code defines a cursor to read a row (or many rows) and loads
    the long raw column into a variable. I then set the value of another
    variable to the result of the utl_raw.length function (which is the
    length of the long raw column. I use dbms_output.put_line to
    display the result to the screen:

    set serveroutput on --- Set this so dbms_output can write to screen
    v_longcol long raw;
    v_size number;

    cursor get_row is
    select bits
    from table_holding_long_column
    where primary_key = '';


    open get_row;
    fetch get_row into v_longcol;
    exit when get_row%notfound;
    v_size := utl_raw.length(v_longcol); --- loads lenght of long raw column into v_size
    dbms_output.put_line(v_size); --- displays lenght of column
    fetch get_row into v_longcol;
    end loop;
    close get_row;

  5. #5
    Join Date
    Jun 2003
    Thanks for the code.
    The first time I try the function like this
    select utl_raw.LENGTH( instancedata ) from pprocinstance where rownum < 2

    After sustitute the columns and table names in your script I get an error that sound similar to me...

    Do you think the oracle version would affect on this behavior ? I have
    oracle running on HP-UX 11.0

    the result is this
      2   v_longcol LONG RAW;
      3   v_size NUMBER;
      4   CURSOR get_row IS
      5   SELECT instancedata
      7   WHERE ROWNUM < 2;
      8   BEGIN
      9   OPEN get_row;
     10   FETCH get_row INTO v_longcol;
     11   LOOP
     12   EXIT WHEN get_row%NOTFOUND;
     13   v_size := utl_raw.LENGTH(v_longcol); --- loads lenght of long raw column into v_size
     14   dbms_output.put_line(v_size); --- displays lenght of column
     15  -- FETCH get_row INTO v_longcol;
     16   END LOOP;
     17   CLOSE get_row;
     18   END;
     19  /
    ERROR at line 1:
    ORA-06502: PL/SQL: numeric or value error
    ORA-06512: at line 10
    BTW I use rownum in the where clause because it was easier. :P
    What do you get ?
    Do you think I need some patch or something ?

  6. #6
    Join Date
    Jun 2003
    gbrabham: I've read ( when start to look for informtation )
    that using that function would be useful only when the data is less than 32k, it does not make sanse to me , and that why I kept looking for more information.

    Here's the link FYI.

    Thanks a lot by being the only one replying.... :P
    Remove the char in the url to get this.
    Last edited by Zkr Ryz; 06-25-03 at 17:26.

  7. #7
    Join Date
    Dec 2003
    Hi, did you find a solution?
    I have the same problem.

Posting Permissions

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