Re: DYNAMIC SQL AND BIND VARS.

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_at_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