Home » SQL & PL/SQL » SQL & PL/SQL » Converting long procedure with repeated code into package
| Converting long procedure with repeated code into package [message #311016] |
Thu, 03 April 2008 04:33  |
sonikumari
Messages: 74
Registered: May 2006
Location: Mumbai
|
Member |
|
|
Hi All,
I have to convert a procedure into package where I am getting three parameters values from another module on runtime.
The procedure is as :
CREATE OR REPLACE PROCEDURE "SP_SUBSTITUTE" (p_start_date IN VARCHAR2, p_end_date IN VARCHAR2, p_meter_profile_id IN VARCHAR2)
AS.....
The third parameter p_meter_profile_id is used extensively to fetch values of many variables in current procedure.
For example it gives the values for the variable "v_dut_desc" as in below query :
SELECT UPPER(f_dut_desc) INTO v_dut_desc
FROM t_def_unit_type
WHERE f_dut_id IN (SELECT dft.f_dut_id
FROM t_def_function_type dft
INNER JOIN t_meter_profile mp ON dft.f_dft_id = mp.f_mp_fk_dft_id
WHERE mp.f_mp_id = TO_NUMBER(p_meter_profile_id));
v_dut_desc can return one of 5 values ('KVAH' or 'KVARH' or......)
The value in v_dut_desc is to be checked and corresponding logic needs to be written.
The logic for any v_dut_desc is very much similar except that the name of corresponding table and columns differ as per v_dut_desc.
Below is an example of difference which lies only in v_dut_desk and all conditions remain same.
SELECT NVL(MAX(F_RI_KVAH_ID),0) INTO v_pk_id FROM T_RDG_INT_KVAH;
SELECT NVL(MAX(F_RI_KVARH_ID),0) INTO v_pk_id FROM T_RDG_INT_KVARH;
Currently its written as a procedure which runs over 15 pages as the condition for each v_dut_desc is checked in different IF conditions.
IF v_dut_desc = 'KVAH' THEN
do ....
end if
IF v_dut_desc = 'KVARH' THEN
do ....
end if
I need to bind it in a package and a way to overcome the need to write the conditions for each type again and again.
Please suggest me something as the code currently present is too big to submit here.
Please give me some hint as how should I refactor the code in a package so that it is more readable and structured.
Thanks,
Soni
[Updated on: Thu, 03 April 2008 04:35] Report message to a moderator |
|
|
|
|
|
| Re: Converting long procedure with repeated code into package [message #311029 is a reply to message #311023] |
Thu, 03 April 2008 04:52  |
sonikumari
Messages: 74
Registered: May 2006
Location: Mumbai
|
Member |
|
|
Hi rajatratewal,
I am sorry that I couldn't phrase the requirement clearly.
I am at a stage where I can't take help of anyone but only internet.
Can you give me some tips when I have to break a lengthly procedure into package where I can divide the parts of a procedure using more than one procedure or something else. I have worked on only simple packages.
I am feeling sorry to paste the long code ....I hope you won't get shocked by the long code. Its written by someone and I have to restructure the code and minimize the repitation.
Below is only one condition and this LONG procedure contains 5 more conditions which are all very similar as you can see some lines of the second condition at the end of this code.
Please hold your breadth....
CREATE OR REPLACE PROCEDURE "sp_substitute" (p_start_date IN VARCHAR2, p_end_date IN VARCHAR2, p_meter_profile_id IN VARCHAR2)
AS
v_int_period t_meter_profile.f_mp_interval_period%TYPE;
v_dut_desc t_def_unit_type.f_dut_desc%TYPE;
v_session_id t_session.f_s_id%TYPE;
v_session_create_date t_session.f_s_created%TYPE;
v_session_start_date t_session.f_s_start%TYPE;
v_session_end_date t_session.f_s_end%TYPE;
v_adl_reading t_data_stream.f_dst_adl%TYPE;
n_int_per_day FLOAT;
v_pk_id NUMBER(15);
d_start_tc VARCHAR(20);
d_end_tc VARCHAR(20);
n_total_valid_tc FLOAT;
n_starttc_reading FLOAT;
n_endtc_reading FLOAT;
v_count INTEGER;
v_reading_taken VARCHAR(20);
v_event INTEGER;
v_weekday VARCHAR2(2);
n_avg_reading FLOAT;
n_total_avg_reading FLOAT;
n_diff_tc FLOAT;
n_start_tc FLOAT;
n_end_tc FLOAT;
n_valid_rdg FLOAT;
n_reading FLOAT;
v_reason VARCHAR2(30);
v_reason_id NUMBER(15);
v_dateformat VARCHAR(30);
v_timeformat VARCHAR(30);
BEGIN
-- Fetch interval period for the meter profile
BEGIN
SELECT f_mp_interval_period INTO v_int_period
FROM t_meter_profile
WHERE f_mp_id = TO_NUMBER(p_meter_profile_id);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('No Interval Period is found');
-- UTL_FILE.PUT_LINE(output,'Interval period not available for meter profile');
-- UTL_FILE.FCLOSE(output);
RETURN;
END;
-- Calculate interval per day
n_int_per_day := (24 * 60)/v_int_period;
-- Fetch ADL reading
BEGIN
SELECT f_dst_adl INTO v_adl_reading
FROM t_data_stream
WHERE f_dst_id IN (SELECT f_dsp_fk_dst_id
FROM t_data_stream_profile
WHERE f_dsp_fk_mp_id = p_meter_profile_id);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('ADL Not found');
v_adl_reading := 0;
END;
-- Fetch the Maximum session ID
SELECT NVL(MAX(f_s_id),0) INTO v_session_id FROM t_session;
-- Fetch unit type for the meter profile
BEGIN
SELECT UPPER(f_dut_desc) INTO v_dut_desc
FROM t_def_unit_type
WHERE f_dut_id IN (SELECT dft.f_dut_id
FROM t_def_function_type dft
INNER JOIN t_meter_profile mp ON dft.f_dft_id = mp.f_mp_fk_dft_id
WHERE mp.f_mp_id = TO_NUMBER(p_meter_profile_id));
EXCEPTION
WHEN NO_DATA_FOUND THEN
v_dut_desc := ' ';
DBMS_OUTPUT.PUT_LINE('Unit type Not found');
END;
v_dateformat := 'DD/MM/YYYY HH24:MI:SS';
v_timeformat := 'HH24:MI:SS';
v_session_create_date := SYSDATE;
v_session_start_date := SYSDATE;
--########################################################################################################
IF v_dut_desc = 'KVAH' THEN
-- Fetch the maximumm ID
SELECT NVL(MAX(f_ri_kvah_id),0) INTO v_pk_id FROM t_rdg_int_kvah;
-- Fetch the Maximum Reason ID
SELECT NVL(MAX(f_si_kvah_id),'0') INTO v_reason_id FROM t_sub_int_kvah;
-- Identify the max. start date for valid TC where reading <= start range
SELECT MAX(TO_CHAR(f_ra_kvah_reading_taken,v_dateformat)) INTO d_start_tc
FROM t_rdg_abs_kvah
WHERE f_ra_kvah_reading_taken <= TO_DATE(p_start_date,v_dateformat)
AND f_ra_kvah_validated = 1
AND f_ra_kvah_fk_mp_id = TO_NUMBER(p_meter_profile_id);
-- Identify the min. end date for valid TC where reading >= end range
SELECT MIN(TO_CHAR(f_ra_kvah_reading_taken,v_dateformat)) INTO d_end_tc
FROM t_rdg_abs_kvah
WHERE f_ra_kvah_reading_taken >= TO_DATE(p_start_date,v_dateformat)
AND f_ra_kvah_validated = 1
AND f_ra_kvah_fk_mp_id = TO_NUMBER(p_meter_profile_id);
-- Fetches the reading of start date valid TC
IF d_start_tc IS NOT NULL THEN
SELECT f_ra_kvah_reading INTO n_starttc_reading
FROM t_rdg_abs_kvah
WHERE f_ra_kvah_reading_taken = TO_DATE(d_start_tc,v_dateformat)
AND f_ra_kvah_validated = 1
AND f_ra_kvah_fk_mp_id = TO_NUMBER(p_meter_profile_id);
END IF;
-- Identify the max. end date for valid TC between the range
IF d_end_tc IS NULL THEN
SELECT MAX(TO_CHAR(f_ra_kvah_reading_taken,v_dateformat)) INTO d_end_tc
FROM t_rdg_abs_kvah
WHERE f_ra_kvah_reading_taken > TO_DATE(p_start_date,v_dateformat)
AND f_ra_kvah_reading_taken < TO_DATE(p_end_date,v_dateformat)
AND f_ra_kvah_validated = 1
AND f_ra_kvah_fk_mp_id = TO_NUMBER(p_meter_profile_id);
END IF;
-- Fetches total valid readings between the valid TC range
IF d_end_tc IS NOT NULL THEN
SELECT NVL(SUM(f_ri_kvah_reading),0) INTO n_total_valid_tc
FROM t_rdg_int_kvah
WHERE f_ri_kvah_reading_taken > TO_DATE(d_start_tc,v_dateformat)
AND f_ri_kvah_reading_taken <= TO_DATE(d_end_tc,v_dateformat)
AND f_ri_kvah_validated = 1
AND f_ri_kvah_fk_mp_id = TO_NUMBER(p_meter_profile_id);
-- Fetches the reading of end date valid TC
SELECT f_ra_kvah_reading INTO n_endtc_reading
FROM t_rdg_abs_kvah
WHERE f_ra_kvah_reading_taken = TO_DATE(d_end_tc,v_dateformat)
AND f_ra_kvah_validated = 1
AND f_ra_kvah_fk_mp_id = TO_NUMBER(p_meter_profile_id);
ELSE
n_total_valid_tc := 0;
n_endtc_reading := 0;
END IF;
DECLARE
CURSOR cur_rdg_int IS
SELECT TO_CHAR(f_ri_kvah_reading_taken,v_dateformat)
FROM t_rdg_int_kvah ri1
WHERE f_ri_kvah_validated = 2
AND f_ri_kvah_reading_taken
BETWEEN TO_DATE(p_start_date,v_dateformat)
AND TO_DATE(p_end_date,v_dateformat)
AND f_ri_kvah_fk_mp_id = TO_NUMBER(p_meter_profile_id)
AND f_ri_kvah_id IN (SELECT MAX(f_ri_kvah_id) FROM t_rdg_int_kvah ri2
WHERE ri2.f_ri_kvah_reading_taken = ri1.f_ri_kvah_reading_taken
AND ri2.f_ri_kvah_fk_mp_id = ri1.f_ri_kvah_fk_mp_id);
BEGIN
v_count := 0;
n_total_avg_reading := 0;
-- DBMS_OUTPUT.PUT_LINE('------ cursor created ----------');
OPEN cur_rdg_int;
LOOP
-- DBMS_OUTPUT.PUT_LINE('------ cursor loop started ----------');
FETCH cur_rdg_int INTO v_reading_taken;
EXIT WHEN cur_rdg_int%NOTFOUND;
-- Find the interval period is a Public Holiday
SELECT COUNT(*) INTO v_count FROM t_public_holiday
WHERE t_ph_date = TRUNC(TO_DATE(v_reading_taken,v_dateformat));
IF v_count > 0 THEN
v_weekday := '1';
ELSE
v_weekday := TO_CHAR(TO_DATE(v_reading_taken,v_dateformat),'D');
END IF;
SELECT COUNT(f_ri_kvah_reading_taken),AVG(f_ri_kvah_reading)
INTO v_count,n_avg_reading
FROM t_rdg_int_kvah
WHERE f_ri_kvah_reading_taken >= (TO_DATE(v_reading_taken,v_dateformat) - 28)
AND f_ri_kvah_reading_taken < TO_DATE(v_reading_taken,v_dateformat)
AND TO_CHAR(f_ri_kvah_reading_taken,'D') = v_weekday
AND TO_CHAR(f_ri_kvah_reading_taken,v_timeformat) = TO_CHAR(TO_DATE(v_reading_taken,v_dateformat),v_timeformat)
AND f_ri_kvah_validated = 1
AND f_ri_kvah_fk_mp_id = TO_NUMBER(p_meter_profile_id);
IF v_count >= 4 THEN
n_total_avg_reading := n_total_avg_reading + n_avg_reading;
-- DBMS_OUTPUT.PUT_LINE('------ find total avg ----------');
-- DBMS_OUTPUT.PUT_LINE(n_total_avg_reading);
END IF;
END LOOP;
CLOSE cur_rdg_int;
END;
-- Calculates the total missing interval
n_diff_tc := n_endtc_reading - n_starttc_reading - n_total_valid_tc;
-- DBMS_OUTPUT.PUT_LINE('------ n_diff_tc ----------');
-- DBMS_OUTPUT.PUT_LINE(n_diff_tc);
DECLARE
CURSOR cur_rdg_int IS
SELECT TO_CHAR(f_ri_kvah_reading_taken,v_dateformat),f_ri_kvah_event
FROM t_rdg_int_kvah ri1
WHERE f_ri_kvah_validated = 2
AND f_ri_kvah_reading_taken
BETWEEN TO_DATE(p_start_date,v_dateformat)
AND TO_DATE(p_end_date,v_dateformat)
AND f_ri_kvah_fk_mp_id = TO_NUMBER(p_meter_profile_id)
AND f_ri_kvah_id IN (SELECT MAX(f_ri_kvah_id) FROM t_rdg_int_kvah ri2
WHERE ri2.f_ri_kvah_reading_taken = ri1.f_ri_kvah_reading_taken
AND ri2.f_ri_kvah_fk_mp_id = ri1.f_ri_kvah_fk_mp_id);
BEGIN
v_count := 0;
OPEN cur_rdg_int;
LOOP
FETCH cur_rdg_int INTO v_reading_taken,v_event;
EXIT WHEN cur_rdg_int%NOTFOUND;
-- Find the interval period is a Public Holiday
SELECT COUNT(*) INTO v_count FROM t_public_holiday
WHERE T_PH_DATE = TRUNC(TO_DATE(v_reading_taken,v_dateformat));
IF v_count > 0 THEN
v_weekday := 1;
ELSE
v_weekday := TO_CHAR(TO_DATE(v_reading_taken,v_dateformat),'D');
END IF;
SELECT COUNT(f_ri_kvah_reading_taken),AVG(f_ri_kvah_reading)
INTO v_count,n_avg_reading
FROM t_rdg_int_kvah
WHERE f_ri_kvah_reading_taken >= (TO_DATE(v_reading_taken,v_dateformat) - 28)
AND f_ri_kvah_reading_taken < TO_DATE(v_reading_taken,v_dateformat)
AND TO_CHAR(f_ri_kvah_reading_taken,'D') = v_weekday
AND TO_CHAR(f_ri_kvah_reading_taken,v_timeformat) = TO_CHAR(TO_DATE(v_reading_taken,v_dateformat),v_timeformat)
AND f_ri_kvah_validated = 1
AND f_ri_kvah_fk_mp_id = TO_NUMBER(p_meter_profile_id);
v_pk_id := v_pk_id + 1;
v_reason_id := v_reason_id + 1;
v_session_id := v_session_id + 1;
v_session_end_date := SYSDATE;
IF v_count < 4 THEN
n_reading := v_adl_reading/n_int_per_day;
v_reason := 'ADL SUBSTITUTE';
ELSE
IF d_end_tc IS NULL OR v_reading_taken > d_end_tc THEN
n_reading := n_avg_reading;
v_reason := 'PROFILING SUBSTITUTE';
ELSE
n_reading := (n_avg_reading / n_total_avg_reading) * n_diff_tc;
v_reason := 'SCALING SUBSTITUTE';
END IF;
END IF;
-- insert a new row in session table
INSERT INTO t_session
VALUES(v_session_id,v_session_create_date,v_session_start_date,
v_session_end_date,'','PASS','SUBSTITUTE',0);
-- insert a new row in T_RDG_INT_K* table
INSERT INTO t_rdg_int_kvah
VALUES(v_pk_id,v_session_id,TO_DATE(v_reading_taken,v_dateformat),round(n_reading,3),
v_event,0,TO_NUMBER(p_meter_profile_id),2);
-- insert a new row in reason table
INSERT INTO t_sub_int_kvah
VALUES(v_reason_id,TO_DATE(v_reading_taken,v_dateformat),v_pk_id,v_reason);
END LOOP;
COMMIT;
CLOSE cur_rdg_int;
END;
########################################################################################################
ELSIF v_dut_desc = 'KVARH' THEN
-- Fetch the maximumm ID
SELECT NVL(MAX(F_RI_KVARH_ID),0) INTO v_pk_id FROM T_RDG_INT_KVARH;
...............
Thanks for your patient....
[Updated on: Thu, 03 April 2008 04:55] Report message to a moderator |
|
|
|
| Re: Converting long procedure with repeated code into package [message #311032 is a reply to message #311029] |
Thu, 03 April 2008 04:58 |
sonikumari
Messages: 74
Registered: May 2006
Location: Mumbai
|
Member |
|
|
please advice me a way to minimize the repetition. I understand its not possible to look into the code and suggest but just scroll down through the code and see if the different conditions can be more structured and clean.
I am asked to do this using a package.
please advice something atleast some hints. The different conditions that start with :
IF v_dut_desc = 'KVAH' THEN
....
IF v_dut_desc = 'KVARH' THEN
.....
IF v_dut_desc = 'KVARH2' THEN
.....
All the five conditions are very much similar except that the tables and columns used in the different conditions have text KVAH or KVARH or KVARH2 or ... or ... different in 5 conditions.
SELECT NVL(MAX(F_RI_KVAH_ID),0) INTO v_pk_id FROM T_RDG_INT_KVAH;
SELECT NVL(MAX(F_RI_KVARH_ID),0) INTO v_pk_id FROM T_RDG_INT_KVARH;
SELECT NVL(MAX(F_RI_KVARH2_ID),0) INTO v_pk_id FROM T_RDG_INT_KVARH2;
Thanks,
Soni
[Updated on: Thu, 03 April 2008 05:03] Report message to a moderator |
|
|
|
| Re: Converting long procedure with repeated code into package [message #311038 is a reply to message #311032] |
Thu, 03 April 2008 05:10 |
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
|
Senior Member |
|
|
Is their is same logic For 'KVAH' or 'KVARH' And Other 3 Conditions
If it is so:-
Find common task that you perform in each condition like
-- Fetch the maximumm ID
-- Fetch the Maximum Reason ID
-- Identify the max. start date for valid TC where reading <= start range
-- Identify the min. end date for valid TC where reading >= end range
-- Fetches the reading of start date valid TC
-- Identify the max. end date for valid TC between the range
-- Fetches total valid readings between the valid TC range
Make a procedure that will return these values.
Don't try pl/sql when work can be done via simple sql.
Study Case statements that will remove unnecessary IF conditions
|
|
|
|
|
|
| Re: Converting long procedure with repeated code into package [message #311055 is a reply to message #311050] |
Thu, 03 April 2008 05:28 |
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
|
Senior Member |
|
|
Yes..
Look you understand your code better.
Take out a pen and paper and think that what are common things that in each condition you are performing.(For KVAH etc.)
Move that functionality to one function or procedure and make it generalized.
I think it's simple enough.If you can understand the code you can break it easily.
|
|
|
|
| Re: Converting long procedure with repeated code into package [message #311307 is a reply to message #311055] |
Fri, 04 April 2008 00:52 |
monasingh
Messages: 229
Registered: May 2006
Location: Mumbai
|
Senior Member |
|
|
Hi,
I am new to package development. I am going to do this by creating a package and maintaining the different functionalities in different procedures or functions. I was looking at packages tutorial where I found that the package spec as well as package body has to be given where as at other tutorials only package was created without body. I am little confused as how to create the package with or without body.
Please guide me on this as when do we create body and whether we should create it as I have less time to accomplish this job and don't want to make any mistakes as well as complex.
Thanks,
Soni
[Updated on: Fri, 04 April 2008 01:04] by Moderator Report message to a moderator |
|
|
|
|
|
|
|
|
|
| Re: Converting long procedure with repeated code into package [message #311376 is a reply to message #311336] |
Fri, 04 April 2008 05:15 |
 |
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
|
Senior Member
Account Moderator |
|
|
| First message | Below is an example of difference which lies only in v_dut_desk and all conditions remain same.SELECT NVL(MAX(F_RI_KVAH_ID),0) INTO v_pk_id FROM T_RDG_INT_KVAH;
SELECT NVL(MAX(F_RI_KVARH_ID),0) INTO v_pk_id FROM T_RDG_INT_KVARH;
|
In order to dynamically modify your query, you'll need to use ... well, dynamic SQL. Basically, you'd prepare a statement and execute it using EXECUTE IMMEDIATE.
If you never did that, and jump into it with a difficult problem, it might be painful to figure it out. However, you might try; now that you know the terms, search for how to use them.
Putting a procedure into a package isn't that difficult - first create a package specification, then its body. In its simple appearance, quite a simple task. Here's an example of both - creating a package specification, its body and use of dynamic SQL:
SQL> CREATE OR REPLACE PACKAGE pkg_sample IS
2 FUNCTION fun_sample (par_table IN VARCHAR2, par_deptno IN NUMBER) RETURN NUMBER;
3 END pkg_sample;
4 /
Package created.
SQL>
SQL> CREATE OR REPLACE PACKAGE BODY pkg_sample IS
2 FUNCTION fun_sample (par_table IN VARCHAR2, par_deptno IN NUMBER) RETURN NUMBER
3 IS
4 str VARCHAR2(200);
5 retval NUMBER;
6 BEGIN
7 str := 'SELECT COUNT(*) FROM ' || par_table ||
8 ' WHERE deptno = ' || par_deptno;
9
10 EXECUTE IMMEDIATE str INTO retval;
11
12 RETURN (retval);
13 END fun_sample;
14 END pkg_sample;
15 /
Package body created.
SQL>
SQL> SELECT pkg_sample.fun_sample('emp', 10) FROM dual;
PKG_SAMPLE.FUN_SAMPLE('EMP',10)
-------------------------------
3
SQL> SELECT pkg_sample.fun_sample('dept', 20) FROM dual;
PKG_SAMPLE.FUN_SAMPLE('DEPT',20)
--------------------------------
1
SQL>
|
|
|
|
| Re: Converting long procedure with repeated code into package [message #311388 is a reply to message #311376] |
Fri, 04 April 2008 06:15 |
sonikumari
Messages: 74
Registered: May 2006
Location: Mumbai
|
Member |
|
|
Thanks littlefoot for your help on this.
I am little relieved.
I am planning to use procedures. A general procedure that will calculate the common things needed for all 5 types('KVAH','KVARH','KVARH2','KWH1','KWH2).
Then I can create 5 procedures based on the 5 type and will call one of them from general procedure based on the value of the type received which I will get in general procedure.
The code is too long for the package. I am pasting the code here though ...the way I have planned to work.
Below is the code where the genralised procedure named sp_substitute has the code which does general things and I believe I will be able to use them in other procedures as the variables I have used are the global variables used in package like 'v_int_period'.
CREATE OR REPLACE PACKAGE pk_substitution AS
/*Declare externally visible contant.*/
v_int_period t_meter_profile.f_mp_interval_period%TYPE;
v_dut_desc t_def_unit_type.f_dut_desc%TYPE;
v_session_id t_session.f_s_id%TYPE;
v_session_create_date t_session.f_s_created%TYPE;
v_session_start_date t_session.f_s_start%TYPE;
v_adl_reading t_data_stream.f_dst_adl%TYPE;
n_int_per_day FLOAT;
v_dateformat VARCHAR(30);
v_dateformat := 'DD/MM/YYYY HH24:MI:SS';
v_timeformat := 'HH24:MI:SS';
v_session_create_date := SYSDATE;
v_session_start_date := SYSDATE;
PROCEDURE sp_substitute (p_start_date IN VARCHAR2, p_end_date IN VARCHAR2, p_meter_profile_id IN VARCHAR2)
PROCEDURE sp_kvah (p_start_date IN VARCHAR2, p_end_date IN VARCHAR2, p_meter_profile_id IN VARCHAR2)
PROCEDURE sp_kvarh (p_start_date IN VARCHAR2, p_end_date IN VARCHAR2, p_meter_profile_id IN VARCHAR2)
PROCEDURE sp_kvarh2 (p_start_date IN VARCHAR2, p_end_date IN VARCHAR2, p_meter_profile_id IN VARCHAR2)
PROCEDURE sp_kwh1 (p_start_date IN VARCHAR2, p_end_date IN VARCHAR2, p_meter_profile_id IN VARCHAR2)
PROCEDURE sp_kwh2 (p_start_date IN VARCHAR2, p_end_date IN VARCHAR2, p_meter_profile_id IN VARCHAR2)
END pk_substitution
CREATE OR REPLACE PACKAGE BODY pk_substitution AS
/*General procedure to call other 5 procedure based on type*/
CREATE OR REPLACE PROCEDURE sp_substitute (p_start_date IN VARCHAR2, p_end_date IN VARCHAR2, p_meter_profile_id IN VARCHAR2)
-- Fetch interval period for the meter profile
BEGIN
SELECT f_mp_interval_period INTO v_int_period
FROM t_meter_profile
WHERE f_mp_id = TO_NUMBER(p_meter_profile_id);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('No Interval Period is found');
RETURN;
END;
-- Calculate interval per day
n_int_per_day := (24 * 60)/v_int_period;
-- Fetch ADL reading
BEGIN
SELECT f_dst_adl INTO v_adl_reading
FROM t_data_stream
WHERE f_dst_id IN (SELECT f_dsp_fk_dst_id
FROM t_data_stream_profile
WHERE f_dsp_fk_mp_id = p_meter_profile_id);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('ADL Not found');
v_adl_reading := 0;
END;
-- Fetch the Maximum session ID
SELECT NVL(MAX(f_s_id),0) INTO v_session_id FROM t_session;
-- Fetch unit type for the meter profile
BEGIN
SELECT UPPER(f_dut_desc) INTO v_dut_desc
FROM t_def_unit_type
WHERE f_dut_id IN (SELECT dft.f_dut_id
FROM t_def_function_type dft
INNER JOIN t_meter_profile mp ON dft.f_dft_id = mp.f_mp_fk_dft_id WHERE mp.f_mp_id = TO_NUMBER(p_meter_profile_id));
EXCEPTION
WHEN NO_DATA_FOUND THEN
v_dut_desc := ' ';
DBMS_OUTPUT.PUT_LINE('Unit type Not found');
END;
IF v_dut_desc = 'KVAH' THEN
sp_kvah(p_start_date IN VARCHAR2, p_end_date IN VARCHAR2)
ELSEIF v_dut_desc = 'KVARH2' THEN
sp_kvarh2(p_start_date IN VARCHAR2, p_end_date IN VARCHAR2)
ELSEIF v_dut_desc = 'KVARH2' THEN
sp_kwh1(p_start_date IN VARCHAR2, p_end_date IN VARCHAR2)
ELSEIF v_dut_desc = 'KVARH2' THEN
sp_kvah(p_start_date IN VARCHAR2, p_end_date IN VARCHAR2)
ELSEIF v_dut_desc = 'KVARH2' THEN
sp_kwh2(p_start_date IN VARCHAR2, p_end_date IN VARCHAR2)
END IF;
END sp_substituion
CREATE OR REPLACE PROCEDURE sp_kvah (p_start_date IN VARCHAR2, p_end_date IN VARCHAR2, p_meter_profile_id IN VARCHAR2)
v_session_end_date t_session.f_s_end%TYPE;
v_pk_id NUMBER(15);
d_start_tc VARCHAR(20);
d_end_tc VARCHAR(20);
n_total_valid_tc FLOAT;
n_starttc_reading FLOAT;
n_endtc_reading FLOAT;
v_count INTEGER;
v_reading_taken VARCHAR(20);
v_event INTEGER;
v_weekday VARCHAR2(2);
n_avg_reading FLOAT;
n_total_avg_reading FLOAT;
n_diff_tc FLOAT;
n_start_tc FLOAT;
n_end_tc FLOAT;
n_valid_rdg FLOAT;
n_reading FLOAT;
v_reason VARCHAR2(30);
-- Fetch the maximumm ID
SELECT NVL(MAX(f_ri_kvah_id),0) INTO v_pk_id FROM t_rdg_int_kvah;
-- Fetch the Maximum Reason ID
SELECT NVL(MAX(f_si_kvah_id),'0') INTO v_reason_id FROM t_sub_int_kvah;
-- Identify the max. start date for valid TC where reading <= start range
SELECT MAX(TO_CHAR(f_ra_kvah_reading_taken,v_dateformat)) INTO d_start_tc
FROM t_rdg_abs_kvah
WHERE f_ra_kvah_reading_taken <= TO_DATE(p_start_date,v_dateformat)
AND f_ra_kvah_validated = 1
AND f_ra_kvah_fk_mp_id = TO_NUMBER(p_meter_profile_id);
-- Identify the min. end date for valid TC where reading >= end range
SELECT MIN(TO_CHAR(f_ra_kvah_reading_taken,v_dateformat)) INTO d_end_tc
FROM t_rdg_abs_kvah
WHERE f_ra_kvah_reading_taken >= TO_DATE(p_end_date,v_dateformat)
AND f_ra_kvah_validated = 1
AND f_ra_kvah_fk_mp_id = TO_NUMBER(p_meter_profile_id);
-- Fetches the reading of start date valid TC
IF d_start_tc IS NOT NULL THEN
SELECT f_ra_kvah_reading INTO n_starttc_reading
FROM t_rdg_abs_kvah
WHERE f_ra_kvah_reading_taken = TO_DATE(d_start_tc,v_dateformat)
AND f_ra_kvah_validated = 1
AND f_ra_kvah_fk_mp_id = TO_NUMBER(p_meter_profile_id);
END IF;
-- Identify the max. end date for valid TC between the range
IF d_end_tc IS NULL THEN
SELECT MAX(TO_CHAR(f_ra_kvah_reading_taken,v_dateformat)) INTO d_end_tc
FROM t_rdg_abs_kvah
WHERE f_ra_kvah_reading_taken > TO_DATE(p_start_date,v_dateformat)
AND f_ra_kvah_reading_taken < TO_DATE(p_end_date,v_dateformat)
AND f_ra_kvah_validated = 1
AND f_ra_kvah_fk_mp_id = TO_NUMBER(p_meter_profile_id);
END IF;
-- Fetches total valid readings between the valid TC range
IF d_end_tc IS NOT NULL THEN
SELECT NVL(SUM(f_ri_kvah_reading),0) INTO n_total_valid_tc
FROM t_rdg_int_kvah
WHERE f_ri_kvah_reading_taken > TO_DATE(d_start_tc,v_dateformat)
AND f_ri_kvah_reading_taken <= TO_DATE(d_end_tc,v_dateformat)
AND f_ri_kvah_validated = 1
AND f_ri_kvah_fk_mp_id = TO_NUMBER(p_meter_profile_id);
-- Fetches the reading of end date valid TC
SELECT f_ra_kvah_reading INTO n_endtc_reading
FROM t_rdg_abs_kvah
WHERE f_ra_kvah_reading_taken = TO_DATE(d_end_tc,v_dateformat)
AND f_ra_kvah_validated = 1
AND f_ra_kvah_fk_mp_id = TO_NUMBER(p_meter_profile_id);
ELSE
n_total_valid_tc := 0;
n_endtc_reading := 0;
END IF;
-- Calculates the total missing interval
n_diff_tc := n_endtc_reading - n_starttc_reading - n_total_valid_tc;
DECLARE
CURSOR cur_rdg_int IS
SELECT TO_CHAR(f_ri_kvah_reading_taken,v_dateformat),f_ri_kvah_event
FROM t_rdg_int_kvah ri1
WHERE f_ri_kvah_validated = 2
AND f_ri_kvah_reading_taken
BETWEEN TO_DATE(p_start_date,v_dateformat)
AND TO_DATE(p_end_date,v_dateformat)
AND f_ri_kvah_fk_mp_id = TO_NUMBER(p_meter_profile_id)
AND f_ri_kvah_id IN (SELECT MAX(f_ri_kvah_id) FROM t_rdg_int_kvah ri2
WHERE ri2.f_ri_kvah_reading_taken = ri1.f_ri_kvah_reading_taken
AND ri2.f_ri_kvah_fk_mp_id = ri1.f_ri_kvah_fk_mp_id);
BEGIN
v_count := 0;
OPEN cur_rdg_int;
LOOP
FETCH cur_rdg_int INTO v_reading_taken,v_event;
EXIT WHEN cur_rdg_int%NOTFOUND;
-- Find the interval period is a Public Holiday
SELECT COUNT(*) INTO v_count FROM t_public_holiday
WHERE T_PH_DATE = TRUNC(TO_DATE(v_reading_taken,v_dateformat));
IF v_count > 0 THEN
v_weekday := 1;
ELSE
v_weekday := TO_CHAR(TO_DATE(v_reading_taken,v_dateformat),'D');
END IF;
SELECT COUNT(f_ri_kvah_reading_taken),AVG(f_ri_kvah_reading)
INTO v_count,n_avg_reading
FROM t_rdg_int_kvah
WHERE f_ri_kvah_reading_taken >= (TO_DATE(v_reading_taken,v_dateformat) - 28)
AND f_ri_kvah_reading_taken < TO_DATE(v_reading_taken,v_dateformat)
AND TO_CHAR(f_ri_kvah_reading_taken,'D') = v_weekday
AND TO_CHAR(f_ri_kvah_reading_taken,v_timeformat) = TO_CHAR(TO_DATE(v_reading_taken,v_dateformat),v_timeformat)
AND f_ri_kvah_validated = 1
AND f_ri_kvah_fk_mp_id = TO_NUMBER(p_meter_profile_id);
v_pk_id := v_pk_id + 1;
v_reason_id := v_reason_id + 1;
v_session_id := v_session_id + 1;
v_session_end_date := SYSDATE;
IF v_count < 4 THEN
n_reading := v_adl_reading/n_int_per_day;
v_reason := 'ADL SUBSTITUTE';
ELSE
IF d_end_tc IS NULL OR v_reading_taken > d_end_tc THEN
n_reading := n_avg_reading;
v_reason := 'PROFILING SUBSTITUTE';
ELSE
n_reading := (n_avg_reading / n_total_avg_reading) * n_diff_tc;
v_reason := 'SCALING SUBSTITUTE';
END IF;
END IF;
-- insert a new row in session table
INSERT INTO t_session
VALUES(v_session_id,v_session_create_date,v_session_start_date,
v_session_end_date,'','PASS','SUBSTITUTE',0);
-- insert a new row in T_RDG_INT_K* table (missing interval insert)
INSERT INTO t_rdg_int_kvah
VALUES(v_pk_id,v_session_id,TO_DATE(v_reading_taken,v_dateformat),round(n_reading,3),
v_event,0,TO_NUMBER(p_meter_profile_id),2);
-- insert a new row in reason table
INSERT INTO t_sub_int_kvah
VALUES(v_reason_id,TO_DATE(v_reading_taken,v_dateformat),v_pk_id,v_reason);
END LOOP;
COMMIT;
CLOSE cur_rdg_int;
END;
END sp_kvah
CREATE OR REPLACE PROCEDURE sp_kvah (p_start_date IN VARCHAR2, p_end_date IN VARCHAR2, p_meter_profile_id IN VARCHAR2)
--#####SAME AS IN procedure sp_kvah
END sp_kvarh2
CREATE OR REPLACE PROCEDURE sp_kvah (p_start_date IN VARCHAR2, p_end_date IN VARCHAR2, p_meter_profile_id IN VARCHAR2)
--#####SAME AS IN procedure sp_kvah
END sp_kwh1
CREATE OR REPLACE PROCEDURE sp_kvah (p_start_date IN VARCHAR2, p_end_date IN VARCHAR2, p_meter_profile_id IN VARCHAR2)
--#####SAME AS IN procedure sp_kvah
END sp_kvah
CREATE OR REPLACE PROCEDURE sp_kvah (p_start_date IN VARCHAR2, p_end_date IN VARCHAR2, p_meter_profile_id IN VARCHAR2)
--#####SAME AS IN procedure sp_kvah
END sp_kwh2
END pk_substitution
I understand I am expecting more from you but please help me this time.... I will try to recover other things once I am done with this.
Please guide me if the way I am trying to create the package created is correct.
You don't look into the code inside the sp_kvah procedure but only the structure of the package as a whole.
Then I can call the procedure as :
exec pk_substitute.sp_substitute('10/11/2007','20/12/2007', 'KVAH');
Thanks for looking into this,
Soni
[Updated on: Fri, 04 April 2008 06:25] Report message to a moderator |
|
|
|
|
|
|
|
|
|
|
|
| Re: Converting long procedure with repeated code into package [message #311837 is a reply to message #311396] |
Mon, 07 April 2008 01:59 |
sonikumari
Messages: 74
Registered: May 2006
Location: Mumbai
|
Member |
|
|
Hi Littlefoot,
Obviously I do not want anyone to write code for me but to guide me as this is my first package 
I have tried to get rid of 5 procedures and tried to pack them in one general procedure by passing the type in a parameter and hopefully only one procedure could do it making the code much smaller than before.
The repeating text would get replaced by the variable v_dut_desc as under :
BEGIN
-- ##Fetch the maximumm ID
strsql := 'SELECT NVL(MAX(f_ri_'||v_dut_desc||'_id),0)
FROM t_rdg_int_' ||v_dut_desc||';'
EXECUTE IMMEDIATE strsql INTO v_pk_id;
I have created the package but the package body is giving error while creation. Below I have put only a part of package body...to run..but its giving error...I am trying to fix it from longtime. But not able to understand as why this error is coming. Please look into it.
My package is :
CREATE OR REPLACE PACKAGE pk_substitution AS
/*Declare externally visible contant that will be used through other procedures.*/
v_int_period t_meter_profile.f_mp_interval_period%TYPE;
v_dut_desc t_def_unit_type.f_dut_desc%TYPE;
v_session_id t_session.f_s_id%TYPE;
v_session_create_date t_session.f_s_created%TYPE;
v_session_start_date t_session.f_s_start%TYPE;
v_adl_reading t_data_stream.f_dst_adl%TYPE;
n_int_per_day FLOAT;
v_dateformat VARCHAR(30);
v_dateformat DATE := 'DD/MM/YYYY HH24:MI:SS';
v_timeformat DATE := 'HH24:MI:SS';
v_session_create_date DATE := SYSDATE;
v_session_start_date DATE := SYSDATE;
/*Proc sp_substitute process general variables that will be used for 5 types in procedure sp_gen_type*/
PROCEDURE sp_substitute (p_start_date IN VARCHAR2, p_end_date IN VARCHAR2, p_meter_profile_id IN VARCHAR2);
/*Proc sp_gen_type is a general procedure for 5 types*/
PROCEDURE sp_gen_type (p_start_date IN VARCHAR2, p_end_date IN VARCHAR2, v_dut_desc IN VARCHAR2);
END pk_substitution;
CREATE OR REPLACE PACKAGE BODY pk_substitution IS
PROCEDURE sp_substitute (p_start_date IN VARCHAR2, p_end_date IN VARCHAR2, p_meter_profile_id IN VARCHAR2)
IS
-- Fetch unit type for the meter profile
BEGIN
SELECT UPPER(f_dut_desc) INTO v_dut_desc
FROM t_def_unit_type
WHERE f_dut_id IN (SELECT dft.f_dut_id
FROM t_def_function_type dft
INNER JOIN t_meter_profile mp ON dft.f_dft_id = mp.f_mp_fk_dft_id
WHERE mp.f_mp_id = TO_NUMBER(p_meter_profile_id));
EXCEPTION
WHEN NO_DATA_FOUND THEN
v_dut_desc := ' ';
DBMS_OUTPUT.PUT_LINE('Unit type Not found');
END;
END sp_substitute;
PROCEDURE sp_gen_type (p_start_date IN VARCHAR2, p_end_date IN VARCHAR2, p_meter_profile_id IN VARCHAR2)
IS
strsql VARCHAR2(400);
v_session_end_date t_session.f_s_end%TYPE;
v_pk_id NUMBER(15);
d_start_tc VARCHAR(20);
d_end_tc VARCHAR(20);
n_total_valid_tc FLOAT;
n_starttc_reading FLOAT;
n_endtc_reading FLOAT;
v_count INTEGER;
v_reading_taken VARCHAR(20);
v_event INTEGER;
v_weekday VARCHAR2(2);
n_avg_reading FLOAT;
n_total_avg_reading FLOAT;
n_diff_tc FLOAT;
n_start_tc FLOAT;
n_end_tc FLOAT;
n_valid_rdg FLOAT;
n_reading FLOAT;
v_reason VARCHAR2(30);
BEGIN
-- ##Fetch the maximumm ID
strsql := 'SELECT NVL(MAX(f_ri_'||v_dut_desc||'_id),0)
FROM t_rdg_int_' ||v_dut_desc||';'
EXECUTE IMMEDIATE strsql INTO v_pk_id;
-- Fetch the Maximum Reason ID
strsql := 'SELECT NVL(MAX(f_si_'||v_dut_desc||'_id),'0')
FROM t_sub_int_' ||v_dut_desc||';'
EXECUTE IMMEDIATE strsql INTO v_reason_id;
END;
END sp_gen_type;
END pk_substitution;
/
ERROR :-
SQL> show err
Errors for PACKAGE BODY PK_SUBSTITUTION:
LINE/COL ERROR
-------- -----------------------------------------------------------------
17/8 PLS-00113: END identifier 'SP_SUBSTITUTE' must match
'PK_SUBSTITUTION' at line 1, column 14
18/4 PLS-00103: Encountered the symbol "PROCEDURE"
44/4 PLS-00103: Encountered the symbol "EXECUTE" when expecting one of
the following:
* & = - + ; < / > at in is mod remainder not rem
<an exponent (**)> <> or != or ~= >= <= <> and or like LIKE2_
LIKE4_ LIKEC_ between || member SUBMULTISET_
Thanks,
Soni
[Updated on: Mon, 07 April 2008 02:07] Report message to a moderator |
|
|
|
| Re: Converting long procedure with repeated code into package [message #311838 is a reply to message #311837] |
Mon, 07 April 2008 02:09 |
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
|
Senior Member |
|
|
One Begin Must Have one End Statement.you first try to make a test Package then go for modification of your code.
PROCEDURE sp_substitute (p_start_date IN VARCHAR2, p_end_date IN VARCHAR2, p_meter_profile_id IN VARCHAR2)
IS
-- Fetch unit type for the meter profile
BEGIN
SELECT UPPER(f_dut_desc) INTO v_dut_desc
FROM t_def_unit_type
WHERE f_dut_id IN (SELECT dft.f_dut_id
FROM t_def_function_type dft
INNER JOIN t_meter_profile mp ON dft.f_dft_id = mp.f_mp_fk_dft_id
WHERE mp.f_mp_id = TO_NUMBER(p_meter_profile_id));
EXCEPTION
WHEN NO_DATA_FOUND THEN
v_dut_desc := ' ';
DBMS_OUTPUT.PUT_LINE('Unit type Not found');
END;--This Is Not Required (Remove This Statement)
END sp_substitute;
|
|
|
|
|
|
| Re: Converting long procedure with repeated code into package [message #311901 is a reply to message #311838] |
Mon, 07 April 2008 05:59 |
sonikumari
Messages: 74
Registered: May 2006
Location: Mumbai
|
Member |
|
|
Hi Rajatratewal,
The package and package body is created.
CREATE OR REPLACE PACKAGE pk_substitution AS
/*Declare externally visible contant that will be used through other procedures.*/
v_int_period t_meter_profile.f_mp_interval_period%TYPE;
v_dut_desc t_def_unit_type.f_dut_desc%TYPE;
v_session_id t_session.f_s_id%TYPE;
v_session_create_date t_session.f_s_created%TYPE;
v_session_start_date t_session.f_s_start%TYPE;
v_adl_reading t_data_stream.f_dst_adl%TYPE;
n_int_per_day FLOAT;
v_dateformat VARCHAR(30);
v_dateformat DATE := 'DD/MM/YYYY HH24:MI:SS';
v_timeformat DATE := 'HH24:MI:SS';
v_session_create_date DATE := SYSDATE;
v_session_start_date DATE := SYSDATE;
/*Proc sp_substitute process general variables that will be used for 5 types in procedure sp_gen_type*/
PROCEDURE sp_substitute (p_start_date IN DATE, p_end_date IN DATE, p_meter_profile_id IN VARCHAR2);
/*Proc sp_gen_type is a general procedure for 5 types*/
PROCEDURE sp_gen_type (p_start_date IN DATE, p_end_date IN DATE, v_dut_desc IN VARCHAR2);
END pk_substitution;
CREATE OR REPLACE PACKAGE BODY pk_substitution IS
PROCEDURE sp_substitute (p_start_date IN DATE, p_end_date IN DATE, p_meter_profile_id IN VARCHAR2)
IS
-- Fetch unit type for the meter profile
BEGIN
SELECT UPPER(f_dut_desc) INTO v_dut_desc
FROM t_def_unit_type
WHERE f_dut_id IN (SELECT dft.f_dut_id
FROM t_def_function_type dft
INNER JOIN t_meter_profile mp ON dft.f_dft_id = mp.f_mp_fk_dft_id
WHERE mp.f_mp_id = TO_NUMBER(p_meter_profile_id));
EXCEPTION
WHEN NO_DATA_FOUND THEN
v_dut_desc := ' ';
DBMS_OUTPUT.PUT_LINE('Unit type Not found');
---------------------------------------------------------
--******CALLING GENERAL PROCEDURE FROM THIS PROCEDURE
---------------------------------------------------------
sp_gen_type (p_start_date, p_end_date, v_dut_desc);
END sp_substitute;
PROCEDURE sp_gen_type (p_start_date IN DATE, p_end_date IN DATE, v_dut_desc IN VARCHAR2)
IS
strsql VARCHAR2(400);
v_session_end_date t_session.f_s_end%TYPE;
v_pk_id NUMBER(15);
d_start_tc VARCHAR(20);
d_end_tc VARCHAR(20);
n_total_valid_tc FLOAT;
n_starttc_reading FLOAT;
n_endtc_reading FLOAT;
v_count INTEGER;
v_reading_taken VARCHAR(20);
v_event INTEGER;
v_weekday VARCHAR2(2);
n_avg_reading FLOAT;
n_total_avg_reading FLOAT;
n_diff_tc FLOAT;
n_start_tc FLOAT;
n_end_tc FLOAT;
n_valid_rdg FLOAT;
n_reading FLOAT;
v_reason VARCHAR2(30);
v_reason_id NUMBER(15);
BEGIN
-- ##Fetch the maximumm ID
strsql := 'SELECT NVL(MAX(f_ri_'||v_dut_desc||'_id),0)
FROM t_rdg_int_' ||v_dut_desc;
EXECUTE IMMEDIATE strsql INTO v_pk_id;
--dbms_output.put_line('--------- v_pk_id ----------');
--dbms_output.put_line(v_pk_id);
-- Fetch the Maximum Reason ID
strsql := 'SELECT NVL(MAX(f_si_'||v_dut_desc||'_id),0)
FROM t_sub_int_' ||v_dut_desc;
EXECUTE IMMEDIATE strsql INTO v_reason_id;
--dbms_output.put_line('--------- v_reason_id ----------');
--dbms_output.put_line(v_reason_id);
END sp_gen_type;
END pk_substitution;
Now I am trying to execute the procedure inside it by the below command on sql prompt for testing step by step before I add more script to it.
SQL> exec pk_substitution.sp_substitute(sysdate,sysdate,'16148425');
BEGIN pk_substitution.sp_substitute(sysdate,sysdate,'16148425'); END;
*
ERROR at line 1:
ORA-00911: invalid character
ORA-06512: at "NOC_TEST.PK_SUBSTITUTION", line 49
ORA-06512: at "NOC_TEST.PK_SUBSTITUTION", line 19
ORA-01403: no data found
ORA-06512: at line 1
I am not able to understand as why I am getting this error.
Can you give me some suggestion if the way I am executng is correct or wrong.
Thanks,
Soni
[EDITED by LF: fixed [code] tags]
[Updated on: Mon, 07 April 2008 06:33] by Moderator Report message to a moderator |
|
|
|
|
|
|
|
|
|
| Re: Converting long procedure with repeated code into package [message #312116 is a reply to message #311016] |
Tue, 08 April 2008 00:46 |
bibsdash
Messages: 47
Registered: April 2008
|
Member |
|
|
Try this:
CREATE OR REPLACE PACKAGE BODY pk_substitution IS
PROCEDURE sp_substitute (p_start_date IN VARCHAR2, p_end_date IN VARCHAR2, p_meter_profile_id IN VARCHAR2)
IS
BEGIN -- Main begin processing
-- Fetch interval period for the meter profile
BEGIN
SELECT f_mp_interval_period INTO v_int_period
FROM t_meter_profile
WHERE f_mp_id = TO_NUMBER(p_meter_profile_id);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('No Interval Period is found');
-- Calculate interval per day
n_int_per_day := (24 * 60)/v_int_period;
END;
-- Fetch unit type for the meter profile
BEGIN
SELECT UPPER(f_dut_desc) INTO v_dut_desc
FROM t_def_unit_type
WHERE f_dut_id IN (SELECT dft.f_dut_id
FROM t_def_function_type dft
INNER JOIN t_meter_profile mp ON dft.f_dft_id = mp.f_mp_fk_dft_id
WHERE mp.f_mp_id = TO_NUMBER(p_meter_profile_id));
EXCEPTION
WHEN NO_DATA_FOUND THEN
v_dut_desc := ' ';
DBMS_OUTPUT.PUT_LINE('Unit type Not found');
END;
END sp_substitute;
END pk_substitution;
|
|
|
|
|
|
|
|
| Re: Converting long procedure with repeated code into package [message #312144 is a reply to message #311901] |
Tue, 08 April 2008 01:47 |
bibsdash
Messages: 47
Registered: April 2008
|
Member |
|
|
SQL> exec pk_substitution.sp_substitute(sysdate,sysdate,'16148425');
BEGIN pk_substitution.sp_substitute(sysdate,sysdate,'16148425'); END;
*
ERROR at line 1:
ORA-00911: invalid character
ORA-06512: at "NOC_TEST.PK_SUBSTITUTION", line 49
ORA-06512: at "NOC_TEST.PK_SUBSTITUTION", line 19
ORA-01403: no data found
ORA-06512: at line 1
Baecause the procedure in the package doesnot have exception handling.
Select statement is giving exception with no data found.
|
|
|
|
| Re: Converting long procedure with repeated code into package [message #312151 is a reply to message #312144] |
Tue, 08 April 2008 02:00 |
sonikumari
Messages: 74
Registered: May 2006
Location: Mumbai
|
Member |
|
|
Thanks for your help. I have used pragma exception and done the different begin statements in only one and its not giving error now.
This procedure is getting a text value and I have to concate this to scripts for different types dynamically.
Its running for the select statements.
Now I have a cursor to run dynamically as below. This cursor declaration I will do in start of the procedure but giving here together :
FOR cur_rdg_int IN ( SELECT TO_CHAR(f_ri_'||v_dut_desc||'_reading_taken,v_dateformat)
FROM t_rdg_int_'||v_dut_desc||' ri1
WHERE f_ri_'||v_dut_desc||'_validated = 2
AND f_ri_'||v_dut_desc||'_reading_taken
BETWEEN TO_DATE(p_start_date,v_dateformat)
AND TO_DATE(p_end_date,v_dateformat)
AND f_ri_'||v_dut_desc||'_fk_mp_id = TO_NUMBER(p_meter_profile_id)
AND f_ri_'||v_dut_desc||'_id IN (SELECT MAX(f_ri_'||v_dut_desc||'_id)
FROM t_rdg_int_'||v_dut_desc||' ri2
WHERE ri2.f_ri_'||v_dut_desc||'_reading_taken = ri1.f_ri_'||v_dut_desc||'_reading_taken
AND ri2.f_ri_'||v_dut_desc||'_fk_mp_id = ri1.f_ri_'||v_dut_desc||'_fk_mp_id))
LOOP
v_count := 0;
n_total_avg_reading := 0;
FETCH cur_rdg_int INTO v_reading_taken;
EXIT WHEN cur_rdg_int%NOTFOUND; -- Find the interval period is a Public Holiday
SELECT COUNT(*) INTO v_count FROM t_public_holiday
WHERE t_ph_date = TRUNC(TO_DATE(v_reading_taken,v_dateformat));
IF v_count > 0 THEN
v_weekday := '1';
ELSE
v_weekday := TO_CHAR(TO_DATE(v_reading_taken,v_dateformat),'D');
END IF;
strsql := 'SELECT COUNT(f_ri_'||v_dut_desc||'_reading_taken),AVG(f_ri_'||v_dut_desc||'_reading)
FROM t_rdg_int_'||v_dut_desc||'
WHERE f_ri_'||v_dut_desc||'_reading_taken >= (TO_DATE(v_reading_taken,v_dateformat) - 28)
AND f_ri_'||v_dut_desc||'_reading_taken < TO_DATE(v_reading_taken,v_dateformat)
AND TO_CHAR(f_ri_'||v_dut_desc||'_reading_taken,'D') = v_weekday
AND TO_CHAR(f_ri_'||v_dut_desc||'_reading_taken,v_timeformat) = TO_CHAR(TO_DATE(v_reading_taken,v_dateformat),v_timeformat)
AND f_ri_'||v_dut_desc||'_validated = 1
AND f_ri_'||v_dut_desc||'_fk_mp_id = TO_NUMBER(p_meter_profile_id)';
EXECUTE IMMEDIATE strsql INTO INTO v_count,n_avg_reading;
IF v_count >= 4 AND v_reading_taken < d_end_tc THEN
n_total_avg_reading := n_total_avg_reading + n_avg_reading;
-- DBMS_OUTPUT.PUT_LINE('------ find total avg ----------');
-- DBMS_OUTPUT.PUT_LINE(n_total_avg_reading);
END IF;
END LOOP;
END;
As you have seen in above other statements I have concatenated '||v_dut_desc||' for different types using EXECUTE IMMEDIATE.
But I am not ablt to do this for cursor in the same procedure.
There might some syntax error with cursor...actually I am trying to do this inside a procedure where it should execute as a string as 'v_dut_desc' is getting concatenated to it dynamically with different values.
Please suggest me on this.
Thanks,
Soni
[Updated on: Tue, 08 April 2008 02:28] Report message to a moderator |
|
|
|
| Re: Converting long procedure with repeated code into package [message #312193 is a reply to message #312151] |
Tue, 08 April 2008 03:01 |
Frank
Messages: 7901
Registered: March 2000
|
Senior Member |
|
|
Do NOT report messages to get attention.
Reporting is meant for abusive posts, so unless you want your post removed, stay away from it.
About your code:
I really suggest you step back from your code, draw on a piece of paper what it is you're after and start over.
If you don't know (PL/)SQL, you should stay away from dynamic sql.
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Sat Feb 08 17:45:33 CST 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
