SQL & PL/SQL

Hi,

Try this

select cast(blobfieldname as varchar2(2000)) test
from tablename;

Regards
Sankar

Hi Sankar,

Getting error :

ORA-00932: inconsistent datatypes: expected NUMBER got BLOB

Thanks!!

Why on earth are you storing text in a BLOB (binary) type of datatype?

Anyway... take a look at DBMS_LOB.CONVERTTOCLOB

and then the other DBMS_LOB functions to extract the strings from that CLOB, such as DBMS_LOB.SUBSTR

http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_lob.htm

Hi Rick,

Try This,

select UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(BLOB_column, 4000,1)) test from tablename;

Regards
Sankar

Thanks Snakar

That helped and now I can see the BLOB filed as character.

Now suppose if I want to extract a certain String from that BLOB fields , how can we acheive that.

e.g:

I have following entry in BLOB field :

12345 test Manager employe To : Cashier 123 By : Employee 456 ........

we now want to extract id that is coming after Employee ,i.e, 456

Can anyone help me on this.

Thansk!!

Have a look at regexp_substr and regexp_replace functions for pattern matching and string extraction. Alternatively, use the regular INSTR and SUBSTR string functions.

Hi All ,

I am using this query :

select UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(BLOB_column, 4000,1)) test from tablename;

to read a blob field.This is working fine in one DB , however the same is not working on another DB and showing me some encrypted values(small square brackets).

Can anyone help me this ?

How can i get this thing corrected?

Thanks !!

Is this could be beacuse of different DB settings? If yes , then what should i chk or do to rectify this.

I tried using only DBMS_LOB.Substr(Blob) , this gives me some data which seems to be in hex..somthing like 1F00011AB111E........

But stilll no sucess in getting the string .

Any suggestions..

Thanks!!

Yes. Provide more details and example data for us to work with.
You haven't even told us what database version you're using.
You haven't told us what the differences in setup for each database are.

We can't see any example of your issue so we can't magically create you an answer.

Hi BluShadow,

The Database details are :

1)Database Type : Oracle
2)Database Version : 10.2.0.4
3)Operating System : Windows (Working) ,SuSE Linux Enterprise Server 10 (x86_64)(Not working)

The only diiference herer is operating system.


I am not sure how to provide example data.

Thanks!!

In the working instance , after using UTL_RAW.Cast_to_varchar2(DBMS_LOB.susbstr(BLOB_Field,2000,1)) i can see output like :

Store:5678 Product iD :123 Employee ID :2345 Item Purchased..............

But in not working instance after using the same query , it gives me some junk data...


But the DBMS_LOB.susbstr(BLOB_Field) give the same output as described earlier .e.g: 100AF00020001110000AC.......