• Post Reply Bookmark Topic Watch Topic
  • New Topic

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

 
Kirtikumar Puthran
Ranch Hand
Posts: 37
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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 ]
 
Mark Spritzler
ranger
Sheriff
Posts: 17309
11
IntelliJ IDE Mac Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
What are you doing? You are supposed to be reading this tiny ad!
the new thread boost feature brings a LOT of attention to your favorite threads
https://coderanch.com/t/674455/Thread-Boost-feature
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!