Hi,
I am new to Hibernate. I need an immediate solution to this unique problem, below is the description and the associated code..
Hibernate version:3
Name and version of the database you are using:Oracle 9.2.1
Below is code for the function
Code:
-- Create object
CREATE OR REPLACE TYPE summaryObject
AS OBJECT
(
kriName VARCHAR2(100),
warningThreshold VARCHAR2(500),
reportingThreshold VARCHAR2(500),
cycleValue number,
cycleComment VARCHAR2(1500)
)
-- Create table of the object to store the recordset to be returned
CREATE OR REPLACE TYPE summaryTable
AS TABLE OF summaryObject
-- Create function
CREATE OR REPLACE
FUNCTION bsl_opr_okri.gen (cycle_id IN NUMBER, business_id IN NUMBER)
RETURN summarytable PIPELINED
AS
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;
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;
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;
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;
OPEN getkriparamdim (vkriidpk);
LOOP
FETCH getkriparamdim
INTO vkriid, vkriparam, vkridim;
EXIT WHEN getkriparamdim%NOTFOUND;
CASE vkriformula
WHEN 2
THEN
SELECT a.cycle_date
INTO vkricycledate
FROM kri_collect_cycle a
WHERE a.collect_cycle_id_pk = cycle_id;
OPEN getkriperiod (vkricycledate);
LOOP
FETCH getkriperiod
INTO vkricycleid;
EXIT WHEN getkriperiod%NOTFOUND;
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;
PIPE ROW (summaryobject (vkriname,
vkriwarningdesc,
vkrireportingdesc,
vkrisummary,
vkricomments
));
END LOOP;
CLOSE getkriattributes;
return;
END;
I want to call this function from java but have no clue how to do so, help in this regards is highly appericated. Please let me know i any more details required.
Thanks,
Amit
Login
FAQ
Search
