When I attempt to use automation Designer to Export to Excel, it works for a SQL file, but only if there is no substitution variable. If there is a substitution variable in the query, I get an error: ORA-01858: a non-numeric character was found where a numeric was expected.
My SQL file is: SELECT...FROM...WHERE TRUNC(date_added) >= '&dt_since'; If I replace the substitution variable with an actual date, it runs without a problem
ok, so I have to use SQL text instead of a SQL file for the hyper 'x' to appear
But when U follow the diagram it points to a query iterator variable (sysdate from dual). should I create the variable in the iterator as SELECT '&dt_since' FROM dual? I have created a number of bad variables. how do I edit/delete them?
Variables in scripts is not supported. What I describe above only works in "SQL Text".
To answer your 2nd question, your query should select a variable value. So maybe "select to_date(...) as dt_since from dual" is more like what you'd want. Then you'd have a variable called 'dt_since' that contains a date variable to use.
BUT....you can't pass that into a Export Dataset with source as "SQL File", so I don't think it helps you.
Maybe instead, you could put each SELECT from your script into a separate Export Dataset action using SQL Text.
So, if you had 3 SELECTs in your script, use 3 Export Dataset actions instead. Like this:
I have created a number of bad variables. how do I edit/delete them?
How did you create them? If they are just the result of query iterators (or some other kind of iterator) in automation designer, they are cleaned up automatically after each run.
If you created them manually, they would be in the options window, under 'Variables', here:
I was able to delete the variables - thanks
unfor I was not able to get the automated export to prompt me for the date variable
You can set up variable prompting like this:
Drop a 'create variable' and 'variable prompt' into the action. Make up a name for your variable.
Just type it into the variable prompt dialog if it isn't in the dropdown.
Set up the export dataset action
use a bind variable, not a substitution variable in your SQL.
Set the bind variable to your variable name. just type it in if it's not
in the dropdown
Use "run from here" on the "create variable" action to run the whole thing.
type in your date value
Super useful! Thanks!
thanks John, I created the variable actions and updated the Export as shown below. However I get this error: ORA01858: a non-numeric character was found where a numeric was expected
@bastak Get rid of the SQL iterator. Its only purpose was to give you a variable. Now you have the "Create Variable" and "Variable Prompt" actions to do that instead. Set it up to look just like my last set of screen shots (with your own SQL in the export dataset action, of course)
Also: note that I have selected "Environment Variable", not "Literal"
"Literal" would be used if you wanted to specify the date value (like 06-10-2021) in the export dataset action itself, rather than take it from a variable.
perfect - thanks!
