1. Application Developer's Guide
  2. Defining Load Sets
  3. Defining Different Load Set Types
  4. Text
  5. Text Load Set Parameters

Text Load Set Parameters

Text Load Sets include the Parameters listed below. You can change their default values in the Execution Setup definition. The user can change their values at runtime.

Note:

Because these are predefined, required Parameters you should change only the values specified below or the Load Set may not function properly. Do not change any other properties of these parameters.

  • Data File Name. Do not select a file when defining the Load Set. Oracle LSH uploads the file when you submit the Load Set's Execution Setup. If you upload the file during Load Set definition, the data will probably be outdated at the time the Load Set is executed. Therefore, leave this field blank. The user must enter the filename at runtime if Load From Server OS is set to No.

  • Data Format. Choose either Fixed or Delimited:

    • Fixed. The system uses the Load Set's target Table Descriptor Column definitions to interpret the data type and length of the values in the text file. The file must contain the correct number of characters for each value in each column of each record.

      In the case of Table Descriptor Columns with a Number data type (with a fixed format file), if you data includes a sign (+/-), you must increase the length by one to accommodate the positive (+) or negative (-) sign. In addition, if the data includes numbers with decimal places, you must declare the precision and increase the length by one to accommodate the decimal marker. See Defining Table Columns for further information.

    • Delimited. With delimited records, you specify the character used in the source file between each record as the separator character. Different records in the same column can be of different lengths, up to the maximum allowed. The system loads the contents between the separator character into each consecutive Column of the target Table instance.

      For example, if you have a Table instance with Columns Patient ID, Patient Initials, and Date of Birth, and the separator character was a comma, and the date format was DDMMYYYY, the first two records might look like this:

      54602,EKP,04081949

      66781,BAH,22011955

  • Delimiter Character. (Required for Delimited-format Load Sets only.) Specify the character(s) used as the value delimiter in the source text file. In the example above, the comma (,) is the separator character. The default character is the comma (,). You can use any two unicode characters as a delimiter. For example, to use tab as the delimiter character, enter \t.

    Note:

    Even if you specify that this is a fixed format Load Set, this Parameter is displayed. Leave the default value as is.

  • Enclosing Character. (For Delimited-format Load Sets only; recommended but not required.) If any record value may contain the delimiter character, you need an enclosing character. Specify the character used to enclose each value. The default character is double quotation marks (").

    For example, if a double quotation mark (") were the enclosing character, the same two records would look like this in the source text file:

    "54602","EKP","04081949"

    "66781","BAH","22011955"

    You can use any single unicode character as an enclosing character.

    Note:

    Even if you specify that this is a fixed format Load Set, this Parameter is displayed and is required. Leave the default value as is; the system does not use this value.

    Note:

    Both delimiter and enclosing characters should be characters that never appear in the data content of the file.

    The delimiter character and the enclosing character must be different. If they are the same, the Load Set execution will fail.

    Note:

    If you use a tab as the delimiter and specify an enclosing character, the following limitations apply:

    • Tabs cannot be part of the data even if the data is enclosed.

    • The length of the data, including enclosing characters and duplicate enclosing characters, cannot exceed 4000 characters.

  • Initial Records to Skip. If you want to not load records at the beginning of the file, enter the number of records you want the system to skip. The default value is zero (0).

  • Maximum Allowed Errors. Tolerance factor; the maximum number of invalid rows you are willing to tolerate before the SQL*Loader stops the load process and marks the Load Set job as failed. The default value is 0.

  • Temp LOB ID. This Parameter is for internal use only. Do not modify it.

  • Date Format. Enter the date format used in the source data or .mdd file, if any. The date format you enter here must correspond exactly with the date field in the source text file, else the Load Set cannot execute correctly. Do not enter a value here if the source text file does not contain a date field.

  • Load From Server OS. You have the option to load a file from your local computer or from a remote system. Set this parameter to Yes (default is No) if your file is either on the Oracle LSH DP server or on another computer that has its drives NFS-mounted on the DP server computer. If you set this parameter to No, you must upload a text file from your local computer when running the Text Load Set job by providing its name in the Data File Name parameter.

    Note:

    The system can load remote text files only from an Oracle LSH DP Server that is running the SQL*Loader service.

  • Server OS Filename. If you set Load From Server OS to Yes, enter the absolute path of the remote file with the filename and extension, else leave this parameter blank.

  • Direct. If set to Yes (default is No), the system uses the direct path INSERT to load data from the text file into the Oracle LSH target Table. If the Table Descriptor mapped to the Text Load Set is not of the Transactional High Throughput processing type, the system loads data using the conventional INSERT and writes warning messages to the job log.

    Note:

    See the Oracle Database Utilities Guide (part number B14215-01) for more information on this SQL*Loader Command-Line Parameter.

  • (Optional) Rows. This parameter applies only when you set Direct to Yes.

    It indicates the number of rows that SQL*Loader reads together at one time from the datafile before writing them to the target Table and committing to the database. If you leave this parameter blank, by default the system reads all the rows from the datafile and then writes the data to the target Table.

    For conventional path loads, (if you set Direct to No), this parameter indicates the number of rows that SQL*Loader assigns to the bind array. If you leave this parameter blank, Oracle LSH calculates the number of rows.

    Note:

    A very high or a very low value for Rows can adversely affect the system's performance. Enter a value only if you are absolutely sure that you need to change this.

    See the Oracle Database Utilities Guide (part number B14215-01) for more information on this SQL*Loader Command-Line Parameter.

  • Drop and Recreate Indexes. If set to Yes (default), and if the value of the parameter Direct is set to Yes, the system drops all non-unique indexes before running a data-loading job and recreates the non-unique indexes after loading data into the target Tables. The system does not drop indexes if Direct is set to No.

  • Recoverable. This parameter applies only when you set Direct to Yes.

    If set to Yes (default), the system can recover data in the event of a failure. If set to No, the data becomes unrecoverable because SQL*Loader does not maintain a redo log file for the data.

    Note:

    For more information on the SQL*Loader UNRECOVERABLE clause at: Oracle® Database Utilities at https://docs.oracle.com/cd/E11882_01/server.112/e22490/toc.htm.

Parent topic: Text