Path: news.easynews.com!newsfeed1.easynews.com!easynews.com!easynews!newsfeed.news2me.com!newsfeed.icl.net!newsfeed.fjserv.net!kibo.news.demon.net!news.demon.co.uk!demon!not-for-mail
From: "Jonathan Lewis" <jonathan@jlcomp.demon.co.uk>
Newsgroups: comp.databases.oracle.server
Subject: Re: DYNAMIC SQL AND BIND VARS.
Date: Thu, 10 Oct 2002 15:43:07 +0100
Lines: 76
Message-ID: <ao43lh$cik$1$8302bc10@news.demon.co.uk>
References: <69ad472f.0210100539.9c51b61@posting.google.com>
NNTP-Posting-Host: jlcomp.demon.co.uk
X-Trace: news.demon.co.uk 1034260977 12884 158.152.75.41 (10 Oct 2002 14:42:57 GMT)
X-Complaints-To: abuse@demon.net
NNTP-Posting-Date: Thu, 10 Oct 2002 14:42:57 +0000 (UTC)
X-MimeOLE: Produced By Microsoft MimeOLE V4.72.3110.3
X-Newsreader: Microsoft Outlook Express 4.72.3110.5
Xref: newsfeed1.easynews.com comp.databases.oracle.server:163730
X-Received-Date: Thu, 10 Oct 2002 07:42:52 MST (news.easynews.com)


You might have started an argument with this one.

It is using bind variables (for the rowstamp) -
and it is vastly better than:

> EXECUTE IMMEDIATE 'SELECT ' || LV_FIELD || ' FROM ' || LV_TABLENAME
>|| ' WHERE ROWSTAMP = ' || LV_ROWSTAMP
> INTO LV_VALUE;

It keeps the number of different SQL statements used
down to
    no. of lv_field_names  x  no. of lv_tablenames

It keeps the quantity of code written to a minimum.


However, if you have a very high pressure system
it is not the best way to code things, as this
will still require Oracle to go through a number
of steps of soft parsing - especially since the
'EXECUTE IMMEDIATE' call forces a parse call
to take place.



--
Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

Next Seminar dates:
(see http://www.jlcomp.demon.co.uk/seminar.html )

____USA__________November 7/9   (Detroit)
____USA__________November 19/21 (Dallas)
____England______November 12/14

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html





John Alen wrote in message
<69ad472f.0210100539.9c51b61@posting.google.com>...
>I have the following bit of code that will be called a lot within a
>package. Can anyone tell me if this is the making use of bind
>variables?
>
> FUNCTION F_GET_VAR_VALUE (LV_FIELD VARCHAR2
> ,LV_TABLENAME VARCHAR2
> ,LV_ROWSTAMP VARCHAR2)
> RETURN VARCHAR2 IS
>
> LV_VALUE VARCHAR2(2000);
>
> BEGIN
> EXECUTE IMMEDIATE 'SELECT ' || LV_FIELD || ' FROM ' || LV_TABLENAME
>|| ' WHERE ROWSTAMP = :ROWSTAMP'
> INTO LV_VALUE
> USING LV_ROWSTAMP;
>
> RETURN LV_VALUE;
>
> END F_GET_VAR_VALUE;
>
>Is this the best way doing this?
>
>Thanks in advance.
>
>John