Informatica Interview Questions

Its thrusday; After coming from office just checked the mail and there was a pop-up appeared, "Interview Scheduled for IBM on Saturday"

Immediately opened a Prints of Informatica Help Files, and fortunately got question bank there.. as in it was waiting for me only.

This question bank is given by "Meenakshi Saxena", a really good resource from Mahindra Satyam working for ASBI - Cisco and was our team member. She had collected questions from different interviews attended by her mates and by her own.

IBM
Please draw the data model of your project

What is operational data source

How do you apply the incremental logic in your project

Do you have type-2 scd being used in your project? How do you deal with the expired keys of SCD-2 in the related fact tables? i.e. when you expire the rows in the dimension, what do you do with the corresponding keys in the fact? Do you replace them?

What is a surrogate key? What is the role that Surrogate key plays in the case of SCD-2

What is incremental aggregation? Please explain with example how it works

Please draw the Architecture of Informatica 8.1.1

What is Pushdown optimization? Please explain the different types of Pushdown Optimization

What are the drawbacks of PushDown optimization ?

What are the different lookup caches? Explain about Dynamic Lookup Cache. What is associated port in the lookup tranformation for dynamic lookup cache.

How do you do Version Control in Informatica?

If there are 11 versions of a mapping and if you want to keep the latest version and delete the remaining versions .. how do you do it?

What is a deployment group? What is a label?

What the difference between a Shared Tranformation and a Reusable Tranformation? How do you create ShortCuts and how do you use them ?

How can you share a tranformation between various repositories?

When you load a target flat file? how do get the column names in that file? Is that possible in Informatica 7.1.1.
How do you attain this in informatica 7.1.1. only through mapping not through windows or unix

Do we have an alternate for this in informatica 8.6.1. ?

What is confirmed dimension? Are their any confirmed dimensions being used in your project ?

Have you used Normelizer Tranformation? There are 2 informatica generated columns in Normalizer the GKey (Generated Key).. what is the significance of this key?

What is the difference between stop and abort? Under what circumstances you would stop and when you would abort? Which one is better?

There is a workflow having 3 sessions in sequence. A-->B-->C. We want session C to run only on 12 successful completions of session B. How would you do that?

Can you briefly explain the Architecture of Informatica 8.1.1.?

Do we have sorted input option in Lookup Tranformation? Can we use this option for relational sources ?
What will happen if you enable sorted input, and the condition columns are not grouped?

What will happen if you create too many partitions in Informatica ?

What are disadvantages of creating too many indexes on database tables?

What is co-related subquery?

What are different types of indexes that you have used?

What are database triggers?

How do ou delete duplicates in a table?

What are the performance tuning activities you follow in your project?

What is the difference between the result-set of Full Outer Join and Union Tranformation?
Will the result-set be the same if the columns are same in both tables?

What is the difference between Star Schema and Snow Flake Schema?

Have you ever worked with Legacy System sources?


IBM - Asked to Tushar

What is incremental aggregation, what are the scenarios it is used?

What is even wait and event raised, asked a scenario where some sessions are arranged in parallel plus sequential order. Where will we put event wait and raise tasks?

Write a query to eliminate duplicate

A map has no update strategy, still how can we update the target rows?

what is normalizer tranformation ?

How would you convert, using informatica, multiple rows in source to single row in target (de-normalize data)?

One sequence generator is connected to 2 different targets and nextval is connected to PK, what will be value of PK when first 5 rows are passes in the mapping?

What is persistent cache, what type of cache has to be used when configuring persistent cache?

How do you use dynamic lookup? Wha is the name of port created by Informtica in this? what are its values and what do they mean?

What is difference between shared object and shortcut?

What is difference between parameter and variable?

Give 3 infromatica pre-defined mapping workflow, mapping and session parameters?

What is transaction control tranformation?

The PK in target is not linked in mapping still it is getting values populated for every row, how is it possible?

Difference between types of indexes?

Flow of project - end ot end

What kind of SCD was implemented in your project? And how was it implemented?

IBM asked to Dona

Please draw the data model of your project.

How many type of tasks are there ? Explain event wait task with practical example?

How is even wait and event raise, asked a scenario where some sessions are arranged in parallel plus sequential order, where will we put event wait and raise tasks?

What are the different lookup caches.. Explain about Dynamic Lookup Cache.. what is associated port in lookup tranformation for dynamic lookup cache.

What the difference between Shared Tranformation and Reusable Tranformation? How do you create shortcuts and how do you use them?

What is indirect files loading?

What is normalizer?

How many ports does unconnected lookup return? If I do lookup on EID and I want Ename and Esal from the same table by using an unconnected lookup then how u will do it?

Why you are using fast load why not Mload in your project?

How are you taking care of exceptions in your project? Develop a mapping for that?

What is constraint based loading? How it works in Informatica?

2 Scenario based questions.

Capgemini

What are the differences between Infa 7.1 and 8.1

What are the new features of 8.1.1 that you are using in your project

What is a shared sequence generator? How does it work? If I have same seq generator used for 2 mappings and one mapping has already updated it say 10.. will the next mapping start with 0 or 11?

There are 3 different flat files... How can you load these 3 flat files in 3 different targets simultaneously.. Not in 3 different pipelines

If you get a file everyday from the user which does not have a specified structure... i.e. on one day the # columns is 5.. is t possible to use a single mapping that can handle this kind of flat file?

What are the different components of Informatica power center?

What is the difference between Repository Manager and Repository Administrator?

What do you know about the new central web service in Informatica 8.1.1 ?

How do you migrate workflows in Repository Manager? Can you take xls of only one workflow at a time or can you take xmls for multiple workflows simultaneously?

What is the difference between Bulk Load and Normal Load? What are the pre-requisites for Bulk Load?

Suppose you have a Relational Table that does not have a Primary Key defined on it. How can you do updates on this kind of a table using Informatica? Can you use update strategy ? If so..how ? {Update Sql OverRide}

Have you used Normalizer Tranformation? What is Normalixer Tranformation?

How do you do version control in Informatica?

Have you used Mapplets? Can we have Source Qualifier Tranformation in a Mapplet?

What is a Global Repository and What is a Local Repository? State all features and difference between the 2.

FCS

Can you briefly explain the Informatica Architecture?

What are different target load types that we use?

Can u bulk insert with Update strategy?

What are the other pre-requisites for bulk insert?

What is the joiner cache?

How do you tune a lookup tranformation?

Under what circumstances do we use a persistent lookup cache?

How many files will be created when we use a persistent lookup cache?

What values do we have in Lookup Index Cache and Data Cache Files?

Can we use a aggregator function in a aggregator tranformation without using a group by?

Can we use Group By in an aggregator tranformation without using an aggregator function?

What are the different performance tunning you hav done in your project?

Have you used PushDown optimization? What is pushdown optimization?

What is a star schema? and what is the snow-flake schema?

When do we go for a snow-flake schema ?

What is the basic reason for maintaining a datawarehouse?

What is a bank atm? Is that an OLTP? or OLAP? It also maintains history so why it is not a DWH?


WIPRO

What is EDW?

What is Dimensional Modeling? Why is it required?

What is the Relationship between Dim and Fact?

Difference between snowflake schema and start schema? In what situations we will go for snowflake ?

How do you maintain Version Control in Informatica in your project?

If you had scd-2 in your lookup, why you did not use dynamic lkp cache? How else did you achieve scd-2?

How to use dynamic lkp cache?

What is a difference between mapping variable and parameter?

Three different examples where mapping variable could be used?

Difference between stored proc and function?

Different type of functions?

Which index to be used if only select statements are required on a table?

How to eliminate duplicates from a table using query only?

How to fetch employees with top 5 salary from EMP table?

DELL

What are the aspects considered while deciding number of facts and dim in a model?

What is fact? How do you decide this element is a part of fact and not dim?

What is incremental loading?

What is bottleneck?

What is constraint based loading?

3 ways in which you can drop indexes on target while loading and then re-create after loading using informatica?

Trace levels?

Which tracing level is bettwer if you have to migrate one mapping to production?

What all tranformations can be set as unconnected?

What is transaction control tranformation?

How to start a failed workflow so that the previous successful sessions do not start again?

What is the syntax of parameter file?

How can we see the number of rows that were sent to and out of each tranformation after session is successful

What are different recovery methods in Informatica?

How can we achieve incremental aggregation?

3 ways to generate seq number without using seq generation tranformation?

Difference in Version 8 and 8.6?

Explain architecture of 8 (domain/node concept)

What do you mean by load balancing?

What is metadata manager?

What are the minimum requirements to run a debugger?

How to set/edit breakpoints in a debugger?

Truncate and Delete difference

Different types of Indexes. what are the differences?

Some unix questions on commands like, how to copy, concatenate files etc

TCS

Which version of Infra you worked on?

What is the difference?

Have you heard about PC9? What are new features?

What is snowflake schema?

When does a client go for snowflake schema?

What is a junk dimension, explain with example?

Give an example of ODS?

what is confirmed dimensions? Example?

What is the need of time dimension?

What is incremental aggregation?

In how many ways can you create source in Infra?

What are different type of loookup cache?

What is dynamic lookup cache? How to implement in Infra?

What happens when you get a null value from lookup and it is used in some function in expression?

Performance Tunning? How will you find bottleneck?

What will happen if we forget to check group by ports in Aggregator?

What will happen if we check sorted input in Aggregator but does not pass sorted inuputs?

What are advantages of bulk loading?

What is constraint based loading? What are advantages and disadvantages?

How to change the data type in SQL Server? cast and convert

Will unique constraint allow null twice?

What is indexing? What is the purpose?

Drop, Delete and Truncate difference?

What are triggers?

Things to be checked in informatica

Difference between Pre and Post SQL at target and Source level

--> Keep one primary key at Target level, try to input duplicate data from source deliberately and delete it using pre and post sql, check which one gets through.

--> Check for "Preserving The Transaction Boundries" at pg. 300 in Joiner Tranformation.

TeraData Basics

5-Jun-2011
[Following things have been copied from Teradata Datbase Administration manual which is available on net]

Overview of Space

Permanent : Perm space allocated to a user or database is a uniquely defined, logical repository for
1. Data Tables
2. Table Hearders (2 header rows per table)
3. Secondary Indexes (SI) [Primary Indexes are part of the table]
4. Join Indexes (JI)
5. Hash Indexes (HI)
6. Permanent Journals
7. Stored Procedures
8. Triggers
9. User Defined Functions (UDF)
10. User Defined Methods (UDM)

When an object is created or data rows are inserted, the space is allocated as needed from the perm space of the immediate owner. The space is returned automatically when no longer needed.

Note: The space allocated to a user or database that is un-assigned to an object is available for spool or temp space.

A database or user with no perm space can still own views, macros, and triggers but can not have objects that require space such as tables, UDFs, stored procedures, HIs, JIs, or journals.

Spool: Spool space holds intermediate query results or formatted answer sets to queries and volatile tables. The system can use unassigned per space for spool space.

When a user creates a new users or databases, the amount of spool space for those new objects must not exceed the spool space limit of their immediate owner. If you do not specify a spool space limit, a new user automatically inherits the same amount of spool as its parent.

To more easily manage spool space, define the value for spool in a profile. You can then assign a profile to users and all of the users will inherit the spool space definition.

Temporary (Temp) : Temp space defines the number of bytes the system will user to store data for global temporary tables. The value you specify must not exceed the value of the immediate parent at the time of creation. If you do not specify a value, the maximum vgalue defaults to that of the parent.

To more easily manage temp spcae, define the value for it in a profile. You can then assign a group of users to this profile and all of the users will inherit thje tmep space definition.



Permanent Space Availability
Initially, all available permanent space in the system is allocated to user DBC. From user DBC, you create other users who use up some of the space owned by DBC. As you create additional new databases, or users under those users, permanent space limits are deducted from the available (unused) space of the immediate owner of that database or user. If those users use up all the space allocated to them by their owner, those users can not create new objects untill they obtain more space.

Note : Unused space is allocated dynamically for temporary or spool space, which can reduce the actual amount of perm space available at a given point in time. Thus, specifying a PERM limit does not guarantee that a session can get all of that space upon demand.

To determine the amount of perm space available by a per-AMP basis, query the DBC.DiskSpace view. To obtain information for all AMPs on your system, use the SUM aggregate. For more information, see DiskSpace[V][X] in data dictionary.

Permanent Space Allocation

Permanent space is dynamically acquired by data blocks and cylinders when the system inserts rows. The data block is a disk-resident structure that contains one or more rows from the same table and is the physical I/O unit for the file system.Data Blocks are stored in physical disk sectors or segments, which are grouped in cylinders.

The total space of your entire Teradata Database configuration is derived by summing all database and user PERM limits. Total unallocated space is equal to the sum of all permanent limits minus the sum of all permanent space currently in use.

You can use the DBC.DiskSpace view to termine how much permanent spacei s being allocated to which database or user. The following table lists the fields that track perm space values.

CURRENTPERM :- The total number of bytes (including table headers) currently allocated to existing data tables, index tables, and subtables, stored procedures, triggers and permanent journals residing in a particular database/user.

This value is maintained on each AMP

MAXPERM : The maximum number of bytes available for storage of all (current and future) data tables, index tables, and subtables, stored procedure, triggers, and permanent journals owned by a particular database/user.

Note: For each database or user reported, the number of permanent bytes is divided by the number of AMPs in the configuration. The result is recorded on each AMP and may not be exceeded on that AMP. That is, a user may own several objects, as long as the combined storage requirements are within the MaxPerm limit set for that user on each AMP.

PEAKPERM: The largest number of bytes ever used to store data in a user or database since the last reset of this value to zero.

This value is maintained on each AMP.

To reset PeakPerm value to Zero, use the DBC.ClearPeakDisk macro.

Specifying Spool Space Limits:

Spool space is permanent space not assigned to any database or user and remains spool until permanent space is required.

The system needs spool space for TJs or to create spool files for processing queries when returning an answer. Spool space is especially important for queries that involves full table scans or user NUSIs.

The system uses spool space for the following :

. The response rows of every query run by that user during a session. Thus, each user needs a high enough spool allocation to contain the biggest anticipated answer set.

. Very large tables. Large tables usually require more available spool space than smaller tables, because intermediate rows are held in spool space during query execution.

. Large Volatile tables. These tables require more available spool space.

Spool Space Allocation

Teradata database allocates spool space dynamically only from disk cylinders that are not being used for permanent or temporary data.

Note: Permanent, temporary and spool data blocks can not coexist on the same cylinder.

Spool space is not reserved. All unused perm space in the Tearadata Database is considered available spool space. When spool is released, the file system returns the cylinders it was using to the free cylinder list.

Limiting spool space for a user helps reduce the impact of possibly bad queries. You set spool space limits for a database, a user, or a profile to limit spool space for a user, but you can not limit spool space at table level.

Defining Temporary Space Limits

Temporary space is used to hold rows of materialized global temporary tables. It is allocated at the database or user level, but not the table level.

You define a temporary space limit with the TEMPORARY parameter of a CREATE/MODIFY PROFILE or CREATE / MODIFY UISER/DATABASE statement.

note: A profile definition overrides any user definition. it does not append settings to the definition.

Query the DBC.DiskSpaceX view to find the system levels for temporary space. When using the CREATE USER, CREATE DATABASE or CREATE PROFILE statement to assign temporary space limits, keep your space limits in mind. Query the DBC.DiskSpaceX view to find the system levels for temporary space.

The following table describes the different types of temporary space.

CURRENTTEMP : This is amount of space currently in use by Global Temporary Tables.

PEAKTEMP : This is the maximum temporary space used since the last session.
Temporary space is released when the session terminates.

MAXTEMP : MaxTemp specifies the limit of space available for global temporary tables.

The may not exceed the limit of
1. The creator or modifier of the profile, when setting TEMPORARY in a profile.
2. The immediate owner of the user being created or modified, if a profile does not apply.

If you do not specify a value and the user is associated with a profile, MaxTemp defaults to the value of the profile, if defined. If the profile TEMPORARY is set to NULL or NONE, or the user is not associated with a profile, MaxTemp defaults to the value of teh parent of the user.


Increasing Space By Giving OwnerShip

This section describes how to increase the permanent space limits of a database or user by transferring a database or user to another database or user.

Transferring OwnerShip

You can transfer both database or users, but to simplify the discussion, this section describes databases. Note that you can easily replace any instance of the word "Database" in the following section with the word "user". Also note that databases can own users and users can own databases.

The GIVE statement transfers ownership of a database, including all the databases owned by that database. In addition, you transfer the permanent space limits defined for that database.

When you give a database to another database in the hierarchy, only the permanent space limit for that database is transferred. The spool and temporary space limits remain the same. All descendents of the given database remain descendents of that given database.

When you drop a database, its permanent space limit is created to its immediate owner. however, its spool space and temporary space is not credited to its immediate owner (that is, the immediate spool and temporary space allocation of the owner remains unchanged)

GIVE does not transfer and change explicit privileges.

Transferring Permanent Space

With Create, Give and Drop, you can transfer the permanent space of one database or user to another. This is particularly useful if you wish to transfer permanent space from a child of a child back to user DBC when user DBC is not the immediate owner.

For example, assume the following hierarchy:


Also assume that:
. F has a MAXPERM of 10, a MAXSPOOL of 50, and a MAXTEMP of 25
. E has a MAXPERM of 10, a MAXSPOOL of 20 and a MAXTEMP of 15

To increase permanent space for E:

1. From space owned by F, create temporary database X with MaxPerm of 5:
Create Database X from F as PERM = 5;
The default is to allocate to a new database the same spool and temp space as its owing database, so MAXSPOOL for X defaults to 50 and MAXTEMP for X defaults to 2. The PERM allocation for X is taken from the space of its owning database; thus, the MAXPERM of F is reduced to 5.

2. Give X to E by using the GIVE statement to transfer ownership.
GIVE X To E;

3. Drop X with the following statement :

Drop Database X;

This increases the MAXPERM of E to 15.

The MAXSPOOL and MAXTEMP of E are unchanged at 20 and 15, respectively.

Query and It's Explain Plan ..

Was working on report extract and one of the table was having 13Cr + records. My whole query was taking hell lot of time, when I checked the explain plan -- table with 13Cr of records was going for Full Table Scan (FTS) and another table was going for FTS twice.

The question came suddenly in my mind, FTS is OK but why another table is getting executed for 2 times? Another team mate helped me and changed the OR clause to UNION and explain plan became fine.

Thought to replicate same issue at Home, but un-fortunately here even putting or condition table is not going for FTS 2 times. At a single go only its fetching the data. Need to check the query at office again...

Till now, what every query formation and its corresponding explain plan I have, I am putting the same here.

1. Normal Join
select emp.first_name || ' ' || emp.last_name, emp.
employee_id,
dep.DEPARTMENT_NAME,
dep.DEPARTMENT_ID,
loc.STATE_PROVINCE,
loc.STREET_ADDRESS,
coun.COUNTRY_NAME,
reg.REGION_NAME,
hop.JOB_TITLE, hop.MAX_SALARY
from
hr.employees emp,
hr.departments dep,
hr.locations loc,
hr.countries coun,
hr.regions reg,
hr.jobs hop
where emp.department_id = dep.department_id
and dep.location_id = loc.location_id
and coun.country_id = loc.country_id
and coun.region_id = reg.region_id
and emp.job_id = hop.job_id
and (dep.department_id = 10 or region_name = 'Europe')

In TOAD its an advantage that, it will number the steps which Oracle Optimizer has followed to fetch the rows from db. As in above image,
1. first LOCATIONS table got scanned, which was having all country_id
2. Using those country id got from location table, it went for index scan on country table - so that it will get rowids corresponding to country names
3. As Oracle Optimizer was having information about department_id from location table, it went for index scan in departments table using those ids.
4. From index scan in departments table, it got rowids for other information in departments table at Step No 5.
5. Resultset of Departments, Locations and Country table got merged at Step 6. using nested loop join.
6. As O.P. has already having Region_Id from Country table, using those Region_Ids it went for index scan in Region Table ; to fetch rowids from Region Table related to other information.
7. As department_ids information was already with Optimizer, using that information it went for index scan in Employees table and later on Table access using those rowids got from index scan.
8. Employees, Regions and Dep, Country, Location information got merged using Nested Loop join.
9. Using locations table, new optimizer got rowids related to region. Using those rowids it went for index scan
10. At last it went for Region table scan using the rowids got from Index Scan of Region table.

If you observe the explain plan here, below some key points we observe
1. Index Unique Scan - Index unique scan happens when Index is defined as a Unique or Primary Key. Generally in case of Primary - Foreign key relationship Index Unique Scan happens and which is very cost effective than other index scan.
2. Index Range Scan - When index is defined as a normal index, Index Range Scan happens. Which is slight costly that Index Unique Scan, but cost effective that Index Full Scan. Index Range Scan happens when there is a range defined in filter condition and that having the cardinality very high. [ I need to verify this again regarding cardinality.]
3. Index Full Scan - If cardinality is less, then Optimizer has to scan whole index which ultimately leads to Index Full Scans. If there is a table scan happening after the Index Full Scan, one should avoid the Index Full Scan and went for Full Table scan either using No Index or Full Hints.
4. Nested Loops - When indexes are defined or Primary Key - Foreign Keys are defined, generally Optimizer went for Nested Loops. If you drop any index on these tables, Optimizer goes for Hash Join. In hash join, Optimizer creates its on Has Function and combines 2 different data sets. For Hash Join, its not required to have indexes on tables whereas to perform Nested Loop Join, there has to have indexes defined on base tables.


Inner Query
select emp.first_name || ' ' || emp.last_name,
emp.employee_id
/*,
dep.DEPARTMENT_NAME,
dep.DEPARTMENT_ID,
loc.STATE_PROVINCE
, loc.STREET_ADDRESS,
coun.COUNTRY_NAME,
reg.REGION_NAME,
hop.JOB_TITLE,
hop.MAX_SALARY*/
from hr.employees emp,
hr.jobs hop
where emp.department_id in(select distinct department_id
from hr.departments dep,
hr.locations loc,
hr.countries coUn,
hr.regions reg
where dep.location_id = loc.location_id
and coun.country_id = loc.country_id
and coun.region_id = reg.region_id
and (dep.department_id = 10 or region_name = 'Europe') ) AND EMP.JOB_ID = HOP.JOB_ID


With Clause
with dd as
(select distinct department_id
from hr.departments dep,
hr.locations loc,
hr.countries coUn,
hr.regions reg
where dep.location_id = loc.location_id
and coun.country_id = loc.country_id
and coun.region_id = reg.region_id
and (dep.department_id = 10 or region_name = 'Europe'))
select emp.first_name || ' ' || emp.last_name,
emp.employee_id
/*, dep.DEPARTMENT_NAME,
dep.DEPARTMENT_ID,
loc.STATE_PROVINCE,
loc.STREET_ADDRESS,
coun.COUNTRY_NAME,
reg.REGION_NAME,
hop.JOB_TITLE,
hop.MAX_SALARY*/
from hr.employees emp,
hr.jobs hop , dd
where emp.department_id = dd.department_ID
AND EMP.JOB_ID = HOP.JOB_ID



Because of the with clause, Optimizer went for execution of whatever defined inside it first. And later on with rest of the statement.

We can generate same explain plan using the Ordered hint without the with clause. Forcing optimizer to execute the tables which we want, we can get similar results as using with clause. You can see the example below of Ordered Hint which gave same results and explain plan as we got from With Clause.

Sub Table


select emp.first_name || ' ' || emp.last_name,
emp.employee_id/*,
dep.DEPARTMENT_NAME,
dep.DEPARTMENT_ID,
loc.STATE_PROVINCE,
loc.STREET_ADDRESS,
coun.COUNTRY_NAME,
reg.REGION_NAME,
hop.JOB_TITLE,
hop.MAX_SALARY*/
from hr.employees emp, hr.jobs hop , (select distinct department_id
from hr.departments dep, hr.locations loc,
hr.countries coUn, hr.regions reg
where dep.location_id = loc.location_id
and coun.country_id = loc.country_id
and coun.region_id = reg.region_id
and (dep.department_id = 10 or region_name = 'Europe'))dd
where emp.department_id = dd.department_ID
AND EMP.JOB_ID = HOP.JOB_ID



Ordered Hint - To get same explain plan as with Clause


select /*+ ordered */ emp.first_name || ' ' || emp.last_name,
emp.employee_id/*,
dep.DEPARTMENT_NAME,
dep.DEPARTMENT_ID,
loc.STATE_PROVINCE,
loc.STREET_ADDRESS,
coun.COUNTRY_NAME,
reg.REGION_NAME,
hop.JOB_TITLE,
hop.MAX_SALARY*/
from (select distinct department_id
from hr.departments dep, hr.locations loc,
hr.countries coUn, hr.regions reg
where dep.location_id = loc.location_id
and coun.country_id = loc.country_id
and coun.region_id = reg.region_id
and (dep.department_id = 10 or region_name = 'Europe'))dd, hr.employees emp, hr.jobs hop
where emp.department_id = dd.department_ID
AND EMP.JOB_ID = HOP.JOB_ID



Incorrect Query

select emp.first_name || ' ' || emp.last_name,
emp.employee_id,
dep.DEPARTMENT_NAME,
dep.DEPARTMENT_ID,
loc.STATE_PROVINCE,
loc.STREET_ADDRESS,
coun.COUNTRY_NAME,
reg.REGION_NAME,
hop.JOB_TITLE,
hop.MAX_SALARY
from hr.employees emp, hr.departments dep,
hr.locations loc, hr.countries coun,
hr.regions reg, hr.jobs hop
where emp.department_id = dep.department_id
and dep.location_id = loc.location_id
and coun.country_id = loc.country_id
and coun.region_id = reg.region_id
and emp.job_id = hop.job_id
and dep.department_id = 10 or region_name = 'Europe'



If we check at above statement as there is no "(" defined in "Or" condition, single statement got considered as below statement

select emp.first_name || ' ' || emp.last_name,
emp.employee_id,
dep.DEPARTMENT_NAME,
dep.DEPARTMENT_ID,
loc.STATE_PROVINCE,
loc.STREET_ADDRESS,
coun.COUNTRY_NAME,
reg.REGION_NAME,
hop.JOB_TITLE,
hop.MAX_SALARY
from hr.employees emp, hr.departments dep,
hr.locations loc, hr.countries coun,
hr.regions reg, hr.jobs hop
where (emp.department_id = dep.department_id
and dep.location_id = loc.location_id
and coun.country_id = loc.country_id
and coun.region_id = reg.region_id
and emp.job_id = hop.job_id
and dep.department_id = 10)
or region_name = 'Europe'

So, if you check explain plan here, for department_id = 10 there are where conditions defined and as because of indexes are exists it went for Nested Loop Join,
whereas for Region_Name = 'Europe' there are no conditions and it went for Merge Join Cartesian. When ever in sql ( except very rare cases) you got to see Merge Join Cartesian, you really need to look at your query, and it will definitely going to end up with incorrect result.

Informatica Interview Questions...

Quite ridiculous, but that is how it is...People are never satisfied..After a switch, people are again thinking of another..

Some how today I felt it again...but question came in my mind, my I ready for it? What questions interviewer will ask and what had been asked last time, which I could not answer? I had gone through the Informatica manuals so every theoretical questions I could answer confidently, but there was a problem with scenario based questions... Somehow today I recollecting those and doing the practice at home. Kudos to Chand and Ameerpeth, because of them I have Informatica 8.6 installed on my PC.

Last Interview which I had given was of CTS, there were some questions which I could not answer properly..below are those

1. How will you transpose rows from one table to 2 target tables wherein 1 target will have all odd rows and another target will have all even rows.


Image might not properly visible, but then also you can make ouut from colors...

As required, taken 2 separate instances of one target, one source, router for dividing the odd and even rows. You require sequence generator in such cases so I added.

Logic -- pretty simple

As you can see, 2 groups odd and even created at group and conditions MOD(NEXTVAL,2) != 0 and MOD(NEXTVAL,2) = 0 added respectively for those groups. Depending upon value of sequence generator (NESTVAL) rows will get routed from router to odd and even targets.

Below is the result




2nd Question -- This was quite tough [at least for me] than the previous one.

Split the records in a table wherein first half part with go to one table and second half part will go to another table.

Solution --> Here I split the task into 2 sessions. First session will count the number of records and another session will use those counts and split the actual records.

First session -->

You need to have mapping variable to store the value. I faced number of difficulties while storing a value to mapping variable.

There are 3 functions by which you can assign a value to mapping variable.

SETVARIABLE --

Sets the current value of a mapping variable to a value you specify. Returns the specified value. The SETVARIABLE function executes only if a row is marked as insert or update. SETVARIABLE ignores all other row types and the current value remains unchanged.

At the end of a successful session, the Integration Service compares the final current value of the variable to the start value of the variable. Based on the aggregate type of the variable, it saves a final current value to the repository. Unless overridden, it uses the saved value as the initial value of the variable for the next session run.

Use the SETVARIABLE function only once for each mapping variable in a pipeline. The Integration Service processes variable functions as it encounters them in the mapping. The order in which the Integration Service encounters variable functions in the mapping may not be the same for every session run. This may cause inconsistent results when you use the same variable function multiple times in a mapping.

SETMAXVARIABLE --

Sets the current value of a mapping variable to the higher of two values: the current value of the variable or the value you specify. Returns the new current value. The function executes only if a row is marked as insert. SETMAXVARIABLE ignores all other row types and the current value remains unchanged.

At the end of a successful session, the Integration Service saves the final current value to the repository. When used with a session that contains multiple partitions, the Integration Service generates different current values for each partition. At the end of the session, it saves the highest current value across all partitions to the repository. Unless overridden, it uses the saved value as the initial value of the variable for the next session run.

When used with a string mapping variable, SETMAXVARIABLE returns the higher string based on the sort order selected for the session.

Use the SETMAXVARIABLE function only once for each mapping variable in a pipeline. The Integration Service processes variable functions as it encounters them in the mapping. The order in which the Integration Service encounters variable functions in the mapping may not be the same for every session run. This can cause inconsistent results when you use the same variable function multiple times in a mapping.

SETMINVARIABLE--

Sets the current value of a mapping variable to the lower of two values: the current value of the variable or the value you specify. Returns the new current value. The SETMINVARIABLE function executes only if a row is marked as insert. SETMINVARIABLE ignores all other row types and the current value remains unchanged.

At the end of a successful session, the Integration Service saves the final current value to the repository. When used with a session that contains multiple partitions, the Integration Service generates different current values for each partition. At the end of the session, it saves the lowest current value across all partitions to the repository. Unless overridden, it uses the saved value as the initial value of the variable for the next session run.

When used with a string mapping variable, SETMINVARIABLE returns the lower string based on the sort order selected for the session.

Use the SETMINVARIABLE function only once for each mapping variable in a pipeline. The Integration Service processes variable functions as it encounters them in the mapping. The order in which the Integration Service encounters variable functions in the mapping may not be the same for every session run. This may cause inconsistent results when you use the same variable function multiple times in a mapping.

Above information have been taken directly from Help files, you can read more if you are interested.

As stated above,
1. your mapping should have one target
2.Mapping should be completed without errors
3. Row should be of Insert Type [ as I have used setMaxVariable]
4. Aggregation type should be Max
[
Here, if at function you are using setMaxVariable and at mapping variable level if you choose min as aggregate type then you will get below error. and mapping will become invalid.

Parsing mapping m_InterviewQ2...
Transformation: exp_Assign_Count Field: V_Assign
<> [SETMAXVARIABLE]: function cannot be used for min aggregate type mapping variable.
... SETMAXVARIABLE(>>>>$$RecordCnt<<<<,O_RecordCount)
...there are parsing errors.
]
5. IsExpVariable should be FALSE [ I do not know what is it]

I struggled a bit while creating this setup and storing a value at mapping variable.

One very IMP thing is, as in an image above, you have to connect a port where you have assign a value to mapping variable to target. Otherwise that function [in this case SetMaxVariable] will not get executed and mapping variable will not get assign any value.

Once you run the workflow, check if its getting completed without any errors or not.

How will you check the mapping variable value? Just right click on your session, where your mapping is defined - click on "view persistent value" and new window will appear with mapping variable and its value.


This only the half task is done; We have retrieved total count of the table, which we have to use in second mapping to divide the rows in 2 parts.

We will follow below procedure to execute this thing
1. Create a new mapping; Declare a $RC mapping variable in it.
2. Create new Seq Transformation and use that in Expression to count current row number.
3. Create new Router and create 2 groups in it a. FirstHalf b. SecondHalf
4. Use logic as $$RC/2 <= NEXTVAL and $$RC/2 > NEXTVAL inside those groups resp.
5. Connect those groups to target instances.

Now the main challenge is to get the RecordCount of previous session to new mapping variable.

Informatica 8.6 has facility wherein you can assign value of Workflow Variable to Mapping Variable and vice versa.

In first session, at components tab there is an option "Post session on successful variable assignment" where we have assigned value of Mapping Variable to Workflow Variable.
In second session, at components tab there is an option "Pre session variable assignment" where we have assigned value of workflow variable to another mappings mapping variable. This is how, we have assigned a value of one mapping variable in mapping 1, to another mapping variable in second mapping.

Java and Oracle Object Basics..

CREATE TYPE MY_TIME AS OBJECT(
MYDATE DATE
)


create type first_names_t is varray(2) of varchar2(100);

create type child_names_t is varray(1) of varchar2(100);

http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14260/adobjmng.htm#i100563

http://download.oracle.com/docs/cd/B10501_01/appdev.920/a96624/05_colls.htm#20425

http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14260/adobjint.htm

Hello World !!

Hola,

Its almost 2+ years.. and I wrote a program of Java

Program which is very first in any standard book, for those who are very new to java..or any programing language in that sense.. its a "Hello World"..


public class MyWorld {
public static void main(String args[]){
System.out.println("Hello World");
}
}

here, are some short cuts those I still remember

1. type "sysout" +and [ctr+space] it will become public static void main
2. Write a class\Package and [Cts+Space] it will display you sub-classes\classes\methods \variables inside that.
3. Ctr+H , which will be useful for searching file or class - it will open a new dialog box.
4. Ctr+L, you will get an option to enter line number on which you wants to move
5. Eclipse will show an error in a program by highlighting the erroneous line and show one red square at left most side. If you double click on the square, it will suggest you the options to correct the code - which will be very helpful to write a code.
.....

In above example we have called the SOP statement inside PSVM method itself. PSVM is mandatory method in any Java program, we will discuss more on this in some days..


public class MyWorld {

void firstMethod(){
System.out.println("Hello World");
}
public static void main(String args[]){
new MyWorld().firstMethod();
}
}

In above program, we have created another method firstMethod() which return nothing - so void . We have called this method from PSVM. As we can not call method - unless it is static - directly, we have to create an object of class. Reference [ a.k.a. Pointer] of that object will point to memory location where this method is reside and will display the result.

Here, Object and Class are 2 different memory locations and because of which you can not call or refer resources belongs to class from object or vice verse.

e.g. void firstMethod() is a method belongs to object and can not be called directly from class. You have to first create an object of that class and after that only you can refer to the method.

---- See below program

public class MyWorld {

static void firstMethod(){
System.out.println("Hello World");
}
public static void main(String args[]){
firstMethod();
MyWorld.firstMethod();

}
}


Here, our method static void firstMethod() is explicitly termed as static, which means this method belongs only to class and not of object. This method can be called from class - no object reference is required. Here, there are 2 calls

firstMethod();
MyWorld.firstMethod();

second one is preceded by Class Name whereas first one is not. when we do not mention the class name, java will consider it as first parent class - so its not necessary to mention it.

We will see in more detail regarding the significance of Public, Static, Void, Main and input String args[] in subsequent sessions.

Good bye till then

Indexes at Oracle and Teradata....

Hola,

Though the syntax of SQL will be same in Oracle and Teradata level, the mail difference is lied at their basic architecture.

Here I am describing the difference of Index structure between Oracle and Tearadata..

Oracle -- As we all know, at oracle Index is created as a separate object [which you can find at user_indexes] which is a B+ tree structure. Indexes are ordered by default in asc order, if you do not specify the order at time of creation of it. As this is a separate object, you can drop and re-create index on particular table, and that will not harm your table data.


--here I am creating a table and defining an index on ID column of it.
create table myTable
(id number,
name varchar2(200)
);


create unique index myIndex on myTable(id)

-- I am re-creating a table using previous table.
create table DummyTable
as
select *
from myTable


-- As index is a separate object altogether,

when you create a table, index will not get create by default and you need to create it explicitly.

Please check below query to verify it..

select a.index_name, b.table_name
from all_indexes a right join all_tables b
on a.table_name = b.table_name
where b.table_name in ('DUMMYTABLE', 'MYTABLE')

If you see here, index did not got created on DummyTable. That proves that, at oracle level index will not get created by default when you re-create a table as its a separate object and not attached with the table.





Same thing applies if you define and index as a primary key - see below

-- I have created a table with primary key defined. i.e. now index will get implicitly created because of primary key constraints.

-- Note : When you define unique or primary key constraint - a unique index will get created automatically on that table for that column.

create table myTable1
(id number primary key,
name varchar2(200)
);


create table DummyTable1
as
select *
from myTable

select a.index_name, b.table_name
from all_indexes a right join all_tables b
on a.table_name = b.table_name
where b.table_name in ('DUMMYTABLE1', 'MYTABLE1')

If you see here also, there is a index [system defined as we mentioned primary key] got created on original table and no index available on dummy table.


Also, as index is a separate object and not attached with the table, you can any time create and drop the index on any table.




Teradata -- Now same thing we will check at tearadata level. In teradata, index[Primary Index] is not created as a separate object. If you did not specify the Primary Key / Primary Index at the time of table creation, teardata will automatically create primary index on first column.

In tearadata, every table rows itself are ordered according to the index column which you specify, because of which there is no need of separate index structure.

If we check below sqls picture will get more clear.

-- we have created table and defined the primary key [primary index]. We can not create primary index separately as we do the same at oracle level.

create table myTable
(id integer not null,
name varchar(200),
primary key (id));

-- we have created dummy table using the pre-existing table on which primary key is defined,
create table DummyTable
as
(select *
from myTable)with data

-- we will check for the indexes on both the tables now

select a.indexname, a.indextype, indexNumber, columnName, b.tableName
from dbc.indicesV a right join dbc.tables b
on a.tableName = b.tableName
where b.tableName in ('DUMMYTABLE', 'MYTABLE')

If you look here, index got created on both the tables. Only thing is, on dummy table index is not unique - i.e. original table is having Unique Primary Index whereas re-created table is having non-unique primary index.

This stats that, when we create table using another table, primary index also gets created automatically - because primary index is not a separate structure at TD level and its a part of Table itself.

Note -- There are indexes like Secondary Index, Join Index [same as MVs in Oralcle] and Hash Index which are separate index structures and are not attached with the table structure.

IMP -- I am not a full fledged TD resource and learning the things on my on and written above block as per my understanding. In case you find any issues in above content please reply back so that even I can understand the things better.

PL-SQL Essentials

Below all programs were taken from "Oracle PL/SQL Programming" book of o'reilly. Book is worth reading and given number of tips for pl-sql programming.

1. Returning Clause and Cursor For Loop
2. Accessing cursor from package
3. Open_Close_Cursor Bulk Collect Type
4. Open_CLose_Cursor Bulk Collect RowType
5. Where current Of _ for update of first_name
6. Object Oriented
7. Returning clause using bulk collect into
8. Returning Clause without Bulk Collect
9. Cursor For Loop
10. Nested Tables
11. Varray
12. Associative Array
13. Collection Methods
14. Execute Immediate -- Using Clause

--==================Returning Clause and Cursor For Loop======================

declare

my_salary hr.employees.salary%TYPE;
my_empName hr.employees.first_name%TYPE;

begin

For rec in (select *
            from hr.employees)
Loop

    update hr.employees
        set salary = 101
        where employee_id = rec.employee_id
        returning salary, first_name
            into my_salary, my_empName;
           
dbms_output.put_line('New Salary For ' || my_empName || '=' || my_salary);              

End Loop;           

end;

--=======================================================================

Accessing cursor from package
/*Package*/

create or replace package empInfo_pkg
is
    cursor emp_cur
        is select employee_id, first_name, email
            from hr.employees
            where MANAGER_ID = 124; 
end empInfo_pkg;

/*Anonymouns Proc*/
declare

    emp_rec empInfo_pkg.emp_cur%rowtype;

begin

    if empInfo_pkg.emp_cur%isopen
    then
        close empInfo_pkg.emp_cur;
    end if;
   
   
    open empInfo_pkg.emp_cur;
   
    loop
   
        fetch empInfo_pkg.emp_cur into emp_rec;       
            exit when empInfo_pkg.emp_cur%notfound or empInfo_pkg.emp_cur%rowcount = 6;
       
        dbms_output.put_line(empInfo_pkg.emp_cur%rowcount || emp_rec.email);
       
    end loop;

end;
--=====================================================================
Open_Close_Cursor Bulk Collect Type
declare

    cursor emp_info is
        select employee_id, first_name, email
            from hr.employees;          

    type empId is table of hr.employees.employee_id%type index by pls_integer;
    type empName is table of hr.employees.first_name%type index by pls_integer;
    type empEmail is table of hr.employees.email%type index by pls_integer;
   
    employeeID empId;
    employeeName empName;
    employeeEmail empEmail;

begin

    open emp_info;
        fetch emp_info bulk collect into employeeID, employeeName, employeeEmail;
    close emp_info;
   
    for l_row in employeeID.first .. employeeID.last
    loop
   
        dbms_output.put_line(employeeID(l_row) ||employeeName(l_row) || employeeName(l_row) );
   
    end loop;
end;
--=======================================================================
Open_CLose_Cursor Bulk Collect RowType

declare

    cursor emp_info is
        select *
            from hr.employees;          

    type empRec is table of hr.employees%rowtype index by pls_integer;
   
    employeeRecord empRec;

begin

    open emp_info;
        fetch emp_info bulk collect into employeeRecord;
    close emp_info;
   
    for l_row in employeeRecord.first .. employeeRecord.last
    loop
   
        dbms_output.put_line(employeeRecord(l_row).employee_id);
   
    end loop;
end;

--========================================================================

Where current Of _ for update of first_name
/*
1. if u remove "for update of first_name" clause program will not compile
 (might be "where current of " requires "for update of"
2. If you remove 2nd "exit", program will give error saying that "ORA-01002:
 fetch out of sequence"
*/

declare

    cursor emp_curr
        is select * from hr.employees
        for update of first_name ;
   
    emp_rec emp_curr%rowtype;

begin

    open emp_curr;
   
    loop
   
        fetch emp_curr into emp_rec;
       
        if emp_curr%notfound       
        then
            exit;
        elsif emp_rec.manager_id = 124
        then
       
            dbms_output.put_line(emp_rec.first_name);
       
            update hr.employees
                set first_name = 'Dada'
                where current of emp_curr;
            commit;          
            exit;
        end if;
   
    end loop;
   
    close emp_curr;

end;

--==============================================================================

Object Oriented

create or replace type catalog_item_t as object
(
    id integer,
    title varchar2(4000),
    not instantiable member function ck_digit_okay return boolean,
    member function print return varchar2
)not instantiable not final;

------------------------------------------------------------------

/*Need to format the code to get it compile- do not know why*/
/* Formatted on 2010/05/16 11:34 (Formatter Plus v4.8.8) */
CREATE OR REPLACE TYPE book_t UNDER catalog_item_t (
   isbn    VARCHAR2 (13),
   pages   INTEGER,
   CONSTRUCTOR FUNCTION book_t (
      ID      IN   INTEGER DEFAULT NULL,
      title   IN   VARCHAR2 DEFAULT NULL,
      isbn    IN   VARCHAR2 DEFAULT NULL,
      pages   IN   INTEGER DEFAULT NULL
   )
      RETURN SELF AS RESULT,
   OVERRIDING MEMBER FUNCTION ck_digit_okay
      RETURN BOOLEAN,
   OVERRIDING MEMBER FUNCTION PRINT
      RETURN VARCHAR2
)
;

------------------------------------------------------------------

/* Formatted on 2010/05/16 13:13 (Formatter Plus v4.8.8) */
CREATE OR REPLACE TYPE BODY book_t
AS
   CONSTRUCTOR FUNCTION book_t (
      ID      IN   INTEGER,
      title   IN   VARCHAR2,
      isbn    IN   VARCHAR2,
      pages   IN   INTEGER
   )
      RETURN SELF AS RESULT
   IS
   BEGIN
      SELF.ID := ID;
      SELF.title := title;
      SELF.isbn := isbn;
      SELF.pages := pages;

      IF isbn IS NULL OR SELF.ck_digit_okay
      THEN
         RETURN;
      ELSE
         raise_application_error (-20000,
                                  'ISBN' || isbn || 'has bad check digit'
                                 );
      END IF;
   END;
   OVERRIDING MEMBER FUNCTION ck_digit_okay
      RETURN BOOLEAN
   IS
      subtotal      PLS_INTEGER   := 0;
      isbn_digits   VARCHAR2 (10);
   BEGIN
      /*Remove dashes and Spaces*/
      isbn_digits := REPLACE (REPLACE (SELF.isbn, '-'), '');

      IF LENGTH (isbn_digits) != 10
      THEN
         RETURN FALSE;
      END IF;

      FOR nth_digit IN 1 .. 9
      LOOP
         subtotal :=
              subtotal
            +   (11 - nth_digit)
              * TO_NUMBER (SUBSTR (isbn_digits, nth_digit), 1);
      END LOOP;

      /*Check digit can be 'X' which has value of 10 */
      IF UPPER (SUBSTR (isbn_digits, 10, 1)) = 'X'
      THEN
         subtotal := subtotal + 10;
      ELSE
         subtotal := subtotal + TO_NUMBER (SUBSTR (isbn_digits, 10, 1));
      END IF;

      RETURN MOD (subtotal, 11) = 0;
   EXCEPTION
      WHEN OTHERS
      THEN
         RETURN FALSE;
   END;
   OVERRIDING MEMBER FUNCTION PRINT
      RETURN VARCHAR2
   IS
   BEGIN
      RETURN    'id='
             || ID
             || '; title='
             || title
             || '; isbn='
             || isbn
             || '; pages='
             || pages;
   END;
END;
/

---------------------------------------------------------------------------

/* Formatted on 2010/05/16 13:21 (Formatter Plus v4.8.8) */
CREATE OR REPLACE TYPE serial_t UNDER catalog_item_t (
   issn             VARCHAR2 (10),
   open_or_closed   VARCHAR2 (1),
   CONSTRUCTOR FUNCTION serial_t (
      ID               IN   INTEGER DEFAULT NULL,
      title            IN   VARCHAR2 DEFAULT NULL,
      issn             IN   VARCHAR2 DEFAULT NULL,
      open_or_closed   IN   VARCHAR2 DEFAULT NULL
   )
      RETURN SELF AS RESULT,
   OVERRIDING MEMBER FUNCTION ck_digit_okay
      RETURN BOOLEAN,
   OVERRIDING MEMBER FUNCTION PRINT
      RETURN VARCHAR2
)
NOT FINAL;
----------------------------OR ------------------------------------------

CREATE OR REPLACE TYPE BODY book_t
AS
   CONSTRUCTOR FUNCTION book_t (
      ID      IN   INTEGER,
      title   IN   VARCHAR2,
      isbn    IN   VARCHAR2,
      pages   IN   INTEGER
   )
      RETURN SELF AS RESULT
   IS
   BEGIN
      SELF.ID := ID;
      SELF.title := title;
      SELF.isbn := isbn;
      SELF.pages := pages;

         RETURN;
    
   END;
   OVERRIDING MEMBER FUNCTION ck_digit_okay
      RETURN BOOLEAN
   IS
      subtotal      PLS_INTEGER   := 0;
      isbn_digits   VARCHAR2 (10);
   BEGIN
      /*Remove dashes and Spaces*/
      isbn_digits := REPLACE (REPLACE (SELF.isbn, '-'), '');

      IF LENGTH (isbn_digits) != 10
      THEN
         RETURN FALSE;
      END IF;

      FOR nth_digit IN 1 .. 9
      LOOP
         subtotal :=
              subtotal
            +   (11 - nth_digit)
              * TO_NUMBER (SUBSTR (isbn_digits, nth_digit), 1);
      END LOOP;

      /*Check digit can be 'X' which has value of 10 */
      IF UPPER (SUBSTR (isbn_digits, 10, 1)) = 'X'
      THEN
         subtotal := subtotal + 10;
      ELSE
         subtotal := subtotal + TO_NUMBER (SUBSTR (isbn_digits, 10, 1));
      END IF;

      RETURN MOD (subtotal, 11) = 0;
   EXCEPTION
      WHEN OTHERS
      THEN
         RETURN FALSE;
   END;
   OVERRIDING MEMBER FUNCTION PRINT
      RETURN VARCHAR2
   IS
   BEGIN
      RETURN    'id='
             || ID
             || '; title='
             || title
             || '; isbn='
             || isbn
             || '; pages='
             || pages;
   END;
END;
/
-----------------------OutPut------------------------------

declare

    superClass  catalog_item_t;
    subClass book_t;
   
begin

    --superClass := catelog_item_t();
    subClass := new book_t(title => '3 mistakes of my life', isbn=>'0-6848-238-02');
    superClass := subClass; --this creates a new object and not just a reference of a object
   
    superClass := new book_t(title => 'My Name Is Khan');
    superClass.title := 'Hello World';
   
    dbms_output.put_line(subClass.print());
    dbms_output.put_line(superClass.print());

end;

id=; title=3 mistakes of my life; isbn=0-6848-238-02; pages=
id=; title=Hello World; isbn=; pages=

--===============================================================================

Returning clause using bulk collect into

declare

    type list_of_names_t is varray(10) of cdw_op_as_resource_dim.resource_code%type;
   
    happyfamily list_of_names_t;
   
    l_row pls_integer;
   
    type list_of_resource is table of cdw_op_as_resource_dim.cdw_op_resource_id%type index by pls_integer;
   
    list_res list_of_resource;
   
begin

    /* You do not need to extend assofiative array
       You do not need to initialize associative array*/
      
    happyfamily := list_of_names_t();
      
    happyfamily.extend(2);
      
    happyfamily(1) := 'pkhvrk';
    happyfamily(2) := 'anbeach';
   
   forall indx in happyfamily.first .. happyfamily.last
    update cdw_op_as_resource_dim
        set current_record_flag = 'Y'
        where resource_code = (happyfamily(indx))
        returning cdw_op_resource_id bulk collect into list_res;

    for l_row in list_res.first .. list_res.last
    loop   
       
         dbms_output.put_line(list_res(l_row));
           
    end loop;
  

    rollback;

end;

--========================================================

Returning Clause without Bulk Collect

declare

    type employee_rec is table of cdw_op_as_resource_dim%rowtype;
   
    l_employees employee_rec;
   
    updated_record cdw_op_as_resource_dim.cdw_op_resource_id%type;

begin

    select * bulk collect into l_employees
    from cdw_op_as_resource_dim
    where rownum < 20
    and current_record_flag = 'Y';
   
    for l_row in l_employees.first .. l_employees.last
    loop   
       
         dbms_output.put_line(l_employees(l_row).resource_code);
        
         update cdw_op_as_resource_dim
         set current_record_flag = 'Y'
         where resource_code =  l_employees(l_row).resource_code
         returning cdw_op_resource_id into updated_record;
           
         dbms_output.put_line(updated_record);
        
    end loop;
   
end;

--===============================================================

--Bulk Collect INto

declare

    type employee_ids is table of cdw_op_as_resource_dim.resource_code%type;
   
    l_employees employee_ids;

begin

    select resource_code bulk collect into l_employees
    from cdw_op_as_resource_dim
    where rownum < 15;
   
    for l_row in l_employees.first .. l_employees.last
    loop   
       
         dbms_output.put_line(l_employees(l_row));
           
    end loop;
   
end;

--===============================================================

Cursor For Loop

begin

    for emp_rec in
        (
            select cdw_op_resource_id, resource_code
                from cdw_op_as_resource_dim
                where resource_code in ('anbeach','venkk','pkhvrk')
            )
    loop
   
        if emp_rec.resource_code in ('venkk','pkhvrk')
        then
       
            dbms_output.put_line(emp_rec.resource_code || emp_rec.cdw_op_resource_id);
           
        end if;   
   
    end loop;

end;

/*

pkhvrk16093
pkhvrk838779
pkhvrk1555737
venkk20419
venkk19817
venkk38099
venkk271747
venkk1555739

*/

--==================================================

Nested Tables - Sir correct if anything wrong*/

create type list_of_names_t is table of varchar2(100);
declare
/*
        Varray and Nested Tables you need to always initialized
        which is not a case in associative array   
    */
    happyfamily list_of_names_t := list_of_names_t();
    children list_of_names_t := list_of_names_t();
    parents list_of_names_t := list_of_names_t ();
begin
    /*
        Varray and Nested tables you always need to extend before inserting a record
        which is not a case in associative array
    */
    happyfamily.extend(4);
    happyfamily(1) := 'magogate';
    happyfamily(2) := 'sgakhar';
    happyfamily(3) := 'staksale';
    happyfamily(4) := 'Ashish Sir';

    children.EXTEND;
    children(1) := 'magogate';
    children.EXTEND;
    children(2) := 'sgakhar';
    parents := happyfamily multiset except children;
    /*
        You can use first and last here as collection is densily filled
        which is not a case in associative array.
        Nested and Varray are always densily filled
    */
    for l_row in parents.FIRST .. parents.LAST
    loop
 dbms_output.put_line(parents(l_row));   
    end loop;
end;
 

 --========================================================================

Varray

create type first_names_t is varray(2) of varchar2(100);

create type child_names_t is varray(1) of varchar2(100);

create table family(
    surname varchar2(1000),
    parent_names first_names_t,
    children_names child_names_t   
);

declare

    parents first_names_t := first_names_t();
    children child_names_t := child_names_t();

begin

    parents.extend(2);
   
        parents(1) := 'staksale';
        parents(2) := 'Ashish Sir';
       
        children.extend;
        children(1) := 'magogate';
       
        insert into family
        (surname, parent_names, children_names)
        values('cisco', parents, children);
       
        commit;

end;

--========================================================

Associative Array

declare

    type list_of_names_t is table of cdw_op_as_resource_dim.resource_code%type index by pls_integer;
   
    happyfamily list_of_names_t;
   
    l_row pls_integer;
   
begin

    /* You do not need to extend assofiative array
       You do not need to initialize associative array*/
    happyfamily(200001001) := 'magogate';
    happyfamily(-157070) := 'staksale';
    happyfamily(-9090) := 'sgakhar';
    happyfamily(-88) := 'AshishSir';
   
    l_row := happyfamily.first;
   
    while(l_row is not null)
    loop
   
        dbms_output.put_line(happyfamily(l_row));
        l_row := happyfamily.next(l_row);
       
    end loop;

end;

/*
Out put started from lowest value first and greatest value at last

staksale
sgakhar
khade
magogate

*
--==================================================================

Collection Methods

declare

    type list_of_names_t is table of cdw_op_as_resource_dim.resource_code%type index by pls_integer;
   
    happyfamily list_of_names_t;
   
    l_row pls_integer;
   
begin

    /* You do not need to extend assofiative array
       You do not need to initialize associative array*/
    happyfamily(200001001) := 'magogate';
    happyfamily(-157070) := 'staksale';
    happyfamily(-9090) := 'sgakhar';
    happyfamily(-88) := 'AshishSir';
   
    l_row := happyfamily.first;
   
    while(l_row is not null)
    loop
   
        dbms_output.put_line('Display -->'||happyfamily(l_row));
        dbms_output.put_line('Prior -->'||happyfamily.prior(l_row));
        dbms_output.put_line('Next -->'||happyfamily.next(l_row));
       
        l_row := happyfamily.next(l_row);
       
    end loop;
   
    dbms_output.put_line('Count -->'||happyfamily.count);   
    dbms_output.put_line('First --> '||happyfamily.first);   
    dbms_output.put_line('Last --> '||happyfamily.last);

    happyfamily.delete(-88);
   
    if not happyfamily.exists(-88)
    then
        dbms_output.put_line('object at -88 is not present');
    end if;
   
   if happyfamily.exists(-88) = false
    then
        dbms_output.put_line('object at -88 is not present');
    end if;
   
    dbms_output.put_line('Count -->'||happyfamily.count);
   
    happyfamily.delete();
   
    dbms_output.put_line('After deletion the Count Is-->'||happyfamily.count);

end;

/*
Display -->staksale
Prior -->
Next -->-9090
Display -->sgakhar
Prior -->-157070
Next -->-88
Display -->AshishSir
Prior -->-9090
Next -->200001001
Display -->magogate
Prior -->-88
Next -->
Count -->4
First --> -157070
Last --> 200001001
object at -88 is not present
object at -88 is not present
Count -->3
After deletion the Count Is-->0

*/

--============================================================

 Execute Immediate -- Using Clause

 

DECLARE
 
REC_COUNT NUMBER;
v_total number;
 
cdate date:='20-May-2010';
 
sql_stmt varchar2(200);
 
begin
 
    selectcount(*)into v_total from all_tables;
   
    dbms_output.put_line('Totatl number of tables at CDW_BOASA ' || v_total );
 
   
    for OBJ_rec in
        (
            SELECT *
            FROM ALL_TABLES  
            where table_name like'%DW%'          
            )
    loop
        begin               
                sql_stmt :='SELECT count(*) FROM '|| OBJ_rec.owner || '.' || OBJ_rec.Table_Name || ' WHERE trunc(create_date) <= :cdate';
               
        EXECUTEIMMEDIATE sql_stmt INTO REC_COUNT USING cdate;
           
                dbms_output.put_line(OBJ_rec.table_NAME || ' = ' || REC_COUNT );
            exception
                whenothers
                then
                    dbms_output.put_line(sql_stmt);
        end;
           
    endloop;
end;

 
 

Children Pages

• Analytical Functions
• TeraData

Add Child Page ,  LastUpdatedDate:6/20/10 4:07 AM,   CreatedBy: Mandar Gogate 0 Attachments

268 Views

Rate this 1 stars out of 5. Rate this 2 stars out of 5. Rate this 3 stars out of 5. Rate this 4 stars out of 5. Rate this 5 stars out of 5.