CODEWITHSUNDEEP
sqloracle-database
user3528745
user3528745

Reputation: 27

How to query hugeblob data

I wanted to query to the hugeblob attribute in a table. I have tried below, but it doesnt give any data while selecting. select DBMS_LOB.substr(mydata, 1000,1) from mytable;

Is there any other to do this?

Upvotes: 0

Views: 2480

Answers (1)

Jagrut Sharma
Jagrut Sharma

Reputation: 4754

DBMS_LOB.substr() is the right function to use. Ensure that there is data in the column.

Example usage:

-- create table
CREATE TABLE myTable (
id INTEGER PRIMARY KEY,
blob_column BLOB
);

-- insert couple of rows
insert into myTable values(1,utl_raw.cast_to_raw('a long data item here'));
insert into myTable values(2,null);

-- select rows
select id, blob_column from myTable;

ID  BLOB_COLUMN
1   (BLOB)
2   null

-- select rows
select id, DBMS_LOB.substr(blob_column, 1000,1) from myTable;

ID  DBMS_LOB.SUBSTR(BLOB_COLUMN,1000,1)
1   61206C6F6E672064617461206974656D2068657265
2   null

-- select rows
select id, UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.substr(blob_column,1000,1)) from myTable;

ID  UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(BLOB_COLUMN,1000,1))
1   a long data item here
2   null

Upvotes: 1

Related Questions