Setting Variables in TDA 3.0 TROUBLE

Toad Data Point
1

MAX(report_dt)

FROM OurSchema.OurReport

I am doing this in order to create an automation that will check the dates in the tables I am using to see if they have been updated prior to running the remaining portion of the script.

The plan is to set the variables (dates from the tables) then use the If condition to determine if they are all updated. I will also use the pause system activity to loop until true.

Anyone can help that would be great. Need an answer ASAP.

I am utilizing TDA 3.0 but I am having some issues setting variables in the automation process. I name my variable and then use a sql script to set the variable. It works to capture the date I am looking for but instead of fomatting it as a date it comes out as
Variable “Var_Dt” set to “2/7/“2012 12:00:00 AM””
Variable value = 2/7/“2012 12:00:00 AM”
The result I am looking for is just the date 2/7/2012.

The script I am using is as follows:

SELECT

Message was edited by: daniel.albrecht_046

2

Hi Daniel

Did you try TO_DATE(:Var_dt,‘DD/MM/YYYY’) in your sql script ?

It easier to set and manipulate a variable date as text and use the ‘TO_DATE’ function in your sql request…

The ‘ADD_MONTH’ function is also very usefull…

3

TO_DATE(MAX(Var_dt),‘DD/MM/YYYY’)

Tried this and no luck…

Now I get
ORA-01830: date format picture ends before converting entire input string

I needed to do it a little different though.

4

TO_DATE(MAX(report_dt),‘DD/MM/YYYY’)

FROM OurSchema.OurReport

I dont know if this is a typo or not but shouldn’t you be using report_dt not var_dt.

SELECT

5

Its a typo…I am using the correct object reference.

6

Unfortunately, there was a bug that failed our parser if there were slashes (/) and/or hyphens (-) in the variable value. I fixed it and hopefully, you won’t see it in our next TDA 3.1 beta.

As for the workaround for right now I would suggest moving dates comparison part of your Automation script into query. I don’t know if it’s possible or not but if you could do dates comparison in the query and return some kind of integer value indicating result of such dates comparison then this should solve your problem.

Sorry for the inconvenience,

Igor.

Message was edited by: IgorM

7

Thanks for the info…hopefully my company will allow us to use the beta. At least I was not crazy.