469,366 Members | 1,805 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,366 developers. It's quick & easy.

Test script to test functions in a package

35
Any suggestions on how to write a test script to test a package. Some of the functions in the package are to_number_zero, CheckIfNumeric(STRING_IN IN VARCHAR2). I need to write some sort of test script for these functions. For example, the to_number_zero test should pass in an alphabetic string and check the result, then pass in a numeric string and check the result.
Dec 5 '07 #1
20 7417
Dave44
153 100+
not completely sure what all the functions do but it sounds straight forward enough. write a script that will spool to a file (for a record of the results), set echo and timing on to see all that is run and dbms_output everything to the spool file as well for all the answers.

make blocks within the main block with exception handlers so you can see all errors that occur.
Dec 5 '07 #2
debasisdas
8,127 Expert 4TB
From your post it is not clear what exactly is your requirment. Can you kindly post more clearly please.
Dec 6 '07 #3
amitpatel66
2,367 Expert 2GB
Any suggestions on how to write a test script to test a package. Some of the functions in the package are to_number_zero, CheckIfNumeric(STRING_IN IN VARCHAR2). I need to write some sort of test script for these functions. For example, the to_number_zero test should pass in an alphabetic string and check the result, then pass in a numeric string and check the result.

Could you please post your functions soure code here for reference?
Dec 6 '07 #4
kwartz
35
Here is my source code. Hope this will enable you guys to help me out.

--
-- Create to_date_null -- Function will convert to a date in the same way as the Oracle to_date
-- function. However rather than throw an exception on invalid input,
-- to_date_null will simply return a null date.
--

Expand|Select|Wrap|Line Numbers
  1.  
  2. prompt Creating to_date_null function
  3. CREATE OR REPLACE FUNCTION to_date_null
  4.   (strDate CHAR, strFormat CHAR)
  5. RETURN DATE
  6. DETERMINISTIC
  7. IS
  8.   d_date DATE;
  9. BEGIN
  10.   d_date := TO_DATE(strDate, strFormat);
  11.   RETURN d_date;
  12. EXCEPTION
  13.   WHEN OTHERS THEN
  14.     RETURN NULL;
  15. END to_date_null;
  16. /
  17.  
  18. --
  19. -- Create to_number_null  -- Function will convert a string to number in the same way as the 
  20. --                           Oracle to_number function.  However rather than throw an exception 
  21. --                           on invalid input, to_number_null will simply return a null number.
  22. --
  23. prompt Creating to_number_null function
  24. CREATE OR REPLACE FUNCTION to_number_null
  25.   (strNumber CHAR)
  26. RETURN NUMBER
  27. DETERMINISTIC
  28. IS
  29.   num_out NUMBER;
  30. BEGIN
  31.   num_out := TO_NUMBER(strNumber);
  32.   RETURN num_out;
  33. EXCEPTION
  34.   WHEN OTHERS THEN
  35.     RETURN NULL;
  36. END to_number_null;
  37. /
  38.  
  39. --
  40. -- IsNumeric  --  Function will test if a string is all numeric digits
  41. --
  42. prompt Creating IsNumeric function
  43. CREATE OR REPLACE FUNCTION IsNumeric
  44.   (strIn VARCHAR2)
  45. RETURN BOOLEAN
  46. DETERMINISTIC
  47. IS
  48.   strNonNumDigits  VARCHAR2(4000);
  49. BEGIN
  50.   -- Remove numeric digits from string
  51.   strNonNumDigits := translate(strIn, '0123456789', '');
  52.   -- Test if any chars are left
  53.   IF strNonNumDigits IS NULL THEN
  54.     return TRUE;
  55.   ELSE
  56.     return FALSE;
  57.   END IF;
  58. END IsNumeric;
  59. /
  60.  
  61. --
  62. -- Create LASTNAME  --  Function pulls the last name out
  63. --                      from the entire pat_name. Will work if there's
  64. --                      a comma or caret.
  65. --
  66. prompt Creating LASTNAME function
  67. CREATE OR REPLACE FUNCTION LASTNAME(pat_name_in IN VARCHAR2)
  68.    RETURN VARCHAR2
  69.    DETERMINISTIC
  70.    IS
  71.       pname_out VARCHAR2(64);
  72.       theseperator NUMBER;
  73.    BEGIN
  74.       -- init pname_out
  75.       pname_out := pat_name_in;
  76.       -- replace ',' with '^'
  77.       pname_out := replace(pname_out, ',', '^');
  78.       pname_out := replace(pname_out, ' ', '^');
  79.       IF instr(pname_out, '^') > 0 THEN
  80.         -- Find last name only
  81.         pname_out := substr(pname_out, 1, instr(pname_out, '^')-1);
  82.       ELSE
  83.         -- Assume we only have a last name
  84.         pname_out := pname_out;
  85.       END IF;
  86.       -- return pname_out
  87.       RETURN (pname_out);
  88.    END;
  89. /
  90.  
  91. --
  92. -- Create FIRSTNAME  --  Function pulls the first name out
  93. --                       from the entire pat_name. Will work if there's
  94. --                       a comma or caret.
  95. --
  96. prompt Creating FIRSTNAME function
  97. CREATE OR REPLACE FUNCTION FIRSTNAME(pat_name_in IN VARCHAR2)
  98.    RETURN VARCHAR2
  99.    DETERMINISTIC
  100.    IS
  101.       pname_out VARCHAR2(64);
  102.       theseperator NUMBER;
  103.    BEGIN
  104. --
  105. -- Create FIRSTNAME  --  Function pulls the first name out
  106. --                       from the entire pat_name. Will work if there's
  107. --                       a comma or caret.
  108. --
  109.       -- init pname_out
  110.       pname_out := pat_name_in;
  111.       -- replace ',' with '^'
  112.       pname_out := replace(pname_out, ',', '^');
  113.       pname_out := replace(pname_out, ' ', '^');
  114.       IF instr(pname_out, '^',1,2) <> 0 THEN
  115.         -- Find first name only, with middle name in string
  116.         pname_out := substr(pname_out, instr(pname_out, '^')+1, instr(pname_out, '^', 1, 2) - instr(pname_out, '^') -1);
  117.       ELSIF instr(pname_out, '^') <> 0 THEN
  118.         -- Find first name only, no middle name
  119.         pname_out := substr(pname_out, instr(pname_out, '^')+1, LENGTH(pname_out) - (instr(pname_out, '^') -1));      
  120.       ELSE
  121.         -- Assume we only have no first name
  122.         pname_out := NULL;
  123.       END IF;
  124.       -- return pname_out
  125.       RETURN (pname_out);
  126.    END;
  127. /
  128.  
  129. --
  130. -- Create MIDDLENAME  --  Function pulls the middle name out
  131. --                        from the entire pat_name. Will work if there's
  132. --                        a comma or caret.
  133. --
  134. prompt Creating MIDDLENAME function
  135. CREATE OR REPLACE FUNCTION MIDDLENAME(pat_name_in IN VARCHAR2)
  136.    RETURN VARCHAR2
  137.    DETERMINISTIC
  138.    IS
  139.       pname_out VARCHAR2(64);
  140.       theseperator NUMBER;
  141.    BEGIN
  142. --
  143. -- Create MIDDLENAME  --  Function pulls the middle name out
  144. --                        from the entire pat_name. Will work if there's
  145. --                        a comma or caret.
  146. --
  147.       -- init pname_out
  148.       pname_out := pat_name_in;
  149.       -- replace ',' with '^'
  150.       pname_out := replace(pname_out, ',', '^');
  151.       pname_out := replace(pname_out, ' ', '^');
  152.       IF instr(pname_out, '^',1,2) <> 0 THEN
  153.         -- Find middle name only
  154.         pname_out := substr(pname_out, instr(pname_out, '^', 1 ,2)+1, LENGTH(pname_out) - (instr(pname_out, '^', 1, 2) -1));
  155.       ELSE
  156.         -- Assume we only have no middle name
  157.         pname_out := NULL;
  158.       END IF;
  159.       -- return pname_out
  160.       RETURN (pname_out);
  161.    END;
  162. /
  163.  
  164. --
  165. -- Create COMBINENAMES  --  Function Will combine name components into a formated
  166. --                          DICOM patient name
  167. --                     
  168. prompt Creating COMBINENAMES function
  169. CREATE OR REPLACE FUNCTION COMBINENAMES(p_last IN VARCHAR2, p_first VARCHAR2, p_middle VARCHAR2)
  170.    RETURN VARCHAR2
  171.    DETERMINISTIC
  172.    IS
  173.       pname_out VARCHAR2(64) := NULL;  -- init p_nameout
  174.       theseperator VARCHAR2(1) := '^'; -- define the separator
  175.       v_name VARCHAR2(64) := NULL;
  176.    BEGIN
  177.       IF p_last IS NOT NULL THEN
  178.          v_name := p_last;
  179.          v_name := replace(v_name, ',', ' ');
  180.          pname_out := LTRIM(RTRIM(v_name));
  181.          IF pname_out IS NOT NULL THEN
  182.             IF p_first IS NOT NULL THEN
  183.                v_name := p_first;
  184.                v_name := replace(v_name, ',', ' ');
  185.                pname_out := pname_out || theseperator || LTRIM(RTRIM(v_name));
  186.                IF p_middle IS NOT NULL THEN
  187.                   v_name := p_middle;
  188.                   v_name := replace(v_name, ',', ' ');
  189.                   pname_out := pname_out || theseperator || LTRIM(RTRIM(v_name));
  190.                END IF;
  191.             END IF;
  192.          END IF;
  193.       END IF;
  194.       -- return pname_out
  195.       RETURN (pname_out);
  196.    END;
  197. /
  198.  
  199. --
  200. -- Create COMBINEDATETIME  --  Function Will convert a date and time field into
  201. --                             a single date field;
  202. --                     
  203. prompt Creating COMBINEDATETIME function
  204. CREATE OR REPLACE FUNCTION COMBINEDATETIME(DATEFIELD IN DATE, TIMEFIELD IN DATE)
  205.    RETURN DATE
  206.    DETERMINISTIC
  207.    IS
  208.       date_out DATE;
  209.       strDate VARCHAR2(10);
  210.       strTime VARCHAR2(10);
  211.    BEGIN
  212. --
  213. -- Create COMBINEDATETIME  --  Function Will convert a date and time field into
  214. --                             a single date field;
  215. --                     
  216.       -- get date
  217.       strdate:= to_char(DATEFIELD, 'YYYYMMDD');
  218.       -- get time
  219.       strtime:= to_char(TIMEFIELD, 'HH24:MI:SS');
  220.       -- Combine
  221.       date_out:= to_date(CONCAT(strdate,strtime), 'YYYYMMDDHH24:MI:SS');
  222.       -- Return
  223.       RETURN(date_out);
  224.    END;
  225. /
  226.  
  227. --
  228. -- Convert2DigitDate -- Function should convert date with 2 digit year.
  229. --
  230. --                      Accepted date formats:
  231. --                      MM/DD/YY
  232. --                      MM/DD/YYYY
  233. --                   
  234. --                      For 2 digit year dates:
  235. --                      Assume 2000's for dates <= '07 and assume
  236. --                      1900's for dates > '07.
  237. --
  238. --                      Updated by JT on 03/02/2007
  239. --
  240. prompt Creating Convert2DigitDate function
  241. CREATE OR REPLACE FUNCTION Convert2DigitDate(date_in IN VARCHAR2)
  242.   RETURN DATE
  243.   DETERMINISTIC
  244. IS
  245.   date_str      VARCHAR2(64);
  246.   year_str      VARCHAR2(64);
  247.   date_out      DATE;
  248. BEGIN
  249. --
  250. -- Convert2DigitDate -- Function should convert date with 2 digit year.
  251. --
  252. --                      Accepted date formats:
  253. --                      MM/DD/YY
  254. --                      MM/DD/YYYY
  255. --                   
  256. --                      For 2 digit year dates:
  257. --                      Assume 2000's for dates <= '07 and assume
  258. --                      1900's for dates > '07.
  259. --
  260.   -- Check whether the date is already in 'MM/DD/YYYY' format
  261.   IF 
  262.     length(date_in) = 10 AND
  263.     substr(date_in, 3, 1) = '/' AND
  264.     substr(date_in, 6, 1) = '/' AND
  265.     IsNumeric(substr(date_in, 7, 4)) = TRUE
  266.   THEN
  267.     --if date is already in 'MM/DD/YYYY' format, then covert it.
  268.     date_out := to_date_null(date_in, 'MM/DD/YYYY'); 
  269.  
  270.   -- Check for date in 'MM/DD/YY' format
  271.   ELSIF 
  272.     length(date_in) = 8 AND 
  273.     substr(date_in, 3, 1) = '/' AND
  274.     substr(date_in, 6, 1) = '/' AND
  275.     IsNumeric(substr(date_in, 7, 2)) = TRUE
  276.   THEN
  277.     -- Process date with 2 digit year
  278.     -- transfer first part of date string
  279.     date_str := substr(date_in, 1, 5);
  280.     -- Get year string from orig date
  281.     year_str := substr(date_in, 7, 2);
  282.     -- Add 2 digits to the year part of the string (making 4 digit)
  283.     IF to_number(year_str) <= 7 THEN
  284.       -- If the year is less or equal to '07, then assume 2000's
  285.       year_str := '20' || year_str;
  286.     ELSE
  287.       -- If the year is greater than '07, assume 1900's
  288.       year_str := '19' || year_str;
  289.     END IF;
  290.     -- Put whole date back together
  291.     date_str := date_str || year_str;
  292.     -- Convert date string to date
  293.     date_out := to_date_null(date_str, 'MM/DD/YYYY');
  294.  
  295.   -- Handle all other date formats by returning null
  296.   ELSE
  297.     -- Invalid date format
  298.     date_out := null;
  299.   END IF;
  300.  
  301.   -- Return our result
  302.   RETURN (date_out);
  303. END;
  304. /
  305.  
  306.  
Dec 6 '07 #5
amitpatel66
2,367 Expert 2GB
So, Are you not able to test these scripts??
You can test them by calling the functions from an anonymous block or select statement itself.

One suggestion is if you are using IS_NUMERIC function in your project requirement then include "." (decimal) in your translate command along with the numbers becuase the number can have decimal places
Dec 7 '07 #6
kwartz
35
Can you please help me with an example?

Thanks
Dec 10 '07 #7
amitpatel66
2,367 Expert 2GB
Can you please help me with an example?

Thanks
Try this:

Note: Not Tested!!

Expand|Select|Wrap|Line Numbers
  1.  
  2. SELECT LASTNAME('BOND,JAMES') FROM DUAL;
  3.  
  4.  
Expand|Select|Wrap|Line Numbers
  1.  
  2. SQL> SET SERVEROUTPUT ON
  3.  
  4. DECLARE
  5. last_name VARCHAR2(20) := NULL;
  6. BEGIN
  7. last_name := LASTNAME('BOND,JAMES');
  8. DBMS_OUTPUT.PUT_LINE(last_name);
  9. END;
  10. /
  11.  
  12.  
Dec 11 '07 #8
kwartz
35
Hi, Thanks for your help. However,I want each test to either show that it pass or failed, how do I do that.
Dec 12 '07 #9
amitpatel66
2,367 Expert 2GB
Hi, Thanks for your help. However,I want each test to either show that it pass or failed, how do I do that.
The output of your function will be the most important validation source for you. If your function gives you correct output then its working fine and it is passed else it is failed.
Dec 13 '07 #10
kwartz
35
I understand that, however what I want is for each test to display pass or fail. thank you
Dec 17 '07 #11
amitpatel66
2,367 Expert 2GB
I understand that, however what I want is for each test to display pass or fail. thank you
I would give you one example without changing your existing function code.
Check this:

Expand|Select|Wrap|Line Numbers
  1.  
  2. SELECT DECODE(LENGTH(LASTNAME('BOND,JAMES')),4,'PASS','FAIL') FROM DUAL;
  3.  
  4.  
I would check the length of the string returned by the function LASTNAME and if it is equal to 4 ('BOND' in this case) then I say 'PASS' else "FAIL'.
Dec 18 '07 #12
kwartz
35
This what I am trying to acheive, however because I am new to pl/sql I am not getting it right. Can you help me?

Thanks

Expand|Select|Wrap|Line Numbers
  1.  
  2. SET SERVEROUTPUT ON
  3.  
  4. DECLARE
  5. last_name VARCHAR2(20) := NULL;
  6. BEGIN
  7. last_name := LASTNAME('Smith,JAMES');
  8. last_name := LASTNAME ('Smith James');
  9. last_name := LASTNAME ('Smith^James');
  10. DBMS_OUTPUT.PUT_LINE(last_name);
  11.  
  12. IF
  13. last_name=('Smith')
  14. THEN
  15. DBMS_OUTPUT.PUT_LINE('LASTNAME test passed');
  16. ELSE
  17. DBMS_OUTPUT.PUT_LINE('LASTNAME test failed');
  18.  
  19.  
Dec 18 '07 #13
amitpatel66
2,367 Expert 2GB
This what I am trying to acheive, however because I am new to pl/sql I am not getting it right. Can you help me?

Thanks

Code: ( oracle8 )

SET SERVEROUTPUT ON

DECLARE
last_name VARCHAR2(20) := NULL;
BEGIN
last_name := LASTNAME('Smith,JAMES');
last_name := LASTNAME ('Smith James');
last_name := LASTNAME ('Smith^James');
DBMS_OUTPUT.PUT_LINE(last_name);

IF
last_name=('Smith')
THEN
DBMS_OUTPUT.PUT_LINE('LASTNAME test passed');
ELSE
DBMS_OUTPUT.PUT_LINE("LASTNAME test failed');

What is the error it is displaying??
Dec 19 '07 #14
amitpatel66
2,367 Expert 2GB
Hi,

Please check how to use CODE TAGS in POSTING GUIDELINES

MODERATOR
Dec 19 '07 #15
kwartz
35
What is the error it is displaying??
Does it sound logical? is it going work?
Dec 19 '07 #16
kwartz
35
This what I am trying to acheive, however because I am new to pl/sql I am not getting it right. Can you help me?

Thanks

Expand|Select|Wrap|Line Numbers
  1.  
  2. SET SERVEROUTPUT ON
  3.  
  4. DECLARE
  5. last_name VARCHAR2(20) := NULL;
  6. BEGIN
  7. last_name := LASTNAME('Smith,JAMES');
  8. last_name := LASTNAME ('Smith James');
  9. last_name := LASTNAME ('Smith^James');
  10. DBMS_OUTPUT.PUT_LINE(last_name);
  11.  
  12. IF
  13. last_name=('Smith')
  14. THEN
  15. DBMS_OUTPUT.PUT_LINE('LASTNAME test passed');
  16. ELSE
  17. DBMS_OUTPUT.PUT_LINE('LASTNAME test failed');
  18.  
  19.  
There are 3 test that I need to run, the way it is set up now, only one, the third one will run. How do I get to go through all 3?
Thanks
Dec 20 '07 #17
amitpatel66
2,367 Expert 2GB
There are 3 test that I need to run, the way it is set up now, only one, the third one will run. How do I get to go through all 3?
Thanks
Try this:

Expand|Select|Wrap|Line Numbers
  1.  
  2. DECLARE
  3. TYPE nmes IS VARRAY(50) OF VARCHAR2(100);
  4. nam names:= names('Smith,JAMES','Smith James','Smith^James');
  5. last_name VARCHAR2(20) := NULL;
  6. cnt NUMBER:= 0;
  7. BEGIN
  8. FOR I IN nam.FIRST..nam.COUNT LOOP
  9. last_name := LASTNAME(nam(i));
  10. IF
  11. (last_name='Smith')
  12. THEN
  13. cnt:=cnt + 1;
  14. END IF;
  15. END LOOP;
  16. IF(cnt = 3) THEN
  17. DBMS_OUTPUT.PUT_LINE('LASTNAME test passed');
  18. ELSE
  19. DBMS_OUTPUT.PUT_LINE('LASTNAME test failed');
  20. END IF;
  21. END;
  22.  
  23.  
Dec 24 '07 #18
kwartz
35
Try this:

Expand|Select|Wrap|Line Numbers
  1.  
  2. DECLARE
  3. TYPE nmes IS VARRAY(50) OF VARCHAR2(100);
  4. nam names:= names('Smith,JAMES','Smith James','Smith^James');
  5. last_name VARCHAR2(20) := NULL;
  6. cnt NUMBER:= 0;
  7. BEGIN
  8. FOR I IN nam.FIRST..nam.COUNT LOOP
  9. last_name := LASTNAME(nam(i));
  10. IF
  11. (last_name='Smith')
  12. THEN
  13. cnt:=cnt + 1;
  14. END IF;
  15. END LOOP;
  16. IF(cnt = 3) THEN
  17. DBMS_OUTPUT.PUT_LINE('LASTNAME test passed');
  18. ELSE
  19. DBMS_OUTPUT.PUT_LINE('LASTNAME test failed');
  20. END IF;
  21. END;
  22.  
  23.  
This works fine, however, all three test are run. What I will prefer is depending on the input, lets say we want to test for 'Smith^James' this time it works and another time we can test for 'Smith,James' . In this current state all three criteria should be present for the test to pass, taking away one fails the test. This shouldn't be so.
Thanks
Dec 27 '07 #19
kwartz
35
This works fine, however, all three test are run. What I will prefer is depending on the input, lets say we want to test for 'Smith^James' this time it works and another time we can test for 'Smith,James' . In this current state all three criteria should be present for the test to pass, taking away one fails the test. This shouldn't be so.
Thanks
Is there anyway, I can use CASE ?
Dec 28 '07 #20
amitpatel66
2,367 Expert 2GB
Is there anyway, I can use CASE ?
I seriously dont know what kind of testing you are trying to do for a very simple function.

If you dont want to use array then use temporary table and store the data in that and call the function.

Expand|Select|Wrap|Line Numbers
  1. SELECT CASE (SELECT LAST_NAME('Smith^James') FROM DUAL WHEN 'Smith' THEN 'PASSED' ELSE 'FAILED' END FROM DUAL;
  2.  
Dec 29 '07 #21

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

4 posts views Thread by Ian Giblin | last post: by
17 posts views Thread by cwdjrxyz | last post: by
5 posts views Thread by Sakcee | last post: by
5 posts views Thread by sylcheung | last post: by
reply views Thread by Alan Isaac | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.