Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Passing list of values to stored proc for in condition

899681Nov 11 2011 — edited Nov 11 2011
Hello.

I am trying to write a stored procedure which will be called from a java app using JDBC. The procedure queries a table which stores information about requests made to a web site, and the goal is to retrieve average response times, which will be graphed on the front end. Anyway, I have the proc almost written, but I am struggling with one piece. In the code below, I have bolded the line where I need help. I would like to be able to pass the proc a list of SERVER's, which can then be used in the "in" clause. At the moment it's hard coded. The length of the list will be at least 1, but may contain many (up to 10).

CREATE OR REPLACE PROCEDURE prc_get_avg_resp_time_data(v_application IN VARCHAR2,
v_activity_date IN DATE := SYSDATE,
v_retrc OUT SYS_REFCURSOR) IS
BEGIN
OPEN v_retrc FOR
SELECT TO_CHAR(TIME_OF_REQUEST, 'MI-HH24-DD-MM-YYYY') AS TIME,
SERVER,
AVG(RESPONSE_TIME) AS AVERAGE
FROM STATS_TABLE
WHERE trunc(TIME_OF_REQUEST) = trunc(v_activity_date)
AND APPLICATION = v_application
AND SERVER in ('server1','server2')
GROUP BY TO_CHAR(TIME_OF_REQUEST, 'MI-HH24-DD-MM-YYYY'), SERVER
ORDER BY SERVER, TIME;
END prc_get_avg_resp_time_data;

Any help would be much appreciated.

Thanks,

Ronan
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Dec 9 2011
Added on Nov 11 2011
6 comments
712 views