Oracle: Server & Client communication

The transaction proceeds as follows:

• The client sends a request for data.
• Oracle Net Services packages the request and sends it to the TNS.
• TNS routes the packaged request to the server.
• Oracle Net Services on the server side unpackages the request and sends it to Oracle Database 10g.
• Oracle Database 10g processes the request and sends the requested data to Oracle Net Services.
• Oracle Net Services packages the data and sends it to TNS.
• TNS routes the data to the client.
• Oracle Net Services on the client side unpackages the data and sends it to the application.

Oracle Date Types

PL/SQL is a Programming Language with SQL commands. Since oracle is an RDBMS, we can not define our own programs by only using it. That’s why it supports a language called PL/SQL. We can compile the sql and non sql statements for performing an action that is related to either a data in the data base or not related to data base.

Oracle Data types :

The information in a database is maintained in the form of table, each table consists of rows and columns to store the data. A particular column in a table must contain similar data, which is of a particular type.

The following are different data types supported by ORACLE

1. CHAR This data type is used to store fixed length character of the specified length. Where the maximum size is 255 bytes for columns/rows.

Syntax: char (size)

Example : Result char(4)

2. VARCHAR2 This data type is used to store variable length characters.

Maximum it can take is 2000 bytes for columns/row.

Syntax: varchar2 (size)

Example : sname varchar2(15)

3. NUMBER this data type is used to store both numbers and numbers with decimal pointes. It can take maximum precision up to 38 digits after decimal.

Syntax: Number(value, precisions)

Example : Empno number(5) -> Pure Integers

Sal number(6,2) -> Numbers With Decimals

4. DATE This data type is used to store date and time in a table. The date data types stores year (including the century) . the month, the days, hours, minutes, seconds. The maximum size is 7 bytes for each row in a table.

Syntax: Date

Example : Doj Date

5. LONG This data type is used to store variable length character containing up to 2 GB of information.

Syntax: Long

Example : Remarks Long

Restriction of Long

There are some restrictions of long data type.

1) Only one column is defined as long for table.

2) Long columns con not be indexed.

3) Long columns can’t appear in integrity constraints.

4) Long columns can’t be used in SQL expressions.

5) Long columns cannot be referenced by the SQL function

Type of repositories

Informatica PowerCenter includes following type of repositories:

• Standalone Repository: A repository that functions individually and this is unrelated to any other repositories.

• Global Repository: This is a centralized repository in a domain. This repository can contain shared objects across the repositories in a domain. The objects are shared through global shortcuts.

• Local Repository: Local repository is within a domain and it’s not a global repository. Local repository can connect to a global repository using global shortcuts and can use objects in it’s shared folders.

• Versioned Repository: This can either be local or global repository but it allows version control for the repository. A versioned repository can store multiple copies, or versions of an object. This features allows to efficiently develop, test and deploy metadata in the production environment.

Workflow Manager

Overview:

In the Workflow Manager, we define a set of instructions called a workflow to execute mappings we build in the Designer. Generally, a workflow contains a session and any other task we may want to perform when you run a session. Tasks can include a session, email notification, or scheduling information. You connect each task with links in the workflow.

We can also create a worklet in the Workflow Manager. A worklet is an object that groups a set of tasks. A worklet is similar to a workflow, but without scheduling information. You can run a batch of worklets inside a workflow.

After We create a workflow, We run the workflow in the Workflow Manager and monitor it in the Workflow Monitor.

Workflow Manager Tools

To create a workflow, we first create tasks such as a session, which contains the mapping you build in the Designer. We can then connect tasks with conditional links to specify the order of execution for the tasks we created. The Workflow Manager consists of three tools to help we develop a workflow:

• Task Developer. Use the Task Developer to create tasks you want to run in the workflow.
• Workflow Designer. Use the Workflow Designer to create a workflow by connecting tasks with links. We can also create tasks in the Workflow Designer as we develop the workflow.
• Worklet Designer. Use the Worklet Designer to create a worklet.

Workflow Tasks

We can create the following types of tasks in the Workflow Manager:

• Assignment. Assigns a value to a workflow variable.
• Command. Specifies a shell command to run during the workflow.
• Control. Stops or aborts the workflow.
• Decision. Specifies a condition to evaluate.
• Email. Sends email during the workflow.
• Event-Raise. Notifies the Event-Wait task that an event has occurred.
• Event-Wait. Waits for an event to occur before executing the next task.
• Session. Runs a mapping you create in the Designer.
• Timer. Waits for a timed event to trigger.

Transformations Lists and Overview

Transformations Overview:
A transformation is a repository object that can be generates, modifies, or passes data. The Designer provides a set of transformations that perform specific functions. 

Transformations in a mapping represent the operations the Integration Service performs on the data. Data passes through transformation ports that you link in a mapping or mapplet.

 Transformations can be active or passive. Transformations can be connected to the data flow, or they can be unconnected.

Active: A transformation that can Change the number of rows that pass through the transformation

Passive: A transformation does not change the number of rows that pass through the transformation

Note: Transformation may be unconnected like Store Procedure, Un-Connected Lookup.

List of Transformation and its Descriptions:

SourceQualifier:

Source Qualifier is a Active/Connected transformation. It Represents the rows that the Integration Service reads from a relational or flat file source when it runs a session.

Aggregator:

Aggregator is an Active/Connected transformation. It Performs aggregate calculations like Sum, Max, Min, Avg, Count,..etc.

Application Source Qualifier:

Application Source Qualifier is Active/Connected transformation. Represents the rows that the Integration Service reads from an application, such as an ERP source, when it runs a session.

Expression:

Expression is Passive/Connected transformation. It Calculates a value in a single row.

Filter:

Filter is Active/Connected transformation. It Filters data.

Joiner:

Joiner is a Active/Connected transformation. It Joins data from different databases or flat file systems.

Lookup:

Lookup is a Active or Passive/Connected or Unconnected transformation. It Look up and return data from a flat file, relational table, view, or synonym.

Normalizer:

Normalizer is a Active/Connected transformation. It is used as Source qualifier for COBOL sources. Can also use in the pipeline to normalize data from relational or flat file sources.

Rank:

Rank is a Active/Connected transformation. It limits records to a top or bottom range.

Router:

Router is a Active/Connected transformation. It Routes data into multiple transformations based on group conditions.

SequenceGenerator:

Sequence Generator is a Passive/Connected transformation. It Generates primary keys.

Sorter:
Sorter is a Active/Connected transformation. It Sorts data based on a sort key.

SQL:

SQL is a Active or Passive/Connected transformation. It Executes SQL queries against a database.

StoredProcedure:

Stored Procedure is a Passive/Connected or Unconnected transformation. It Calls a stored procedure.

Transaction Control:

Transaction Control is a Active/Connected transformation. It defines commit and rollback transactions.

Union:
Union is a Active/Connected transformation. It Merges data from different databases or flat file systems.

UpdateStrategy:

Update Strategy is a Active/Connected transformation. It Determines whether to insert, delete, update, or reject rows.

XML:
XML Generator is a Active/Connected transformation. It Reads data from one or more input ports and outputs XML through a single output port.

XML Parser is a Active/Connected transformation. It Reads XML from one input port and outputs data to one or more output ports.

XML Source Qualifier is a Active/Connected transformation. It Represents the rows that the Integration Service reads from an XML source when it runs a session.