Executing informatica query from command prompt

In every difficulty there is an opportunity, yes its very true. Today after manager asked me to automate something, there was a big question mark on my head that how will I do it? will it really work? and after coming at home just checked informatica help and they have give it very straight forward..

How will you execute the informatica query from command prompt?

My task was to find out all the checked out object in a repository, I am doing that by creating a query at Repository Manager. But main pain area was to automate this query. To automate this, only way is to run a shell or batch script, but how will I call that.. so below is the way for it.

1. First create a query from Repository Manager; that is a simple task.

2. Execute the query from command prompt

You can do this using pmrep command of informatica. pmrep.exe file will be located at C:\Informatica\PowerCenter8.6.0\server\bin path [if you have installed informatica at windows machine]. From command prompt just go till this directory.

Before actually executing your query, you have to first connect to the repository. If you direct try to execute the query, you will get an error message saying you first have to connect to repository.

How will you connect to Repository--? so that will be via Connect command as below

C:\Informatica\PowerCenter8.6.0\server\bin>pmrep connect -r EDWDEV -d Domain_inf
ra-34af3549f [-h infra-34af3549f -o 6001] -n Administrator -s native -x magogate

Here host and ports are optional, if you are specifying Domain then do not specify host and port. Using this command you will be able to connect to repository.  You will only require to specify Repository, Domain, User, Security Domain, and Password.

Once you get connected to repository last task is to run a query

C:\Informatica\PowerCenter8.6.0\server\bin>pmrep executequery -q CheckedOutObjec
ts -u CheckedOutObjectsList.txt

Here, we have specified -q <Query Name> and -u <OutputFileName>

If you do not specify outputfilename, results will get displayed at command prompt itself..

Please find below log which I have tried at my system

You can also view the video for it at
https://www.youtube.com/watch?v=p-hzjTuqPMI
=============================================================

C:\Documents and Settings\Administrator>cd C:\Informatica\PowerCenter8.6.0\serve
r\bin
C:\Informatica\PowerCenter8.6.0\server\bin>pmrep executequery -q CheckedInObject
 -u CheckedInObject.txt
Informatica(r) PMREP, version [8.6.0], build [178.0613], Windows 32-bit
Copyright (c) Informatica Corporation 1994 - 2008
All Rights Reserved.
This Software may be protected by U.S. Patent Numbers 6,208,990; 6,044,374; 6,01
4,670; 6,032,158; 5,794,246; 6,339,775; 6,850,947; 6,895,471; 7,254,590 and othe
r U.S. Patents Pending.
Invoked at Thu Jan 08 00:21:32 2009
Failed to read repository connection information. Please first connect to the re
pository using the connect command.
Failed to execute executequery.
Completed at Thu Jan 08 00:21:32 2009

===================
C:\Informatica\PowerCenter8.6.0\server\bin>pmrep connect -r EDWDEV
Informatica(r) PMREP, version [8.6.0], build [178.0613], Windows 32-bit
Copyright (c) Informatica Corporation 1994 - 2008
All Rights Reserved.
This Software may be protected by U.S. Patent Numbers 6,208,990; 6,044,374; 6,01
4,670; 6,032,158; 5,794,246; 6,339,775; 6,850,947; 6,895,471; 7,254,590 and othe
r U.S. Patents Pending.
Invoked at Thu Jan 08 00:24:09 2009
 [01/08/2009 00:24:09-[REP_12164] Domain-related error: [[PCSF_46006] Domain nam
e is not specified. [FAULT_PCSFUSEREXCEPTION]].]
 [Failed to connect to repository service [EDWDEV].]
An error occurred while accessing the repository[Failed to connect to repository
 service [EDWDEV].]
 [01/08/2009 00:24:09-[REP_55102] Failed to connect to repository service [EDWDE
V].]
Repository connection failed.
Failed to execute connect.
Completed at Thu Jan 08 00:24:09 2009
C:\Informatica\PowerCenter8.6.0\server\bin>pmrep connect -r EDWDEV -d Domain_inf
ra-34af3549f -h infra-34af3549f -o 6001 -n Administrator -s native -x magogate
Informatica(r) PMREP, version [8.6.0], build [178.0613], Windows 32-bit
Copyright (c) Informatica Corporation 1994 - 2008
All Rights Reserved.
This Software may be protected by U.S. Patent Numbers 6,208,990; 6,044,374; 6,01
4,670; 6,032,158; 5,794,246; 6,339,775; 6,850,947; 6,895,471; 7,254,590 and othe
r U.S. Patents Pending.
Invoked at Thu Jan 08 00:26:32 2009
If -d is specified, do not specify -h or -o.
Failed to execute connect.
Completed at Thu Jan 08 00:26:32 2009
C:\Informatica\PowerCenter8.6.0\server\bin>pmrep connect -r EDWDEV -d Domain_inf
ra-34af3549f -h infra-34af3549f -o 6001 -n Administrator -s native -x magogate
C:\Informatica\PowerCenter8.6.0\server\bin>pmrep connect -r EDWDEV -d Domain_inf
ra-34af3549f -n Administrator -s Native -x magogate
Informatica(r) PMREP, version [8.6.0], build [178.0613], Windows 32-bit
Copyright (c) Informatica Corporation 1994 - 2008
All Rights Reserved.
This Software may be protected by U.S. Patent Numbers 6,208,990; 6,044,374; 6,01
4,670; 6,032,158; 5,794,246; 6,339,775; 6,850,947; 6,895,471; 7,254,590 and othe
r U.S. Patents Pending.
Invoked at Thu Jan 08 00:28:11 2009
Connected to repository EDWDEV in Domain_infra-34af3549f as user Administrator
connect completed successfully.
Completed at Thu Jan 08 00:28:16 2009
C:\Informatica\PowerCenter8.6.0\server\bin>
==================
C:\Informatica\PowerCenter8.6.0\server\bin>pmrep executequery -q CheckedInObject

Informatica(r) PMREP, version [8.6.0], build [178.0613], Windows 32-bit
Copyright (c) Informatica Corporation 1994 - 2008
All Rights Reserved.
This Software may be protected by U.S. Patent Numbers 6,208,990; 6,044,374; 6,01
4,670; 6,032,158; 5,794,246; 6,339,775; 6,850,947; 6,895,471; 7,254,590 and othe
r U.S. Patents Pending.
Invoked at Thu Jan 08 00:29:25 2009
mapping m_Dynamic_Target
.
The total number of records returned: 1
executequery completed successfully.
Completed at Thu Jan 08 00:29:30 2009

C:\Informatica\PowerCenter8.6.0\server\bin>pmrep executequery -q CheckedOutObjec
ts -u CheckedOutObjectsList.txt
Informatica(r) PMREP, version [8.6.0], build [178.0613], Windows 32-bit
Copyright (c) Informatica Corporation 1994 - 2008
All Rights Reserved.
This Software may be protected by U.S. Patent Numbers 6,208,990; 6,044,374; 6,01
4,670; 6,032,158; 5,794,246; 6,339,775; 6,850,947; 6,895,471; 7,254,590 and othe
r U.S. Patents Pending.
Invoked at Thu Jan 08 01:07:04 2009

The total number of records returned: 50
executequery completed successfully.
Completed at Thu Jan 08 01:07:09 2009
C:\Informatica\PowerCenter8.6.0\server\bin>

Partitioning : Things which did not worked for me !

Partitioning - now even heraing this word gives me a shock, for last 20+ days I am quite frustrated, just because of Informatica Partitioning.

I am not sure where it works correctly and actually increases the performance (increases throughput at source and eventually at target), but I definetly know where id does not work. Being optimistic and showing positive attitude , I am describing where I failed and Partitioning this which did not worked for me.

Before describing actual problem, I just walk through the climex ...

I have a mapping, which has source as a csv file (having 5-Million records in it) and target as a partition table (partitioned based on date) and inbtween source and target there are number of tranformations like joiner, sorter, normalizer.

My Manager has forcee the performance problem because in future the source records are going to get increase. Currently source file do not have 5 million records. Earlier he asked my on-site co-ordinator to work on this and to check if adding partitioning at infra level, improves the performance or not. After onsite-corodinartor got released from the project, that task ultimately got assigned to me.

First Hurdle -- At first run, instead of getting any kind of performance gain, mapping started taking hell lot of time. I tried number of things at session paramter level, but not a single thing helped. After that I tried enabling "Collect Performance Statistics" and while going through that realized that data getting loded at target it quite more than expected.

When I looked at it closely, came to know that from one of the relational source, data was coming duplicate. That's because, when we add partition, say 3 for an example. all 3 partitions will fetch the data from relational source, whereas that's not the case with file as a source. In FileSource, if you specify input to only one partition, informatica automatically transfer data to remaining partition, so data will get divided into number of partitions. But in case relational source, if you do not specify proper filter condition at each partiion, same data will be fected by all partitions.

If you check at "Partitioning Joiner Tranformation" there u will find there are 2 types
1. 1:n partitioning
2. n:n partitioning

In case of a joiner tranformation, you can only partition Detail source if you do not want to add partition point at Joiner level that is called as 1:n partitionng

In case of n:n partitioing, you can add Partition Point at Joiner but with that you have to add a partition at master level too. Here, you have to work cauciously. If your master is relational source, add condition 1=2 except one partition to restrict duplicate data.

2. After completing this hurdle, I ran mapping again, and got success at certain level. I would not say throughput / performance was fantastic, but at least much more positive that earlier.

With my past experience, I modified below parameters at session level
a. Default buffer block size -- chaned to 1000000
b. Max Percentage of total memory allowed for auto memory attributes -- changed to 25
c. Commit Interval -- Changed ot 1000000
d. DTM Buffer Size -- Changed to 1GB, 2GB from auto

This helped me to gain performance for single or uptop 4-5 simultaneous sessions. But as I kicked off all 10 workflows together, perforamnce went down, and sometime un-partition sessions were working much better than the partitioned sessions.

3. I had a discussion with Nilesh [Seems I bugged nilesh very much, but for me he was the only guy to expect some help from informatica or oracle point of view], when we checked the session logs, we came to conclusion that buttleneck might be at target level.

At session logs, it was the Target Thread which was always busy more than 98%+ and that too for singnificat amount of time. So, what was the to overcome that.. then we tried Oracle Loader utility.

4. Created Oracle Loader connection at Workflow level, and changed the session paramters. It got failed, tried changing number of things, but all efforts went into vein. I was totally frustrated. Left that option aside, and started working on other prod issues.

Again on next day, when I looked closly at logs, it was telling me that the password is incorrect. So, instead of QA, changed the loader connection to Dev db, and again kicked off the session, but still no success. This time at least control file, input files and logs files got created. Input file was empty, and control file was showing inserted 0 rows.

I was expecting informatica to create 11 input files, and if not same number of control files but in same contorl files I was expecting entries for 11 input files. But that was not the case. There was only 1 input file and same entry was there in control file. Not sure why this is happening, but seems informatica always creates only one control files and only one input file.

Still I have not got any success in performance but lot many questions are there
1. Why performance is going down at the time of insertion? Is that because of Constraints? or because of concurrent insertions?

2. Why informatica creates only 1 contorl and input file? what we have to do if we are running multiple session/tasks in a single workflow?

Will update this blog if I get any answers for above questions, but till the time, for my manger, what every I did is just a waste. For him, whole time/work is good for nothing, end of the day my output is 0. :-(

How to pass Target File Name dynamically ?

Had a discussion with Santhosh Garu regarding target file creation but passing its name from mapping itself and appening the name with date. I had a discussion about this with Nilesh earlier, and even I had tried it at that time.

Again today I did it, I passed the file name from mapping with date appened to it. Below are the steps for it.

1. Import the target file and after importing it, click on the "Add FileName Columns to this table icon". Once you click on it, automatically new port will get added to target table.

2. Import source (File or Relational)
3. Creation a simple mapping.

Source --> Expression (to specify the filename) --> Target

In the expression you specify below as a filename'HelloWorld_' || TO_CHAR(SYSTIMESTAMP(),'DD-MM-YYYY')
4. Create a workflow and specify target directory and target filename at session properties level.
5. file name which you give at session level will get over-ridden by file name which you will pass from mapping.

Please watch below video for the same.http://www.youtube.com/watch?v=QCo929SrBsQ&feature=BFa&list=UUZ4jBpZoYcbYNQAOnuV_edg

Unix Commands ..

Today we will practice some Unix commands which usually we use and are really helpful and required when your file size is more than 60K records, because those files you can not open at Excel.

1. How you will find the row count of a file?
2. How you will find total number of files inside any directory?
3. How will you print first 2 lines of file?
4. How to find number of records without displaying file name in output
5. Suppose you have 100 records, out of which you want to read 10 to 20 records. You have a header in a file, how will you do that?
6. I want distinct Department_Id from Employee File
7. Check with Piyush what command he used to fire for record count without the header record.
8. I want all Employees with department as 10 ?
9. I've a flat file where 1st two bytes of each record is an identifier for that record. How do I find distinct count of these records?
10. How will you compare 2 files
11. Find out files starting with Emp in a particular directory
12. How will you find particular name in a file ?
13. How will you find files in a directory ?
14. How will you search file in same / current directory?
15. How will you change the file permissions of all files inside particular directory ?
16. How will you search all files owned by a particular user
17. How will you replace character / string in a particular file with another character / string ?
18. How will you sort files by their size in a particular direcotry?
19. How will you sort files by their timestamps
20. To find out total size of a folder
21. How will you delete particular content of a file
22. How will you print last 2 lines of a file
23. How to find folders in particular directory

1. How you will find the row count of a file?
Mandar@Mandar-PC /MyFiles
$ wc -l Employees.csv
108 Employees.csv

2. How you will find total number of files inside any directory?
Mandar@Mandar-PC /MyFiles
$ ls | wc -l
8

3. How will you print first 2 lines of file?
Mandar@Mandar-PC /MyFiles
$ head -2 Employees.csv
employee_id,first_name,last_name,email,phone_number,hire_date,job_id,salary,commission_pct,manager_id,department_id
100,Steven,King,SKING,515.123.4567,6/17/1987,AD_PRES,24000,null,null,90


4. How to find number of records without displaying file name in output
Mandar@Mandar-PC /MyFiles
$ awk '{n++} END {print n}' Employees.csv
108


Even this will work same way Mandar@Mandar-PC /MyFiles
$ wc -l < Employees.csv
108

5. Suppose you have 100 records, out of which you want to read 10 to 20 records. You have a header in a file, how will you do that?
Mandar@Mandar-PC /MyFiles
$ head -20 Employees.csv | tail -10
109,Daniel,Faviet,DFAVIET,515.124.4169,8/16/1994,FI_ACCOUNT,9000,null,108,100
110,John,Chen,JCHEN,515.124.4269,9/28/1997,FI_ACCOUNT,8200,null,108,100
111,Ismael,Sciarra,ISCIARRA,515.124.4369,9/30/1997,FI_ACCOUNT,7700,null,108,100
112,Jose Manuel,Urman,JMURMAN,515.124.4469,3/7/1998,FI_ACCOUNT,7800,null,108,100
113,Luis,Popp,LPOPP,515.124.4567,12/7/1999,FI_ACCOUNT,6900,null,108,100
114,Den,Raphaely,DRAPHEAL,515.127.4561,12/7/1994,PU_MAN,11000,null,100,30
115,Alexander,Khoo,AKHOO,515.127.4562,5/18/1995,PU_CLERK,3100,null,114,30
116,Shelli,Baida,SBAIDA,515.127.4563,12/24/1997,PU_CLERK,2900,null,114,30
117,Sigal,Tobias,STOBIAS,515.127.4564,7/24/1997,PU_CLERK,2800,null,114,30
118,Guy,Himuro,GHIMURO,515.127.4565,11/15/1998,PU_CLERK,2600,null,114,30


6. I want distinct Department_Id from Employee File
Mandar@Mandar-PC /MyFiles
$ cut -d"," -f11 Employees.csv | sort | uniq
10
100
110
20
30
40
50
60
70
80
90
department_id
null

Give man cut so that you will get all parameter informationuniq will output all lines exactly once:
uniq -d will output all lines that appear more than once, and it will print them once:
uniq -u will output all lines that appear exactly once, and it will print them once


7. Check with Piyush what command he used to fire for record count without the header record.


8. I want all Employees with department as 10 ?
Mandar@Mandar-PC /MyFiles/NewFolder
$ awk -F"," '{ if($11=10) print $0}' Employees1.csv > Dep10File.csv


9.I've a flat file where 1st two bytes of each record is an identifier for that record. How do I find distinct count of these records?
Flat file ex:
AB|123|hello moto|
AB|456|googly|
BC|sick day|booya|
ID|inter doing|hahahah

So I want to find distinct record count... it should give me
AB 2
BC 1
ID 1

Mandar@Mandar-PC /MyFiles
$ awk -F'|' '{++c[$1]}END{for (i in c)print i, c[i]}' SampleData.txt
AB 2
BC 1
ID 1


10. How will you compare 2 files
Mandar@Mandar-PC /MyFiles
$ comp mandar.txt myname.txt
Comparing mandar.txt and myname.txt...
Compare error at OFFSET 6
file1 = A
file2 = 20
Compare error at OFFSET 10
file1 = A
file2 = 20
Compare more files (Y/N) ? n

Mandar@Mandar-PC /MyFiles
$ diff mandar.txt myname.txt
1,3c1
< mandar
< raghunath
< gogate
---
> mandar raghunath gogate

Mandar@Mandar-PC /MyFiles
$ sdiff mandar.txt myname.txt
mandar | mandar raghunath gogate
raghunath <
gogate <


11. Find out files starting with Emp in a particular directory
Mandar@Mandar-PC /MyFiles
$ ls -l | grep Emp*
-rwxr-xr-x 1 Mandar None 8314 May 1 00:39 Employees.csv


12. How will you find particular name in a file ?
Mandar@Mandar-PC /MyFiles
$ grep Sundi* Employees.csv
166,Sundar,Ande,SANDE,011.44.1346.629268,3/24/2000,SA_REP,6400,0.1,147,80
173,Sundita,Kumar,SKUMAR,011.44.1343.329268,4/21/2000,SA_REP,6100,0.1,148,80


13. How will you find files in a directory ?
Mandar@Mandar-PC /MyFiles
$ find -name 'Emp*'
./Employees.csv
./Employees1.csv
./Employees10.csv
./Employees11.csv
./Employees12.csv
./Employees2.csv
./Employees3.csv
./Employees4.csv
./Employees5.csv
./Employees6.csv
./Employees7.csv
./Employees8.csv
./Employees9.csv

As files were inside MyFile folder itself, it gave the search result like this. but same command works for finding files in sub - direcotry also. Now I have moded all Empl* files to new directory called NewFolder which is inside MyFiles directory
Mandar@Mandar-PC /MyFiles
$ find -name 'Emp*'
./NewFolder/Employees.csv
./NewFolder/Employees1.csv
./NewFolder/Employees10.csv
./NewFolder/Employees11.csv
./NewFolder/Employees12.csv
./NewFolder/Employees2.csv
./NewFolder/Employees3.csv
./NewFolder/Employees4.csv
./NewFolder/Employees5.csv
./NewFolder/Employees6.csv
./NewFolder/Employees7.csv
./NewFolder/Employees8.csv
./NewFolder/Employees9.csv

This command searches even the directory name and not only the file name
Mandar@Mandar-PC /MyFiles
$ find . -name New*
./NewFolder

To find only the files and exclude directories Mandar@Mandar-PC /MyFiles
$ find . -type f -name New*

14. How will you search file in same / current directory?
To search in all directories Mandar@Mandar-PC /MyFiles
$ find / -type f -name Emp*

15. How will you change the file permissions of all files inside particular directory ?
Mandar@Mandar-PC /MyFiles
$ find ./NewFolder/ -exec chmod 777 {} \;

16. How will you search all files owned by a particular user
Mandar@Mandar-PC /MyFiles
$ find . -user mandar

17. How will you replace character / string in a particular file with another character / string ?
Mandar@Mandar-PC /MyFiles/NewFolder
$ less Employees1.csv | tr 'manager_id' 'MANAGER_ID' | less
Above command will give display character with MANAGER_ID at command line You can redirect this output to new file with below command
Mandar@Mandar-PC /MyFiles/NewFolder
$ less Employees1.csv | tr 'manager_id' 'MANAGER_ID' > MyNewFile.csv

18. How will you sort files by their size in a particular direcotry?
Mandar@Mandar-PC /MyFiles/NewFolder
$ du -s * | sort -n
0 Employees.csv
12 Employees1.csv
12 MyNewFile.csv
20 Employees2.csv
36 Employees3.csv
68 Employees4.csv
180 Employees5.csv
344 Employees6.csv
764 Employees7.csv
2292 Employees8.csv
9160 Employees9.csv
27476 Employees10.csv
137376 Employees11.csv
412128 Employees12.csv

Mandar@Mandar-PC /MyFiles/NewFolder
$ du -a | sort -n
0 ./Employees.csv
1 ./helloworld.txt
12 ./Employees1.csv
12 ./MyNewFile.csv
20 ./Employees2.csv
36 ./Employees3.csv
68 ./Employees4.csv
180 ./Employees5.csv
344 ./Employees6.csv
764 ./Employees7.csv
2292 ./Employees8.csv
9160 ./Employees9.csv
27476 ./Employees10.csv
137376 ./Employees11.csv
412128 ./Employees12.csv
589873 .


19. How will you sort files by their timestamps
Mandar@Mandar-PC /MyFiles/NewFolder
$ ls -ctl | sort -n
-rw-r--r-- 1 Mandar None 11 May 5 12:34 helloworld.txt
-rw-r--r-- 1 Mandar None 8314 May 5 12:20 MyNewFile.csv
-rwxrwxrwx 1 Mandar None 0 May 5 12:09 Employees.csv
-rwxrwxrwx 1 Mandar None 8314 May 5 12:03 Employees1.csv
-rwxrwxrwx 1 Mandar None 16628 May 5 12:03 Employees2.csv
-rwxrwxrwx 1 Mandar None 33256 May 5 12:03 Employees3.csv
-rwxrwxrwx 1 Mandar None 66512 May 5 12:03 Employees4.csv
-rwxrwxrwx 1 Mandar None 182908 May 5 12:03 Employees5.csv
-rwxrwxrwx 1 Mandar None 349188 May 5 12:03 Employees6.csv
-rwxrwxrwx 1 Mandar None 781516 May 5 12:03 Employees7.csv
-rwxrwxrwx 1 Mandar None 2344548 May 5 12:03 Employees8.csv
-rwxrwxrwx 1 Mandar None 9378192 May 5 12:03 Employees9.csv
-rwxrwxrwx 1 Mandar None 28134576 May 5 12:03 Employees10.csv
-rwxrwxrwx 1 Mandar None 140672880 May 5 12:03 Employees11.csv
-rwxrwxrwx 1 Mandar None 422018640 May 5 12:03 Employees12.csv
total 589869


20. To find out total size of a folder
Mandar@Mandar-PC /MyFiles/NewFolder
$ du -h
577M .
22. How will you print last 2 lines of a file
Mandar@Mandar-PC /MyFiles
$ tail -2 Second.sh
echo -e "\n\nFile $1, found and successfully echoed"
fi

23. How to find folders in particular directory
Mandar@Mandar-PC /MyFiles
$ find . -type d
.
./NewFolder

Shell Scripting - First Day !!

Aftar long duration, writing this blog on Shell Scripting.. somethinug which is still a new thing for me being in IT industry for more than 5 yeras now. To begin with we will execute HelloWorld program as per the tradition; but before that below is the link which I am using for practicing

http://www.freeos.com/guides/lsst/ch03sec01.html

I have installed cygwin for practicing shell and unix commands, though vi do not work here, you can always type your commands on windows machine [notepad, wordpad or notepad++] and execte those with cygwin. Yes, you can access windows files from cygwin.

First Program - HelloWorld

------------------
#!/bin/sh
#
#First Script of Hello World
echo "Hello World"
------------------
Just run above command on command prompt as below
./HelloWorld.sh


Second Program - Print Contents Inside Files

-----------------------
#!/bin/sh
#
#Second Program to print the content of file
if cat $1
then
echo -e "\n\nFile $1, found and successfully echoed"
fi


Wrote above script in notepad++ ; but when tried to execute that from command prompt, it failed giving below error
./Second.sh: line 9: syntax error: unexpected end of file

But, when I wrote same command from command line itself, it got executed successfully.. Need to know the reason behind this.

Detailed explanation
if cat command finds foo file and if its successfully shown on screen, it means our cat command is successful and its exist status is 0 (indicates success), So our if condition is also true and hence statement echo -e "\n\nFile $1, found and successfully echoed" is proceed by shell. Now if cat command is not successful then it returns non-zero value (indicates some sort of failure) and this statement echo -e "\n\nFile $1, found and successfully echoed" is skipped by our shell.

Now here you will also require a echo parameter description which are as follows

echo [options] [string, variables...]
Displays text or variables value on screen.
Options
-n Do not output the trailing new line.
-e Enable interpretation of the following backslash escaped characters in the strings:
\a alert (bell)
\b backspace
\c suppress trailing new line
\n new line
\r carriage return
\t horizontal tab
\\ backslash
For e.g. $ echo -e "An apple a day keeps away \a\t\tdoctor\n"

Creating dummy data !!

Just going through oracle blogs, and got focus on below site...http://www.oracle-developer.net/display.php?id=515

Most of the times when you are practicing Oracle or Informatica code, you require a dummy data to be available at table. Particularly when you are working on Performance testing of oracle and informatica, large data is required. How will you create it ? One way is insert some records, then take union of it and again union of it. and so on.. But this will create repetation of data in your table..

Best way is below

Create table test as

SELECT ROWNUM                     AS id

       ,      MOD(ROWNUM,2000)           AS grp

       ,      DBMS_RANDOM.STRING('u',5)  AS val

       ,      DBMS_RANDOM.STRING('u',30) AS pad

       FROM   dual

       CONNECT BY ROWNUM <= 1000000;

For dbms_random pkg, you can get more info at http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:3181424400346795479

Saving an error while DML statement...

It was an interview times, as our project went to tcs... every one was either facing some interview or preparing for the same.

Birwatkar was facing interview at CnC, and below was one of the question asked to him..

How to log errors while performing DML Operations through sql ?

Below code I got from http://www.oracle-base.com/articles/10g/DmlErrorLogging_10gR2.php. This is really useful functionality given by Oracle and saves lots of debugging time.

1. First create source and target table

create table insertTestSourec

(

    id varchar(200)   

)

create table insertTestTarget

(id number

)

2. Create error log table using proc create_error_log

BEGIN

  DBMS_ERRLOG.create_error_log (dml_table_name => 'insertTestTarget');

END;

insert into insertTestSourec values(1)

insert into insertTestSourec values(2)

insert into insertTestSourec values('t')

insert into insertTestTarget

select * from insertTestSourec

Here, statement will get rollback and not a single record will get inserted into target table.

Check your error log table using below sql

select *

from all_tables

where table_name like upper('%insertTestTarget%')

3. Verify the error log table

insert into insertTestTarget

select * from insertTestSourec

LOG ERRORS INTO ERR$_INSERTTESTTARGET ('INSERT') REJECT LIMIT UNLIMITED;

4. Here, after execution of above statement, 2 records (1 and 2) will get inserted into target table and incorrect record will get inserted into ERR$_INSERTTESTTARGET.

select *

from ERR$_INSERTTESTTARGET