Fetching Data from PL-SQL Table as OUT parameter in Java

[Kirtikumar Puthran]

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


[e-mail]
[ March 02, 2004: Message edited by: Kirtikumar Puthran ]
[ March 02, 2004: Message edited by: Kirtikumar Puthran ]

[Mark Spritzler]

Kirti, good question, although I don't think it is a Servlets question.
I am going to move this to the JDBC forum, because I think it is more database general than Oracle specific.
Good Luck
Mark