How to get the maximum length of a column with datatype long?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: How to get the maximum length of a column with datatype long?

  1. #1
    Join Date
    Apr 2001
    Location
    Congleton
    Posts
    258
    How do I get the maximum length or size of a column with a datatype long?
    select vsize(select_string) from table

    returns:
    ERROR at line 1:
    ORA-00997: illegal use of LONG datatype
    Thanks.

  2. #2
    Join Date
    Sep 2001
    Location
    NJ, USA
    Posts
    1,287
    u have to use dbms_lob.getlength function.

    SQL> select dbms_lob.getlength(text) from how_to_.....readlob;

    DBMS_LOB.GETLENGTH(TEXT)
    ------------------------
    28

  3. #3
    Join Date
    Apr 2001
    Location
    Congleton
    Posts
    258
    SQL> select dbms_lob.getlength(select_string) from output_table;
    select dbms_lob.getlength(select_string) from output_table
    *
    ERROR at line 1:
    ORA-00997: illegal use of LONG datatype

    I know you need to use UTL_RAW, but how do I get the maximum for the table? and How do I get the size in Bytes? Thanks.

  4. #4
    Join Date
    Apr 2001
    Location
    Congleton
    Posts
    258
    I have found the solution if anyone is interested:
    DECLARE
    vRaw number;
    v_max_len number;

    CURSOR a1 is
    SELECT select_string
    FROM output_table;

    BEGIN
    vRaw :=0;
    v_max_len := 0;

    FOR arec in a1 LOOP

    vRaw := utl_raw.length(arec.select_string);

    if vRaw > v_max_len then
    v_max_len := vRaw;
    end if;

    END LOOP;
    dbms_output.put_line('Max Length is '||v_max_len);
    END;
    /


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