[prev in list] [next in list] [prev in thread] [next in thread]
List: odtug-sqlplus-l
Subject: KSH question
From: "Berthoff, Tom" <Tom.Berthoff () sig ! com>
Date: 2008-02-01 20:45:33
Message-ID: F001.00612E7F.20080201124533 () fatcity ! com
[Download RAW message or body]
Hi there,
I have the following in one of my ksh load scripts.
############################################
Load_MarkitEntities( ) {
# Call stored procedure to load and transform input xml entitites data
file into relational
# staging tables
$ORACLE_HOME/bin/sqlplus -s $USERNM/$PASSWD@$ORACLE_SID <<! >/dev/null
2>&1
WHENEVER SQLERROR EXIT 08 ROLLBACK
WHENEVER OSERROR EXIT 16 ROLLBACK
BEGIN
PKG_ES_RED_ENT_XML_SHRED.ins_entities_xml('$FILE_NAME');
EXCEPTION
WHEN OTHERS
THEN RAISE;
END;
/
EXIT;
!
EXITCODE=$?
if [ "${EXITCODE}" -ne 0 ]
then
MSG="ERROR calling ENTSTGADMIN.PKG_ES_RED_ENT_XML_SHRED"
echo $MSG
Post_Failure
exit 1
fi
}
------------------------------------------------------------------------
--------------------
This afternoon, the load kept failing. When I tried the same anonymous
block from my ksh script in Toad, I was able to see the Oracle Error,
and determine what happened.
------------------------------------------------------------------------
----------------------------
Here is the output from Toad:
BEGIN pkg_es_red_ent_xml_shred.ins_entities_xml('MergerBasic.xml'); END;
ERROR shredding input Entity,xml file.
ORA-04098: trigger 'ENTSTGADMIN.TRG_FP_FIDS_RED_ENT_XML_SHRED' is
invalid and failed re-validation
My question is, can anyone suggest a better method for checking oracle
errors returned in a ksh sqlplus session, becuase it seems that my
current method is not sufficient?
Thanks,
Tom
Tom Berthoff
Enterprise Technology
x1024
484-562-1024
tom.berthoff@sig.com
IMPORTANT: The information contained in this email and/or its attachments is \
confidential. If you are not the intended recipient, please notify the sender \
immediately by reply and immediately delete this message and all its attachments. \
Any review, use, reproduction, disclosure or dissemination of this message or any \
attachment by an unintended recipient is strictly prohibited. Neither this message \
nor any attachment is intended as or should be construed as an offer, solicitation or \
recommendation to buy or sell any security or other financial instrument. Neither \
the sender, his or her employer nor any of their respective affiliates makes any \
warranties as to the completeness or accuracy of any of the information contained \
herein or that this message or any of its attachments is free of viruses.
[Attachment #3 (text/html)]
<html xmlns:o="urn:schemas-microsoft-com:office:office" \
xmlns:w="urn:schemas-microsoft-com:office:word" \
xmlns:st1="urn:schemas-microsoft-com:office:smarttags" \
xmlns="http://www.w3.org/TR/REC-html40">
<head>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=us-ascii">
<meta name=Generator content="Microsoft Word 11 (filtered medium)">
<o:SmartTagType namespaceuri="urn:schemas-microsoft-com:office:smarttags"
name="City"/>
<o:SmartTagType namespaceuri="urn:schemas-microsoft-com:office:smarttags"
name="place"/>
<!--[if !mso]>
<style>
st1\:*{behavior:url(#default#ieooui) }
</style>
<![endif]-->
<style>
<!--
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0in;
margin-bottom:.0001pt;
font-size:12.0pt;
font-family:"Times New Roman";}
a:link, span.MsoHyperlink
{color:blue;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{color:purple;
text-decoration:underline;}
p
{mso-margin-top-alt:auto;
margin-right:0in;
mso-margin-bottom-alt:auto;
margin-left:0in;
font-size:12.0pt;
font-family:"Times New Roman";}
span.EmailStyle17
{mso-style-type:personal-compose;
font-family:Arial;
color:windowtext;}
@page Section1
{size:8.5in 11.0in;
margin:1.0in 1.25in 1.0in 1.25in;}
div.Section1
{page:Section1;}
-->
</style>
</head>
<body lang=EN-US link=blue vlink=purple>
<div class=Section1>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'>Hi there,<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'><o:p> </o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'>I have the following in one of my ksh load \
scripts. </span></font><o:p></o:p></p>
<p class=MsoNormal><font size=3 face="Times New Roman"><span style='font-size:
12.0pt'> <o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'>############################################<br>
Load_MarkitEntities( ) {<br>
# Call stored procedure to load and transform input xml entitites data file
into relational<br>
# staging tables </span></font><o:p></o:p></p>
<p class=MsoNormal><font size=3 face="Times New Roman"><span style='font-size:
12.0pt'> <o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'>$ORACLE_HOME/bin/sqlplus -s <a
href="mailto:$USERNM/$PASSWD@$ORACLE_SID"
title="mailto:$USERNM/$PASSWD@$ORACLE_SID">$USERNM/$PASSWD@$ORACLE_SID</a>
<<! >/dev/null 2>&1<br>
WHENEVER SQLERROR EXIT 08 ROLLBACK<br>
WHENEVER OSERROR EXIT 16 ROLLBACK<br>
BEGIN<br>
PKG_ES_RED_ENT_XML_SHRED.ins_entities_xml('$FILE_NAME');<br>
EXCEPTION<br>
WHEN OTHERS <br>
THEN RAISE;<br>
END;<br>
/<br>
EXIT;<br>
!<br>
EXITCODE=$?<br>
if [ "${EXITCODE}" -ne 0 ]<br>
then<br>
MSG="ERROR calling ENTSTGADMIN.PKG_ES_RED_ENT_XML_SHRED"<br>
echo $MSG<br>
Post_Failure<br>
exit 1<br>
fi</span></font><o:p></o:p></p>
<p class=MsoNormal><font size=3 face="Times New Roman"><span style='font-size:
12.0pt'> <o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'>}</span></font><o:p></o:p></p>
<p class=MsoNormal><font size=3 face="Times New Roman"><span style='font-size:
12.0pt'> <o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'>--------------------------------------------------------------------------------------------</span></font><o:p></o:p></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'> This afternoon, the load kept failing. When I
tried the same anonymous block from my ksh script in Toad, I was able to
see the Oracle Error, and determine what happened.</span></font><o:p></o:p></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'>----------------------------------------------------------------------------------------------------</span></font><o:p></o:p></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'>Here is the output from Toad:</span></font><o:p></o:p></p>
<p><font size=2 color="#0000f0" face=Arial><span style='font-size:10.0pt;
font-family:Arial;color:#0000F0'>BEGIN </span></font><font size=2 face=Arial><span
style='font-size:10.0pt;font-family:Arial'>pkg_es_red_ent_xml_shred<font
color="#0000f0"><span style='color:#0000F0'>.</span></font>ins_entities_xml<font
color="#0000f0"><span style='color:#0000F0'>(</span></font><font color=red><span
style='color:red'>'MergerBasic.xml'</span></font><font color="#0000f0"><span
style='color:#0000F0'>); END;</span></font></span></font><o:p></o:p></p>
<p><font size=1 face=Arial><span style='font-size:7.5pt;font-family:Arial'>ERROR
shredding input Entity,xml file.<o:p></o:p></span></font></p>
<p><font size=1 face=Arial><span style='font-size:7.5pt;font-family:Arial'>ORA-04098:
trigger 'ENTSTGADMIN.TRG_FP_FIDS_RED_ENT_XML_SHRED' is invalid and failed
re-validation<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'>My question is, can anyone suggest a better method for
checking oracle errors returned in a ksh sqlplus session, becuase it seems that
my current method is not sufficient?<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'><o:p> </o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'>Thanks,<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'><o:p> </o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'>Tom</span></font><o:p></o:p></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'><o:p> </o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'><o:p> </o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'>Tom Berthoff</span></font><o:p></o:p></p>
<p class=MsoNormal><st1:City w:st="on"><st1:place w:st="on"><font size=2
face=Arial><span style='font-size:10.0pt;font-family:Arial'>Enterprise</span></font></st1:place></st1:City><font
size=2 face=Arial><span style='font-size:10.0pt;font-family:Arial'> \
Technology</span></font><o:p></o:p></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'>x1024</span></font><o:p></o:p></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'>484-562-1024</span></font><o:p></o:p></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'>tom.berthoff@sig.com</span></font><o:p></o:p></p>
<p class=MsoNormal><font size=3 face="Times New Roman"><span style='font-size:
12.0pt'><o:p> </o:p></span></font></p>
</div>
<p></p><p><br>IMPORTANT: The information contained in this email and/or its \
attachments is confidential. If you are not the intended recipient, please notify the \
sender immediately by reply and immediately delete this message and all its \
attachments. Any review, use, reproduction, disclosure or dissemination of this \
message or any attachment by an unintended recipient is strictly prohibited. Neither \
this message nor any attachment is intended as or should be construed as an offer, \
solicitation or recommendation to buy or sell any security or other financial \
instrument. Neither the sender, his or her employer nor any of their respective \
affiliates makes any warranties as to the completeness or accuracy of any of the \
information contained herein or that this message or any of its attachments is free \
of viruses.</body>
</html>
--
For more information on this topic or to become a member, visit our web site at \
http://www.ODTUG.com Be sure to check out our Seriously Practical (SP) Conferences \
coming up this year!
ODTUG is pleased to announce that Kaleidoscope 2008 will be held at The New Orleans \
Sheraton from June 15-19. Keep checking www.ODTUG.com for more details.
--
Author: Berthoff, Tom
INET: Tom.Berthoff@sig.com
Fat City Hosting, San Diego, California -- http://www.fatcity.com
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru@fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ODTUG-SQLPLUS-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic