Hi all,
I have written the following procedure with takes 3 input parameters and the 4th is an out parameter (i.e v_tab_hits) which is of TYPE PL/SQL TABLE.
I am stuck with the problem of retrieving the values from v_tab_hits in my Java program, any help is appreciated.
PSB: the Code Snippet
-----------------------------------Procedure -----------------------------------------------
TYPE rec_hits IS RECORD ( v_period VARCHAR2(100),
v_vis_cnt NUMBER(10),
v_hit_cnt NUMBER(10));
TYPE tab_hits IS TABLE of rec_hits INDEX BY BINARY_INTEGER; PROCEDURE get_Hit_Daily( v_country IN VARCHAR2, v_sdate IN VARCHAR2, v_edate IN VARCHAR2,
v_tab_hits OUT cca_jtac_pk.tab_hits) AS
i NUMBER := -1;
CURSOR curec_hits is
SELECT to_char(access_date,'MM/DD/YYYY') AS access_date,
count(distinct(visitor_id)) AS visitors,
sum(TOTAL_HITS) AS total_hits
FROM CCO_TAC_VISITORS
WHERE country = v_country AND
access_date BETWEEN v_sdate AND v_edate
GROUP BY access_date;
BEGIN
OPEN curec_hits;
LOOP
i := i + 1;
FETCH curec_hits INTO v_tab_hits(i).v_period,v_tab_hits(i).v_vis_cnt,v_tab_hits(i).v_hit_cnt;
EXIT WHEN curec_hits%NOTFOUND;
END LOOP;
CLOSE curec_hits;
END get_Hit_Daily;
--------------------------------Java Code -------------------------------------------
CallableStatement cs = con.prepareCall("CALL cca_jtac_pk.get_Hit_Daily(?,?,?,?)");
//rset = stmt.executeQuery();
cs.setString( 1, country );
cs.setDate( 2, new java.sql.Date( fromDate.getTime() ) );
cs.setDate( 3, new java.sql.Date( toDate.getTime() ) );
//---------Q: What will be the Out Type Here......? cs.registerOutParameter(4, Types.ARRAY );
cs.execute();
//----Q: How will I fetch data from v_tab_hits in Java Code
k.puthran@zensar.com [ March 02, 2004: Message edited by: Kirtikumar Puthran ]
[ March 02, 2004: Message edited by: Kirtikumar Puthran ]