Thursday, December 15, 2016

Types of Transformations in informatica 9.5V:

1.Source Qualifier
2.Update Strategy
3.Expression
4.Stored Procedure
5.Sequence Generator
6.Aggregator
7.Filter
8.Lookup
9.Joiner
10.Normalizer
11.Router
12.Rank
13.Application Source Qualifier
14.XML Source Qualifier
15. MQ Series Source Qualifier
16.Sorter
17.Application Multi-group Source Qualifier
18.Transaction Control
19. Custom Transformation
20.Flexible Target Key
21.HTTP
22. Web Service Consumer
23.SQL
24.Union
25.Java
26.SalesForce Lookup
27.SalesForce PickList
28.SalesForce Merge
29. XML Parser
30. XML Generator
31. Identity Resolution
32. Unstructured Data
33. Data Masking


Monday, April 13, 2015

Can one COMMIT/ ROLLBACK from within a trigger?

A commit inside a trigger would defeat the basic definition of an atomic transaction (see ACID). Trigger logic is by definition an extension of the original DML operation. Changes made within triggers should thus be committed or rolled back as part of the transaction in which they execute. For this reason, triggers are NOT allowed to execute COMMIT or ROLLBACK statements (with the exception of autonomous triggers). Here is an example of what will happen when they do:

SQL> CREATE TABLE tab1 (col1 NUMBER);
Table created.

SQL> CREATE TABLE log (timestamp DATE, operation VARCHAR2(2000));
Table created.

SQL> CREATE TRIGGER tab1_trig
  2     AFTER insert ON tab1
  3  BEGIN
  4     INSERT INTO log VALUES (SYSDATE, 'Insert on TAB1');
  5     COMMIT;
  6  END;
  7  /
Trigger created.

SQL> INSERT INTO tab1 VALUES (1);
INSERT INTO tab1 VALUES (1)
            *
ERROR at line 1:
ORA-04092: cannot COMMIT in a trigger
ORA-06512: at "SCOTT.TAB1_TRIG", line 3
ORA-04088: error during execution of trigger 'SCOTT.TAB1_TRIG'

Autonomous transactions:

As workaround, one can use autonomous transactions. Autonomous transactions execute separate from the current transaction.

Unlike regular triggers, autonomous triggers can contain COMMIT and ROLLBACK statements. Example:

SQL> CREATE OR REPLACE TRIGGER tab1_trig
  2    AFTER insert ON tab1
  3  DECLARE
  4    PRAGMA AUTONOMOUS_TRANSACTION;
  5  BEGIN
  6    INSERT INTO log VALUES (SYSDATE, 'Insert on TAB1');
  7    COMMIT; -- only allowed in autonomous triggers
  8  END;
  9  /
Trigger created.

SQL> INSERT INTO tab1 VALUES (1);
1 row created.

Note that with the above example will insert and commit log entries - even if the main transaction is rolled-back!

Remember that an "autonomous_transaction" procedure/function/trigger is a whole transaction in itself and so it must end with a commit or a rollback statement. 

Saturday, November 8, 2014

How to call one function in another function ?

SQL> create or replace function fun_two( num in number)
  2  return number
  3  as
  4  val number;
  5  begin
  6  val:=num+500;
  7  return val;
  8  end;
  9  /

Function created.

SQL> create or replace function fun_one( num in number)
  2  return number
  3  as
  4  val number;
  5  begin
  6  val:=fun_two(num);
  7  return val;
  8  end;
  9  /

Function created.

SQL> select fun_one(10) from dual;

FUN_ONE(10)
-----------
        510

SQL> select fun_one(30) from dual;

FUN_ONE(30)
-----------
        530

***********************************



SQL> declare
  2       function bob(x number) return number is
  3       begin
  4         return x*x;
  5       end;
  6       function fred(x number) return number is
  7       begin
  8         return x+bob(x);
  9       end;
 10     begin
 11      dbms_output.put_line('Value: '||to_char(fred(5),'fm9999'));
 12    end;
 13  /
Value: 30

PL/SQL procedure successfully completed.

SQL>

Saturday, October 11, 2014

Static IP in REDHAT Linux accessing by Oracle Virtual Box

I installed REDHAT linux  on oracle virtual box and now because i had to take a trip, i can only ssh into my server and i have to change my ip adress, Originally when i installed the os, i set it to DHCP, but now i want to change it to a static ip. so if you are in the same situation as i am, maybe you can learn from this on how you can change your ip address remotely..

these are the steps i took to make it happen

1. login as root

2. get your 
current IP address with this command:
ifconfig

The OUTPUT will look someting like this:
[root@host ~]# ifconfig
eth0 Link encap:Ethernet HWaddr 00:D0:BC:08:09:BC
inet addr:70.238.17.69 Bcast:255.255.255.255 Mask:255.255.255.248
inet6 addr: fe80::2d0:b7ff:fe08:9bb/64 Scope:Link
UP BROADCAST RUNNING MULTICAST MTU:1500 Metric:1
RX packets:7174 
errors:0 dropped:0 overruns:0 frame:0
TX packets:2305 errors:0 dropped:0 overruns:0 carrier:0
collisions:0 txqueuelen:1000
RX bytes:5339910 (5.0 MiB) TX bytes:170109 (166.1 KiB)

lo Link encap:Local Loopback
inet addr:127.0.0.1 Mask:255.0.0.0
inet6 addr: ::1/128 Scope:Host
UP LOOPBACK RUNNING MTU:16436 Metric:1
RX packets:34 errors:0 dropped:0 overruns:0 frame:0
TX packets:34 errors:0 dropped:0 overruns:0 carrier:0
collisions:0 txqueuelen:0
RX bytes:4275 (4.1 KiB) TX bytes:4275 (4.1 KiB)


OR you can also get the ip from the 
network configuration file wit this command:
COMMAND to show configuration:
cat /etc/sysconfig/network-scripts/ifcfg-eth0


THe OUTPUT will look like this (i have it set to DHCP)
DEVICE=eth0
BOOTPROTO=dhcp
HWADDR=00:D0:B7:08:09:BB
ONBOOT=yes

Change to static IP as follows :
Just comment the existing ip configuration.



OUTPUT for static IP (example)
#
# File: ifcfg-eth0
#
DEVICE=eth0
IPADDR=192.168.1.100
NETMASK=255.255.255.0
BOOTPROTO=
static
ONBOOT=yes
#
# The following settings are optional
#
BROADCAST=192.168.1.255
NETWORK=192.168.1.0


4. Disable Firewall on RHEL / CentOS / RedHat Linux

Next enter the following three commands to disable firewall.
# service iptables save
# service iptables stop
# chkconfig iptables off

If you are using IPv6 firewall, enter:
# service ip6tables save
# service ip6tables stop
# chkconfig ip6tables off




5.  Now save your changes and reboot your server.


***********************

6. The network type should be bridged on Virtual Box. 


































Cheers
Rajani

Thursday, October 2, 2014

Database Normalization

In relational database design, we not only want to create a structure that stores all of the data, but we also want to do it in a way that minimize potential errors when we work with the data. The default language for accessing data from a relational database is SQL. In particular, SQL can be used to manipulate data in the following ways: insert new data, delete unwanted data, and update existing data. Similarly, in an un-normalized design, there are 3 problems that can occur when we work with the data:

INSERT ANOMALY: This refers to the situation when it is impossible to insert certain types of data into the database.

DELETE ANOMALY: The deletion of data leads to unintended loss of additional data, data that we had wished to preserve.

UPDATE ANOMALY: This refers to the situation where updating the value of a column leads to database inconsistencies (i.e., different rows on the table have different values).

To address the 3 problems above, we go through the process of normalization. When we go through the normalization process, we increase the number of tables in the database, while decreasing the amount of data stored in each table. There are several different levels of database normalization:

1st Normal Form (1NF)
2nd Normal Form (2NF)
3rd Normal Form (3NF)
Bryce-Codd Normal Form (BCNF)
4th Normal Form (4NF)
5th Normal Form (5NF)
The opposite of normalization is denormalization, where we want to combine multiple tables together into a larger table. Denormalization is most frequently associated with designing the fact table in a data warehouse.


1st Normal Form Definition

A database is in first normal form if it satisfies the following conditions:


  • Contains only atomic values
  • There are no repeating groups

An atomic value is a value that cannot be divided. For example, in the table shown below, the values in the [Color] column in the first row can be divided into "red" and "green", hence [TABLE_PRODUCT] is not in 1NF.

A repeating group means that a table contains two or more columns that are closely related. For example, a table that records data on a book and its author(s) with the following columns: [Book ID], [Author 1], [Author 2], [Author 3] is not in 1NF because [Author 1], [Author 2], and [Author 3] are all repeating the same attribute.

1st Normal Form Example

How do we bring an unnormalized table into first normal form? Consider the following example:












This table is not in first normal form because the [Color] column can contain multiple values. For example, the first row includes values "red" and "green."

To bring this table to first normal form, we split the table into two tables and now we have the resulting tables:












Now first normal form is satisfied, as the columns on each table all hold just one value.


2nd Normal Form Definition

A database is in second normal form if it satisfies the following conditions:

  • It is in first normal form
  • All non-key attributes are fully functional dependent on the primary key
In a table, if attribute B is functionally dependent on A, but is not functionally dependent on a proper subset of A, then B is considered fully functional dependent on A. Hence, in a 2NF table, all non-key attributes cannot be dependent on a subset of the primary key. Note that if the primary key is not a composite key, all non-key attributes are always fully functional dependent on the primary key. A table that is in 1st normal form and contains only a single key as the primary key is automatically in 2nd normal form.

2nd Normal Form Example

Consider the following example:












This table has a composite primary key [Customer ID, Store ID]. The non-key attribute is [Purchase Location]. In this case, [Purchase Location] only depends on [Store ID], which is only part of the primary key. Therefore, this table does not satisfy second normal form.

To bring this table to second normal form, we break the table into two tables, and now we have the following:










What we have done is to remove the partial functional dependency that we initially had. Now, in the table [TABLE_STORE], the column [Purchase Location] is fully dependent on the primary key of that table, which is [Store ID].

3rd Normal Form Definition

A database is in third normal form if it satisfies the following conditions:

  • It is in second normal form
  • There is no transitive functional dependency
By transitive functional dependency, we mean we have the following relationships in the table: A is functionally dependent on B, and B is functionally dependent on C. In this case, C is transitively dependent on A via B.

3rd Normal Form Example

Consider the following example:












In the table able, [Book ID] determines [Genre ID], and [Genre ID] determines [Genre Type]. Therefore, [Book ID] determines [Genre Type] via [Genre ID] and we have transitive functional dependency, and this structure does not satisfy third normal form.

To bring this table to third normal form, we split the table into two as follows:









Now all non-key attributes are fully functional dependent only on the primary key. In [TABLE_BOOK], both [Genre ID] and [Price] are only dependent on [Book ID]. In [TABLE_GENRE], [Genre Type] is only dependent on [Genre ID].


Saturday, September 13, 2014

Best Quotes by great persons I have ever came across !!!!

A burning desire is the starting point of all accomplishments. Just like a small fire cannot give much heat, a weak desire cannot produce great results.

To Live a creative life we must lose the fear of being wrong.

Dream needs effortless sleep but to achieve it needs sleepless efforts.

Always consider elders' advice not because they are always correct, but they have more experince of being wrong.

The dream is not that comes in sleep , dream is which does not let you sleep.

 The Palm lines will change according to your work.

Be your own light.

Those who shed blood during practice, they must not shed blood during war.

If one does not know to which port one is sailing, no wind is favourable.

Success doesn't depend on making important decisions quickly, but it depends on taking quick action on important decisions.

Every Morning leave home  with some expectation and come back with some experience. The biggest Alarm Clock is your Heart. Listen to it.

Dr APJ Abdul Kalam:

You cannot change your future.But you can change your habits.and surely your habits will change your future.

If you salute your duty, you need not salute anybody. But if you pollute your duty, You have to salute everybody.

Confidence and hard work is the best medicine to kill the disease called failure. It  will make you a successful person. 

It Is Very Easy To Defeat Someone, But It Is Very Hard To Win Someone. 


DIFFICULTIES IN YOUR LIFE DO NOT COME TO DESTROY YOU, BUT TO HELP YOU REALISE YOUR HIDDEN POTENTIAL AND POWER, LET DIFFICULTIES KNOW THAT YOU TOO ARE DIFFICULT.

To succeed in your mission, you must have single-minded devotion to your goal.

* If you plant honesty, you will reap trust.
* If you plant goodness, you will reap friends.
* If you plant humility, you will reap greatness.
* If you plant perseverance, you will reap contentment.
* If you plant consideration, you will reap perspective.
* If you plant hard work, you will reap success.
* If you plant forgiveness, you will reap reconciliation.
* If you plant faith in God , you will reap a harvest.

So, be careful what you plant now; it will determine what you will reap later..

“Whatever You Give To Life, Life Gives You Back”

Give Respect to the Study, Study will definitely respect you back.


When adversity knocks on your door, how do you respond ? Best inspirational Story :


A young woman went to her mother and told her about her life and how things were so hard for her. She did not know how she was going to make it and wanted to give up. She was tired of fighting and struggling. It seemed as when one problem was solved, a new one arose.

Her mother took her to the kitchen. She filled three pots with water and placed each on a high fire. Soon the pots came to boil. In the first she placed carrots, in the second she placed eggs, and in the last she placed ground coffee beans.

She let them sit and boil; without saying a word. In about twenty minutes she turned off the burners. She fished the carrots out and placed them in a bowl. She pulled the eggs out and placed them in a bowl. Then she ladled the coffee out and placed it in a bowl. Turning to her daughter, she asked, “Tell me what you see.”
“Carrots, eggs, and coffee,” she replied.

Her mother brought her closer and asked her to feel the carrots. She did and noted that they were soft. The mother then asked the daughter to take an egg and break it. After pulling off the shell, she observed the hard boiled egg. Finally, the mother asked the daughter to sip the coffee. The daughter smiled as she tasted its rich aroma. The daughter then asked, “What does it mean, mother?”

Her mother explained that each of these objects had faced the same adversity: boiling water. Each reacted differently. The carrot went in strong, hard, and unrelenting. However, after being subjected to the boiling water, it softened and became weak.

The egg had been fragile. Its thin outer shell had protected its liquid interior, but after sitting through the boiling water, its inside became hardened.

However, the ground coffee beans were unique. After they were in the boiling water, they had changed the water. “Which are you?” she asked her daughter.

When adversity knocks on your door, how do you respond? Are you a carrot, an egg, or a coffee bean?
Think of this: Which am I?

Am I the carrot that seems strong, but with pain and adversity do I wilt and become soft and lose my strength?

Am I the egg that starts with a malleable heart, but changes with the heat? Did I have a fluid spirit, but after a death, a breakup, a financial hardship or some other trial, have I become hardened and stiff? Does my shell look the same, but on the inside am I bitter and tough with a stiff spirit and hardened heart?

Or am I like the coffee bean? The bean actually changes the hot water, the very circumstance that brings the pain. When the water gets hot, it releases the fragrance and flavour.

If you are like the bean, when things are at their worst, you get better and change the situation around you.

When the hour is the darkest and trials are their greatest do you elevate yourself to another level?

How do you handle adversity?

Are you a carrot, an egg or a coffee bean?




How can I execute a function that is defined in a package using Oracle/PLSQL?

To execute a function that is defined in a package, you'll have to prefix the function name with the package name.

package_name.function_name (parameter1, parameter2, ... parameter_n)
You can execute a function a few different ways.

SOLUTION #1

First, we'll take a look at how to execute a function using a test block. Below we've declared a variable called result that is a number. We've passed in a value of 15000 into the function and the result of the function will be returned to the variable called result.

declare
   result number;
begin
   -- Call the function
   result := package_name.function_name (15000);
end;