Oracle SQL Loader and Informatica PowerCenter Quiz

Source Qualifier is editable because it allows customization of the SQL query used to extract data from the source. Source and Target definitions are metadata that cannot be directly edited in the mapping designer.

When joining two tables from the same database, using Source Qualifier with a custom SQL join is more efficient than Joiner transformation. This leverages the database's native join capabilities and avoids moving data between systems unnecessarily.

The Aggregator transformation outputs the last row of each group by default because it processes rows sequentially and keeps updating the aggregate values until the final row is processed. The first and intermediate rows are used for calculations but only the final row of each group is retained and passed through to the output.

Sorted input cannot be used in Aggregator transformations when nested aggregate functions are present in the expressions or when incremental aggregation is enabled in the session settings. Since the other options describe situations where sorted input is invalid, "None of above" is the correct choice.

Stored Procedure and Lookup transformations can run in connected or unconnected modes in Informatica PowerCenter mappings. Filter, Joiner, and Expression transformations are strictly connected, meaning they must be connected to adjacent transformations in the pipeline to pass data, making other choices incorrect.

Reusable tasks can be created in the Task Developer and then used across multiple workflows, reducing development effort and ensuring consistency. Sessions, Commands, and Email tasks all support reusable templates in Workflow Manager. This reusability means you define the task once and instantiate it wherever needed.

The Sequence Generator transformation has exactly two ports: NEXTVAL (generates the next sequence value) and CURRVAL (returns the current sequence value). These are the only ports available and you cannot add custom ports to this transformation. It is a passive transformation and not connected to the data flow directly.

The Return port is specific to unconnected lookup and is used to return the lookup value when the lookup function is called from an expression. Connected lookups pass values through output ports directly, while unconnected lookups require a Return port to send the result back to the calling transformation via the lookup function.

In Informatica, a Joiner transformation is used to combine data from two different sources. This includes joining two flat files, joining tables from entirely different databases, or joining a flat file source with a relational database table. Therefore, all the listed use cases are correct.

Router transformation is more efficient than Filter when you need to route the same source data based on different conditions because it can handle multiple conditions in a single transformation. You would need multiple Filter transformations to achieve the same result. Router allows you to divide data into multiple groups based on different filter conditions all at once.

The 'Data driven' option allows the mapping to handle both inserts and updates based on flags set in the data flow. When selected, Informatica uses the update strategy flags (DD_INSERT, DD_UPDATE, DD_DELETE) set within the mapping to determine the operation type for each row. This enables a single session to perform different DML operations dynamically.

Source Qualifier can eliminate duplicates at the source level using the 'Select Distinct' option, while Sorter transformation can remove duplicates from the pipeline after sorting. Joiner does not remove duplicates - it combines data from different sources. Expression transformation only performs row-level calculations without duplicate handling capability.

Unconnected lookup is only executed when the lookup function is explicitly called from an expression, unlike connected lookup which runs for every input row. It must have a Return port to pass the value back, and it is not executed automatically for each record passed through the mapping. This on-demand execution makes unconnected lookup more efficient for conditional lookups.

In Informatica PowerCenter, using a Sorter transformation can be faster because it utilizes the Integration Service's memory (cache) and parallel processing, avoiding database overhead and network roundtrips caused by database-level sorting via an 'ORDER BY' clause. However, performance varies based on database optimization, making the absolute claim debatable.

Lookup transformation has an override option that allows you to override the default SQL query used for the lookup. Source Qualifier (SQ) also has a SQL override option to modify the source query. Joiner does not have an SQL override option because it joins data from different pipelines rather than querying a single source.

SQL*Loader is an Oracle utility used to load data from external flat files. All individual options accurately describe its capabilities, limitations, and standard processes, making 'All the above' the correct choice.

SQL Loader (sqlldr) invocation syntax: 'sqlldr' followed by 'userid=' for credentials, 'control=' for the control file, 'log=' for log file, 'data=' for input data file, and 'direct=' for load path. All four options show valid syntax variations - the command works with just the mandatory control parameter, or with any combination of the optional parameters.

SQL Loader requires: a Control File (defines load rules), Data File (source data), and Target Table (where data loads). The Bad File is an OUTPUT file that records rejected records - it's created during loading, not required beforehand.

The control file in SQL Loader specifies: how to interpret the data format (field delimiters, data types), location and format of the source data file, and paths for bad/discard files. Database connection parameters are passed on the command line (userid=), not defined in the control file.