Home » SQL & PL/SQL » SQL & PL/SQL » using a VARRAY inside a WHERE clause (oracle10)
using a VARRAY inside a WHERE clause [message #284693] Fri, 30 November 2007 10:25 Go to next message
roger_at_alu
Messages: 3
Registered: November 2007
Junior Member
hello,

I have a function which returns a VARRAY of INTEGERS,
and I have a procedure with a long SELECT statement, where I want to extend the WHERE clause with the following condition:
AND some_scalar IN my_varray
(my_varray := result_of_function_returning_varray)
I'm getting inconsistent datatypes: expected NUMBER got MY_ARRAY_TYPE

If I change the clause to
AND some_scalar IN (my_varray(1)..my_varray(my_varray.count))
then I'm getting statement ignored: invalid function

How can I solve this?

thanks!
Roger
Re: using a VARRAY inside a WHERE clause [message #284695 is a reply to message #284693] Fri, 30 November 2007 10:28 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>How can I solve this?
You can NOT "solve this".
VARRAY is a PL/SQL datatype & is NOT valid in a SQL statement.
Re: using a VARRAY inside a WHERE clause [message #284697 is a reply to message #284695] Fri, 30 November 2007 10:52 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
If it's inside a pl/sql procedure, you can cast the array to an pl/sql - table.

...
AND some_scalar IN (select * from table(my_varray))
...


Re: using a VARRAY inside a WHERE clause [message #284703 is a reply to message #284697] Fri, 30 November 2007 11:11 Go to previous messageGo to next message
roger_at_alu
Messages: 3
Registered: November 2007
Junior Member
this seems to compile at least, thanks!
Monday I will be trying to actually run it as well...
Re: using a VARRAY inside a WHERE clause [message #284706 is a reply to message #284697] Fri, 30 November 2007 11:13 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
@ThomasG

Only if it is a permanent type that is created with CREATE TYPE.

Regards
Michel
Re: using a VARRAY inside a WHERE clause [message #284707 is a reply to message #284706] Fri, 30 November 2007 11:15 Go to previous messageGo to next message
roger_at_alu
Messages: 3
Registered: November 2007
Junior Member
yes, it is created in the schema, outside of any package bodies or procedures... thanks, your reply makes me feel more confident now.
Re: using a VARRAY inside a WHERE clause [message #284713 is a reply to message #284707] Fri, 30 November 2007 11:31 Go to previous message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Quote:


Only if it is a permanent type that is created with CREATE TYPE.



I thought that something called MY_ARRAY_TYPE was declared somewhere to show up in an error message that way. Wink

Previous Topic: Modify query based on variable
Next Topic: remove duplicate rows in Hierarchical Queries
Goto Forum:
  


Current Time: Sun Dec 11 02:28:00 CST 2016

Total time taken to generate the page: 0.22319 seconds