Using Parameters in TDA and an Oracle DB

Toad Data Point
1

Select trunc(:StDT) as rptStartDT from DUAL;

So I have a multiple step Query that I need to have a Date parameter on each step, same date just used several times.

If I use the bind variable I get errors “ORA-01036: illegal Variable Name/Number” and it would prompt me for each time I used it.

So I thought ok I would create a table with just the date parms then just join this table in with all steps. If I run the select without create it works great as soon as I use the Create I get the above error. And as soon as I start using PL/SQL with a Declare, Begin, End. then it really throws a monkey wrench because of all the Create Table statements and Drop Table Statements. Oh I am not a fan of Oracle, but it is what I have to use.

create table tmpParms as

2

You cannot use a bind variable in a create statement. You can use a replacement var. Use the ‘&’ symbol. You will be prompted for a value just like a bind but it replaces the value not bind it. You may need to surround with single quotes if is text. IE: ‘&Stdt’. This will not make a date column. You would need to cast it to accomplish that. (I did not try)

create table tmpParms as
Select trunc(&StDT) as rptStartDT from DUAL;

Debbie

3

from DUAL;

worked… Thanks

Select trunc(to_date(’&StDT’, ‘MM/DD/YYYY HH:MI:SS AM’)) as rptStartDT

create table tmpParms as

4

Now this works and at the end of my script I have a select fields to display. I then create a report from it and want to automate it. I keep getting a msg that there is a VAriable and that I need to define it. I cannot find where to do this. Been looking for the last 30+ minutes.

5

I assume this is in the automation script designer. Click on the red bang validation error. The hint should say what it thinks is a variable that is not defined.

If this does not resolve the issue, please send me your script and all supporting files to dpeabody@quest.com

Debbie