How to find out the referenced table and column name if the foreign key constraint name is given

The following query will give the desired result:

select
ac.table_name child_tab,
acc1.column_name child_col,
ac.constraint_name foreign_key,
acc.constraint_name primary_key,
acc.table_name parent_tab,
acc.column_name parent_col
from
all_constraints ac,
all_cons_columns acc,
all_cons_columns acc1
where
ac.owner=acc.owner
and acc.constraint_name=ac.r_constraint_name
and ac.constraint_name=acc1.constraint_name
and ac.constraint_name= :foreign_key_constraint_name

Constraints

In Oracle, constraints are a facility to enforce rules to make sure that only allowable data values are stored in the database(i.e to make sure the data integrity in database). All constraints have a name. The developer who defines the table or adds a constraint can name it at that time. However, if the name is not supplied, Oracle will assign a system generated name that will uniquely identify the constraint. System generated names are pre-fixed with SYS_C (for "system constraint") followed by a 7 digit integer.

Types of Constraints:

1. NOT NULL: If as per business logic, a column or a set of columns in a table can not allow NULL values, then NOT NULL constraint can be used to enforce this rule.

e.g

alter table SALES modify (cust_id NOT NULL);

2. UNIQUE: If as per business logic, a colum or a set of columns in a table need to store unique values, then, UNIQUE constraint can be used to enforce this rule.

e.g

create table test (col1 number UNIQUE);

UNIQUE constraints allow NULL values to be stored.

3. PRIMARY KEY : Primary Key constraint is a combination of NOT NULL and UNIQUE constraints. The column or the set of columns on which Primary Key is defined, will allow only unique and not null values. There can only be 1 (and only 1) primary key in an Oracle table.

e.g

create table test (col1 number PRIMARY KEY);

4. FOREIGN KEY:  It is frequenly required that data in one table should be validated by comparing it to data in other table.(e.g if you add a new order in your ORDERS table, you must cross check that a valid product corresponding to this order is present in your PRODUCTS table). To achieve this kind of data integrity, foeign key constrained is used. This type of validation is also known as referential integrity. A foreign key constraint always makes refrence to a Primary key or a unique constraint of other table. The table that has foreign key defined is called referencing table. The table that has Primary key or Unique constraint defined is called referenced table.

e.g

create table orders
(
  order_no number primary key,
  customer_name varchar2(10),
  constraint cons_prod_fk foreign key(prod_no) references product(prod_no)
);

> If you define the foreign key constraint with 'ON DELETE CASCADE' option, then if any rows are deleted from the referenced table, then the corresponding rows will also be deleted from the referencing table.

> NULL values are allowed in Foreign Key columns.

5. CHECK:  Check constraints are used to enforce one or more conditions to be checked for the data row.

e.g

alter table customers add constraint customer_credit_limit CHECK (credit_limit <= 1000)

Some additional information regarding Constraints:

• Constraint names can be found in ALL_CONSTRAINTS table. The column names on which constraints are defined can be found in ALL_CONS_COLUMNS.

• Constraints can, at any time, be either enabled or disabled. When you create, disable or enable a constraint, you may speify some other information regarding how the constraint behaves. An ebabled constraint can have two options VALIDATE and NOVALIDATE. VALIDATE will validate the existing data in the table while NOVALIDATE will not validate the existing data afyter the constraint is enabled.

• When you create or enable a Primary key or Unique constraint, Oracle will create a unique index on the columns of that constraint. Foreign key constraints do not enforce an automatic creation of index.However it is worthwhile to build an index on the columns of each foreign key constraint. Without an index on the corresponding columns in the child table, Oracle is forced to take out a table lock on the child while it performs the DELETE on the parent. If an index is existing, Orcale uses it to identify and lock just the necessary rows in the child while the parent row is deleted.

OBIEE Training for Beginners

Here is a PPT Presentation to introduce Beginners to OBIEE

OBIEE Session

Simple things about Lookup Transformation

Q Define lookup transformation?

A lookup transformation is used to lookup data in a ‘data-pool’. This data-pool may be a flat-file, relational table, view or a synonym. You can also create a lookup definition from a source qualifier. The Integration Service queries the lookup source based on the lookup ports in the transformation and a lookup condition. The Lookup transformation returns the result of the lookup to the target or another transformation.

Lookups are generally used to get a related value, to perform a calculation using the derived related value or to update a slowly changing dimension. 

When you configure a flat file Lookup transformation for sorted input, the condition columns must be grouped. If the condition columns are not grouped, the Integration Service cannot cache the lookup and fails the session. For optimal caching performance, sort the condition columns. The Integration Service always caches flat file and pipeline lookups. If you configure a Lookup transformation to use a dynamic cache, you can use only the equality operator (=) in the lookup condition.

Q What are the differences between connected and unconnected lookups?

1. Connected Lokkup uses a dynamic or static cache while unconnected lookup uses only static cache.

2. Connected lookup can return multiple columns from the same row or insert into the dynamic lookup cache while unconnected lookup returns one column from each row.

3. Connected lookup supports user-defined default values while unconnected lookup does not supports user-defined default values.

Q How can you return multiple ports from an unconnected lookup transformation?

Unconnected lookup transformation returns only 1 port. To return multiple ports, concatenate all those ports in the overwritten lookup query and return the concatenated port. Now separate out those columns in an expression transformation.

Q How can you optimize a lookup transformation?

1. If you have privileges to modify the database containing a lookup table, you can improve lookup initialization time by adding an index to the lookup table.

2. You can improve performance by indexing the columns in the lookup ORDER BY.

3. By default, the Integration Service generates an ORDER BY clause for a cached lookup. The ORDER BY clause contains all lookup ports. To increase performance, you can suppress the default ORDER BY clause and enter an override ORDER BY with fewer columns. Place two dashes ‘--’ as a comment notation after the ORDER BY clause to suppress the ORDER BY clause that the Integration Service generates.

4. If you include more than one lookup condition, place the conditions in the following order to optimize lookup performance:

- Equal to (=)

- Less than (<), greater than (>), less than or equal to (<=), greater than or equal to (>=)

- Not equal to (!=)

5. Improve session performance by caching small lookup tables.

6. If the lookup table is on the same database as the source table in the mapping and caching is not feasible, join the tables in the source database rather than using a Lookup transformation.

Oracle BULK Loader

SQL *Loader (sqlldr) is a bulk loader utility to load data from external files into Oracle database. To use this utility, a control file is required which specifies how data should be loaded into the database and a data file is required which specifies what data should be loaded.  A sample control file is as follows:

LOAD DATA

INFILE "datafile"

APPEND INTO TABLE "tablename"

FIELDS TERMINATED BY "separater"

("list of all attribute names to be loaded")

 

A sample data file can be of following form:

 

1,'Gaurav'

2, 'ABC'

3,'PQR'

Informatica Scenario - 5

Previous Scenarios

Scenario: A source table contains emp_name and salary columns. Develop an Informatica mapping to load all  records with 5th highest salary into the target table.

Solution:

The mapping will contain following transformations after the Source Qualifier Transformation:

1. Sorter : It will contain 2 ports - emp_name and salary. The property 'Direction' will be selected as 'Descending' on key 'Salary'

2. Expression transformation: It will 6 ports as follows -
     a> emp_name : It will be an I/O port directly connected from previous sorter transformation
     b> salary_prev : It will be a variable type port. Give any vriable name e.g val in its Expression column
     c> salary : It will be an I/O port directly connected from previous transformation
     d> val : It will be a variable port. The expression column of this port will contain 'salary'
     e> rank: It will be a variable type port. The expression column will contain decode          
                   (salary,salary_prev,rank,rank+1)
     f> rank_o : It will be an output port containg the value of 'rank'.

3. Filter Transformation : It will have 2 I/O ports emp_name and salary with a filter condition rank_o = 5

The ports emp_name and salary from Filter Transformation will be connected to target

Divisions

A table contains certain columns. One of the columns is Division. The user enters a division name through a prompt. Write a query to satisfy below scenario:

If the value entered in prompt is 'a' or 'b', then all records should be displayed else, the records pertaining to that particular division should be displayed.

Solution:

The query would be written as follows:

select * from my_table
where
division = &division
OR
'^' = decode(&division,'a','^','b','^',&division)

Here any special character (like '^' in above query) can be choosed to give all results in case division value entered from prompt is either 'a' or 'b'.