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 Go to next message
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 #311023 is a reply to message #311016] Thu, 03 April 2008 04:41 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
I am not able to understand what is required??./fa/1620/0/

What you expect we are going to write a package with our own imagination without knowing what has to be changed.

You better consult your seniors which have the knowledge of functioning of that procedure.
Re: Converting long procedure with repeated code into package [message #311029 is a reply to message #311023] Thu, 03 April 2008 04:52 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #311050 is a reply to message #311038] Thu, 03 April 2008 05:23 Go to previous messageGo to next message
sonikumari
Messages: 74
Registered: May 2006
Location: Mumbai
Member

You mean
CASE WHEN v_dut_desc = 'KVAH' THEN
Fetch the maximumm ID for KVAH
CASE WHEN v_dut_desc = 'KVARH' THEN
Fetch the maximumm ID for KVARH
CASE WHEN v_dut_desc = 'KVARH2' THEN
Fetch the maximumm ID for KVARH2
.....

And then Same way different cases for rest conditions.
-- 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

But this way the length of the code won't decrease . Only the if condition will change to case and little structured.

Please suggest me a way to make it smaller.

Or if I can create 5 procedures in a package.... please advice as client won't be able to able to maintain such alarge procedure.
Re: Converting long procedure with repeated code into package [message #311055 is a reply to message #311050] Thu, 03 April 2008 05:28 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #311316 is a reply to message #311055] Fri, 04 April 2008 01:25 Go to previous messageGo to next message
sonikumari
Messages: 74
Registered: May 2006
Location: Mumbai
Member

Hi rajatratewal,
I am really sorry to disturb you but I am new to package development and has to do this job asap. I am looking into tutorials but little confused about the structure of package with spec and body.
Please guide me little on this .... as I have very limited time.

Thanks,
Soni
Re: Converting long procedure with repeated code into package [message #311333 is a reply to message #311316] Fri, 04 April 2008 02:03 Go to previous messageGo to next message
Littlefoot
Messages: 20895
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
sonikumari
I have less time to accomplish this job and don't want to make any mistakes as well as complex.
I am new to package development and has to do this job asap.
I have very limited time.


I believe you should give up. Obviously, you are not capable of doing the job with the knowledge you currently have.

Why wouldn't you admit that? Go to your boss and tell him/her so (by the way, what kind of a boss is that if he/she expects you to do something you can not; does he/she not know what is your knowledge level?).

Because, if you insist on doing it ASAP and expect to do it correctly, I think you're digging yourself a deep hole.
Re: Converting long procedure with repeated code into package [message #311336 is a reply to message #311333] Fri, 04 April 2008 02:11 Go to previous messageGo to next message
sonikumari
Messages: 74
Registered: May 2006
Location: Mumbai
Member

Hi Littlefoot,
its the demand of time... and I have to do it only.
If you can give me some insight ...it will help me otherwise I am looking into tutorials.

Thanks in advance,
Soni
Re: Converting long procedure with repeated code into package [message #311376 is a reply to message #311336] Fri, 04 April 2008 05:15 Go to previous messageGo to next message
Littlefoot
Messages: 20895
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 Go to previous messageGo to next message
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 #311396 is a reply to message #311388] Fri, 04 April 2008 06:42 Go to previous messageGo to next message
Littlefoot
Messages: 20895
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Obviously, you didn't pay much attention to a simple example I provided. If you did, you'd see that there's no CREATE withing the package body.

Therefore, I doubt that your code would compile.

What's with part that is "the same as in procedure sp_kvah"? As I've understood your problem, you'd want to get rid of these procedures as they are the same as the first one (difference is in table names (something else?)).

I hope you don't expect me (or anyone else here) to do that job for you. I can't speak for others, but I certainly will not modify that code. I did what I could - provided guidelines. The rest is up to you.
Re: Converting long procedure with repeated code into package [message #311401 is a reply to message #311396] Fri, 04 April 2008 06:54 Go to previous messageGo to next message
sonikumari
Messages: 74
Registered: May 2006
Location: Mumbai
Member

If you see the code for one procedure sp_kvah...there are many columns and many tables being used in one procedure and they are all different in name.That is why I have used 5 procedures.
For the syntax I have just copied and pasted fast just to show you the structure.
The syntaxes must be wrong at many places. I will check them all once structure is decided if its worth.

Thanks,
Soni
Re: Converting long procedure with repeated code into package [message #311459 is a reply to message #311401] Fri, 04 April 2008 09:48 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Why do you have two different user-accounts?
Re: Converting long procedure with repeated code into package [message #311831 is a reply to message #311459] Mon, 07 April 2008 01:02 Go to previous messageGo to next message
sonikumari
Messages: 74
Registered: May 2006
Location: Mumbai
Member

Hi Frank,
One of my collegue's ID is monasingh. We both work together...by mistake replyed the message while looking at my problem when she was logged in with her login id.

Thanks,
Soni
Re: Converting long procedure with repeated code into package [message #311837 is a reply to message #311396] Mon, 07 April 2008 01:59 Go to previous messageGo to next message
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 Sad

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 Go to previous messageGo to next message
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 #311859 is a reply to message #311838] Mon, 07 April 2008 02:56 Go to previous messageGo to next message
sonikumari
Messages: 74
Registered: May 2006
Location: Mumbai
Member

Thanks rajatratewal,
I removed END and made some more changes and the package body is created too. I will now keep on adding the code little by little into it and testing.

Thanks for your help.

Soni

Re: Converting long procedure with repeated code into package [message #311901 is a reply to message #311838] Mon, 07 April 2008 05:59 Go to previous messageGo to next message
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 #311926 is a reply to message #311901] Mon, 07 April 2008 07:02 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
Have you handled exception in each and every procedure??

Quote:
sp_gen_type don't have any exception block


When you are putting data into a global variable and when no data is found it will return NO_DATA_FOUND exception.

Place an exception block in each and every procedure??
Re: Converting long procedure with repeated code into package [message #312108 is a reply to message #311926] Tue, 08 April 2008 00:26 Go to previous messageGo to next message
sonikumari
Messages: 74
Registered: May 2006
Location: Mumbai
Member

Hi rajatratewal,
As you said 'One Begin Must Have one End Statement'. I have multiple Begin End statement in a procedure.
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 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 sp_substitute; 
END pk_substitution;


Here I have given only two Begin End statement but I have 5 Begin End statement. Please advice as what to in this case as if I write the above code it is giving 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

20/4     PLS-00103: Encountered the symbol "PROCEDURE"
57/1     PLS-00103: Encountered the symbol "END" when expecting one of the
         following:
         begin function package pragma procedure form


Thanks,
Soni
Re: Converting long procedure with repeated code into package [message #312110 is a reply to message #312108] Tue, 08 April 2008 00:34 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Read a good book or document on PL/SQL coding. Don't try to guess how the syntax should be.
It is ok to have multiple begin-exception-end blocks, but they need to be nested in a procedure's main level, they cannot be siblings at the procedure-level.

[Updated on: Tue, 08 April 2008 00:36]

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 Go to previous messageGo to next message
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 #312132 is a reply to message #312108] Tue, 08 April 2008 01:20 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
END pk_substitution

where is the begin statement for this??
Re: Converting long procedure with repeated code into package [message #312141 is a reply to message #312132] Tue, 08 April 2008 01:39 Go to previous messageGo to next message
Littlefoot
Messages: 20895
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Here:
CREATE OR REPLACE PACKAGE BODY pk_substitution IS

Re: Converting long procedure with repeated code into package [message #312144 is a reply to message #311901] Tue, 08 April 2008 01:47 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Frank
Messages: 7880
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.
Re: Converting long procedure with repeated code into package [message #312197 is a reply to message #312193] Tue, 08 April 2008 03:14 Go to previous messageGo to next message
sonikumari
Messages: 74
Registered: May 2006
Location: Mumbai
Member

Hi Frank,
I will take care of reporting messages in future.
Please suggest me something as I have to do this dynamically.

Thanks,
Soni
Re: Converting long procedure with repeated code into package [message #312275 is a reply to message #311016] Tue, 08 April 2008 06:24 Go to previous message
sonikumari
Messages: 74
Registered: May 2006
Location: Mumbai
Member

Hi Frank,
I was able to solve the problem of dynamic cursor easily.
If anyone face this kind of problem please look into the link
http://www.adp-gmbh.ch/ora/plsql/cursors/dynamic.html
Thank you all for your help.

Thanks,
Soni
Previous Topic: few clarifications
Next Topic: Union clause casuing the first query to show up on the bottom
Goto Forum:
  


Current Time: Mon Dec 05 23:57:48 CST 2016

Total time taken to generate the page: 0.11154 seconds