473,605 Members | 2,135 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Test script to test functions in a package

35 New Member
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 7661
Dave44
153 New Member
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 Recognized Expert Expert
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 Recognized Expert Top Contributor
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 New Member
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 Recognized Expert Top Contributor
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 New Member
Can you please help me with an example?

Thanks
Dec 10 '07 #7
amitpatel66
2,367 Recognized Expert Top Contributor
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 New Member
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 Recognized Expert Top Contributor
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

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

Similar topics

2
2108
by: TheDustbustr | last post by:
I'm writing a game in C++ that calls out to Python for scripting. The C++ kernel holds an instance of ScriptCtl and calls the load(filename) method to load a script, then run() to run all loaded scripts. <code> class ScriptCtl: threads= def load(self, filename):
4
1978
by: Ian Giblin | last post by:
I am an experienced C programmer, learning C++ by writinging a mathematical toolkit in the framework of a script interpreter. I am posting here to ask for advice (or references) on the object design and implimentation. Currently I have a portable "ScriptSession" class which contains the mechanics of looping with a user prompt, parsing a sentence and handling syntax errors, etc., and I wan this to be a class I can use for any script...
17
3604
by: cwdjrxyz | last post by:
Javascript has a very small math function list. However there is no reason that this list can not be extended greatly. Speed is not an issue, unless you nest complicated calculations several levels deep. In that case you need much more ram than a PC has to store functions calculated in loops so that you do not have to recalculate every time you cycle through the nest of loops. Using a HD for storage to extend ram is much too slow for many...
10
3474
by: Noah | last post by:
I would like to package my main script and all the modules it imports into a single script that will run just as the collection would. It should not need to package standard Python lib modules -- just my local modules. This is not the same question that would be answered with py2exe or py2app. I don't need to package the script into a binary along with Python. I expect Python to be installed. I also don't want to use distutils to install...
5
3544
by: Sakcee | last post by:
Hi I am trying to use pyUnit to create a framework for testing functions I am reading function name, expected output, from a text file. and in python generating dynamic test functions something like this class getUsStatesTest( unittest.TestCase ):
5
11455
by: sylcheung | last post by:
Is it possible to use python to unit test C++ code? If yes, is there any example available? Thank you.
1
1383
by: steven.harms | last post by:
Hi gurus, 1. foo.html has a script tag to reference'foo.js' 2. foo.js wants to use the handy datadumper.js routines 3. How does foo.js become aware of functions in the datadumper.js namespace? Is this possible? I'm sure it must be? Assuming it is....
5
2765
by: Martijn Saly | last post by:
I'd like to test in my script, if it's going to be possible to enable priviliges. If I use this... netscape.security.PrivilegeManager.enablePrivilege("UniversalXPConnect") ....it presents a dialog to the user asking if it's ok. Now I don't want to hide that dialog, I'd like to know if it's going to be possible to click the Allow button, before ever making this call. Basically I need
0
1189
by: Alan Isaac | last post by:
This is really a repackaging of an earlier question, probably illustrating that I still do not understand relative imports. Suppose I have the package structure (taken from the example at http://www.python.org/dev/peps/pep-0328/) package/ __init__.py subpackage1/ __init__.py
0
7937
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
8428
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
8423
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
8290
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
6749
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
5446
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
3960
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
2438
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
0
1274
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.