Home » SQL & PL/SQL » SQL & PL/SQL » ref cursor and count location wise (merged 4)
ref cursor and count location wise (merged 4) [message #318905] Thu, 08 May 2008 07:26 Go to next message
VEDDETA
Messages: 54
Registered: May 2008
Member
CREATE OR REPLACE PROCEDURE  MQ_PROC 
	( p_application_nm IN CODE.INDICATOR_TXT%TYPE,
	  p_loc_cd IN LOCATION.LOC_CD%TYPE DEFAULT NULL, 
	  p_profile_ind IN MQ_PROCESS_TBL.PROFILE_IND%TYPE DEFAULT NULL,
	  p_queue_name IN CODE.DESC%TYPE DEFAULT NULL,
	  p_count1 OUT number,
	  p_count2 OUT number, 
	  p_count3 OUT number,
	  p_count4 OUT number,
	  p_count5 OUT number)
 IS 
 	
 BEGIN
	
		IF p_loc_cd IS NULL THEN
			IF p_profile_ind IS NULL THEN
		    		    
					SELECT  
						COUNT(CASE WHEN(  A.IN_TMS > SYSTIMESTAMP - INTERVAL '60' MINUTE)  THEN 1  END),
						COUNT(CASE WHEN  A.IN_TMS> SYSTIMESTAMP - INTERVAL '2' hour  AND A.IN_TMS <= SYSTIMESTAMP - INTERVAL '1' hour THEN  1  END),
						COUNT(CASE WHEN  A.IN_TMS >= SYSTIMESTAMP - INTERVAL '24' hour  AND A.IN_TMS <= SYSTIMESTAMP - INTERVAL '2' hour THEN  1  END),
						COUNT(CASE WHEN (  A.IN_TMS < SYSTIMESTAMP - INTERVAL '1' DAY)  THEN  1  END),
						COUNT(*) AS CNT INTO p_count1, p_count2, p_count3, p_count4, p_count5
					FROM  MQ_PROCESS_TBL A,CODE B,LOCATION  C
					WHERE SUBSTR(A.PROCESS_DESC,1,4) = B.CODE_ENCR_TXT
					AND B.INDICATOR_TXT = p_application_nm
					AND INDICATOR_ID IN ('N','Y')
					AND B.DESC = p_queue_name; 
					--AND A.PROFILE_IND = p_profile_ind;
			
			ELSE
            		
				SELECT  
					COUNT(CASE WHEN(  A.IN_TMS > SYSTIMESTAMP - INTERVAL '60' MINUTE)  THEN 1  END),
					COUNT(CASE WHEN  A.IN_TMS> SYSTIMESTAMP - INTERVAL '2' hour  AND A.IN_TMS <= SYSTIMESTAMP - INTERVAL '1' hour THEN  1  END),
					COUNT(CASE WHEN  A.IN_TMS >= SYSTIMESTAMP - INTERVAL '24' hour  AND A.IN_TMS <= SYSTIMESTAMP - INTERVAL '2' hour THEN  1  END),
					COUNT(CASE WHEN (  A.IN_TMS < SYSTIMESTAMP - INTERVAL '1' DAY)  THEN  1  END),
					COUNT(*) AS CNT INTO p_count1, p_count2, p_count3, p_count4, p_count5
					FROM  MQ_PROCESS_TBL A,CODE B,LOCATION  C
				WHERE SUBSTR(A.PROCESS_DESC,1,4) = B.CODE_ENCR_TXT
				AND B.INDICATOR_TXT = p_application_nm
				AND INDICATOR_ID IN ('N','Y')
				AND A.PROFILE_IND = p_profile_ind
				AND B.DESC=p_queue_name ;
			END IF;
		ELSE
	
			SELECT  
				COUNT(CASE WHEN(  A.IN_TMS > SYSTIMESTAMP - INTERVAL '60' MINUTE)  THEN 1  END),
				COUNT(CASE WHEN  A.IN_TMS> SYSTIMESTAMP - INTERVAL '2' hour  AND A.IN_TMS <= SYSTIMESTAMP - INTERVAL '1' hour THEN  1  END),
				COUNT(CASE WHEN  A.IN_TMS >= SYSTIMESTAMP - INTERVAL '24' hour  AND A.IN_TMS <= SYSTIMESTAMP - INTERVAL '2' hour THEN  1  END),
				COUNT(CASE WHEN (  A.IN_TMS < SYSTIMESTAMP - INTERVAL '1' DAY)  THEN  1  END),
				COUNT(*) AS CNT INTO p_count1, p_count2, p_count3, p_count4, p_count5
			FROM  MQ_PROCESS_TBL A,CODE B,LOCATION  C
			WHERE SUBSTR(A.PROCESS_DESC,1,4) = B.CODE_ENCR_TXT
			AND B.INDICATOR_TXT = p_application_nm
			AND INDICATOR_ID IN ('N','Y')
			and B.DESC=p_queue_name 
			AND A.LOC_CD = p_loc_id;
			--AND A.PROFILE_IND = p_profile_ind;
		END IF;
	
	
	EXCEPTION
		WHEN NO_DATA_FOUND THEN
		NULL;
		

 END MQ_PROC;
 
/
procedure created successfully


After modifying the procedure,I am getting the below compliation error!

CREATE OR REPLACE PROCEDURE  MQ_PROC 
	( p_application_nm IN CODE.INDICATOR_TXT%TYPE,
	  p_loc_cd IN LOCATION.LOC_CD%TYPE DEFAULT NULL, 
	  p_profile_ind IN MQ_PROCESS_TBL.PROFILE_IND%TYPE DEFAULT NULL,
	  p_queue_name IN CODE.DESC%TYPE DEFAULT NULL,
	  p_count1 OUT number,
	  p_count2 OUT number, 
	  p_count3 OUT number,
	  p_count4 OUT number,
	  p_count5 OUT number)
 IS 
 	
 BEGIN
     ------ Below codition is appened ie, if queue name is not null 
	IF p_queue_name IS NOT NULL THEN

		IF p_loc_cd IS NULL THEN
			IF p_profile_ind IS NULL THEN
		    		    
					SELECT  
						COUNT(CASE WHEN(  A.IN_TMS > SYSTIMESTAMP - INTERVAL '60' MINUTE)  THEN 1  END),
						COUNT(CASE WHEN  A.IN_TMS> SYSTIMESTAMP - INTERVAL '2' hour  AND A.IN_TMS <= SYSTIMESTAMP - INTERVAL '1' hour THEN  1  END),
						COUNT(CASE WHEN  A.IN_TMS >= SYSTIMESTAMP - INTERVAL '24' hour  AND A.IN_TMS <= SYSTIMESTAMP - INTERVAL '2' hour THEN  1  END),
						COUNT(CASE WHEN (  A.IN_TMS < SYSTIMESTAMP - INTERVAL '1' DAY)  THEN  1  END),
						COUNT(*) AS CNT INTO p_count1, p_count2, p_count3, p_count4, p_count5
					FROM  MQ_PROCESS_TBL A,CODE B,LOCATION  C
					WHERE SUBSTR(A.PROCESS_DESC,1,4) = B.CODE_ENCR_TXT
					AND B.INDICATOR_TXT = p_application_nm
					AND INDICATOR_ID IN ('N','Y')
					AND B.DESC = p_queue_name; 
					--AND A.PROFILE_IND = p_profile_ind;
			
			ELSE
            		
				SELECT  
					COUNT(CASE WHEN(  A.IN_TMS > SYSTIMESTAMP - INTERVAL '60' MINUTE)  THEN 1  END),
					COUNT(CASE WHEN  A.IN_TMS> SYSTIMESTAMP - INTERVAL '2' hour  AND A.IN_TMS <= SYSTIMESTAMP - INTERVAL '1' hour THEN  1  END),
					COUNT(CASE WHEN  A.IN_TMS >= SYSTIMESTAMP - INTERVAL '24' hour  AND A.IN_TMS <= SYSTIMESTAMP - INTERVAL '2' hour THEN  1  END),
					COUNT(CASE WHEN (  A.IN_TMS < SYSTIMESTAMP - INTERVAL '1' DAY)  THEN  1  END),
					COUNT(*) AS CNT INTO p_count1, p_count2, p_count3, p_count4, p_count5
					FROM  MQ_PROCESS_TBL A,CODE B,LOCATION  C
				WHERE SUBSTR(A.PROCESS_DESC,1,4) = B.CODE_ENCR_TXT
				AND B.INDICATOR_TXT = p_application_nm
				AND INDICATOR_ID IN ('N','Y')
				AND A.PROFILE_IND = p_profile_ind
				AND B.DESC=p_queue_name ;
			END IF;
		ELSE
	
			SELECT  
				COUNT(CASE WHEN(  A.IN_TMS > SYSTIMESTAMP - INTERVAL '60' MINUTE)  THEN 1  END),
				COUNT(CASE WHEN  A.IN_TMS> SYSTIMESTAMP - INTERVAL '2' hour  AND A.IN_TMS <= SYSTIMESTAMP - INTERVAL '1' hour THEN  1  END),
				COUNT(CASE WHEN  A.IN_TMS >= SYSTIMESTAMP - INTERVAL '24' hour  AND A.IN_TMS <= SYSTIMESTAMP - INTERVAL '2' hour THEN  1  END),
				COUNT(CASE WHEN (  A.IN_TMS < SYSTIMESTAMP - INTERVAL '1' DAY)  THEN  1  END),
				COUNT(*) AS CNT INTO p_count1, p_count2, p_count3, p_count4, p_count5
			FROM  MQ_PROCESS_TBL A,CODE B,LOCATION  C
			WHERE SUBSTR(A.PROCESS_DESC,1,4) = B.CODE_ENCR_TXT
			AND B.INDICATOR_TXT = p_application_nm
			AND INDICATOR_ID IN ('N','Y')
			and B.DESC=p_queue_name 
			AND A.LOC_CD = p_loc_id;
			--AND A.PROFILE_IND = p_profile_ind;
		END IF;
        -------// the below lines has been appended  
	ELSE
		SELECT  
				COUNT(CASE WHEN(  A.IN_TMS > SYSTIMESTAMP - INTERVAL '60' MINUTE)  THEN 1  END),
				COUNT(CASE WHEN  A.IN_TMS> SYSTIMESTAMP - INTERVAL '2' hour  AND A.IN_TMS <= SYSTIMESTAMP - INTERVAL '1' hour THEN  1  END),
				COUNT(CASE WHEN  A.IN_TMS >= SYSTIMESTAMP - INTERVAL '24' hour  AND A.IN_TMS <= SYSTIMESTAMP - INTERVAL '2' hour THEN  1  END),
				COUNT(CASE WHEN (  A.IN_TMS < SYSTIMESTAMP - INTERVAL '1' DAY)  THEN  1  END),
				COUNT(*) AS CNT INTO p_count1, p_count2, p_count3, p_count4, p_count5
			FROM  MQ_PROCESS_TBL A,CODE B,LOCATION  C
			WHERE SUBSTR(A.PROCESS_DESC,1,4) = B.CODE_ENCR_TXT
			AND B.INDICATOR_TXT = p_application_nm
			AND INDICATOR_ID IN ('N','Y')
			--and B.DESC=p_queue_name 
			-- AND A.LOC_CD = p_loc_id;
			--AND A.PROFILE_IND = p_profile_ind;
	
	
	
	END IF;
        -------- modified upto here
	
	EXCEPTION
		WHEN NO_DATA_FOUND THEN
		NULL;
		

 END MQ_PROC;
 
/

Warning: Procedure created with compilation errors.

SQL> SHOW ERRORS
Usage: SHOW ERRORS [{ FUNCTION | PROCEDURE | PACKAGE |
   PACKAGE BODY | TRIGGER | VIEW
   | TYPE | TYPE BODY | DIMENSION
   | JAVA SOURCE | JAVA CLASS } [schema.]name]






Need help on this issue.

Regards,
VEDDETA

[Updated on: Thu, 08 May 2008 07:35]

Report message to a moderator

Re: Compilation error! [message #318910 is a reply to message #318905] Thu, 08 May 2008 07:34 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Quote:

AND INDICATOR_ID IN ('N','Y')
--and B.DESC=p_queue_name
-- AND A.LOC_CD = p_loc_id;
--AND A.PROFILE_IND = p_profile_ind;



Regards

Raj
Re: Whats the significance of this Compilation error! [message #318914 is a reply to message #318905] Thu, 08 May 2008 07:46 Go to previous messageGo to next message
VEDDETA
Messages: 54
Registered: May 2008
Member
Thanks Raj for pointing out the mistake that I have made. But its still showing the same compilation error!
Re: Whats the significance of this Compilation error! [message #318916 is a reply to message #318914] Thu, 08 May 2008 07:49 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
It would really help if you could actually show us the error.

Try
select * 
from   user_errors
where  name = 'MQ_PROC'
Re: Whats the significance of this Compilation error! [message #318924 is a reply to message #318914] Thu, 08 May 2008 08:02 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Quote:

CREATE OR REPLACE PROCEDURE MQ_PROC
( p_application_nm IN CODE.INDICATOR_TXT%TYPE,
p_loc_cd IN LOCATION.LOC_CD%TYPE DEFAULT NULL,

and B.DESC=p_queue_name
AND A.LOC_CD = p_loc_id;



Variable name p_loc_id is misspelt.

From next time please follow what @Jrowbottom has suggested.

Regards

Raj
Re: Whats the significance of this Compilation error! [message #318927 is a reply to message #318924] Thu, 08 May 2008 08:05 Go to previous messageGo to next message
joy_division
Messages: 4643
Registered: February 2005
Location: East Coast USA
Senior Member
Holy cow! Amazing eyes. Good one Raj.
Re: Whats the significance of this Compilation error! [message #318934 is a reply to message #318905] Thu, 08 May 2008 08:25 Go to previous messageGo to next message
VEDDETA
Messages: 54
Registered: May 2008
Member
Thanks for your help! I was been able to create the procedure successfully without any compilation error after making some changes.


Modified: IF p_queue_name IS NULL THEN ( Instead of IF p_queue_name IS NOT NULL THEN)


One more question. How I would get the count for all location id's and queues.I mean how can I modify the OUT parameter to REF CURSOR.


I want to get the counts based on GROUP BY LOCATION, QUEUE.
Do I need to use Ref cursor for that? What need to be modified to make this happen? Need help!


CREATE OR REPLACE PROCEDURE  MQ_PROC 
	( p_application_nm IN CODE.INDICATOR_TXT%TYPE,
	  p_loc_cd IN LOCATION.LOC_CD%TYPE DEFAULT NULL, 
	  p_profile_ind IN MQ_PROCESS_TBL.PROFILE_IND%TYPE DEFAULT NULL,
	  p_queue_name IN CODE.DESC%TYPE DEFAULT NULL,
	  p_count1 OUT number,
	  p_count2 OUT number, 
	  p_count3 OUT number,
	  p_count4 OUT number,
	  p_count5 OUT number)
 IS 
 	
 BEGIN
  ------ Below codition is appened ie, if queue name is not null 
-----Modified if queue name is null 	
IF p_queue_name IS  NULL THEN

		SELECT  
				COUNT(CASE WHEN(  A.IN_TMS > SYSTIMESTAMP - INTERVAL '60' MINUTE)  THEN 1  END),
				COUNT(CASE WHEN  A.IN_TMS> SYSTIMESTAMP - INTERVAL '2' hour  AND A.IN_TMS <= SYSTIMESTAMP - INTERVAL '1' hour THEN  1  END),
				COUNT(CASE WHEN  A.IN_TMS >= SYSTIMESTAMP - INTERVAL '24' hour  AND A.IN_TMS <= SYSTIMESTAMP - INTERVAL '2' hour THEN  1  END),
				COUNT(CASE WHEN (  A.IN_TMS < SYSTIMESTAMP - INTERVAL '1' DAY)  THEN  1  END),
				COUNT(*) AS CNT INTO p_count1, p_count2, p_count3, p_count4, p_count5
			FROM  MQ_PROCESS_TBL A,CODE B,LOCATION  C
			WHERE SUBSTR(A.PROCESS_DESC,1,4) = B.CODE_ENCR_TXT
			AND B.INDICATOR_TXT = p_application_nm
			AND INDICATOR_ID IN ('N','Y')
			--and B.DESC=p_queue_name 
			-- AND A.LOC_CD = p_loc_id;
			--AND A.PROFILE_IND = p_profile_ind;
ELSE

		IF p_loc_cd IS NULL THEN
			IF p_profile_ind IS NULL THEN
		    		    
					SELECT  
						COUNT(CASE WHEN(  A.IN_TMS > SYSTIMESTAMP - INTERVAL '60' MINUTE)  THEN 1  END),
						COUNT(CASE WHEN  A.IN_TMS> SYSTIMESTAMP - INTERVAL '2' hour  AND A.IN_TMS <= SYSTIMESTAMP - INTERVAL '1' hour THEN  1  END),
						COUNT(CASE WHEN  A.IN_TMS >= SYSTIMESTAMP - INTERVAL '24' hour  AND A.IN_TMS <= SYSTIMESTAMP - INTERVAL '2' hour THEN  1  END),
						COUNT(CASE WHEN (  A.IN_TMS < SYSTIMESTAMP - INTERVAL '1' DAY)  THEN  1  END),
						COUNT(*) AS CNT INTO p_count1, p_count2, p_count3, p_count4, p_count5
					FROM  MQ_PROCESS_TBL A,CODE B,LOCATION  C
					WHERE SUBSTR(A.PROCESS_DESC,1,4) = B.CODE_ENCR_TXT
					AND B.INDICATOR_TXT = p_application_nm
					AND INDICATOR_ID IN ('N','Y')
					AND B.DESC = p_queue_name; 
					--AND A.PROFILE_IND = p_profile_ind;
			
			ELSE
            		
				SELECT  
					COUNT(CASE WHEN(  A.IN_TMS > SYSTIMESTAMP - INTERVAL '60' MINUTE)  THEN 1  END),
					COUNT(CASE WHEN  A.IN_TMS> SYSTIMESTAMP - INTERVAL '2' hour  AND A.IN_TMS <= SYSTIMESTAMP - INTERVAL '1' hour THEN  1  END),
					COUNT(CASE WHEN  A.IN_TMS >= SYSTIMESTAMP - INTERVAL '24' hour  AND A.IN_TMS <= SYSTIMESTAMP - INTERVAL '2' hour THEN  1  END),
					COUNT(CASE WHEN (  A.IN_TMS < SYSTIMESTAMP - INTERVAL '1' DAY)  THEN  1  END),
					COUNT(*) AS CNT INTO p_count1, p_count2, p_count3, p_count4, p_count5
					FROM  MQ_PROCESS_TBL A,CODE B,LOCATION  C
				WHERE SUBSTR(A.PROCESS_DESC,1,4) = B.CODE_ENCR_TXT
				AND B.INDICATOR_TXT = p_application_nm
				AND INDICATOR_ID IN ('N','Y')
				AND A.PROFILE_IND = p_profile_ind
				AND B.DESC=p_queue_name ;
			END IF;
		ELSE
	
			SELECT  
				COUNT(CASE WHEN(  A.IN_TMS > SYSTIMESTAMP - INTERVAL '60' MINUTE)  THEN 1  END),
				COUNT(CASE WHEN  A.IN_TMS> SYSTIMESTAMP - INTERVAL '2' hour  AND A.IN_TMS <= SYSTIMESTAMP - INTERVAL '1' hour THEN  1  END),
				COUNT(CASE WHEN  A.IN_TMS >= SYSTIMESTAMP - INTERVAL '24' hour  AND A.IN_TMS <= SYSTIMESTAMP - INTERVAL '2' hour THEN  1  END),
				COUNT(CASE WHEN (  A.IN_TMS < SYSTIMESTAMP - INTERVAL '1' DAY)  THEN  1  END),
				COUNT(*) AS CNT INTO p_count1, p_count2, p_count3, p_count4, p_count5
			FROM  MQ_PROCESS_TBL A,CODE B,LOCATION  C
			WHERE SUBSTR(A.PROCESS_DESC,1,4) = B.CODE_ENCR_TXT
			AND B.INDICATOR_TXT = p_application_nm
			AND INDICATOR_ID IN ('N','Y')
			and B.DESC=p_queue_name 
			AND A.LOC_CD = p_loc_id;
			--AND A.PROFILE_IND = p_profile_ind;
		END IF;
        -------// the below lines has been appended  
	
	
	
	
	END IF;
        -------- modified upto here
	
	EXCEPTION
		WHEN NO_DATA_FOUND THEN
		NULL;
		

 END MQ_PROC;
 
/



Re: Whats the significance of this Compilation error! [message #318936 is a reply to message #318916] Thu, 08 May 2008 08:27 Go to previous messageGo to next message
VEDDETA
Messages: 54
Registered: May 2008
Member
JRowbottom wrote on Thu, 08 May 2008 07:49
It would really help if you could actually show us the error.

Try
select * 
from   user_errors
where  name = 'MQ_PROC'





Thank you very much! Thanks for your help!
ref cursor and count locatio nwise (merged 3) [message #318940 is a reply to message #318905] Thu, 08 May 2008 08:38 Go to previous messageGo to next message
VEDDETA
Messages: 54
Registered: May 2008
Member
In the procedure below I am getting the count. But I want to the count based on GROUP BY Location, Queue.

Do I need to use ref cursor for this? If yes, how I will modify the below procedure to get the count for ALL location, All Queue as well.

How I would modify the below stored procedure?

CREATE OR REPLACE PROCEDURE  MQ_PROC 
	( p_application_nm IN CODE.INDICATOR_TXT%TYPE,
	  p_loc_cd IN LOCATION.LOC_CD%TYPE DEFAULT NULL, 
	  p_profile_ind IN MQ_PROCESS_TBL.PROFILE_IND%TYPE DEFAULT NULL,
	  p_queue_name IN CODE.DESC%TYPE DEFAULT NULL,
	  p_count1 OUT number,
	  p_count2 OUT number, 
	  p_count3 OUT number,
	  p_count4 OUT number,
	  p_count5 OUT number)
 IS 
 	
 BEGIN
	
		IF p_loc_cd IS NULL THEN
			IF p_profile_ind IS NULL THEN
		    		    
					SELECT  
						COUNT(CASE WHEN(  A.IN_TMS > SYSTIMESTAMP - INTERVAL '60' MINUTE)  THEN 1  END),
						COUNT(CASE WHEN  A.IN_TMS> SYSTIMESTAMP - INTERVAL '2' hour  AND A.IN_TMS <= SYSTIMESTAMP - INTERVAL '1' hour THEN  1  END),
						COUNT(CASE WHEN  A.IN_TMS >= SYSTIMESTAMP - INTERVAL '24' hour  AND A.IN_TMS <= SYSTIMESTAMP - INTERVAL '2' hour THEN  1  END),
						COUNT(CASE WHEN (  A.IN_TMS < SYSTIMESTAMP - INTERVAL '1' DAY)  THEN  1  END),
						COUNT(*) AS CNT INTO p_count1, p_count2, p_count3, p_count4, p_count5
					FROM  MQ_PROCESS_TBL A,CODE B,LOCATION  C
					WHERE SUBSTR(A.PROCESS_DESC,1,4) = B.CODE_ENCR_TXT
					AND B.INDICATOR_TXT = p_application_nm
					AND INDICATOR_ID IN ('N','Y')
					AND B.DESC = p_queue_name; 
					--AND A.PROFILE_IND = p_profile_ind;
			
			ELSE
            		
				SELECT  
					COUNT(CASE WHEN(  A.IN_TMS > SYSTIMESTAMP - INTERVAL '60' MINUTE)  THEN 1  END),
					COUNT(CASE WHEN  A.IN_TMS> SYSTIMESTAMP - INTERVAL '2' hour  AND A.IN_TMS <= SYSTIMESTAMP - INTERVAL '1' hour THEN  1  END),
					COUNT(CASE WHEN  A.IN_TMS >= SYSTIMESTAMP - INTERVAL '24' hour  AND A.IN_TMS <= SYSTIMESTAMP - INTERVAL '2' hour THEN  1  END),
					COUNT(CASE WHEN (  A.IN_TMS < SYSTIMESTAMP - INTERVAL '1' DAY)  THEN  1  END),
					COUNT(*) AS CNT INTO p_count1, p_count2, p_count3, p_count4, p_count5
					FROM  MQ_PROCESS_TBL A,CODE B,LOCATION  C
				WHERE SUBSTR(A.PROCESS_DESC,1,4) = B.CODE_ENCR_TXT
				AND B.INDICATOR_TXT = p_application_nm
				AND INDICATOR_ID IN ('N','Y')
				AND A.PROFILE_IND = p_profile_ind
				AND B.DESC=p_queue_name ;
			END IF;
		ELSE
	
			SELECT  
				COUNT(CASE WHEN(  A.IN_TMS > SYSTIMESTAMP - INTERVAL '60' MINUTE)  THEN 1  END),
				COUNT(CASE WHEN  A.IN_TMS> SYSTIMESTAMP - INTERVAL '2' hour  AND A.IN_TMS <= SYSTIMESTAMP - INTERVAL '1' hour THEN  1  END),
				COUNT(CASE WHEN  A.IN_TMS >= SYSTIMESTAMP - INTERVAL '24' hour  AND A.IN_TMS <= SYSTIMESTAMP - INTERVAL '2' hour THEN  1  END),
				COUNT(CASE WHEN (  A.IN_TMS < SYSTIMESTAMP - INTERVAL '1' DAY)  THEN  1  END),
				COUNT(*) AS CNT INTO p_count1, p_count2, p_count3, p_count4, p_count5
			FROM  MQ_PROCESS_TBL A,CODE B,LOCATION  C
			WHERE SUBSTR(A.PROCESS_DESC,1,4) = B.CODE_ENCR_TXT
			AND B.INDICATOR_TXT = p_application_nm
			AND INDICATOR_ID IN ('N','Y')
			and B.DESC=p_queue_name 
			AND A.LOC_CD = p_loc_cd;
			--AND A.PROFILE_IND = p_profile_ind;
		END IF;
	
	
	EXCEPTION
		WHEN NO_DATA_FOUND THEN
		NULL;
		

 END MQ_PROC;
 
/



Need help!

[Updated on: Thu, 08 May 2008 09:02]

Report message to a moderator

Requirement: How to get count location wise ( group by all location)? [message #319101 is a reply to message #318940] Fri, 09 May 2008 01:22 Go to previous messageGo to next message
VEDDETA
Messages: 54
Registered: May 2008
Member

How can I modify the below procedure to get the queue count locationwise (group by all location) also ? Need help!



CREATE OR REPLACE PROCEDURE  MQ_PROC 
	( p_application_nm IN CODE.INDICATOR_TXT%TYPE,
	  p_loc_cd IN LOCATION.LOC_CD%TYPE DEFAULT NULL, 
	  p_profile_ind IN MQ_PROCESS_TBL.PROFILE_IND%TYPE DEFAULT NULL,
	  p_queue_name IN CODE.DESC%TYPE DEFAULT NULL,
	  p_count1 OUT number,
	  p_count2 OUT number, 
	  p_count3 OUT number,
	  p_count4 OUT number,
	  p_count5 OUT number)
 IS 
 	
 BEGIN
	
		IF p_loc_cd IS NULL THEN
			IF p_profile_ind IS NULL THEN
		    		    
					SELECT  
						COUNT(CASE WHEN(  A.IN_TMS > SYSTIMESTAMP - INTERVAL '60' MINUTE)  THEN 1  END),
						COUNT(CASE WHEN  A.IN_TMS> SYSTIMESTAMP - INTERVAL '2' hour  AND A.IN_TMS <= SYSTIMESTAMP - INTERVAL '1' hour THEN  1  END),
						COUNT(CASE WHEN  A.IN_TMS >= SYSTIMESTAMP - INTERVAL '24' hour  AND A.IN_TMS <= SYSTIMESTAMP - INTERVAL '2' hour THEN  1  END),
						COUNT(CASE WHEN (  A.IN_TMS < SYSTIMESTAMP - INTERVAL '1' DAY)  THEN  1  END),
						COUNT(*) AS CNT INTO p_count1, p_count2, p_count3, p_count4, p_count5
					FROM  MQ_PROCESS_TBL A,CODE B,LOCATION  C
					WHERE SUBSTR(A.PROCESS_DESC,1,4) = B.CODE_ENCR_TXT
					AND B.INDICATOR_TXT = p_application_nm
					AND INDICATOR_ID IN ('N','Y')
					AND B.DESC = p_queue_name; 
					--AND A.PROFILE_IND = p_profile_ind;
			
			ELSE
            		
				SELECT  
					COUNT(CASE WHEN(  A.IN_TMS > SYSTIMESTAMP - INTERVAL '60' MINUTE)  THEN 1  END),
					COUNT(CASE WHEN  A.IN_TMS> SYSTIMESTAMP - INTERVAL '2' hour  AND A.IN_TMS <= SYSTIMESTAMP - INTERVAL '1' hour THEN  1  END),
					COUNT(CASE WHEN  A.IN_TMS >= SYSTIMESTAMP - INTERVAL '24' hour  AND A.IN_TMS <= SYSTIMESTAMP - INTERVAL '2' hour THEN  1  END),
					COUNT(CASE WHEN (  A.IN_TMS < SYSTIMESTAMP - INTERVAL '1' DAY)  THEN  1  END),
					COUNT(*) AS CNT INTO p_count1, p_count2, p_count3, p_count4, p_count5
					FROM  MQ_PROCESS_TBL A,CODE B,LOCATION  C
				WHERE SUBSTR(A.PROCESS_DESC,1,4) = B.CODE_ENCR_TXT
				AND B.INDICATOR_TXT = p_application_nm
				AND INDICATOR_ID IN ('N','Y')
				AND A.PROFILE_IND = p_profile_ind
				AND B.DESC=p_queue_name ;
			END IF;
		ELSE
	
			SELECT  
				COUNT(CASE WHEN(  A.IN_TMS > SYSTIMESTAMP - INTERVAL '60' MINUTE)  THEN 1  END),
				COUNT(CASE WHEN  A.IN_TMS> SYSTIMESTAMP - INTERVAL '2' hour  AND A.IN_TMS <= SYSTIMESTAMP - INTERVAL '1' hour THEN  1  END),
				COUNT(CASE WHEN  A.IN_TMS >= SYSTIMESTAMP - INTERVAL '24' hour  AND A.IN_TMS <= SYSTIMESTAMP - INTERVAL '2' hour THEN  1  END),
				COUNT(CASE WHEN (  A.IN_TMS < SYSTIMESTAMP - INTERVAL '1' DAY)  THEN  1  END),
				COUNT(*) AS CNT INTO p_count1, p_count2, p_count3, p_count4, p_count5
			FROM  MQ_PROCESS_TBL A,CODE B,LOCATION  C
			WHERE SUBSTR(A.PROCESS_DESC,1,4) = B.CODE_ENCR_TXT
			AND B.INDICATOR_TXT = p_application_nm
			AND INDICATOR_ID IN ('N','Y')
			and B.DESC=p_queue_name 
			AND A.LOC_CD = p_loc_cd;
			--AND A.PROFILE_IND = p_profile_ind;
		END IF;
	
	
	EXCEPTION
		WHEN NO_DATA_FOUND THEN
		NULL;
		

 END MQ_PROC;
 
/

REF CURSOR [message #319174 is a reply to message #318940] Fri, 09 May 2008 04:51 Go to previous messageGo to next message
VEDDETA
Messages: 54
Registered: May 2008
Member
Whats the difference of using REF CURSOR as OUT parameter or declare it as a SYS_REFCURSOR?

I have created the below procedure using sys ref cursor.
My question is..
1. Can I use ref cursor here as OUT parameter
2. How can I see the values?

Please help!


CREATE OR REPLACE PROCEDURE c1_COUNT 
	( p_application_nm IN CODE.IND_TXT%TYPE,
	  p_loc_id IN LOCATION.LOCATION_ID%TYPE DEFAULT NULL, 
	  p_queue_nm IN CODE.DESC%TYPE DEFAULT NULL)
	
 IS 
 	  c1_refcur      SYS_REFCURSOR;
	  p_cnt_1   number;
	  p_cnt_2  number;
	  p_cnt_3  number;
	  p_cnt_4  number;
	  p_cnt_5 number;
	
	
 BEGIN
 OPEN c1_refcur FOR
	SELECT  
		COUNT(CASE WHEN(  A.TMS1> SYSTIMESTAMP - INTERVAL '60' MINUTE)  THEN 1  END),
		COUNT(CASE WHEN  A.LAST_UPD_TMS> SYSTIMESTAMP - INTERVAL '2' hour  AND A.TMS1<= SYSTIMESTAMP - INTERVAL '1' hour THEN  1  END),
		COUNT(CASE WHEN  A.TMS1>= SYSTIMESTAMP - INTERVAL '24' hour  AND A.TMS1<= SYSTIMESTAMP - INTERVAL '2' hour THEN  1  END),
		COUNT(CASE WHEN (  A.TMS1< SYSTIMESTAMP - INTERVAL '1' DAY)  THEN  1  END),
        COUNT(*) AS CNT 
	FROM MQ A,CODE B,LOCATION  C
	WHERE SUBSTR(A.PROCESS_NAME,1,4) = B.INDICATOR_TXT
	--// Pass application name
	AND B.IND_TXT = p_application_nm
	AND LOCK_IND IN ('N','Y')
	---and NVL(RTRIM(RETRY_COUNTER_NBR),0) < 3
	--// Pass queue name
    and B.DESC=p_queue_nm 
	--// Pass location
	AND A.LOCATION_ID = p_loc_id;
	DBMS_OUTPUT.PUT_LINE('-----  OUTPUT   -------');
    LOOP
        FETCH c1_refcur INTO  p_cnt_1, p_cnt_2,p_cnt_3, p_cnt_4, p_cnt_5 ;
        EXIT WHEN c1_refcur%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(p_cnt_1 || '     ' || p_cnt_2 || '     ' || p_cnt_3 || '     ' || p_cnt_4 || '     ' || p_cnt_5);
    END LOOP;
    CLOSE c1_refcur;

	
	EXCEPTION
		WHEN NO_DATA_FOUND THEN
		NULL;
		WHEN OTHERS THEN
		NULL;

 END c1_COUNT;
 
/

Re: REF CURSOR [message #319188 is a reply to message #319174] Fri, 09 May 2008 05:07 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Database PL/SQL User's Guide and Reference
Chapter 6 Performing SQL Operations from PL/SQL
Section Using Cursor Variables (REF CURSORs)
Subsection Declaring REF CURSOR Types and Cursor Variables

Regards
Michel
Re: REF CURSOR [message #319232 is a reply to message #319174] Fri, 09 May 2008 07:18 Go to previous messageGo to next message
VEDDETA
Messages: 54
Registered: May 2008
Member
Thank you very much for the link you have provided.

Regards,
Veddeta
Re: REF CURSOR [message #319234 is a reply to message #319232] Fri, 09 May 2008 07:30 Go to previous message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member

Simple example

SQL> create or replace procedure myproc (p_ref out sys_refcursor)
  2  is
  3  begin
  4  open p_ref for select * from scott.dept;
  5  end;
  6  /

Procedure created.

SQL> var c refcursor;
SQL> exec myproc(:c);

PL/SQL procedure successfully completed.

SQL> print c;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

SQL> 

Previous Topic: Read images from Long Raw datatype column in table
Next Topic: help me
Goto Forum:
  


Current Time: Fri Dec 09 00:08:32 CST 2016

Total time taken to generate the page: 0.27939 seconds