Toad World® Forums

Problem with Test Query when Defining Expected Outcome


#1

Here is an issue that I have found when deriving an expected outcome from a SQL Query and trying to use the “Test Query” Button.

(A screen shot is attached for reference)

When defining an expected outcome with a SQL Query that references the input value (which Code Tester generated and I added no additional customization), I was getting an Oracle Error Box saying that the input value is an Invalid Identifier when I clicked the “Test Query” button on the screen I have attached.

However, when I simply ignored clicking the test query button and just ran the test, the test worked fine, and the expected result was reached. So, it seems as though it was a valid query, but Code Tester was telling me that there was a problem.

For reference, the query looks like this:

SELECT c.primary_key

FROM my_table c

WHERE c.Value = i_ukrecord_in.Value

Also, I have successfully gotten the Test Query functionality to work for me when defining the input value of another test, after a few quirks. I set the SQL, then clicked Test Query, and an Oracle Error saying “Missing Symbol” came up. I continued and ran the test anyway, and the test worked fine. Then I went back to the Test Query option and clicked again, this time it came up with data in the Data Display box and I did not receive an error. So, it seems like the Test Query functionality is a little shaky/misleading at this point

code_tester_screen_expected_outcome_from_query (41.6 KB)


#2

Thanks for pointing this out. Here’s the problem:

When we test the query / evaluate the query for correctness, we are doing so OUTSIDE OF the generated test package. Your query references a local variable from the test. So the query cannot execute/is not valid.

Yet, when you run the query inside the test, it is OK.

I don’t see an easy way around this right now. But what we COULD do is change the message so that we tell you that if you are referencing a local variable, it will probably work later, etc.

What do you think of that?


#3

A message or something to that effect would be good, even if its not the most desirable.

Here’s a new problem I’ve run across with this situation. Now I have some existing test cases for a package, and I want to add a new test case similar to the one I have detailed above (defining expected outcome using a query referencing a local variable). I get the same invalid identifier error when I use Test Query. Then I click continue on the error screen and click OK on the expected outcome definition screen (the one I attached to previous post). I then get the Oracle error message again. I click continue again, and this time the expected outcome screen goes away when I click OK again.

Now I am back at the Test Builder screen. I click to save my changes and close the Test Builder window. But, now my new test case is not there in the “Quest Code Tester: Editor” screen. It is nowhere to be found. Now, in order to get the test case to remain in the program, I have to create a blank test case (with some dummy definition data) and save it. Then in the “Quest Code Tester: Editor” screen, I can go in and change the expected outcome definition to what I need it to be, regenerate the test code, and the test works fine.

I did not get this problem with my initial attempt/post, but since it was my first time doing it, the steps could have been different.

Thanks for the help Steven, and let me know what you think,

Josh


#4

Josh,

What version are you using?

In 1.6, available as a beta off this community and about to go GA/production, I cannot even close my window. It now does not let me use a query that does not compile.

For our patch release 1.6.1 (planned for Nov), we can definitely tweak this logic, let you save, but warn you.

Perhaps you upgrade to 1.6 and see what behavior you get.

SF


#5

The client version we are using is 1.5.3.

The situation is that my group/company is a government contractor and there is a long approval process involved with getting new software in-house for use in development (and we don’t have internet on our development computers… I have to come to a separate computer to use the internet). So, we would most likely not be able to bring in 1.6 until it was officially released and then it will most likely take at least 2-3 weeks before actually getting to use it. I realize it is not brand new software, but even a download involves approval and migration from one network to another.

Addressing this issue now, does that mean that 1.6 does not support the referencing of local variables in queries when defining expected outcomes (or any definition for that matter??). If that’s the case, upgrading to 1.6 probably would not be desirable for our group, as hard coding the input values would not benefit us a whole lot.

This seems sort of weird that you are essentialy removing the ability to do this in 1.6. (Even if the 1.5.3 method is sort of a bug/work around).

Thanks for the info,

Josh


#6

Well…I don’t know if “weird” is the right word for it, but hey the customer is always right!

In this case, I’d say it was one of those situations where we tightened up validation and in the process shut down a workaround. Not all that unusual in the world of software, but you are right - it does make things difficult for you.

Unfortunately it is too late to change 1.6. Are you allowed to “test” beta versions? Then we could get something to you sooner than the formal 1.6.1 patch release.

I also believe I can also show you how to work around the restriction.

SF


#7

Steven,

Thanks for the help and the info, I appreciate it.

You say that you have tightened up validation and in turn prevented the workaround. Is that to say the usage of local variables in the queries was unintended and/or undesired functionality? If that is the case, perhaps there is another way of gaining the same result within Code Tester that I am unaware of? I am new to the tool and the PL/SQL world, so I do not doubt the possibility of my missing something.

As far as 1.6 and the Beta testing goes, I will have to wait until Monday to get the official word as to whether it would be possible to bring it in. I am only an intern for the group so I do not know the full extent of what is allowed or not allowed. Also, I will most likely will be out of the loop soon since I will heading back to school as the summer ends. However, I appreciate the help and willingness to get that functionality out to us.

Thanks once again and I will let you know if it will be possible to bring in a beta version.

Josh


#8

Josh, glad to help and referencing local variables is definitely desirable. Furthermore, there IS a way to do what you want right now. I describe the steps (admittedly too many, but what can I say? This is the current reality):

HOW TO REFERENCE LOCAL VARIABLES AND ARGUMENTS IN QUERIES

I will show you how to define a custom test data group for an outcome that is based on a query, which references the value of an IN argument. In other words, you want to define a query that retrieves an expected value from a table like this:

select my_value
from my_table
where condition_column =

Here’s how you do it:

First, you cannot reference the argument directly, or even the variable to which that IN argument is assigned, inside the query. That is because we validate the query outside of the scope of the test package and that variable is not available in “global scope.” [Note: the 1.6.1 patch release should let you do that, and so you can avoid having to take these steps.]

So instead you will reference a function defined in the test package specification. That is, your query looks like this:

select my_value
from my_table
where condition_column = q##<your_program>.func_value()

But before you can do that, you must define the function. So take these steps:

  1. Open the Test Editor for the program and click on the Properties node in the tree. Then click on Customizations.

  2. In the “-- Declarations of public, custom non-program elements” section, press the + symbol and type this:

my_global VARCHAR2(100);

or whatever the datatype should be.

  1. In the "-- Headers of public, custom non-program elements " section enter this:

function func_value return varchar2;

or whatever datatype matches the global variable.

  1. In the “-- Implementation of all custom subprograms” section, enter:

function func_value return varchar2 is begin return my_global; end;

  1. Drill down to the test case that contains the outcome you want to set up a query in. Expand the list of inputs. Click on the input whose value you want to use.

  2. In the Properties tab, check the “Declare variable and use it in call to program”. Then copy into your clipboard the name in the field next to the “Passed to program as local variable named” label.

  3. Click on Customizations for the Test Case.

  4. In the "-- Post-execution code " section, enter:

my_global :=

  1. Generate the test code, which also compiles the package, making the function available to the SQL layer.

  2. Navigate to your outcome, open up the custom test data group and specify SQL query, then type in your select statement along these lines:

select my_value
from my_table
where condition_column = q##<your_program>.func_value()

and everything should work.

WHEW. That’s a lot and my apologies for not making it easier.


#9

Steven,

Thanks for the in-depth response! I have done the steps you described and, unfortunately, a couple others problems have cropped up when I attempted this.

Dealing with just this problem, when I do all of the steps you outlined, and I try to test the query with the Test Query button, I get a Code Tester Application error (the little box asking if you want to continue, restart, or terminate the application). I expanded the details and found Exception Class: EVariantInvalidArgError with the Exception Message as Invalid Argument.
It is important to note that the test code compiled fine.
I then go and try to run the test, but I get the Yellow “No test results available at the level” face at every level for this test case.
So, I am not sure if your solution was for 1.6 or if I did something wrong.

Also, I have run into a related problem when trying out your solution to my other question in the Testing Records forum. (found here: http://unittest.inside.quest.com/thread.jspa?threadID=3291&tstart=0).

Here, I was attempting to reference a single value from a record variable from the function. I followed your steps for this in the Changed by Program section, then went on to define the expected outcome as I originally did in this forum (a SQL Query referencing the input variable) and got the usual “Invalid Identifier” Oracle error message. I preceded with my “workaround”, but this time I got a compilation error as well, stating that the input variable was an “Invalid Indentifier” and it would not compile. Thus, I could not now run the program.

Now, I am not sure if this problem stems from changing the “Changed by Program” part, or its some other problem or step I’m missing.
So, I went ahead and tried your fix with this test case, but got the same application error and inability to see test results.

Just thought you would want to see the differences here with the similar problem. Hope my explanantions are as clear as possible.

Thanks for the help once again,
Josh


#10

I figured out my second problem as to why it was a compile error instead of just simply an Oracle. The input was not marked as “Declare variable in Call to Program”. Should not have anything to do with modifying the “Change by Program” outcome, but I cannot really test that until I get the first issue squared away.

The first problem still remains, I still cannot manage to get the test to actually run using your method.

Josh


#11

Josh,

Just so you know for 1.6.1 (due out in the fall), we will ALWAYS declare a variable for your program, so you won’t have to check that silly little box.

As to your other problems, could you please generate a support bundle when you get the application, make sure you list the program you are working on in the appropriate page in the support bundle wizard, and then send the zip file to me:

steven.feuerstein@quest.com

so I can look more closely at the errors you are getting?

Thanks, SF


#12

And another heads-up for 1.6.1. In this release, we are going to make all these steps I listed above pretty much go away.

After the program call in each test case, we will copy all IN argument values to a global cache. You will then simply reference that cache in your customization code, as in:

SELECT last_name
FROM employees
WHERE employee_id = qu_result_xp.argval_number (‘EMPLOYEE_ID’)

and then we will retrieve the appropriate value and apply it for you.

No mess, no fuss.

I hope this sounds better.

SF


#13

Steven,

Thanks for the heads up on 1.6.1. The functionality sounds good, and I will make sure to have my group get that in.

Unfortunately, I won’t be able to sent a support bundle. The computers Code Tester is on is not on the internet, and there would be a lot of red tape and process to get files from our stand alone development network over to the internet (if it is even possible…).

So for now, I am forging ahead and entering in Test Cases using work-arounds for the local variable referencing. When 1.6.1 is released, my group will be able to get that in and start running with Code Tester.

Thanks for all of your help with this problem.

One more lingering question I have… will 1.6.1 (or 1.6) be able to handle records more elegantly? I have still not fully resolved my “test just one value in a record” problem. When I utilize your method, using Q##FUNCTION_RETURN_VALUE.value in the Changed by Program area, the test compiles but does not run the test (yellow “no results” face).
Is 1.6 going to have increase records/collections functionality?

Thanks again for the help,

Josh


#14

Josh, can you please describe how you would like us to better support the testing of records and fields within records?

I just tried again to set up an outcome as a type Expression, put the record.field into the code box and then set up the outcome for that value. It worked as expected. I sure wish I could see what you are doing.

Perhaps you could send me some screenshots to steven.feuerstein@quest.com?

SF


#15

Steven,

I have initiated the process to send out a Support Bundle to you. It may take a while to go through the process, so you won’t get it right away. In there you can see what I am trying to do with records.

Once again, another problem has cropped up. This does not seem directly related to either of my big problems though.

Since I sent you the support package, I have run into a handful of programs that Code Tester refuses to generate test code for. When I enter in a test case (even just testing if a null input throws an exception) and try to generate test code, I get the error “the test code package specification is not found”, on the screen where you view the test results.

This strikes me as odd. Code Tester is supposd to generate the spec, or so I assume, but it is saying it cannot be found. Some programs work fine, while others won’t generate test code.

Any idea on what could be causing this problem?


#16

As for this other problem:

“the test code package specification is not found”

Answering questions in the abstract like this is always tough.

Could you post in your reply the header of the program for which you experience this problem? I can then try to reproduce it.

SF


#17

By your response then, do you think that the problem lies in the spec of the program I am writing the test case for?

If so, I will look closer for similarities in the specs (headers) of the programs that Code Tester does not like.

Sorry to leave the problem ambiguous, that is the only error message I got from Code Tester.

And unfortunately, I do not believe I would be allowed to post source code in an open forum like this. And even if I were, it would take just as long as the support bundle will take and I only have two weeks left here :).

So for now, I will consider that the problems will mostly stay where they are at, and I will let my group bring in 1.6.1 when it is released. Hopefully that will take care of the issues.

Thanks for your help and patience,

Josh