Informatica 7.1: How to import export using shell script

The following script is useful to export various mappings from DEV environment and then import into QA environment.

1/ Firstly run.sh which will export mappings from DEV and then will import into QA.You have total flexibility while this export-import process.

$ cat run.sh
################################################################
# Script: This script will do import export of mappings using two sub scripts
# Author: M.A.Hasim
# Modification: Create on 28th Dec 2006
# Modification
# History:
# Calling: exportHasim_v1.sh,importHasim_v1.sh
# Called By:
##################################################################
echo "Info...Starting Export.\n"
exportHasim_v1.sh
echo "Info...Completed Export.\n"
sleep 5
echo "Info...Starting Import.\n"
importHasim_v1.sh
echo "Info...Completed Import.\n"
sleep 3
echo "Info...Everything Successful...Good Luck [ Hasim ].\n"

2/ This is an environment file to make things alive.Like as INFORMATICA repository name,folder name.Database parameters,Scrript location,log location etc.

$ cat .my_env
# INFA DEV PARAMETERS
REPODEV=devuser; export REPODEV
REPODEVUSER=Hasim; export REPODEVUSER
REPODEVUSERPASSWORD=Hasim; export REPODEVUSERPASSWORD
REPODEVHOST=utopia; export REPODEVHOST
REPODEVPORT=6410; export REPODEVPORT

# INFA QA PARAMETERS
REPOQA=qauser; export REPOQA
REPOQAUSER=Hasim; export REPOQAUSER
REPOQAUSERPASSWORD=Hasim; export REPOQAUSERPASSWORD
REPOQAHOST=utopia; export REPOQAHOST
REPOQAPORT=6510; export REPOQAPORT

# DATABASE LEVEL PARAMETERS #
$CNCTSTRNGORACLEDEV=devuser/devpasswd@devdatabase; export $CNCTSTRNGORACLEDEV
$CNCTSTRNGORACLEQA=qauser/qapasswd@qadatabase; export $CNCTSTRNGORACLEQA


# LOG ETC
LOG=/mnt/home/abulhasim/IMPEXPMAPPINGS/log; export LOG
SCRPT=/mnt/home/abulhasim/IMPEXPMAPPINGS/script; export SCRPT

3/ This is the main script used for exporting mappings.

$ cat exportHasim_v1.sh
####################################################
# Script: This script will export mappings from INFA Repository .
# Author: M.A.Hasim
# Modification: Create on 28th Dec 2006
# Modification
# History:
# Calling: clctexpobj_v1.sql
# Called By:
####################################################

# Setting all of the environment variables
. .my_env
DT=`date +%m%d%y%H%M%S`
echo "Info....Starting Export."

# Collecting Mappings to be imported and then creating Objectexport statement for pmrep
# and then making a unix script on the fly to be used in command
# the following sql will create a exportHasimmapng_v1.run script which we will use in
# command.So we need change the mode also.
sqlplus -s $CNCTSTRNGORACLE @clctexpobj_v1.sql
CHK1=`wc -l exportHasimmapng_v1.runcut -d" " -f1`
if [ $CHK1 -eq 0 ]; then
echo "Err....Please make exp_stat as 'W' in tmp_infa \n"
exit;
fi
chmod 777 exportHasimmapng_v1.run
mv exportHasimmapng_v1.run /mnt/home/abulhasim/informatica/repositoryserver

# Changing directory to INFA repository
cd /mnt/home/abulhasim/informatica/repositoryserver

# Lets connect to the repository first and then export
pmrep connect -r $REPODEV -n $REPODEVUSER -x $REPODEVUSERPASSWORD -h $REPODEVHOST -o $REPODEVPORT
if [ $? -ne 0 ];then
echo "Err...Connection with the repository $REPODEV failed.\n"
exit;
fi

# Now we will export
# The content of exportHasimmapng_v1.run is like
# objectexport -u 123T456_exportHasim.xml -n Trial -f GDC_PRACTICE -o Mapping -l 123T456_exportHasim.log
pmrep run -f exportHasimmapng_v1.run -o exportHasim$DT.log -e Hasim

if [ $? -ne 0 ];then
echo "Err...Connection with the repository $REPODEV failed.\n"
exit;
fi

# Now we will collect all three files(two logs having the string exportHasim and the other pmrep script
# generated into pmrepository directory for clean up purpose and dump into this local dir

#mv *exportHasim*.log $LOG
cp *exportHasim*.xml $SCRPT
#mv *exportHasim.xml $SCRPT

#rm -f exportHasimmapng_v1.run
cd -
# Update tmp_infa exp_stat uncomment in original run
#sqlplus -s $CNCTSTRNGORACLE @updtMasterTblExpStat.sql

4/ This is the main script for importing mappings.

$ cat importHasim_v2.sh
##########################################################################################################
# Script: This script will import mappings from one Repository to the other
# Author: M.A.Hasim
# Modification: Create on 28th Dec 2006
# Modification
# History:
# Calling:
# Called By:
##########################################################################################################

# Setting environment
. .my_env
# Removing all lst files
rm -f ImpExpData.lst conflictstatfrmtbl.lst
#rm -f *.lst *.xml
echo "Info....Starting Import."

# Collecting SOURCEFOLDERNAME,SOURCEREPOSITORYNAME,TARGETFOLDERNAME,TARGETREPOSITORYNAME
sqlplus -s $CNCTSTRNGORACLE @colctSrcTgtData_v1.sql

# Grep the conflictstatfrmtbl.lst file to get those values into UNIX variable
SOURCEFOLDERNAME=`cat ImpExpData.lstcut -d"" -f1`
SOURCEREPOSITORYNAME=`cat ImpExpData.lstcut -d"" -f2`
TARGETFOLDERNAME=`cat ImpExpData.lstcut -d"" -f3`
TARGETREPOSITORYNAME=`cat ImpExpData.lstcut -d"" -f4`
CHECKINCOMMENTS=`cat ImpExpData.lstcut -d"" -f5`
CHECKINLABEL=`cat ImpExpData.lstcut -d"" -f6`

echo "----- Source and Target Info -----"
echo "Info....SOURCEFOLDERNAME:$SOURCEFOLDERNAME"
echo "Info....SOURCEREPOSITORYNAME:$SOURCEREPOSITORYNAME"
echo "Info....TARGETFOLDERNAME:$TARGETFOLDERNAME"
echo "Info....TARGETREPOSITORYNAME:$TARGETREPOSITORYNAME"
echo "Info....CHECKINCOMMENTS:$CHECKINCOMMENTS"
echo "Info....CHECKINLABEL:$CHECKINLABEL"
echo "-----------------------------------"
echo "Info....Sleeping 1 secs.Press CNTRL-C if you not satisfied with the Info"
sleep 1

# Collecting conflict info from table tmp_infa_conflict and collect those into spool file conflictstatfrmtbl.lst
sqlplus -s $CNCTSTRNGORACLE @createCntrlXml_v1.sql

# Grep the conflictstatfrmtbl.lst file to get those values into UNIX variable
changeno=`cat conflictstatfrmtbl.lstcut -d"" -f1`
cpeno_ttno=`cat conflictstatfrmtbl.lstcut -d"" -f2`
mapng_cnflct=`cat conflictstatfrmtbl.lstcut -d"" -f3`
src_cnflct=`cat conflictstatfrmtbl.lstcut -d"" -f4`
trg_cnflct=`cat conflictstatfrmtbl.lstcut -d"" -f5`
exp_cnflct=`cat conflictstatfrmtbl.lstcut -d"" -f6`
fltr_cnflct=`cat conflictstatfrmtbl.lstcut -d"" -f7`
agg_cnflct=`cat conflictstatfrmtbl.lstcut -d"" -f8`
rank_cnflct=`cat conflictstatfrmtbl.lstcut -d"" -f9`
norm_cnflct=`cat conflictstatfrmtbl.lstcut -d"" -f10`
rtr_cnflct=`cat conflictstatfrmtbl.lstcut -d"" -f11`
seq_cnflct=`cat conflictstatfrmtbl.lstcut -d"" -f12`
sort_cnflct=`cat conflictstatfrmtbl.lstcut -d"" -f13`
updstrt_cnflct=`cat conflictstatfrmtbl.lstcut -d"" -f14`
custtrnsfrmtn_cnflct=`cat conflictstatfrmtbl.lstcut -d"" -f15`
lkp_cnflct=`cat conflictstatfrmtbl.lstcut -d"" -f16`
trnsctncntrl_cnflct=`cat conflictstatfrmtbl.lstcut -d"" -f17`
sp_cnflct=`cat conflictstatfrmtbl.lstcut -d"" -f18`
extproc_cnflct=`cat conflictstatfrmtbl.lstcut -d"" -f19`
joiner_cnflct=`cat conflictstatfrmtbl.lstcut -d"" -f20`

echo "----- Control XML Info -----"
echo "Info....CHANGE NO:$changeno"
echo "Info....CPENO TTNO:$cpeno_ttno"
echo "Info....MAPPING CONFILCT:$mapng_cnflct"
echo "Info....SOURCE CONFLICT:$src_cnflct"
echo "Info....TARGET CONFLICT:$trg_cnflct"
echo "Info....EXPRESSION CONFLICT:$exp_cnflct"
echo "Info....FILTER CONFLICT:$fltr_cnflct"
echo "Info....AGGREGATOR CONFLICT:$agg_cnflct"
echo "Info....RABK CONFLICT:$rank_cnflct"
echo "Info....NORMALIZER CONFLICT:$norm_cnflct"
echo "Info....ROUTER CONFLICT:$rtr_cnflct"
echo "Info....SEQUENCE CONFLICT:$seq_cnflct"
echo "Info....SORTER CONFLICT:$sort_cnflct"
echo "Info....UPDATE STRATEGY CONFLICT:$updstrt_cnflct"
echo "Info....CUSTOM TRANSFORMATION CONFLICT:$custtrnsfrmtn_cnflct"
echo "Info....LOOKUP CONFLICT:$lkp_cnflct"
echo "Info....TRANSACTION CONFLICT:$trnsctncntrl_cnflct"
echo "Info....STORED PROCEDURE CONFLICT:$sp_cnflct"
echo "Info....EXTERNAL PROCEDURE CONFLICT:$extproc_cnflct"
echo "Info....JOINER CONFLICT:$joiner_cnflct"
echo "-----------------------------------"
echo "Info....Sleeping 1 secs.Press CNTRL-C if you not satisfied with the Info"
sleep 1

# The control xml file for inporting objects will be of naming convention like 123T456.xml
CNTRLXML=importHasim$changeno_$cpeno_ttno.xml
# .
# .
# Check the pic above for this portions code.Blogger mess up with angular brackets of XML
# .
# .
# Creting run script for pmrep
IMPRTRUN=importHasim$changeno.$cpeno_ttno.run
echo "ObjectImport -i $cpeno_ttno"_exportHasimCore.xml" -c $CNTRLXML" >$IMPRTRUN
cp $IMPRTRUN /mnt/home/abulhasim/informatica/repositoryserver
cp $CNTRLXML /mnt/home/abulhasim/informatica/repositoryserver
cp $cpeno_ttno"_exportHasimCore.xml" /mnt/home/abulhasim/informatica/repositoryserver >>/dev/null 2>&1
if [ $? -ne 0 ];then
echo "Err....There are no xml files to import."
echo "Err....There should must be matching entry in master tables tmp_infa and tmp_infa_conflict"
exit;
fi
# Changinf directory to PMREp
cd /mnt/home/abulhasim/informatica/repositoryserver

pmrep connect -r $REPOQA -n $REPOQAUSER -x $REPOQAUSERPASSWORD -h $REPOQAHOST -o $REPOQAPORT
pmrep run -f $IMPRTRUN -o importHasim$DT.log -e Hasim
#if [ $? -ne 0 ];then
#echo "Err....Error occurred while importing"
cat importHasim.log
#else
#echo "Update exp status"
#sqlplus -s $CNCTSTRNGORACLEDEV @updtMasterTblImpStat_v1.sql
#fi
mv *importHasim* $LOG
cd -

5/ This a master table which keeps track of the changes you are about to made
The structure of tmp_infa is as follows:-


CREATE TABLE tmp_infa
(
changeno NUMBER(5) NOT NULL,
cpeno_ttno VARCHAR2(300) NOT NULL,
exp_fldr VARCHAR2(300),
exp_rep VARCHAR2(300),
exp_objct VARCHAR2(300),
exp_objcttyp VARCHAR2(300),
exp_objctsubtyp VARCHAR2(300),
exp_stat VARCHAR2(90) DEFAULT 'W',
imp_fldr VARCHAR2(300),
imp_rep VARCHAR2(300),
imp_chkincmnts VARCHAR2(600),
imp_chkinlbl VARCHAR2(600),
imp_stat VARCHAR2(90) DEFAULT 'W'
)

6/ This is another details table which will be used to resolve conflict issues while importing back mappings into QA repository.

Table structure of tmp_infa_conflict is as follows:-

CREATE TABLE tmp_infa_conflict
(
changeno NUMBER(5) NOT NULL,
cpeno_ttno VARCHAR2(300) NOT NULL,
mapng_cnflct VARCHAR2(24),
src_cnflct VARCHAR2(24),
trg_cnflct VARCHAR2(24),
exp_cnflct VARCHAR2(24),
fltr_cnflct VARCHAR2(24),
agg_cnflct VARCHAR2(24),
rank_cnflct VARCHAR2(24),
norm_cnflct VARCHAR2(24),
rtr_cnflct VARCHAR2(24),
seq_cnflct VARCHAR2(24),
sort_cnflct VARCHAR2(24),
updstrt_cnflct VARCHAR2(24),
custtrnsfrmtn_cnflct VARCHAR2(24),
lkp_cnflct VARCHAR2(24),
trnsctncntrl_cnflct VARCHAR2(24),
sp_cnflct VARCHAR2(24),
extproc_cnflct VARCHAR2(24),
joiner_cnflct VARCHAR2(24)
)




7/ This query is used to collect information from a master table named tmp_infa

$ cat clctexpobj_v1.sql
SET NEWPAGE 1
SET SPACE 0
SET LINESIZE 180
SET PAGESIZE 0
SET ECHO OFF
SET FEEDBACK OFF
SET TERMOUT OFF
SET TRIMSPOOL ON
SET VERIFY OFF

spool exportHasimmapng_v1.run
select 'objectexport -n 'exp_objct' -o 'exp_objcttyp' -f 'exp_fldr' -u 'cpeno_ttno'_exportHasimCore.xml'' -l 'cpeno_ttno'_exportHasim.log' from TMP_INFA where exp_stat = 'W' order by changeno;
spool off;
exit;

8/ Update script to update tmp_infa export status as 'S' after successfully exported.

$ cat updtMasterTblExpStat.sql
update tmp_infa set exp_stat = 'S' where exp_stat = 'W';
commit;
exit;

9/ colctSrcTgtData_v1.sql is to collect informations to which QA informatica folder we are to import of which informatica repository.

$cat colctSrcTgtData_v1.sql
SET NEWPAGE 1
SET SPACE 0
SET LINESIZE 180
SET PAGESIZE 0
SET ECHO OFF
SET FEEDBACK OFF
SET TERMOUT OFF
SET TRIMSPOOL ON
SET VERIFY OFF

spool ImpExpData.lst
select exp_fldr''exp_rep''imp_fldr''imp_rep''imp_chkincmnts''imp_chkinlbl from tmp_infa where exp_stat = 'W';
spool off;
exit;

10/ createCntrlXml_v1.sql is to collect all conflict related issues while importing.
You will have all informations related to confilct in tmp_infa_conflict table.

$ cat createCntrlXml_v1.sql
SET NEWPAGE 1
SET SPACE 0
SET LINESIZE 180
SET PAGESIZE 0
SET ECHO OFF
SET FEEDBACK OFF
SET TERMOUT OFF
SET TRIMSPOOL ON
SET VERIFY OFF

spool conflictstatfrmtbl.lst

select
cnflct.changeno ''
cnflct.cpeno_ttno ''
mapng_cnflct ''
src_cnflct ''
trg_cnflct ''
exp_cnflct ''
fltr_cnflct ''
agg_cnflct ''
rank_cnflct ''
norm_cnflct ''
rtr_cnflct ''
seq_cnflct ''
sort_cnflct ''
updstrt_cnflct ''
custtrnsfrmtn_cnflct ''
lkp_cnflct ''
trnsctncntrl_cnflct''
sp_cnflct ''
extproc_cnflct ''
joiner_cnflct
from tmp_infa_conflict cnflct,tmp_infa main
WHERE
cnflct.CHANGENO=main.CHANGENO
AND cnflct.CPENO_TTNO=main.CPENO_TTNO
AND main.imp_stat = 'W';

spool off;
exit;

11/ updtMasterTblImpStat_v1.sql is to update the status of tmp_infa table import status to S after successfully imported the mappings.

$ cat updtMasterTblImpStat_v1.sql
update tmp_infa set imp_stat = 'S' where imp_stat = 'W';
commit;
exit;


---: My blog is not responsible for any damages happened from the suggestion of my blog :---
Reach me at : m_a_hasim@yahoo.com

Informatica 7.1:How to invoke sessions through UNIX shell scripting.

In most of the ETL environment having INFA installed in UNIX we used to invoke sessions by UNIX.

1/
It gives better control.
2/
Can be embedded in a UNIX ETL scripts and thus can the process be automated.
3/
Can do all things which you can do in GUI mode.

You can invoke a session using pmcmd command.
A typical script may look like as follows:
.
.
.
.
. .my_env #Environment file having all the variables which
# we will use in PMCMD command.
.
.
# Some more logic- May be collecting info regarding all the sessions to be
# picked up alongwith their folder name from a Master table of the database.
.
.
.
PMCMD_STATUS=pmcmd startworkflow -u $INFO_USER -p $INFO_PWD -s $INFO_PORT -f $INFO_FOLDER -paramfile $INFO_PARAMFILE -nowait $INFO_SESSION
.
# Check whether your session has successfully instantiated or not.
.
if [ $PMCMD_STATUS -ne 0 ]; then
echo "\n Error Occurred... `date +%m%d%y%H%M%S`: The Informatica Session Named $INFO_SESSION Failed"
exit;
else
echo "\n Cheers... `date +%m%d%y%H%M%S`: The Informatica Session Named $INFO_SESSION Successfully Started on `date +%m%d%y%H%M%S`\n"
fi
.
# Do something- Blah blah blah
.
.
.


Informatica Workflow Administration guide is a good help for doing such things from command line mode.

All available tasks which can be performed by pmcmd are as follows:


$ pmcmd
pmcmd>
pmcmd> help
Usage: help [command]
Usage: version
Usage: pingserver
Usage: getserverproperties
Usage: shutdownserver <-complete|-stop|-abort>
Usage: getserverdetails [-all|-running|-scheduled]
Usage: getrunningsessionsdetails
Usage: startworkflow [<-folder|-f> folder]
[<-startfrom> taskInstancePath [<-recovery>]]
[<-paramfile> paramfile] [-wait|-nowait] workflow
Usage: stopworkflow [<-folder|-f> folder] [-wait|-nowait] workflow
Usage: abortworkflow [<-folder|-f> folder] [-wait|-nowait] workflow
Usage: waitworkflow [<-folder|-f> folder] workflow
Usage: resumeworkflow [<-folder|-f> folder] [-wait|-nowait] [<-recovery>]
workflow
Usage: scheduleworkflow [<-folder|-f> folder] workflow
Usage: unscheduleworkflow [<-folder|-f> folder] workflow
Usage: getworkflowdetails [<-folder|-f> folder] workflow
Usage: starttask [<-folder|-f> folder] <-workflow|-w> workflow
[<-paramfile> paramfile] [-wait|-nowait] [<-recovery>]
taskInstancePath
Usage: stoptask [<-folder|-f> folder] <-workflow|-w> workflow [-wait|-nowait]
taskInstancePath
Usage: aborttask [<-folder|-f> folder] <-workflow|-w> workflow [-wait|-nowait]
taskInstancePath
Usage: waittask [<-folder|-f> folder] <-workflow|-w> workflow taskInstancePath
Usage: resumeworklet [<-folder|-f> folder] <-workflow|-w> workflow
[-wait|-nowait] [<-recovery>] taskInstancePath
Usage: gettaskdetails [<-folder|-f> folder] <-workflow|-w> workflow
taskInstancePath
Usage: getsessionstatistics [<-folder|-f> folder] <-workflow|-w> workflow
taskInstancePath
Usage: connect <-serveraddr|-s> [host:]portno
<<-user|-u> username|<-uservar|-uv> userEnvVar>
<<-password|-p> password|<-passwordvar|-pv> passwordEnvVar>
Usage: disconnect
Usage: setwait
Usage: setnowait
Usage: unsetfolder
Usage: setfolder folder
Usage: showsettings
Usage: exit


---: My blog is not responsible for any damages happened from the suggestion of my blog :---
Reach me at : m_a_hasim@yahoo.com

PGP ERROR:received signal 11 and Unable to open default key rings

PROBLEM:
-----------
The problem "Unable to open default key rings" came when I was to decrypt a pgp file.
And then to investigate this I wanted to see the keys in our keyring as we have updated a key days before and then I got stucked in "received signal 11"

Actually we have more than one users in that specific UNIX system.And whereas one user ( user2 ) can see pgp -kv but another user ( (user1) is getting error while trying to see the keyring.

user2/home$ pgp -kv
Pretty Good Privacy(tm) Version 6.5.8
(c) 1999 Network Associates Inc.
Uses the RSAREF(tm) Toolkit, which is copyright RSA Data Security, Inc.
Export of this software may be restricted by the U.S. government.


Type bits/keyID Date User ID
pub 1024/4812D8FF 2002/07/19 hasim
sec 1024/45FD19FA 2003/10/20 mahasim
2 matching keys found.

0 memory frags found
exitPGP: exitcode = 0

user1/home$ pgp -kv
Pretty Good Privacy(tm) Version 6.5.8
(c) 1999 Network Associates Inc.
Uses the RSAREF(tm) Toolkit, which is copyright RSA Data Security, Inc.
Export of this software may be restricted by the U.S. government.


received signal 11

0 memory frags found
exitPGP: exitcode = 6

user1/home$ pgp +force -z "My PGP Key" samplefile.pgp
Pretty Good Privacy(tm) Version 6.5.8
(c) 1999 Network Associates Inc.
Uses the RSAREF(tm) Toolkit, which is copyright RSA Data Security, Inc.
Export of this software may be restricted by the U.S. government.

Error: Unable to open default key rings.
0 memory frags found
exitPGP: exitcode = 32

SOLUTION
-----------
I have checked the MANPATH,PATH for both users and did not get any difference.Later I have discovered that some files permission has been changed ( read permission has been removed for user1 ) in pgp home directory.I granted read permission to all users and it started working fine.

---: My blog is not responsible for any damages happened from the suggestion of my blog :---
Reach me at : m_a_hasim@yahoo.com

HOW TO ESCAPE SPECIAL CHARACTER IN SQL QUERY IN Oracle9i Enterprise Edition Release 9.2.0.6.0

Select * from emp;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
7369 SMITH_N CLERK 7902 17-Dec-1980 1064.8 20
7499 ALLEN_
SALESMAN 7698 20-Feb-1981 1600 300 30

select * from emp where ename like '%*_%' escape '*' --Here we are telling to escape the character(s) immetiadely after the asterisk.

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
7369 SMITH_N CLERK 7902 17-Dec-1980 1064.8 20


select * from emp

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
7369 SMITH_%N CLERK 7902 17-Dec-1980 1064.8 20
7499 ALLEN% SALESMAN 7698 20-Feb-1981 1600 300 30
7521 WARD SALESMAN 7698 22-Feb-1981 1250 500 30
7566 JONES MANAGER 7839 2-Apr-1981 3959.73 20
7654 MARTIN SALESMAN 7698 28-Sep-1981 1250 1400 30
7698 BLAKE MANAGER 7839 1-May-1981 2850 30
7782 CLARK MANAGER 7839 9-Jun-1981 3260.95 10
7788 SCOTT ANALYST 7566 9-Dec-1982 3993 20
7839 KING PRESIDENT 17-Nov-1981 6655 10
7844 TURNER SALESMAN 7698 8-Sep-1981 1500 0 30
7876 ADAMS CLERK 7788 12-Jan-1983 1464.1 20
7900 JAMES CLERK 7698 3-Dec-1981 950 30
7902 FORD ANALYST 7566 3-Dec-1981 3993 20
7934 MILLER CLERK 7782 23-Jan-1982 1730.3 10


select * from emp where ename like '%*_*%%' escape '*' --Here we are telling to escape the character(s) immetiadely after the asterisk
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
7369 SMITH_%N CLERK 7902 17-Dec-1980 1064.8 20


select * from emp

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
7369 SMITH&N CLERK 7902 17-Dec-1980 1064.8 20
7499 ALLEN% SALESMAN 7698 20-Feb-1981 1600 300 30
7521 WARD SALESMAN 7698 22-Feb-1981 1250 500 30
7566 JONES MANAGER 7839 2-Apr-1981 3959.73 20
7654 MARTIN SALESMAN 7698 28-Sep-1981 1250 1400 30
7698 BLAKE MANAGER 7839 1-May-1981 2850 30
7782 CLARK MANAGER 7839 9-Jun-1981 3260.95 10
7788 SCOTT ANALYST 7566 9-Dec-1982 3993 20
7839 KING PRESIDENT 17-Nov-1981 6655 10
7844 TURNER SALESMAN 7698 8-Sep-1981 1500 0 30
7876 ADAMS CLERK 7788 12-Jan-1983 1464.1 20
7900 JAMES CLERK 7698 3-Dec-1981 950 30
7902 FORD ANALYST 7566 3-Dec-1981 3993 20
7934 MILLER CLERK 7782 23-Jan-1982 1730.3 10


select * from emp where ename like '%*&%' escape '*'
13:19:36 ORA-01424: missing or illegal character following the escape character --As '&' does not have any special meaning in pattern-search in SQL query.

---: My blog is not responsible for any damages happened from the suggestion of my blog :---
Reach me at : m_a_hasim@yahoo.com