Explore topic-wise InterviewSolutions in .

1. Claration SECTION
2. EXECUTION section
3. EXCEPTION section

• Variables and constants
• Embedded SQL SUPPORT
• Flow CONTROL
• CURSOR management
• Exception handling
• Stored procedures and packages
• TRIGGERS

• Stored procedures and FUNCTIONS
• PACKAGES
• TRIGGERS
• Cursors

• Block-structured language.
• STORED PROCEDURES help better sharing of application.
• Portable to all environments that SUPPORT Oracle.
• INTEGRATION with the Oracle DATA dictionary.

The colon (: )SIGN implies that the VARIABLE :DEFICIT is an external variable.

The colon (: )sign implies that the variable :deficit is an external variable.

It assigns a variable the same data TYPE USED by the COLUMN, for which the variable is created. For EXAMPLE,
dcode := DEPT.detpno%type;
The variable dcode is created with the same data type as that of the deptno column of the dept table.

It assigns a variable the same data type used by the column, for which the variable is created. For example,
dcode := dept.detpno%type;
The variable dcode is created with the same data type as that of the deptno column of the dept table.

It DECLARES a composed variable that is EQUIVALENT to the row of a table. After the variable is created, the FIELDS of the table can be ACCESSED, using the name of this variable.
For example
emptype := emp%rowtype;
name := emptype.empname;

It declares a composed variable that is equivalent to the row of a table. After the variable is created, the fields of the table can be accessed, using the name of this variable.
For example
emptype := emp%rowtype;
name := emptype.empname;

A PACKAGE is a file that groups FUNCTIONS, cursors, STORED PROCEDURES, and variables in one place.

A package is a file that groups functions, cursors, stored procedures, and variables in one place.

Oracle uses workspaces to EXECUTE the SQL COMMANDS. In other words, when Oracle processes a SQL command, it opens an area in the memory CALLED Private SQL Area. A cursor is an identifier for this area. It ALLOWS programmers to name this area and access it’s INFORMATION.

Oracle uses workspaces to execute the SQL commands. In other words, when Oracle processes a SQL command, it opens an area in the memory called Private SQL Area. A cursor is an identifier for this area. It allows programmers to name this area and access it’s information.

It returns the NUMBER of ROWS that are PROCESSED by a SQL STATEMENT.

It returns the number of rows that are processed by a SQL statement.

It returns the Boolean value TRUE if at least one ROW was PROCESSED.

It returns the Boolean value TRUE if at least one row was processed.

It returns the BOOLEAN VALUE TRUE if no ROWS were PROCESSED.

It returns the Boolean value TRUE if no rows were processed.

LOOP COMMAND, FOR.. LOOP command, WHILE command.

LOOP command, FOR.. LOOP command, WHILE command.

A TRIGGER is automatically EXECUTED without any action required by the user, whereas, a STORED procedure needs to be explicitly INVOKED.

A trigger is automatically executed without any action required by the user, whereas, a stored procedure needs to be explicitly invoked.

Basically triggers are used to create consistencies, access RESTRICTION and implement SECURITIES to the database. Triggers are also used for −

• Creating validation mechanisms involving SEARCHES in multiple TABLES
• Creating logs to register the use of a table
• Update other tables as a result of inclusion or changes in the current table.

Basically triggers are used to create consistencies, access restriction and implement securities to the database. Triggers are also used for −

A WHEN clause specifies the condition that MUST be true for the TRIGGER to be TRIGGERED.

A WHEN clause specifies the condition that must be true for the trigger to be triggered.

The optional ARGUMENT [OR REPLACE] in a CREATE TRIGGER command re-creates an EXISTING trigger. Using this option allows changing the definition of an existing trigger without having to DELETE it FIRST.

The optional argument [OR REPLACE] in a CREATE TRIGGER command re-creates an existing trigger. Using this option allows changing the definition of an existing trigger without having to delete it first.

DROP TRIGGER COMMAND.

DROP TRIGGER command.

ALTER TRIGGER update_marks DISABLE;

ALTER TRIGGER update_marks DISABLE;

A FUNCTION returns a VALUE and a STORED PROCEDURE doesn’t return a value.

A function returns a value and a stored procedure doesn’t return a value.

User defined exceptions are declared under the DECLARE section, with the keyword EXCEPTION. SYNTAX −
<exception_name> EXCEPTION;

User defined exceptions are declared under the DECLARE section, with the keyword EXCEPTION. Syntax −
<exception_name> EXCEPTION;

Explicit cursors are defined explicitly USING the CURSOR statement, with a general SYNTAX −
CURSOR cursor_name [(parameters)] IS query_expression;
It allows PROCESSING queries that return multiple rows.

Explicit cursors are defined explicitly using the CURSOR statement, with a general syntax −
CURSOR cursor_name [(parameters)] IS query_expression;
It allows processing queries that return multiple rows.

The steps that need to be performed on explicit cursor are −
DECLARE − assigns a NAME to the cursor and defines the structure of query within it.
OPEN − EXECUTES the query, whereby the rows returned by the query are available for fetching.
FETCH − assigns values from the current row (cursor POSITION) into SPECIFIED variables.
CLOSE − releases the memory space.

The steps that need to be performed on explicit cursor are −
DECLARE − assigns a name to the cursor and defines the structure of query within it.
OPEN − executes the query, whereby the rows returned by the query are available for fetching.
FETCH − assigns values from the current row (cursor position) into specified variables.
CLOSE − releases the memory space.

PL/SQL packages have TWO parts −
SPECIFICATION part − where the interface to the application are DEFINED.
Body part − where the IMPLEMENTATION of the specification are defined.

PL/SQL packages have two parts −
Specification part − where the interface to the application are defined.
Body part − where the implementation of the specification are defined.

CREATE PACKAGE COMMAND is used for CREATING the SPECIFICATION part. CREATE PACKAGE BODY command is used for creating the body part.

CREATE PACKAGE command is used for creating the specification part. CREATE PACKAGE BODY command is used for creating the body part.

The types, objects, and SUBPROGRAMS DECLARED within a PACKAGE are referred to using the dot NOTATION as −

• package_name.type_name
• package_name.object_name
• package_name.subprogram_name

The types, objects, and subprograms declared within a package are referred to using the dot notation as −

The DROP PACKAGE COMMAND.

The DROP PACKAGE command.

Oracle IMPLICITLY declares a cursor to all the DDL and DML COMMANDS that return only one row. For QUERIES returning MULTIPLE rows, an explicit cursor is created.

Oracle implicitly declares a cursor to all the DDL and DML commands that return only one row. For queries returning multiple rows, an explicit cursor is created.

PL/SQL is an extension of SQL. SQL is non-PROCEDURAL. PL/SQL is a procedural language DESIGNED by ORACLE to overcome the LIMITATIONS that exist in SQL.

PL/SQL is an extension of SQL. SQL is non-procedural. PL/SQL is a procedural language designed by oracle to overcome the limitations that exist in SQL.

During SESSION runs, the files CREATED are NAMELY Errors log, Bad file, Workflow LOW and session log.

During session runs, the files created are namely Errors log, Bad file, Workflow low and session log.

In order to perform session PARTITION one need to configure the session to partition source data and then installing the Informatica SERVER MACHINE in multifold CPU's.

In order to perform session partition one need to configure the session to partition source data and then installing the Informatica server machine in multifold CPU's.

Surrogate key is a REPLACEMENT for the natural prime key. It is a unique identification for each row in the table. It is very beneficial because the natural primary key can change which eventually makes update more difficult. They are ALWAYS used in form of a DIGIT or integer.

Surrogate key is a replacement for the natural prime key. It is a unique identification for each row in the table. It is very beneficial because the natural primary key can change which eventually makes update more difficult. They are always used in form of a digit or integer.

This happens when it faces DD_Reject in UPDATE STRATEGY transformation. Moreover it disrupts the database constraint filed in the ROWS was CONDENSED.

This happens when it faces DD_Reject in update strategy transformation. Moreover it disrupts the database constraint filed in the rows was condensed.

Row by row processing is done by informatica. Every row is inserted in the target table because it is MARKED as default. Update strategy is USED whenever the row has to be updated or inserted based on some sequence. Moreover the CONDITION MUST be SPECIFIED in update strategy for the processed row to be marked as updated or inserted.

Row by row processing is done by informatica. Every row is inserted in the target table because it is marked as default. Update strategy is used whenever the row has to be updated or inserted based on some sequence. Moreover the condition must be specified in update strategy for the processed row to be marked as updated or inserted.

Reusable TRANSFORMATION is used numerous TIMES in mapping. It is different from other mappings which use the transformation SINCE it is stored as a metadata. The TRANSFORMATIONS will be nullified in the mappings whenever any change in the reusable transformation is MADE.

Reusable transformation is used numerous times in mapping. It is different from other mappings which use the transformation since it is stored as a metadata. The transformations will be nullified in the mappings whenever any change in the reusable transformation is made.

When the inputs are taken directly from other transformations in the pipeline it is called CONNECTED LOOKUP. While unconnected lookup doesn't TAKE inputs directly from other transformations, but it can be used in any transformations and can be RAISED as a function using LKP expression. So it can be said that an unconnected lookup can be called multiple times in mapping.

When the inputs are taken directly from other transformations in the pipeline it is called connected lookup. While unconnected lookup doesn't take inputs directly from other transformations, but it can be used in any transformations and can be raised as a function using LKP expression. So it can be said that an unconnected lookup can be called multiple times in mapping.

Option for incremental aggregation is ENABLED WHENEVER a session is created for a MAPPING aggregate. Power CENTER performs incremental aggregation through the mapping and historical cache data to perform new aggregation CALCULATIONS incrementally.

Option for incremental aggregation is enabled whenever a session is created for a mapping aggregate. Power center performs incremental aggregation through the mapping and historical cache data to perform new aggregation calculations incrementally.

It is a diverse input group transformation which can be USED to combine DATA from different sources. It WORKS LIKE UNION All STATEMENT in SQL that is used to combine result set of two SELECT statements.

It is a diverse input group transformation which can be used to combine data from different sources. It works like UNION All statement in SQL that is used to combine result set of two SELECT statements.

It is used for looking up data in a relational table through mapping. LOOKUP DEFINITION from any relational database is imported from a SOURCE which has tendency of CONNECTING client and server. ONE can use multiple lookup transformation in a mapping.

It is used for looking up data in a relational table through mapping. Lookup definition from any relational database is imported from a source which has tendency of connecting client and server. One can use multiple lookup transformation in a mapping.

JOINER transformation combines two AFFILIATED heterogeneous sources living in different locations while a source QUALIFIER transformation can combine data EMERGING from a common source.

Joiner transformation combines two affiliated heterogeneous sources living in different locations while a source qualifier transformation can combine data emerging from a common source.

It is a MEDIUM of filtering ROWS in a mapping. Data needs to be TRANSFORMED through filter transformation and then filter condition is applied. Filter transformation contains all ports of input/output, and the rows which MEET the condition can only pass through that filter.

It is a medium of filtering rows in a mapping. Data needs to be transformed through filter transformation and then filter condition is applied. Filter transformation contains all ports of input/output, and the rows which meet the condition can only pass through that filter.

VALUES can be calculated in single ROW before writing on the target in this form of TRANSFORMATION. It can be used to perform non aggregate CALCULATIONS. Conditional statements can also be tested before output results go to target TABLES.

Values can be calculated in single row before writing on the target in this form of transformation. It can be used to perform non aggregate calculations. Conditional statements can also be tested before output results go to target tables.

It allows ONE to do AGGREGATE calculations such as SUMS, averages etc. It is unlike expression TRANSFORMATION in which one can do calculations in GROUPS.

It allows one to do aggregate calculations such as sums, averages etc. It is unlike expression transformation in which one can do calculations in groups.

All the MAPPINGS cannot be VALIDATED SIMULTANEOUSLY because each time only ONE MAPPING can be validated.

All the mappings cannot be validated simultaneously because each time only one mapping can be validated.

When DATA moves from one CODE page to another provided that both code pages have the same character sets then data loss cannot occur. All the CHARACTERISTICS of SOURCE page must be available in the target page. MOREOVER if all the characters of source page are not present in the target page then it would be a subset and data loss will definitely occur during transformation due the fact the two code pages are not compatible.

When data moves from one code page to another provided that both code pages have the same character sets then data loss cannot occur. All the characteristics of source page must be available in the target page. Moreover if all the characters of source page are not present in the target page then it would be a subset and data loss will definitely occur during transformation due the fact the two code pages are not compatible.

The TYPES of METADATA includes SOURCE definition, Target definition, MAPPINGS, Mapplet, TRANSFORMATIONS.

The types of metadata includes Source definition, Target definition, Mappings, Mapplet, Transformations.

Repositoryreports are established by METADATA reporter. There is no NEED of SQL or other transformation since it is a WEB app.

Repositoryreports are established by metadata reporter. There is no need of SQL or other transformation since it is a web app.

The dimensions that are utilized for playing DIVERSIFIED ROLES while remaining in the same database DOMAIN are called role playing dimensions.

The dimensions that are utilized for playing diversified roles while remaining in the same database domain are called role playing dimensions.

Lookup transformations are those transformations which have admission right to RDBMS based data SET. The server makes the access FASTER by using the lookup TABLES to look at explicit table data or the DATABASE. Concluding data is achieved by matching the look up CONDITION for all look up ports delivered during transformations.

Lookup transformations are those transformations which have admission right to RDBMS based data set. The server makes the access faster by using the lookup tables to look at explicit table data or the database. Concluding data is achieved by matching the look up condition for all look up ports delivered during transformations.

Aggregator transformations are handled in chunks of instructions during each run. It STORES transitional VALUES which are found in local buffer memory. Aggregators provides extra cache FILES for storing the transformation values if extra memory is required.

Aggregator transformations are handled in chunks of instructions during each run. It stores transitional values which are found in local buffer memory. Aggregators provides extra cache files for storing the transformation values if extra memory is required.