Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1004
  • Last Modified:

How to find number of pages in tiff images stored as Blobs in Oracle database

I would like to know an easy method (preferable using an SQL Query) to find the number of pages stored in each record having blob data containing tiff images.  Using a middle tier or client application to do this for our large databases running into several crores of records is not viable.

Thanks in advance
0
TG_Sekhar
Asked:
TG_Sekhar
  • 6
  • 4
1 Solution
 
pinkurayCommented:
try this:

select regexp_replace(<<your COLUMN name >>, '(.)*<</Type/Pages' || chr(10) || '/Count ([[:digit:]]+).*', '\2', 1, 1, 'n') Pages from <<your table>>;

0
 
TG_SekharAuthor Commented:
Dear Pinkuray,

Thanks for a quick revert.  I tried and am getting the following error:

ORA-00932: inconsistent datatypes: expected NUMBER got BLOB
0
 
pinkurayCommented:
ARE YOU ABLE RUN THIS:

SELECT utl_raw.cast_to_number(YOUR COLUMN)FROM YOURTABLE ;

0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
pinkurayCommented:
then try this:

SELECT regexp_replace(utl_raw.cast_to_number(YOUR COL), '(.)*<</Type/Pages'
  || chr(10)
  || '/Count ([[:digit:]]+).*', '\2', 1, 1, 'n') PAGES
FROM YOURTABLE;

0
 
TG_SekharAuthor Commented:
When I tried
then try this:

SELECT regexp_replace(utl_raw.cast_to_number(YOUR COL), '(.)*<</Type/Pages'
  || chr(10)
  || '/Count ([[:digit:]]+).*', '\2', 1, 1, 'n') PAGES
FROM YOURTABLE;,
I am getting the following error:

ORA-22835: Buffer too small for CLOB to CHAR or BLOB to RAW conversion (actual:57210, maximum:2000)
0
 
pinkurayCommented:
it is exceeding the limits.

0
 
pinkurayCommented:
is it a PDF stored in your column ?
0
 
TG_SekharAuthor Commented:
Blob contains TIFF images as I stated in my question.  The files are xxx.TIF files
0
 
pinkurayCommented:
yes ..

you can check this:
http://www.experts-exchange.com/Programming/Languages/Java/Q_23254555.html

this link has given from a JAVA program you can read the lines.

Once way is to use this java inside a PLSQL and get the page numbers
0
 
TG_SekharAuthor Commented:
The solution provided is acceptable.  However for the current needs we were requiring the information quickly and hence found an alternate workaround by taking some estimated pages based on size.
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

  • 6
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now