0

storing filedata via a hibernate (using postgresql or oracle 10g/11g) property mapped as

        <property name="fileData" type="binary">
            <column name="fileData" length="104857600" />
        </property>

from a java application i need to access the length of the field from the db (i do not want to load whole the object, for performance reasons). i did not find any hibernate (hql) solution to query that information, so i decided to search for features of the different databases that were used. for postgresql i found:

select BIT_LENGTH(filedata) from table

which works perfectly (and astonishing fast). now i need something similar for the use with oracle. i already tried 

select utl_raw.length(filedata) from table

and select DBMS_LOB.GETLENGTH(filedata) from table

which both results in the error msg:

"Error: ORA-00997: illegal use of LONG datatype"

is there any possibilty to query the length of that hibernate property on an oracle db without selecting the object itself?

(hibernate creates a "long raw" field on oracle, and a "bytea" field on postgresql)

thx in advance

(addition: statement will be used in a migration-context, newly created filedata entities will get a filesize property programatically)

hage
  • 95
  • 11
  • `104857600` length's field - it is very powerful – Andremoniy Feb 20 '15 at 08:45
  • content length of the field is dynamic, 104857600 is just the upper bound for that field – hage Feb 20 '15 at 08:49
  • possible duplicate of [Get the LENGTH of a LONG RAW](http://stackoverflow.com/questions/5497238/get-the-length-of-a-long-raw) – Lalit Kumar B Feb 20 '15 at 09:00
  • it's possible i just don't get it (i'm do not have much oracle knowledge), but "SELECT my_long_raw_column FROM my_table WHERE id = ?" from the mentioned answer will load the complete long raw / bytearray which is what i want to prevent my code from. pl/sql is not an option for me also, at least afaik – hage Feb 20 '15 at 11:25

1 Answers1

0

Well, looks like i found an answer here: https://community.oracle.com/thread/2137593

create table temp_deleteme as select to_lob(<long raw field>) obj from <tablename>;
select dbms_lob.getlength(obj) from temp_deleteme;

after creating the tempTable i can select the filedatalength from it, write it to my entities, and delete the table again. still not really good performance, but that let the db do the work and i will not have to transfer entities to my server for only calculating the needed information.

hage
  • 95
  • 11
  • you can use a database function to avoid using temp table https://community.oracle.com/thread/657507 – psaraj12 Feb 20 '15 at 14:30