Excuse me immediate

From: mr <rogel_at_web.de>
Date: Wed, 19 Feb 2003 21:30:25 +0100
Message-ID: <b30pp2$1gfk1v$1@ID-86071.news.dfncis.de>

hallo,

> Hi Matthias,
>
> It needed to be 'INTO MyResult USING TextToFind' otherwise it won't
> compile :o)

of course U are right

>
> However, it's going to hard parse every time anyway as the query expands
> out to have a different column name each time, and a different table
> name (less often) at others, so I'm afraid parsing is what we get stuck
> with because the following is (unfortunately) not allowed :
>
> EXECUTE IMMEDIATE 'select 1 from dual where exists '||
> '(select 1 from :table '||
> ' where :column like ''%'' ||
> :TextToFind || ''%'')'
> INTO MyResult
> USING x.table_name, x.column_name,
> TextToFind;
>

of course not.

But the version with

         EXECUTE IMMEDIATE 'select 1 from dual where exists '||
                                   '(select 1 from '||x.table_name||
                                   ' where '||x.column_name||' like

''%'' || :TextToFind || ''%'')' INTO MyResult USING TextToFind;

has the advantage that a second call to your procedure FindText will most likely only soft parse each of this statements.

Golden rule:
*ALWAYS* use bind variables *WHENEVER POSSIBLE*

matthias Received on Wed Feb 19 2003 - 14:30:25 CST