Running Oracle Cursor at Before SQL

chulett · Post by **chulett** » Thu Dec 01, 2011 7:34 am

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

joycerecacho · Post by **joycerecacho** » Thu Dec 01, 2011 10:14 am

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

chulett · Post by **chulett** » Thu Dec 01, 2011 1:27 pm

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

joycerecacho · Post by **joycerecacho** » Fri Dec 02, 2011 5:41 am

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

chulett · Post by **chulett** » Fri Dec 02, 2011 8:31 am

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.

chulett · Post by **chulett** » Fri Dec 02, 2011 8:33 am

viewtopic.php?t=124375

chulett · Post by **chulett** » Fri Dec 02, 2011 12:23 pm

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.

joycerecacho · Post by **joycerecacho** » Fri Dec 02, 2011 12:34 pm

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,

nani1974 · Post by **nani1974** » Fri Dec 02, 2011 12:59 pm

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

chulett · Post by **chulett** » Fri Dec 02, 2011 1:32 pm

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

chulett · Post by **chulett** » Fri Dec 02, 2011 3:24 pm

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.

chulett · Post by **chulett** » Mon Dec 05, 2011 8:17 am

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.

chulett · Post by **chulett** » Mon Dec 05, 2011 9:28 am

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

joycerecacho · Post by **joycerecacho** » Mon Dec 05, 2011 9:58 am

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

Best regards,