Oracle procedure testing

As a developer, most of the time you have to testing on your script output.
Some how, you also need to debug into the script & checking on each variable value.

In Oracle procedure, if you doesn’t having any debugging tools like sql developer. It is very difficult to trace your procedure whether is working properly or not.
For me, I will using the DBMS_OUTPUT.PUTLINE command & sqlplus into the Oracle for the testing.

Below is the example.Oracle Procedure Example :

CREATE OR REPLACE PROCEDURE TEST1
(
	X IN NUMBER
)
AS
	E_ID VARCHAR2(36);
	E_EMAIL_ADDRESS VARCHAR2(150);
	E_EMAIL_ADDRESS_CAPS VARCHAR2(150);
	E_EMAIL_NOT_EXIST BOOLEAN;
	E_RELATION_NOT_EXIST BOOLEAN;
	V_ERR VARCHAR(2000);

BEGIN
	IF X > 10 THEN
		Y := 100;
	ELSEIF X < 10 THEN
		Y := 50;
	END IF;
	DBMS_OUTPUT.PUTLINE('RESULT Y = ' || Y);
END;

Command Example : 

[root@server01 ~]# sqlplus username/password@servicename

SQL*Plus: Release 11.1.0.6.0 - Production on Thu Mar 19 18:09:00 2009


Copyright (c) 1982, 2007, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options


SQL> set serveroutput on;
SQL> execute TEST1(20);
100 


					

					














	
3 Comments to “Oracle procedure testing”




	
    

	                 
   
  1. 
      
      
    
      
                
    
                    
    
                  Robertonio 25 March 2011 at 11:43 pm #          
    
      
    
      
    
                                
    Should the output not read “RESULT Y = 100”
    
                                                                
    
                              Reply            
    

      
    

    2. 
                 
   
  2. 
      
      
    
      
                
    
                    
    
                  Advanced Option Strategy Screening and Analysis 22 March 2012 at 5:47 pm #          
    
      
    
      
    
                                
    It’s really a cool and helpful piece of information. I am happy that you shared this helpful info with us. Please keep us informed like this. Thank you for sharing.
    
                                                                
    
                              Reply            
    

      
    

    3. 
                 
   
  3. 
      
      
    
      
                
    
                    
    
                  Sean Prahm 21 August 2012 at 12:16 am #          
    
      
    
      
    
                                
    Really good site, thank you very much for your effort in writing the posts.
    
                                                                
    
                              Reply            
    

      
    

    4. 

	
    



	


		


		


		


	


	


 





 












Leave a Reply