Welcome Guest ( Log In | Register )


 
 
 
 
 
 

 
 
Oracle 

Performance Tuning Reference poster
 
Oracle training in Linux 

commands
 
Oracle training Weblogic Book
 
Easy Oracle Jumpstart
 
Oracle training & performance tuning books
 
Burleson Consulting Remote DB Administration
 
 
 
Reply to this topicStart new topic
> Using Array Values in IN clause of a select Query, Using Array Values in IN clause of a select Query
ArunaG
post Sep 9 2008, 07:03 AM
Post #1


Newbie
*

Group: Members
Posts: 5
Joined: 9-September 08
Member No.: 34,787



Hi Sir,

Good Morning. I have some issues in passing array values to IN clause.

I am passing a String Array from Java to PL\SQL and want to use the Array values in the IN CLAUSE of Select Query

cust_array is the Array
search_id VARCHAR2(1000);

search_id := '';
FOR j IN 1 .. cust_array.count
LOOP
IF (j != 1) THEN
search_id := search_id || ''',''' || cust_array(j) || ''';
ELSE
search_id := search_id || '''' || cust_array(j) || '''';
END IF;
END LOOP;

trying to form a string of below form: search_id '3211335201','3211335209','3211335279','3211335509'

and use the string search_id in the IN clause of the search Query select * from DPP_EMP where empl in (search_id)

but the query does not returns any result

When I try to hardcode the values in the query as below, its returing 4 rows

select * from DPP_EMP where empl in ('3211335201','3211335209','3211335279','3211335509')

How to achieve this (String to the IN clause) or is there a better way of passing the Array values to the IN clause

Thanks In Advance!
Ur help will be greatfull! Kindly looking forward for your Reply!

Regards,
Aruna
Go to the top of the page
 
+Quote Post
ArunaG
post Sep 9 2008, 09:26 AM
Post #2


Newbie
*

Group: Members
Posts: 5
Joined: 9-September 08
Member No.: 34,787



hi Andrew,

Thxs for the reply.

Even if I pass the string directly

search_id := '3211335201'',''3211335209'',''3211335279'',''3211335509';

to the where clause

select * from DPP_EMP where empl in (search_id)

its not interpreting the string values correctly.

I suspect there is some issue with the Apostrophe. In PL\SQL, the strings will be enclosed with Apostrophe.
so there is some issue with the String formation.

Kindly Help!

Thanks & Regards,
Aruna
Go to the top of the page
 
+Quote Post
acesiowa
post Sep 9 2008, 04:34 PM
Post #3


Advanced Member
***

Group: Members
Posts: 42
Joined: 24-January 08
Member No.: 16,432



QUOTE (andrew kerber @ Sep 9 2008, 07:55 AM) *
What about when you assign a variable to cust_array.count, eg
i:=cust_array.count
for j=1..i

Does it work then? This is really a debugging exercise, not an Oracle exercise.


In PL/SQL, within a string value, two apostrophe is equal to one apostrophes. Example:

execute immediate
'select to_char(sysdate,' ' Month, DD YYYY ' ')
from dual'
into <var>;

QUOTE (ArunaG @ Sep 9 2008, 09:27 AM) *
hi Andrew,

Thxs for the reply.

Even if I pass the string directly

search_id := '3211335201'',''3211335209'',''3211335279'',''3211335509';

to the where clause

select * from DPP_EMP where empl in (search_id)

its not interpreting the string values correctly.

I suspect there is some issue with the Apostrophe. In PL\SQL, the strings will be enclosed with Apostrophe.
so there is some issue with the String formation.

Kindly Help!


Thanks & Regards,
Aruna




Try search_id := ' ' '3211335201' ',' '3211335209' ',' '3211335279' ',' '3211335509' ' ';

Note: There is no space between each single quote. And the example does not user a double quote.


--------------------
acesiowa
Go to the top of the page
 
+Quote Post
ArunaG
post Sep 10 2008, 12:39 AM
Post #4


Newbie
*

Group: Members
Posts: 5
Joined: 9-September 08
Member No.: 34,787



Hi,

I have tried the below one

search_id := ' ' '3211335201' ',' '3211335209' ',' '3211335279' ',' '3211335509' ' ';

but the Query is not returning any result.

When I tried with a single value like

search_id := ' 3211335201' ;

in select * from DPP_EMP where empl in (search_id)

I am getting the result for '3211335201'

Only when multiple values are passed, the query is not returning any result.

Please Advice.

Thanks & Regards,
Aruna
Go to the top of the page
 
+Quote Post
ArunaG
post Sep 10 2008, 10:42 AM
Post #5


Newbie
*

Group: Members
Posts: 5
Joined: 9-September 08
Member No.: 34,787



Hi,

I have tried using OR, but still the query is not returning the results.

search_id := ''''||'9171082133393211335201'|| ','||''''||'9171082133393211335270'||'''';

and used search_id as

select * from DPP_EMP where empl in (search_id)


Kindly help.

Thanks & Regards,
Aruna
Go to the top of the page
 
+Quote Post
oracleonsun
post Sep 10 2008, 01:27 PM
Post #6


Advanced Member
***

Group: Members
Posts: 345
Joined: 24-November 05
From: London, England
Member No.: 3,317



You don't mention how many elements you may need to deal with.

How about either using your list to populate a global temporary table and querying that as a subselect in your main query.

Or a more scalable and faster solution is populate a user defined pl/sql array with your list, then use "IN table(cast (array_name))"

Barry
Go to the top of the page
 
+Quote Post
ArunaG
post Sep 11 2008, 01:35 AM
Post #7


Newbie
*

Group: Members
Posts: 5
Joined: 9-September 08
Member No.: 34,787



Hiii,

Thanks All sooo much!!!
table(cast (array_name)) has worked sooo well!!!

Thxs A Lot!

Cheers,
Aruna
Go to the top of the page
 
+Quote Post
Mohammed Ayyub
post Sep 18 2008, 12:14 AM
Post #8


Newbie
*

Group: Members
Posts: 1
Joined: 18-September 08
Member No.: 35,801



Hi oracleonsun,

I am also facing the same problem, can you please explain me giving a simple example, how can I use the TYPE in IN clause.

Thanks in advance
Go to the top of the page
 
+Quote Post
oracleonsun
post Sep 18 2008, 05:54 AM
Post #9


Advanced Member
***

Group: Members
Posts: 345
Joined: 24-November 05
From: London, England
Member No.: 3,317



create or replace type g_array as table of varchar2(50);

then

DECLARE
p_list g_array;
sString VARCHAR2(50);
BEGIN
p_list := g_array(ONE');
SELECT wgp_ids.column_value
INTO sString
FROM TABLE(CAST(p_list AS g_array)) wgp_ids;
END;
/

should give you enough to go on.
using table(cast()) you can then use wgp_ids as you would a normal table.

Barry
Go to the top of the page
 
+Quote Post
bandlamudi
post Jan 14 2010, 03:40 AM
Post #10


Newbie
*

Group: Members
Posts: 1
Joined: 14-January 10
Member No.: 42,464



QUOTE (ArunaG @ Sep 11 2008, 02:36 AM) *
Hiii,

Thanks All sooo much!!!
table(cast (array_name)) has worked sooo well!!!

Thxs A Lot!

Cheers,
Aruna



Hi Aruna Can you share the query or procedure which you have written I have same requirement here. Kindly reply ASAP.

The queries are like this

This query is not giving output.

select COLUMN_NAME,COMMENTS FROM USER_COL_COMMENTS WHERE TABLE_NAME LIKE 'SUPGLD'
AND COLUMN_NAME IN(cast(GETDEPARTMENTS('0063') as varchar2(4000)))

The following is giving output.

select COLUMN_NAME,COMMENTS FROM USER_COL_COMMENTS WHERE TABLE_NAME LIKE 'SUPGLD'
AND COLUMN_NAME IN('SUPCSTNO','SUPPANNO','SUPMSMENO','SUPSERVTAXNO','SUPEXCISEID','SUPTELNO','SUPFAXNO')

Cheers
Suresh
Go to the top of the page
 
+Quote Post
singhk
post Feb 5 2012, 11:42 AM
Post #11


Newbie
*

Group: Members
Posts: 1
Joined: 5-February 12
Member No.: 46,717



This solution works but is very slow.

tried multiple solutions all work but not acceptable performance

where
--exists (select 1 from TABLE(cast(r_sm as sm_list)) Where column_value=sub_market_Code) ---- Works but Very Slow
--rtrim(sub_market_code) in (select * from TABLE(cast(r_sm as SM_LIST))) ---- Works but Very Slow
--rtrim(sub_market_code) in (SELECT * FROM TABLE(cmgt.str_array('AL001','01801'))) ---- Works but Very Slow
--sub_market_Code in ('AL001','01801') --Works and fast but not dynamic and requires hard coded values, which is not acceptable.
Go to the top of the page
 
+Quote Post
burleson
post Feb 5 2012, 12:57 PM
Post #12


Advanced Member
***

Group: Members
Posts: 11,425
Joined: 26-January 04
Member No.: 13



Hi Singh,

Thanks for the feedback and solutions to this use or annay values on a CAST issue!

CODE
where sub_market_Code in ('AL001','01801') --Works and fast but not dynamic and requires hard coded values, which is not acceptable.

where exists (select 1 from TABLE(cast(r_sm as sm_list))

Where column_value=sub_market_Code) ---- Works but Very Slow

--rtrim(sub_market_code) in (select * from TABLE(cast(r_sm as SM_LIST))) ---- Works but Very Slow

--rtrim(sub_market_code) in (SELECT * FROM TABLE(cmgt.str_array('AL001','01801'))) ---- Works but Very Slow


--------------------
Hope this helps. . .

Donald K. Burleson
Oracle Press author
Author of Oracle Tuning: The Definitive Reference
Go to the top of the page
 
+Quote Post
Arahanth
post Sep 25 2012, 01:14 AM
Post #13


Newbie
*

Group: Members
Posts: 1
Joined: 25-September 12
Member No.: 47,710



QUOTE (ArunaG @ Sep 11 2008, 02:35 AM) *
Hiii,

Thanks All sooo much!!!
table(cast (array_name)) has worked sooo well!!!

Thxs A Lot!

Cheers,
Aruna



I am facing similar problem. Can you please share the code where you created a custom array and used the same in the table(cast(array_name)). I am getting a PL/SQL: ORA-00936: missing expression at the table(cast())) line.
Go to the top of the page
 
+Quote Post
burleson
post Sep 25 2012, 08:42 AM
Post #14


Advanced Member
***

Group: Members
Posts: 11,425
Joined: 26-January 04
Member No.: 13



Hi Arahanth,

>> Can you please share the code where you created a custom array and used the same in the table(cast(array_name)).

I found this example by Jim:


http://www.databasejournal.com/features/or...ns-in-PLSQL.htm


--------------------
Hope this helps. . .

Donald K. Burleson
Oracle Press author
Author of Oracle Tuning: The Definitive Reference
Go to the top of the page
 
+Quote Post

Reply to this topicStart new topic
1 User(s) are reading this topic (1 Guests and 0 Anonymous Users)
0 Members:

 

Lo-Fi Version Time is now: 29th August 2014 - 03:00 AM