aspose file tools*
The moose likes Servlets and the fly likes Fetching Data from PL-SQL Table as OUT parameter in Java Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Java » Servlets
Bookmark "Fetching Data from PL-SQL Table as OUT parameter in Java" Watch "Fetching Data from PL-SQL Table as OUT parameter in Java" New topic
Author

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

Kirtikumar Puthran
Ranch Hand

Joined: Mar 04, 2003
Posts: 37
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 ]

Regards,<br />Kirti
Mark Spritzler
ranger
Sheriff

Joined: Feb 05, 2001
Posts: 17257
    
    6

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


Perfect World Programming, LLC - Two Laptop Bag - Tube Organizer
How to Ask Questions the Smart Way FAQ
 
wood burning stoves
 
subject: Fetching Data from PL-SQL Table as OUT parameter in Java