Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2004
    Location
    India
    Posts
    3

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

    Hi,

    I am new to Java Programming.

    I have written the following procedure with takes 3 input parameters and the 4th is out parameter (i.e v_tab_hits)
    which is of TYPE PL/SQL TABLE.

    I am stuck with the problem of retriving the values from v_tab_hits in my Java program, any help is
    appriciated.

    PSB: the Code Snippet

    Regards,
    Bharat

    -----------------------------------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_t ab_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

  2. #2
    Join Date
    Jan 2004
    Posts
    370

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •