LOB Quick Reference

Home

Toolkit:

My OraFAQ Blog

Contact me

The two types of LOBs - Analogues to string manipulation - For BLOBs

The two types of LOBs

TypeDatatypesDescription
Internal LOBsBLOB, CLOB, NCLOB
  • Stored in the database
  • Can participate in transactions
  • Can be written to
External LOBsBFILE
  • Stored in the OS
  • Cannot participate in transactions
  • Access is read-only

Analogues to string manipulation - useful for CLOBs, NCLOBs

String manipulationExampleLOB equivalentNotes
Lengthlength(str)dbms_lob.getlength(lobpointer)
Substringsubstr(str,startpos,[len])dbms_lob.substr(lobpointer, [len], [offset])Note reversal of 2nd and 3rd arguments. offset is like startpos
Find in stringinstr(str,pattern,[startpos],[nth])dbms_lob.instr(lobpointer, pattern, [offset], [nth])"pattern" is in raw
Appendstr1 := str1 || str2dbms_lob.append(lobpointer1, lobpointer2)lobpointer1 and lobpointer2 must be internal LOBs; can't use in SQL
Set string value in table to nullupdate mytable set mystrfield = NULL;update mytable set myclobfield=EMPTY_CLOB(); update mytable set myblobfield=EMPTY_BLOB();The LOB data is set to Null, but the LOB locater in myclobfield is set to a valid pointer. dbms_lob functions will error out if passed a null pointer.
Compareif (str1 = str2) then...if ( dbms_lob.compare(lobloc1, lobloc2) = 0 ) then...

For BLOBs

Purposedbms_lob program
Like Substringdbms_lob.read(lobpointer IN BLOB/CLOB, length_to_read IN BINARY_INTEGER, offset IN INTEGER, buffer OUT RAW)

Links


Note: Proofread any scripts before using. Always try scripts on a test instance first. I'm not responsible for any damage, even if you somehow manage to make my scripts corrupt every last byte of your data, set your server on fire and serve you personally with an eviction notice from your landlord!
All scripts and tips © Natalka Roshak 2001-2005.
Enjoy the FREE tips folks...