reading value from a falt file using in oracle query.

abhi989 · Post by **abhi989** » Fri Oct 07, 2005 9:09 am

Hi everyone,

This is my first time posting questions!!

I am trying to read a flat file which has bunch of pl/sql procedure names. Then I am trying to pass one value at a time into oracle stage which has a user defined SQL written. After reading each value, I want SQL to insert the result into the oracle table.

How do I pass one value at a time into the oracle stage? How do I get that bind variable which contains procedure name from a flat file?

kcbland · Post by **kcbland** » Fri Oct 07, 2005 9:19 am

Welcome. You're trying to use DS to read a list of sp from a file and execute them?

Can't do that using the OCI stage. You're doing something very wrong with the tool. The OCI stage is a data streamer, either as input or output.

If you have a text file of stored procedures and want to execute them, take that list of stored procedures and rewrite it as a PL/SQL script and then use DS to run a sqlplus command line session passing it that script name.

abhi989 · Post by **abhi989** » Fri Oct 07, 2005 9:30 am

I think you miss understood my question.

Disregard there are pl/sql procedures. They are list of values. I am trying to pass them into OCI stage, which has sql query. This query takes each value out of the flat file and insert the result into the database.

I am trying to figure out that how can I pass those value from a flat file into OCI sql.

mabramson · Post by **mabramson** » Fri Oct 07, 2005 2:08 pm

Hello My Fellow DSer,

This could be a very long answer. Iwill try to make it short. I will also make some assumptions. If we are talking about Windows, you can run a command script that pulls the value from the file. Hint: use the batch file commands and a parameter value in your command script. Once the command script is able to read the value out, you can pass that value to your DSJOB at the command line in Datastage. If you set Job Parameters in your Job and in your user define SQL these values can be passed to the job everytime you run the job. Since you want to insert the values each time, you need to run the job as a multi-instance job. This will allow you to parse the flat file until all values are read and inserted one at a time.

Hope this helps.

MA

kcbland · Post by **kcbland** » Fri Oct 07, 2005 2:28 pm

abhi989 wrote: I am trying to figure out that how can I pass those value from a flat file into OCI sql.

As arguments? You can't do that. The SQL is prepared, you cannot have dynamic or different SQL statement with each row. Ex:

Code: Select all

SQL statement = delete from fred where col1=':1'

Your file contains different values for :1. This will work, because the SQL is prepared and then the cursor just does what it needs to do. Data streams into the bind variable and all is well. This is how DS works.

However, if your SQL statement is:

Code: Select all

SQL statement = delete from fred where :1 ='Barney'

This does not work. This SQL cannot be prepared, and thus you get funny results or bind variable issue messages.

Can we go back to the beginning and ask what are you trying to accomplish? Not what are you trying to do, what is the intended result?

ray.wurlod · Post by **ray.wurlod** » Fri Oct 07, 2005 4:58 pm

Are you just trying to load the stored procedure names in to a VARCHAR2 column in an Oracle table?

That is a very easy job; read them with a Sequential File stage and write them, with an ORAOCI stage. You may need an intermediate Transformer stage to generate any other column you want to insert at the same time, such as DATE_INSERTED, DATE_UPDATED, WHO_UPDATED.