This discussion is archived
3 Replies Latest reply: Aug 31, 2012 10:36 AM by BillArms_GS RSS

Variable substitution in Unit Testing validation - Date format?

BillArms_GS Newbie
Currently Being Moderated
Using SQL Developer unit testing, I am having difficulty comparing Date datatype in a validation.

I use a Dynamic Value Query to set the parameters of an insertion procedure under test.
select * from (select doc_nbr as p_mstr_doc_nbr, doc_nbr as p_assoc_doc_nbr, sysdate as p_date_added from documents sample(0.01) order by dbms_random.random)  where rownum <= 1;
So P_DATE_ADDED is a DATE datatype.

I wish to validate that the insertion was correct. So I run a Process Validation returning one or more rows.
SELECT * FROM X100_ASSOC 
  WHERE MSTR_DOC_NBR = '{P_MSTR_DOC_NBR}' 
    AND ASSOC_DOC_NBR = '{P_ASSOC_DOC_NBR}' 
    AND to_char(DATE_ADDED,'dd-MON-yy HH24:MI:SS')  = {P_DATE_ADDED};
I understand that the {P_DATE_ADDED} is substituting in a string. From Help page :

"What is actually substituted is the string representation of the parameter value. For example:

If P1 is a parameter of type NUMBER and has the value 2.1, the string {P1} will be replaced by the string 2.1.

If P1 is a parameter of type VARCHAR2 and has the value ABC, the string '{P1}' will be replaced by the string 'ABC'. (Note the single-quotation marks around {P1} in this example.)"

I need to know the format that the date is cast into as a string. Then I can do the comparision on the where clause.

Any answers? or details of where is more in depth documentation?

Thank you,
Bill

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points