dataProfileColumn

Parent: dataLoadDefinition

Direct Properties:

PropertyTypeRequiredDefault ValueDetails
columnNamestringYesdata type=string; maxLen=128

Name under which the column is exposed to Oracle APEX components.

If the data profile is part of a Data Load Definition, this name is used to map data profile columns to the target table. For instance, the ENAME data profile column will be loaded to the ENAME column of the target table. If the profile column does not exist in the target table, it will be ignored during data load.

Property Groups:

  • source
  • parsing
  • computation
  • lookup
  • transformation
  • advanced
  • comments
source
PropertyTypeRequiredDefault ValueDetails
sequencenumberYesdata type=number; maxLen=22
typeenumYesdatadata type=string; lov=STATIC
Valid values: data lookup sqlExpression sqlQuery
lineNumberbooleanYesfalsedata type=boolean
Applies whentype = data
dataTypeenumYesdata type=string; lov=STATIC
Valid values: varchar2 number date timestamp timestampWithLocalTimeZone timestampWithTimeZone clob boolean blob geometrySdoGeometry array jsonDocument
Applies whentype = data and lineNumber = false
maxLengthintegerYes4000data type=integer; maxLen=5
Applies whentype = data and lineNumber = false and dataType = varchar2
primaryKeybooleanYesfalsedata type=boolean
Determines whether this is a primary key column. A primary key can consist of multiple columns.
 
source {
  sequence: 42
  type: data
  lineNumber: false
  dataType: varchar2
  maxLength: 42
  primaryKey: false
}
parsing
PropertyTypeRequiredDefault ValueDetails
selectorTypeenumYesnamedata type=string; lov=STATIC
Valid values: name sequence regexp
Applies whentype = data and format in (csv)type = data and format in (xlsx)
selectorstringYesdata type=string; maxLen=255
Applies whentype = data and format in (csv) and selectorType in (name)type = data and format in (csv) and selectorType in (regexp)type = data and format in (xlsx) and selectorType in (name)type = data and format in (xlsx) and selectorType in (regexp)
columnNumberintegerYesdata type=integer; maxLen=32767
Applies whentype = data and format in (csv) and selectorType = sequencetype = data and format in (xlsx) and selectorType = sequence
pathExpressionstringYesdata type=string; maxLen=255
Applies whenformat in (json) and type = data and lineNumber = falseformat in (xml) and type = data and lineNumber = false
formatMaskstringNodata type=string; maxLen=255
Applies whentype = data and lineNumber = false and dataType in (number)type = data and lineNumber = false and dataType in (date)type = data and lineNumber = false and dataType in (timestamp)type = data and lineNumber = false and dataType in (timestampWithTimeZone)type = data and lineNumber = false and dataType in (timestampWithLocalTimeZone)
hasTimeZonebooleanYestruedata type=boolean
Applies whentype = data and lineNumber = false and dataType in (date)type = data and lineNumber = false and dataType in (timestamp)type = data and lineNumber = false and dataType in (timestampWithTimeZone)type = data and lineNumber = false and dataType in (timestampWithLocalTimeZone)
decimalCharstringNodata type=string; maxLen=1
Applies whentype = data and lineNumber = false and dataType = number
groupCharstringNodata type=string; maxLen=1
Applies whentype = data and lineNumber = false and dataType = number
 
parsing {
  selectorType: name
  selector: value
  columnNumber: 42
  pathExpression: value
  formatMask: value
  hasTimeZone: false
  decimalChar: value
  groupChar: value
}
computation
PropertyTypeRequiredDefault ValueDetails
sqlExpressionmultiline stringYesdata type=multiline string; maxLen=4000
Applies whentype = sqlExpression
Enter the SQL Expression to compute the value of this column. The SQL expression can reference data profile columns of the Data column type.
sqlQuerymultiline stringYesdata type=multiline string; maxLen=4000
Applies whentype = sqlQuery
Enter the SQL Query to compute the value of this column. The SQL query must return a single value and can reference data profile columns of the Data column type.
 
computation {
  sqlExpression: ```sql
    value
  ```
  sqlQuery: ```sql
    value
  ```
}
lookup
PropertyTypeRequiredDefault ValueDetails
tableOwnerstringNodata type=string; maxLen=32767
Applies whentype = lookup
tableNamestringYesdata type=string; maxLen=128
Applies whentype = lookup
returnColumnstringYesdata type=string; maxLen=32767
Applies whentype = lookup
whereClausemultiline stringNodata type=multiline string; maxLen=4000
Applies whentype = lookup
tableColumn1stringYesdata type=string; maxLen=32767
Applies whentype = lookup
dataColumn1stringYesdata type=string; maxLen=32767
Applies whentype = lookup
tableColumn2stringNodata type=string; maxLen=32767
Applies whentype = lookup and tableColumn1 is set
dataColumn2stringYesdata type=string; maxLen=32767
Applies whentype = lookup and tableColumn1 is set and tableColumn2 is set
tableColumn3stringNodata type=string; maxLen=32767
Applies whentype = lookup and tableColumn1 is set and tableColumn2 is set
dataColumn3stringYesdata type=string; maxLen=32767
Applies whentype = lookup and tableColumn1 is set and tableColumn2 is set and tableColumn3 is set
 
lookup {
  tableOwner: value
  tableName: value
  returnColumn: value
  whereClause: ```sql
    value
  ```
  tableColumn1: value
  dataColumn1: value
  tableColumn2: value
  dataColumn2: value
  tableColumn3: value
  dataColumn3: value
}
transformation
PropertyTypeRequiredDefault ValueDetails
typeenumNodata type=string; lov=STATIC
Valid values: leftTrim rightTrim trim replace lower upper nullIf regexpReplace regexpNullIf
Applies whentype = data and lineNumber = false
trimCharsstringNodata type=string; maxLen=4000
Applies whentype = data and lineNumber = false and type in (leftTrim)type = data and lineNumber = false and type in (rightTrim)type = data and lineNumber = false and type in (trim)
findstringYesdata type=string; maxLen=4000
Applies whentype = data and lineNumber = false and type = replace
regexpstringYesdata type=string; maxLen=4000
Applies whentype = data and lineNumber = false and type in (regexpReplace)type = data and lineNumber = false and type in (regexpNullIf)
replaceWithstringNodata type=string; maxLen=4000
Applies whentype = data and lineNumber = false and type in (replace)type = data and lineNumber = false and type in (regexpReplace)
valuestringYesdata type=string; maxLen=4000
Applies whentype = data and lineNumber = false and type = nullIf
 
transformation {
  type: leftTrim
  trimChars: value
  find: value
  regexp: value
  replaceWith: value
  value: value
}
advanced
PropertyTypeRequiredDefault ValueDetails
staticIdstringYesdata type=string; maxLen=255
 
advanced {
  staticId: value
}
comments
PropertyTypeRequiredDefault ValueDetails
commentsmultiline stringNodata type=multiline string; maxLen=4000
 
comments {
  comments: ```
    value
  ```
}

APEXlang Example

dataProfileColumn sample (
    columnName: value
    source {
        sequence: 42
        type: data
        lineNumber: false
        dataType: varchar2
        maxLength: 42
        primaryKey: false
    }
    parsing {
        selectorType: name
        selector: value
        columnNumber: 42
        pathExpression: value
        formatMask: value
        hasTimeZone: false
        decimalChar: value
        groupChar: value
    }
    computation {
        sqlExpression: ```sql
          value
        ```
        sqlQuery: ```sql
          value
        ```
    }
    lookup {
        tableOwner: value
        tableName: value
        returnColumn: value
        whereClause: ```sql
          value
        ```
        tableColumn1: value
        dataColumn1: value
        tableColumn2: value
        dataColumn2: value
        tableColumn3: value
        dataColumn3: value
    }
    transformation {
        type: leftTrim
        trimChars: value
        find: value
        regexp: value
        replaceWith: value
        value: value
    }
    advanced {
        staticId: value
    }
    comments {
        comments: ```
          value
        ```
    }
)