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

    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
    http://dbforums.com/showthread.php?threadid=762428

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

    utl_raw.length()

    Gregg

  3. #3
    Join Date
    Jun 2003
    Posts
    4
    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 ?

    Thanks.

  4. #4
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    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
    declare
    v_longcol long raw;
    v_size number;

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

    begin

    open get_row;
    fetch get_row into v_longcol;
    loop
    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;
    end;
    /

  5. #5
    Join Date
    Jun 2003
    Posts
    4
    Thanks for the code.
    The first time I try the function like this
    Code:
    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 8.1.7.2 running on HP-UX 11.0


    the result is this
    Code:
    SQL>  DECLARE
      2   v_longcol LONG RAW;
      3   v_size NUMBER;
      4   CURSOR get_row IS
      5   SELECT instancedata
      6   FROM  PPROCINSTANCE
      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  /
     DECLARE
    *
    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
    Posts
    4
    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


    http://asktom.oracle.com/pls/ask/f?p...A:376618219530
    Remove the char in the url to get this.
    ask/f?p=49.....F4950_P8_CRITERIA:37....
    Last edited by Zkr Ryz; 06-25-03 at 18:26.

  7. #7
    Join Date
    Dec 2003
    Posts
    1
    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
  •