Toad World® Forums

Using variables in "Add Query" option of Import Data Wizard

Hello,

I’m wondering if it’s possible to use variables in the query specified in the “Add Query” option of the Import Data Wizard. I have tried to do this within the context of an Automation script where I create and set a variable and then try to reference the variable with :variable_name in the Import Data Wizard query. After specifying the query and hitting the “Next” button I get the error message “ORA-01008: not all variables bound”. I’m not sure if I’m doing something wrong or if what I’m doing is impossible.

Thanks,
Jacob

What version of TDP are you using. This feature was added in TDP 3.4.

I’m using the trial version of TDP 3.4.

Woops. You are correct. We added support for bind vars in export wizard not import. I added QAT-1489 to add this for our next release. In the meantime you will need to link a SQL file and do a find an replace of literal string instead of bind.

Hi Debbie,

I tried using your suggestion of linking a SQL file and doing a replace and I am running into a couple of problems.

The first is that when I try and run the Import Wizard using a linked SQL file with an automation script it fails giving the error “Object reference not set to an instance of an object.”. When I run the Import Wizard outside of an automation script with the same specifications things go off without a hitch.

The second problem occurs when I try and do a find and replace using a variable for the replace value. The drop down selection is not populated with a variable which I create using a “Set Variable” action prior to the “Find and Replace” action in the automation script. However when I create a “Set Variable Value” action the same variable does show in the drop down selection there.

Thanks,

Jacob

Let me try out exactly what you are trying to do.

The null ref is a bug in the GA release. We have a fix for that. Please down load from one of the links below. Use the one that goes with your license, base or pro.

community-downloads.quest.com/…/ToadDataPoint_Base_3.4.0.2056.zip

community-downloads.quest.com/…/ToadDataPoint_pro_3.4.0.2056.zip

I don’t quite understand your comment on the find and replace. But here is what I did.

  1. Define variable “a” in variable activity.

  2. Create Test.sql with content of 'Select * from address where region_id = 1"

  3. Add Find and Replace activity. Find and replace “1” with variable value of "a’. Make sure and place out put file as different name such as TestCopy.sql

findandreplace1.png

  1. Build import script linking the TestCopy.sql.

It should look like this. This works for me if you use the build above.

script.png

After installing the build I downloaded from the link you provided the two problems I was having went away.

In regards to the second problem I was having selecting the variable I created in the “Find and Replace” variable drop down list, it seems as though this was another bug in the old build I was using. The steps you laid out in the provided screenshots were exactly how I built my automation script before the build update and I was not able to see the name of the variable I created in the “Find and Replace” step.

Thank you so much for you help thus far. I have another question though. Do you have any suggestions on how to continue to use the “Find and Replace” step effectively on the saved query file? In your example you replace “1” with your variable “a”. If you were to run this script a second time you probably wouldn’t find the string “1” in the query.

Thanks,

Jacob

I am glad this is working for you.

On your last statement, you need to use the original file as a template and never copy over it. In my screen shot I show replacing the values in Test.sql but saving the result after the Find and Replace to TestCopy.sql. It is TestCopy.sql that you want to link to your import script. This will solve the issue you are mentioning.

I knew the solution was something as simple as that and I was just missing it. I’ve got everything working now thanks to your awesome help and responsiveness!

Great news! Happy Holidays!