Sheesh, how hard can this be:

IF p_code IN v_code_list THEN
v_query := v_query || ' AND p_code IN v_code_list';
END IF;


Does not like that. The IN list will be dynamic.......

Oracle is being picky here......

On Feb 17, 1:47*pm, Mtek <m... (AT) mtekusa (DOT) com> wrote:
More research: It gives error on - IF p_code IN v_code_list THEN

LINE/COL ERROR
--------
-----------------------------------------------------------------
20/16 PLS-00103: Encountered the symbol "V_CODE_LIST" when
expecting
one of the following:

Ho hum.....

On Feb 17, 1:47*pm, Mtek <m... (AT) mtekusa (DOT) com> wrote:
You might want to read here:

http://oratips-ddf.blogspot.com/2008...w-dynamic.html


David Fitzjarrell

On Feb 17, 2:05*pm, ddf <orat... (AT) msn (DOT) com> wrote:

Crap that is a lot of work to do this simple task....

On Feb 17, 2:10*pm, Mtek <m... (AT) mtekusa (DOT) com> wrote:
That's why it's called 'work'. As I said in the blog entry:

"The problem, though simple to state, isn't quite as simple to solve,
as proven above. That's because Oracle is a database, it isn't your
brain, so it can't draw on prior experience and know that '10,20,30'
is glorified shorthand for 10,20,30. And because it's software
(really, it is) running on a computer, it does what you tell it to do,
whether or not those instructions provide the output you'd intended.
Since it can't think like you do you'll have to start 'thinking' like
it does, and that may throw a wrench into your logical picture of the
situation. Knowing what to do with that wrench is half of the battle."

You've been provided the tools to get this task done. Unless you're
willing to pay for a consultant to come in and do this for you it's
now up to you to use those tools. Remember that we are here to help,
and we will.


David Fitzjarrell

Mtek (mtek (AT) mtekusa (DOT) com) wrote:
: On Feb 17, 2:05=A0pm, ddf <orat... (AT) msn (DOT) com> wrote:
: > On Feb 17, 1:47=A0pm, Mtek <m... (AT) mtekusa (DOT) com> wrote:
: >
: > > Sheesh, how hard can this be:
: >
: > > =A0 =A0 =A0IF p_code IN v_code_list THEN
: > > =A0 =A0 =A0 =A0 v_query :=3D v_query || ' AND p_code IN v_code_list';
: > > =A0 =A0 =A0END IF;
: >
: > > Does not like that. =A0The IN list will be dynamic.......
: >
: > > Oracle is being picky here......
: >
: > You might want to read here:
: >
: > http://oratips-ddf.blogspot.com/2008...w-dynamic.html
: >
: > David Fitzjarrell


: Crap that is a lot of work to do this simple task....

The only work is in reading through the numerous possibilities.

Personally I often find that INSTR is the easiest technique.


(untested snippet)

v_code_list := '~1~2~3~4~5~'; -- example of list as string

if instr( v_code_list , '~'||p_code||'~') > 0 then

On Feb 17, 7:47*pm, Mtek <m... (AT) mtekusa (DOT) com> wrote:
Try using collections and the MEMBER OF operator.
http://www.williamrobertson.net/docu...separated.html

Is there any programming language in which a comma-separated string
works the way you just made up?

On Feb 18, 6:54*am, William Robertson <williamr2... (AT) googlemail (DOT) com>
wrote:
I guess not.