Configuring F2ORA

The F2ORA node is used when data needs to be transferred from File to database table. In this section, we discuss how to map the File to Destination. In F2ORA, source will be a file and destination will be a table.

Prerequisites for F2ORA

Below are the prerequisites for running F2ORA model:

• TNS entry of the database SID (example: DEV), should be present in "tnsnames.ora" file which will be used by sql loader.

  For Example: If ETK is using the sql loader which is present at path <ORACLE-HOME>/bin and we are loading data into DEV database then DEV tns entries must be there in "tnsnames.ora" file at <ORACLE-HOME>/network/admin/.

• If "tnsnames.ora" file, which is used by the sql loader is present in some other user defined directory then we need to mention that path for the key TNS_ADMIN in "actualpath.properties" file which will be at path <TRINITI-HOME>/ETK Directory/WEB-INF/classes/.

  For Example: If "tnsnames.ora" file is available at path <ORACLE-HOME>/network/admin/<User defined directory>/, then we need to enter value for TNS_ADMIN key in "actualpath.properties" file as mentioned below (TNS_ADMIN key will be already in the file, only value needs to be entered).

  TNS_ADMIN=<ORACLE-HOME>/network/admin/<User defined directory>/tnsnames.ora (no spaces allowed before and after '=').

The following are steps to be followed to configure the F2ORA node:

1. Click the F2ORA node in the work area. The F2ORA frame is displayed as shown below:

   

   Nodes Configuration_Figure 74:

2. Double-click on the menu bar of the F2ORA frame to maximize the screen. Click on the File, you see the following screen:

   

   Nodes Configuration_Figure 75:

3. There are two ways to map the data into a file, User-defined Configuration and Fixed Type File Configuration

   User-defined configuration - Users can select a delimiter or specify a character which acts as the separator in the file.

   Select the separator from Separator Options pane. The options are:

   Semi Colon

   Tab

   Space

   Comma

   Others - Type a character which should act as a delimiter.

   Qualifier: Specify a character like double quote (") or single quote (') to act as a text qualifier. By default it takes double quote (") as Qualifier when Apply button is clicked.

   Fixed Type File Configuration: Select Fixed Width to specify the position of the fields in the file as shown in Nodes Configuration_Figure 81.

4. Click Browse File, the dialog box for File Column Selection appears as shown in the figure below:

   

   Nodes Configuration_Figure 76:

5. Select the Skip Starting Rows From File checkbox when the required number of starting rows in the file needs to be skipped. The spinner will be enabled on selecting the checkbox.

   

6. Move the up/down arrow of the spinner for increasing/decreasing the number of starting rows that should be specified to skip.
7. Click on the List of Values icon at Browse File field for selecting file.

   The Open dialog box is displayed as shown in the figure below:

   

   Nodes Configuration_Figure 77:

8. Select a file and click Open. You see the following screen:

   

   Nodes Configuration_Figure 78:

   The column names of the selected file are displayed in the Column Name field as shown in the above figure.

9. Click Apply. You see the selected columns under File as shown below:

   

   Nodes Configuration_Figure 79:

   When a File that contains empty Column Name is selected, then a message as shown below will be displayed on clicking Apply button.

   

   Note: The same message as shown in above figure will be displayed on generating code for an existing model, for which the source file contains empty Column Name.

10. Select Fixed width option, the following screen is displayed:

    

    Nodes Configuration_Figure 80:

11. Drag the slider to change the Maximum Length of the column as shown in the figure below:

    

    Nodes Configuration_Figure 81:

    The user can configure auto number for F2ORA node using Auto Number Configuration tab.

12. Click on the Auto Number Configuration tab. Two buttons, namely, Add Row and Delete Row appears. To add an Auto Number, click on the Add Row button. A new row is displayed as shown below:

    

    Nodes Configuration_Figure 82:

13. Click on the new row to display list of values.
14. Select the required value from list and click OK. The selected value appears as follows:

    

    Nodes Configuration_Figure 83:

    If the AutoNumber with name that is used by any other model is added then the following message box appears.

    

    Nodes Configuration_Figure 84:

    In Auto Number Configuration tab, the auto number cannot be created; it can only be added/applied to the F2ORA node configuration. To create an auto number, the user needs to navigate to Application Objects module through Triniti Portal.

    

    Nodes Configuration_Figure 85:

    To create an Auto Number:

    In the Application Objects build frame, click on the Auto Number node in the nodes area. The property sheet for Auto Number node appears. Enter the values for required fields. Now, click Apply and then click Operations�Commit. For more information on Auto Number creation refer to TAB-User Manual.

15. Click on the Destination node, two tabs namely, Configuration and Mapping are displayed as shown in the figure below:

    

    Nodes Configuration_Figure 86:

16. Click on the button available at Table Name field in the Configuration tab, the Table Selection Dialog box appears as shown in the figure below:

    

    Nodes Configuration_Figure 87:

17. Select a table and click 0K. The columns of the table are displayed in the Available Columns field.

    

    Nodes Configuration_Figure 88:

18. Select the required column under Available Columns. Click the '>' (rightward arrow) button. The selected column appears on the right pane under Selected Columns. Click the '>>' button to select all columns. Click Apply, the selected columns appear under Destination node as shown in the figure below:

    

    Nodes Configuration_Figure 89:

19. Select the column under the File, drag and drop on the column for which mapping is required under Destination node.

    

    Nodes Configuration_Figure 90:

20. Default value can be mapped to the table column by entering the required value in the Defaultvalue column of the Mapping tab as shown in the above figure. If the data type of the table column is date, select the format for date from the drop-down list box available at DateFormat cells.
21. The maximum value in a range of column values is displayed at Maximum Size column as shown in the above figure.
22. SQL Functions can be applied to the mapped column.

    To apply SQL functions:

    1. Select mapped column for which sql function needs to be applied. Right-click on that column to see the Functions popup menu.
    2. Click on the Functions popup menu. You see the screen as shown in the figure below:

    

    Nodes Configuration_Figure 91:

    1. Select the required sql function from the list on left pane and click OK to apply to the selected column.

• Clear: Click on this button to clear the values entered for Mapped Column, Defaultvalue, and DateFormat for each Table Column.
• Enable ETK Constants: Select this checkbox when you need to specify the filename/sequence of a file in the Defaultvalue column. The format in which filename/sequence can be entered is mentioned in the below example.

  Example:

  @<filename>@

  @<sequence>@

• File Process Order : Is based on the Timestamp of files in In Bound Directory. A message is displayed in instance runner about file process order.

  

  • Maximum Allowed Errors : The value in this field allows the user to provide the option for SQL loader, till how many errors are allowed for an execution. By default SQL loader takes 50 errors. And user can set any maximum number.

Case 1 : When value is given (For example 10),then the query is executed as below.

Case 2 : When value is not given, then the query is executed as below.

Case 3 : When a non numeric value is give, then it raises an error as shown bellow.