I am creating a package for Oracle 8i. I need to pass a variable in
and use that in the SQL statement IN clause. This worked for a simple
equal statement but not for the IN.

'select * from CUSTOMERS where CUSTOMERS_CODE in (:x1) ' USING IDS;

where IDS is VARCHAR2 and = 999,1000

Thanks in advance

ndpace (AT) gmail (DOT) com wrote:

http://www.psoug.org
click on Morgan's Library
click on Conditions
scroll down to the "COMPLEX IN" Demo
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace 'x' with 'u' to respond)

none of these examples worked. I get an error that says:
PLS-00642: local collection types not allowed in SQL statements

As a reminder, I am on version 8i. I noticed the page with the
examples says Version 10.1-not sure if that is the example version or
the Oracle version

any other help?
thanks in advance

ndpace wrote:
Simply put, you can not use a PL/SQL type in a SQL statement. This has
nothing to do with the version.

But in general doing anything in 8i, unless it is your production
environment is a waste of time and doing anything in 8i while looking
at 10g instructions a near guarantee of grave disappointment. In the
future, posting the actual DML may help clarify what you are doing.
In this case it wasn't necessary.
--
Daniel A. Morgan
http://www.psoug.org
damorgan@x.washington.edu
(replace x with u to respond)

Any other suggestions?
Thanks, jim

ndpace (AT) gmail (DOT) com wrote:
: I am creating a package for Oracle 8i. I need to pass a variable in
: and use that in the SQL statement IN clause. This worked for a simple
: equal statement but not for the IN.

: 'select * from CUSTOMERS where CUSTOMERS_CODE in (:x1) ' USING IDS;

: where IDS is VARCHAR2 and = 999,1000

I'm sure google would turn up suggestions, since this is often discussed.

One technique is to pass the list (999,1000) as a string '999,1000', and
then use a string function such as the PL/SQL Oracle equivalent of C's
strstr() (I don't recall the name of the function, that is why I don't
give the name).

If strstr where available, you could do something like


where strstr(:X,to_char(CUSTOMERS_CODE)) is not null



--

This space not for rent.

Malcolm Dew-Jones wrote:
Why SUBSTR when you can feed it in directly?

http://www.psoug.org
click on Morgan's Library
click on Conditions
search for the "Complex IN Demo
--
Daniel A. Morgan
Relational theory is not something that is simply a nice-to-have.
http://www.psoug.org
damorgan@x.washington.edu
(replace x with u to respond)

DA Morgan (damorgan (AT) psoug (DOT) org) wrote:
: Malcolm Dew-Jones wrote:
: > ndpace (AT) gmail (DOT) com wrote:
: > : I am creating a package for Oracle 8i. I need to pass a variable in
: > : and use that in the SQL statement IN clause. This worked for a simple
: > : equal statement but not for the IN.
: >
: > : 'select * from CUSTOMERS where CUSTOMERS_CODE in (:x1) ' USING IDS;
: >
: > : where IDS is VARCHAR2 and = 999,1000
: >
: > I'm sure google would turn up suggestions, since this is often discussed.
: >
: > One technique is to pass the list (999,1000) as a string '999,1000', and
: > then use a string function such as the PL/SQL Oracle equivalent of C's
: > strstr() (I don't recall the name of the function, that is why I don't
: > give the name).
: >
: > If strstr where available, you could do something like
: >
: >
: > where strstr(:X,to_char(CUSTOMERS_CODE)) is not null

: Why SUBSTR when you can feed it in directly?

I didn't use SUBSTR, so I'm not sure what you mean.

--

This space not for rent.

Malcolm Dew-Jones wrote:
Sorry ... responded to the wrong post.
--
Daniel A. Morgan
Relational theory is not something that is simply a nice-to-have.
http://www.psoug.org
damorgan@x.washington.edu
(replace x with u to respond)