Analytics Software

Thanks very much I will look at those.

The variable FileName is not getting populated. I am using it as input to a mapping. Any ideas? thank you

Hi,

You have to create a package and add the variable (with the status Refresh Variable) and the mapping.

The variable may be assigned a value in the package or it may have a refreshing SQL statement.

Adrian

I have the variable and the mapping in the package and am running the scenario. The variable

I am using https://blogs.oracle.com/dataintegration/using-parameters-in-odi:-the-dynamic-file-name-example for instrucation on how to do this.

I have verified with a mapping that I can process (in mapping) the file which lives on the ODI server. So my model is setup correctly. I updated the resource name in the model

including the project code prefacing the filename. FileName is the my variable name.

thank you!

When you generate a scenario from the package you are asked if you want to include the variable in the scenario. So check the variable to be included in the scenario.

When you execute the scenario you are asked to provide a value for the variable: provide the file name.

Adrian

In odi package you have just declared the variable, its similar to declare section in pl/sql block.

You have to pass some value to it then only variable will holds some value and the package will run successfully.

Try to use the procedure to get the file name in the package and then link odi variable to it.

You package will be something like this

1) Odi procedure to get the file names

2) Odi variable

3) Odi mapping

Regards,

Pavan

Thank you for your response Adrian. I did check the box to include the variable when regenerating the scenario.  I will not know the file name at run time. I have created a variable (FileName) to house a dynamic filename.

Deena

Pavan,

Are you saying that I need a procedure (also) to get the value of the variable?

Christopphe, in his article , https://blogs.oracle.com/dataintegration/using-parameters-in-odi:-the-dynamic-file-name-example

made no mention of needing a procedure to accomplish retrieving a dynamic filename. But, I see nothing from what I have written

to this point , that would be performing that part.

Deena

Hi,

In the blog you have mentioned they are passing file name dynamically during run time, they are not retrieving the file name.

But in your case you want to read the file name during run time.

You have to write ODI procedure to read the file name.

Regards,

Pavan

I have one more question,

If you don't know the file name, how are you passing in to the datastore?

Regards,

Pavan

I know that filename that I am prototyping with, but I will not know it when this is up and running in production.

Regards,

Deena

Now you came to the point

Create a temporary table say file_name_tbl, use above blog and write file name into that temporary table.

Use variable in refresh mode and read the file name into that variable from temporary table file_name_tbl

Any way you are already using variable in datastore.

Regards,

Pavan

Thanks.

I am not a java developer.

I created a procedure in ODI (below). When i run the proc scenario I get the value /dw/data/dev/datain/File added to my table. I was expecting to get the name of the first file with the /dw/data/dev/datain directory.

Hi,

Indeed, the script inserts the full path and file name. Using Oracle SQL you can extract file name. So replace last line in your script with:

INSERT INTO URDW.ODI_GETFILENAME

select substr( '<@=theFile@>' , instr('<@=theFile@>','/',-1,1) +1) from dual

I have tested and it works. Script extract all the characters from last "/" to the end of the path - which is file name.

Hope this helps,

Adrian

thank you

Hi,

Please let us know if the workaround I have suggested works.

Adrian

Adrian,

Thank you for your help. It makes sense your suggestion and I will  hold onto it for safe keeping.

I realized after speaking with our lead that I was trying to resolve an issue that in fact we will not have!  We will actually be fed the

pertinent info for the filename and will be able to put it into a variable to use when running our package.

I will hold onto your script however.. you never know what we will need to address down the road.

Regards,

Deena