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 :
[[email protected] ~]# sqlplus username/[email protected]SQL*Plus: Release 11.1.0.6.0 - Production on Thu Mar 19 18:09:00 2009Copyright (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 optionsSQL> set serveroutput on; SQL> execute TEST1(20); 1003 Comments to “Oracle procedure testing”
Leave a Reply
Should the output not read “RESULT Y = 100”
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.
Really good site, thank you very much for your effort in writing the posts.