How do you search through multiple tables?

From: Norman Dunbar <Norman.Dunbar_at_lfs.co.uk>
Date: Thu, 20 Feb 2003 11:11:36 -0000
Message-ID: <E2F6A70FE45242488C865C3BC1245DA703547C61@lnewton.leeds.lfs.co.uk>

Hi Matthias,

sorry - I get you now !

You mean when the user calls my procedure again, all the existing SQL in the cache may be able to be used again without a hard parse, in the situation where the user ends up seraching the same columns in the same tables as on the previous run !

Yes, I agree - use bind variables wherever possible,

Cheers,
Norman.

---

Norman Dunbar
Database/Unix administrator
Lynx Financial Systems Ltd.
mailto:Norman.Dunbar_at_LFS.co.uk

Tel: 0113 289 6265
Fax: 0113 289 3146
URL: http://www.Lynx-FS.com


-------------------------------------

-----Original Message-----
From: mr [mailto:rogel_at_web.de]
Posted At: Wednesday, February 19, 2003 8:30 PM Posted To: server
Conversation: How do you search through multiple tables? Subject: Excuse me immediate

hallo,

<SNIP>

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 Thu Feb 20 2003 - 05:11:36 CST