Results 1 to 1 of 1
  1. #1
    Join Date
    Dec 2003
    Location
    USA
    Posts
    33

    Unanswered: Returning recordset in SP

    Hi,

    Below is my code. Presently it is writing the output in the console. I want to return the result to the calling method in java. Kindly let me how do i implement this.

    Code:
    PROCEDURE SP_GET_SUMMARY (cycle_id IN NUMBER, business_id IN NUMBER)
    AS
    -- This cursor holdes the parameter and dimensions, which are to be included in the calculation, for a perticular KRI
       CURSOR getkriparamdim (kriidpk IN NUMBER)
       IS
          SELECT a.kri_id_pk, b.param_id_pk, c.dim_id_pk
            FROM kri a, kri_param b, kri_dim c
           WHERE a.kri_id_pk = b.kri_id_pk
             AND a.kri_id_pk = c.kri_id_pk
             AND b.calc_include = 'Y'
             AND a.kri_id_pk = kriidpk;
    
    -- This cursor get collect_cycle_id_pk between the specified time frame (Months), i.e startPeriod to startPeriod - 12
       CURSOR getkriperiod (startperiod IN DATE)
       IS
          SELECT a.collect_cycle_id_pk
            FROM kri_collect_cycle a
           WHERE a.cycle_date BETWEEN ADD_MONTHS (startperiod, -12) AND startperiod;
    
    -- This cursor holdes the kri_cycle_val for a combination of kri_ipk, collect_cycle_id_pk, param_id_pk and dim_id_pk
       CURSOR krisummary (
          kriidpk   IN   NUMBER,
          cycleid   IN   NUMBER,
          paramid   IN   NUMBER,
          dimid     IN   NUMBER
       )
       IS
          SELECT a.cycle_value
            FROM kri_cycle_val a
           WHERE a.kri_id_pk = kriidpk
             AND a.collect_cycle_id_pk = cycleid
             AND a.param_id_pk = paramid
             AND a.dim_id_pk = dimid;
    
    -- This cursor holdes the kri attributes for a perticular cycle_id and business_id
       CURSOR getkriattributes
       IS
          SELECT c.kri_id_pk d, c.kri_name, c.kri_is_lag, c.threshold_comprator,
                 c.threshold_val, c.threshold_desc, d.threshold_val,
                 d.threshold_desc, d.formula_id_pk, e.kri_data_avil_flag,
                 e.kri_comment
            FROM (SELECT a.kri_id_pk, a.org_unit_id_pk, a.kri_name, a.kri_is_lag,
                         b.threshold_comprator, b.threshold_type_id_pk,
                         b.threshold_val, b.threshold_desc, b.formula_id_pk
                    FROM kri a, kri_threshold_val b
                   WHERE a.kri_id_pk = b.kri_id_pk AND threshold_type_id_pk = 1) c,
                 (SELECT a.kri_id_pk, a.kri_name, a.kri_is_lag,
                         b.threshold_comprator, b.threshold_type_id_pk,
                         b.threshold_val, b.threshold_desc, b.formula_id_pk
                    FROM kri a, kri_threshold_val b
                   WHERE a.kri_id_pk = b.kri_id_pk AND threshold_type_id_pk = 2) d,
                 (SELECT a.kri_id_pk, a.kri_name, a.kri_is_lag,
                         b.kri_data_avil_flag, b.kri_comment
                    FROM kri a, kri_info b
                   WHERE a.kri_id_pk = b.kri_id_pk AND b.collect_cycle_id_pk = 1) e
           WHERE c.kri_id_pk = d.kri_id_pk
             AND c.kri_id_pk = e.kri_id_pk
             AND c.org_unit_id_pk = business_id;
    
    -- Local variables
       vkriidpk            kri.kri_id_pk%TYPE;
       vkriid              kri.kri_id_pk%TYPE;
       vkriname            kri.kri_name%TYPE;
       vkriislag           kri.kri_is_lag%TYPE;
       vkricomparator      kri_threshold_val.threshold_comprator%TYPE;
       vkriwarning         kri_threshold_val.threshold_val%TYPE;
       vkriwarningdesc     kri_threshold_val.threshold_desc%TYPE;
       vkrireporting       kri_threshold_val.threshold_val%TYPE;
       vkrireportingdesc   kri_threshold_val.threshold_desc%TYPE;
       vkriformula         kri_threshold_val.formula_id_pk%TYPE;
       vkridataavai        kri_info.kri_data_avil_flag%TYPE;
       vkrisummaryval      kri_cycle_val.cycle_value%TYPE               DEFAULT 0;
       vkrisummary         kri_cycle_val.cycle_value%TYPE               DEFAULT 0;
       vkricomments        kri_info.kri_comment%TYPE;
       vkridim             kri_dim.dim_id_pk%TYPE;
       vkriparam           kri_param.param_id_pk%TYPE;
       vkricycleid         kri_collect_cycle.collect_cycle_id_pk%TYPE;
       vkricycledate       kri_collect_cycle.cycle_date%TYPE;
    BEGIN
       OPEN getkriattributes;
    
       LOOP
          vkrisummary := 0;
    
          FETCH getkriattributes
           INTO vkriidpk, vkriname, vkriislag, vkricomparator, vkriwarning,
                vkriwarningdesc, vkrireporting, vkrireportingdesc, vkriformula,
                vkridataavai, vkricomments;
    
          EXIT WHEN getkriattributes%NOTFOUND;
    
          --DBMS_OUTPUT.put_line ('vkriidpk after fetching getkriattributes: ' || vkriidpk);
          OPEN getkriparamdim (vkriidpk);
    
          LOOP
             FETCH getkriparamdim
              INTO vkriid, vkriparam, vkridim;
    
             EXIT WHEN getkriparamdim%NOTFOUND;
    
             --DBMS_OUTPUT.put_line ('vkriid after fetching getkriparamdim: ' || vkriid);
             CASE vkriformula
                WHEN 2
                THEN
                   SELECT a.cycle_date
                     INTO vkricycledate
                     FROM kri_collect_cycle a
                    WHERE a.collect_cycle_id_pk = cycle_id;
    
                   --DBMS_OUTPUT.put_line ('Start Date: ' || vkricycledate);
                   OPEN getkriperiod (vkricycledate);
    
                   LOOP
                      FETCH getkriperiod
                       INTO vkricycleid;
    
                      EXIT WHEN getkriperiod%NOTFOUND;
    
                      --DBMS_OUTPUT.put_line ('Period ID: ' || vkricycleid);
                      OPEN krisummary (vkriid, vkricycleid, vkriparam, vkridim);
    
                      vkrisummaryval := 0;
    
                      FETCH krisummary
                       INTO vkrisummaryval;
    
                      CLOSE krisummary;
    
                      vkrisummary := vkrisummary + vkrisummaryval;
                   END LOOP;
    
                   vkrisummary := vkrisummary / 12;
    
                   CLOSE getkriperiod;
                ELSE
                   OPEN krisummary (vkriid, cycle_id, vkriparam, vkridim);
    
                   vkrisummaryval := 0;
    
                   FETCH krisummary
                    INTO vkrisummaryval;
    
                   CLOSE krisummary;
    
                   vkrisummary := vkrisummary + vkrisummaryval;
             END CASE;
          END LOOP;
    
          CLOSE getkriparamdim;
    
          DBMS_OUTPUT.put_line (   '*KRI NAME* '
                                || vkriname
                                || ' *KRI WTH* '
                                || vkriwarningdesc
                                || ' *KRI RTH* '
                                || vkrireportingdesc
                                || ' *VALUE* '
                                || vkrisummary
                                || ' *COMMENTS* '
                                || vkricomments
                               );
       END LOOP;
    
       DBMS_OUTPUT.put_line ('-------------------------------------');
    
       CLOSE getkriattributes;
    END;
    Thanks in advance.
    Amit
    Last edited by versoft; 11-22-06 at 17:31.
    A friend in need is a friend in deed

Posting Permissions

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