using clob in where clause

[gupta.harika@googlemail.com]

Hi everyone,

I am a developer working on php with oracle as backend.
I am facing a problem related with the CLOB data.
The problem is as follows
My application uses a table which contains Clob datatype.
I need to do a search on this table based on the clob data,i.e., I am
using the column which is a clob datatype in the where clause.
The query is as follows
" select * from mytable where (dbms_lob.instr (mybody, 'as') > 0)"
If the result set has less records then the data gets displayed in the
front end page of the application...
If the result set contains more records and the query takes more that
30 seconds to execute in the backend the data does not get displayed in
the frontend page of the application.
I get an error telling
"Fatal error: Maximum execution time of 30 seconds exceeded."
Can any one of you please help me out in solving this problem.

Thanks in advance.

[Steve]

Re: using clob in where clause

[color=blue]
> If the result set contains more records and the query takes more that
> 30 seconds to execute in the backend the data does not get displayed in
> the frontend page of the application.
> I get an error telling
> "Fatal error: Maximum execution time of 30 seconds exceeded."
> Can any one of you please help me out in solving this problem.[/color]

set_time_limit( 0);

See <http://www.php.net/set_time_limit> .

---
Steve

[Andy Hassall]

Re: using clob in where clause

On 1 Mar 2006 04:20:42 -0800, gupta.harika@go oglemail.com wrote:
[color=blue]
>I am a developer working on php with oracle as backend.
>I am facing a problem related with the CLOB data.
>The problem is as follows
>My application uses a table which contains Clob datatype.
>I need to do a search on this table based on the clob data,i.e., I am
>using the column which is a clob datatype in the where clause.
>The query is as follows
>" select * from mytable where (dbms_lob.instr (mybody, 'as') > 0)"
>If the result set has less records then the data gets displayed in the
>front end page of the application...
>If the result set contains more records and the query takes more that
>30 seconds to execute in the backend the data does not get displayed in
>the frontend page of the application.
>I get an error telling
>"Fatal error: Maximum execution time of 30 seconds exceeded."
>Can any one of you please help me out in solving this problem.[/color]

Querying the contents of a CLOB like this involves full table scans, and even
worse, accessing all of each of the CLOBs. This is expensive, and will
inevitably take a long time.

If you want to query for text within a CLOB more quickly, you should probably
consider using Oracle Text.

Follow-ups set to comp.databases. oracle.server.

--
Andy Hassall :: andy@andyh.co.u k :: http://www.andyh.co.uk
http://www.andyhsoftware.co.uk/space :: disk and FTP usage analysis tool

[gupta.harika@googlemail.com]

Re: using clob in where clause


Steve wrote:[color=blue][color=green]
> > If the result set contains more records and the query takes more that
> > 30 seconds to execute in the backend the data does not get displayed in
> > the frontend page of the application.
> > I get an error telling
> > "Fatal error: Maximum execution time of 30 seconds exceeded."
> > Can any one of you please help me out in solving this problem.[/color]
>
> set_time_limit( 0);
>
> See <http://www.php.net/set_time_limit> .
>
> ---
> Steve[/color]

Hi Steve,

Thanks a lot for the help u provided me...
I copied set_time_limit( 0) in the php file that was displaying the
search results and all worked fine...
Now the page is not breaking....

Harika