Dynamically generating target file names

A little known fact is how to dynamically create a file name for a map that has a file as a target (for example). The configuration property 'Target Data File Name' allows a data file name to be entered, it is actually possible to insert a PLSQL expression here given the correct formatting. This let's you define any valid PLSQL expression, so you could add a date to a file name for example.

In the example below  append the SYSDATE to the string 'orders' and this is the name of my file (a file name such as 'orders05-JUL-07' will be written).

Map File Target:

The generated code will be like the following snippet with the expression
being evaluated in PLSQL:

   

        "ORDERS_FILE_Fh" := UTL_FILE.FOPEN(

          'ITD_FILES_LOCATION1',

          'orders'||sysdate||'',

          'w',

          32767);



Basically the file name you type will be wrapped in single quotes, so
if you use quotes in your expression which end up with a valid PLSQL
expression you can do what you want.

Comments:

Post a Comment:
  • HTML Syntax: NOT allowed
About

ETL, CDC, Real-Time DI and Data Quality for the Oracle Database from the inside.

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today