Can't be done there. That's PL/SQL rather than SQL. You can either put that work into the job or (since it returns nothing) build a stored procedure from that code and CALL the proc 'before SQL'.
-craig
"You can never have too many knives" -- Logan Nine Fingers
I'm pretty sure that bascially all of the stages support loading their source SQL from a file, either from a specific 'SQL File' option or by using a specific tag... {FILE} perhaps? It is documented.
-craig
"You can never have too many knives" -- Logan Nine Fingers
For the official 'Load from file' option, leave off the {FILE} part, just include the true pathname. That prefix is for stages that don't support the 'Load from file' option directly.
-craig
"You can never have too many knives" -- Logan Nine Fingers
As far as I know, all that is supported are SELECT statements. Where are you doing this, on the target side? If it is letting you use that option there, you'd still need to bind all of the columns in the stage to the sql...
-craig
"You can never have too many knives" -- Logan Nine Fingers
Let the stage generate the SQL so you can see an example of what it needs to look like in your file, specifically the numbered bind parameters that it uses.
-craig
"You can never have too many knives" -- Logan Nine Fingers
Correct, it's not really meant to be used for the purpose you attempted and if you want to 'insert thousands of rows' you should source from a file of values and let the 'main flow of data' handle all that.
I've used it to dynamically build SQL for the stage to read so we could customize the select sql to fit different situations. You always need to ensure you select the same number of fields and data types but how you get them can vary as needed. Last project, we used it simply to vary the Oracle hints we used based on volumes - different explain plans for large monthly loads versus small daily incremental loads - and all controlled by parameterized filenames. Worked great.
-craig
"You can never have too many knives" -- Logan Nine Fingers