DSXchange

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'.

Yeah, you are right, Chulett.

Actually my colleague would like to read a file - where the cursor statement was written in - and execute it somehow at the DataStage.

Do u think it is possible?

Thank you!!

For SQL, sure... but not for PL/SQL.

A FILE??

Do u think it is possible to execute an statement written in a Sequential File, for example?

Even it is a SQL and not PL/SQL. SQL is also interesting.
How would I do that?

Tkssssss a lot!!

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.

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.

I left it off and now the messages are:

"Invalid SQL SELECT statement was entered";

"INSERT INTO DW.LKP_AREA_ATUAC_FUNC (func_area_atuac_cod,func_area_atuac_des) VALUES (99,'CANTINA')
teste..Oracle_OCI_26.DSLink1: DSP.Open GCI $DSP.Open error -100.";

"Attempting to Cleanup after ABORT raised in stage teste..Oracle_OCI_26";

I ran this SQL at SQL Developer and it worked, the Statement in the file (which is very simple) runs ok. Of course it is simple, it is just a test.

What can it be?

Thank you a lot!

Best regards,

Hi,
If you are running this SQL in DataStage .......it will not take Semicolon...remove that and it should work..

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...

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.

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.

Right. Those options were made for other purposes and when used as you noted, there are... limitations.

Thank you, Chulett.
I marked this topic as 'Resolved'.

Best regards,