Passing list of values to stored proc for in condition
899681Nov 11 2011 — edited Nov 11 2011Hello.
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