Test script to test functions in a package

[kwartz]

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.

[Dave44]

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.

[debasisdas]

From your post it is not clear what exactly is your requirment. Can you kindly post more clearly please.

[amitpatel66]

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?

[kwartz]

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.
--

 
prompt Creating to_date_null function
CREATE OR REPLACE FUNCTION to_date_null
  (strDate CHAR, strFormat CHAR)
RETURN DATE
DETERMINISTIC
IS
  d_date DATE;
BEGIN
  d_date := TO_DATE(strDate, strFormat);
  RETURN d_date;
EXCEPTION
  WHEN OTHERS THEN
    RETURN NULL;
END to_date_null;
/
 
--
-- Create to_number_null  -- Function will convert a string to number in the same way as the 
--                           Oracle to_number function.  However rather than throw an exception 
--                           on invalid input, to_number_null will simply return a null number.
--
prompt Creating to_number_null function
CREATE OR REPLACE FUNCTION to_number_null
  (strNumber CHAR)
RETURN NUMBER
DETERMINISTIC
IS
  num_out NUMBER;
BEGIN
  num_out := TO_NUMBER(strNumber);
  RETURN num_out;
EXCEPTION
  WHEN OTHERS THEN
    RETURN NULL;
END to_number_null;
/
 
--
-- IsNumeric  --  Function will test if a string is all numeric digits
--
prompt Creating IsNumeric function
CREATE OR REPLACE FUNCTION IsNumeric
  (strIn VARCHAR2)
RETURN BOOLEAN
DETERMINISTIC
IS
  strNonNumDigits  VARCHAR2(4000);
BEGIN
  -- Remove numeric digits from string
  strNonNumDigits := translate(strIn, '0123456789', '');
  -- Test if any chars are left
  IF strNonNumDigits IS NULL THEN
    return TRUE;
  ELSE
    return FALSE;
  END IF;
END IsNumeric;
/
 
--
-- Create LASTNAME  --  Function pulls the last name out
--                      from the entire pat_name. Will work if there's
--                      a comma or caret.
--
prompt Creating LASTNAME function
CREATE OR REPLACE FUNCTION LASTNAME(pat_name_in IN VARCHAR2)
   RETURN VARCHAR2
   DETERMINISTIC
   IS
      pname_out VARCHAR2(64);
      theseperator NUMBER;
   BEGIN
      -- init pname_out
      pname_out := pat_name_in;
      -- replace ',' with '^'
      pname_out := replace(pname_out, ',', '^');
      pname_out := replace(pname_out, ' ', '^');
      IF instr(pname_out, '^') > 0 THEN
        -- Find last name only
        pname_out := substr(pname_out, 1, instr(pname_out, '^')-1);
      ELSE
        -- Assume we only have a last name
        pname_out := pname_out;
      END IF;
      -- return pname_out
      RETURN (pname_out);
   END;
/
 
--
-- Create FIRSTNAME  --  Function pulls the first name out
--                       from the entire pat_name. Will work if there's
--                       a comma or caret.
--
prompt Creating FIRSTNAME function
CREATE OR REPLACE FUNCTION FIRSTNAME(pat_name_in IN VARCHAR2)
   RETURN VARCHAR2
   DETERMINISTIC
   IS
      pname_out VARCHAR2(64);
      theseperator NUMBER;
   BEGIN
--
-- Create FIRSTNAME  --  Function pulls the first name out
--                       from the entire pat_name. Will work if there's
--                       a comma or caret.
--
      -- init pname_out
      pname_out := pat_name_in;
      -- replace ',' with '^'
      pname_out := replace(pname_out, ',', '^');
      pname_out := replace(pname_out, ' ', '^');
      IF instr(pname_out, '^',1,2) <> 0 THEN
        -- Find first name only, with middle name in string
        pname_out := substr(pname_out, instr(pname_out, '^')+1, instr(pname_out, '^', 1, 2) - instr(pname_out, '^') -1);
      ELSIF instr(pname_out, '^') <> 0 THEN
        -- Find first name only, no middle name
        pname_out := substr(pname_out, instr(pname_out, '^')+1, LENGTH(pname_out) - (instr(pname_out, '^') -1));      
      ELSE
        -- Assume we only have no first name
        pname_out := NULL;
      END IF;
      -- return pname_out
      RETURN (pname_out);
   END;
/
 
--
-- Create MIDDLENAME  --  Function pulls the middle name out
--                        from the entire pat_name. Will work if there's
--                        a comma or caret.
--
prompt Creating MIDDLENAME function
CREATE OR REPLACE FUNCTION MIDDLENAME(pat_name_in IN VARCHAR2)
   RETURN VARCHAR2
   DETERMINISTIC
   IS
      pname_out VARCHAR2(64);
      theseperator NUMBER;
   BEGIN
--
-- Create MIDDLENAME  --  Function pulls the middle name out
--                        from the entire pat_name. Will work if there's
--                        a comma or caret.
--
      -- init pname_out
      pname_out := pat_name_in;
      -- replace ',' with '^'
      pname_out := replace(pname_out, ',', '^');
      pname_out := replace(pname_out, ' ', '^');
      IF instr(pname_out, '^',1,2) <> 0 THEN
        -- Find middle name only
        pname_out := substr(pname_out, instr(pname_out, '^', 1 ,2)+1, LENGTH(pname_out) - (instr(pname_out, '^', 1, 2) -1));
      ELSE
        -- Assume we only have no middle name
        pname_out := NULL;
      END IF;
      -- return pname_out
      RETURN (pname_out);
   END;
/
 
--
-- Create COMBINENAMES  --  Function Will combine name components into a formated
--                          DICOM patient name
--                     
prompt Creating COMBINENAMES function
CREATE OR REPLACE FUNCTION COMBINENAMES(p_last IN VARCHAR2, p_first VARCHAR2, p_middle VARCHAR2)
   RETURN VARCHAR2
   DETERMINISTIC
   IS
      pname_out VARCHAR2(64) := NULL;  -- init p_nameout
      theseperator VARCHAR2(1) := '^'; -- define the separator
      v_name VARCHAR2(64) := NULL;
   BEGIN
      IF p_last IS NOT NULL THEN
         v_name := p_last;
         v_name := replace(v_name, ',', ' ');
         pname_out := LTRIM(RTRIM(v_name));
         IF pname_out IS NOT NULL THEN
            IF p_first IS NOT NULL THEN
               v_name := p_first;
               v_name := replace(v_name, ',', ' ');
               pname_out := pname_out || theseperator || LTRIM(RTRIM(v_name));
               IF p_middle IS NOT NULL THEN
                  v_name := p_middle;
                  v_name := replace(v_name, ',', ' ');
                  pname_out := pname_out || theseperator || LTRIM(RTRIM(v_name));
               END IF;
            END IF;
         END IF;
      END IF;
      -- return pname_out
      RETURN (pname_out);
   END;
/
 
--
-- Create COMBINEDATETIME  --  Function Will convert a date and time field into
--                             a single date field;
--                     
prompt Creating COMBINEDATETIME function
CREATE OR REPLACE FUNCTION COMBINEDATETIME(DATEFIELD IN DATE, TIMEFIELD IN DATE)
   RETURN DATE
   DETERMINISTIC
   IS
      date_out DATE;
      strDate VARCHAR2(10);
      strTime VARCHAR2(10);
   BEGIN
--
-- Create COMBINEDATETIME  --  Function Will convert a date and time field into
--                             a single date field;
--                     
      -- get date
      strdate:= to_char(DATEFIELD, 'YYYYMMDD');
      -- get time
      strtime:= to_char(TIMEFIELD, 'HH24:MI:SS');
      -- Combine
      date_out:= to_date(CONCAT(strdate,strtime), 'YYYYMMDDHH24:MI:SS');
      -- Return
      RETURN(date_out);
   END;
/
 
--
-- Convert2DigitDate -- Function should convert date with 2 digit year.
--
--                      Accepted date formats:
--                      MM/DD/YY
--                      MM/DD/YYYY
--                   
--                      For 2 digit year dates:
--                      Assume 2000's for dates <= '07 and assume
--                      1900's for dates > '07.
--
--                      Updated by JT on 03/02/2007
--
prompt Creating Convert2DigitDate function
CREATE OR REPLACE FUNCTION Convert2DigitDate(date_in IN VARCHAR2)
  RETURN DATE
  DETERMINISTIC
IS
  date_str      VARCHAR2(64);
  year_str      VARCHAR2(64);
  date_out      DATE;
BEGIN
--
-- Convert2DigitDate -- Function should convert date with 2 digit year.
--
--                      Accepted date formats:
--                      MM/DD/YY
--                      MM/DD/YYYY
--                   
--                      For 2 digit year dates:
--                      Assume 2000's for dates <= '07 and assume
--                      1900's for dates > '07.
--
  -- Check whether the date is already in 'MM/DD/YYYY' format
  IF 
    length(date_in) = 10 AND
    substr(date_in, 3, 1) = '/' AND
    substr(date_in, 6, 1) = '/' AND
    IsNumeric(substr(date_in, 7, 4)) = TRUE
  THEN
    --if date is already in 'MM/DD/YYYY' format, then covert it.
    date_out := to_date_null(date_in, 'MM/DD/YYYY'); 
 
  -- Check for date in 'MM/DD/YY' format
  ELSIF 
    length(date_in) = 8 AND 
    substr(date_in, 3, 1) = '/' AND
    substr(date_in, 6, 1) = '/' AND
    IsNumeric(substr(date_in, 7, 2)) = TRUE
  THEN
    -- Process date with 2 digit year
    -- transfer first part of date string
    date_str := substr(date_in, 1, 5);
    -- Get year string from orig date
    year_str := substr(date_in, 7, 2);
    -- Add 2 digits to the year part of the string (making 4 digit)
    IF to_number(year_str) <= 7 THEN
      -- If the year is less or equal to '07, then assume 2000's
      year_str := '20' || year_str;
    ELSE
      -- If the year is greater than '07, assume 1900's
      year_str := '19' || year_str;
    END IF;
    -- Put whole date back together
    date_str := date_str || year_str;
    -- Convert date string to date
    date_out := to_date_null(date_str, 'MM/DD/YYYY');
 
  -- Handle all other date formats by returning null
  ELSE
    -- Invalid date format
    date_out := null;
  END IF;
 
  -- Return our result
  RETURN (date_out);
END;
/
 
 

[amitpatel66]

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

[kwartz]

Can you please help me with an example?

Thanks

[amitpatel66]

Can you please help me with an example?

Thanks

Try this:

Note: Not Tested!!

 
SELECT LASTNAME('BOND,JAMES') FROM DUAL;
 
 

 
SQL> SET SERVEROUTPUT ON
 
DECLARE
last_name VARCHAR2(20) := NULL;
BEGIN
last_name := LASTNAME('BOND,JAMES');
DBMS_OUTPUT.PUT_LINE(last_name);
END;
/
 
 

[kwartz]

Hi, Thanks for your help. However,I want each test to either show that it pass or failed, how do I do that.

[amitpatel66]

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.

[kwartz]

I understand that, however what I want is for each test to display pass or fail. thank you

[amitpatel66]

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:

 
SELECT DECODE(LENGTH(LASTNAME('BOND,JAMES')),4,'PASS','FAIL') FROM DUAL;
 
 

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'.

[kwartz]

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

 
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');
 
 

[amitpatel66]

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??

[amitpatel66]

Hi,

Please check how to use CODE TAGS in POSTING GUIDELINES

MODERATOR

[kwartz]

What is the error it is displaying??

Does it sound logical? is it going work?

[kwartz]

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

 
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');
 
 

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

[amitpatel66]

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:

 
DECLARE
TYPE nmes IS VARRAY(50) OF VARCHAR2(100);
nam names:= names('Smith,JAMES','Smith James','Smith^James');
last_name VARCHAR2(20) := NULL;
cnt NUMBER:= 0;
BEGIN
FOR I IN nam.FIRST..nam.COUNT LOOP
last_name := LASTNAME(nam(i));
IF
(last_name='Smith')
THEN
cnt:=cnt + 1;
END IF;
END LOOP;
IF(cnt = 3) THEN
DBMS_OUTPUT.PUT_LINE('LASTNAME test passed');
ELSE
DBMS_OUTPUT.PUT_LINE('LASTNAME test failed');
END IF;
END;
 
 

[kwartz]

Try this:

 
DECLARE
TYPE nmes IS VARRAY(50) OF VARCHAR2(100);
nam names:= names('Smith,JAMES','Smith James','Smith^James');
last_name VARCHAR2(20) := NULL;
cnt NUMBER:= 0;
BEGIN
FOR I IN nam.FIRST..nam.COUNT LOOP
last_name := LASTNAME(nam(i));
IF
(last_name='Smith')
THEN
cnt:=cnt + 1;
END IF;
END LOOP;
IF(cnt = 3) THEN
DBMS_OUTPUT.PUT_LINE('LASTNAME test passed');
ELSE
DBMS_OUTPUT.PUT_LINE('LASTNAME test failed');
END IF;
END;
 
 

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

[kwartz]

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 ?

[amitpatel66]

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.

SELECT CASE (SELECT LAST_NAME('Smith^James') FROM DUAL WHEN 'Smith' THEN 'PASSED' ELSE 'FAILED' END FROM DUAL;