Home » SQL & PL/SQL » SQL & PL/SQL » PRAGMA EXCEPTION_INIT not working as desired (O/S:Windows 7 PRO; Product: Oracle; version: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production)
| PRAGMA EXCEPTION_INIT not working as desired [message #637064] |
Sat, 09 May 2015 16:16  |
buggleboy007
Messages: 282
Registered: November 2010
Location: Canada
|
Senior Member |
|
|
Hi all,
I have created a procedure where in I am trying to generate an error message saying"Table is missing from the database" when an id (2617804) is being passed. For sure the table name does not exist, so instead of the error being handled by WHEN OTHERS I want it to be handled by my own error message and however that's failing as the control goes to "WHEN OTHERS".
Can any one help me out?
Here's the code:
Package:
CREATE OR REPLACE PACKAGE CTNAPP.SANDEEP_XMLXTRACT IS
/*****************************************************************************
* Global Public Variables for error handling
*****************************************************************************/
g_vProgramName VARCHAR2(30):= 'CNZ017';
g_vPackageName VARCHAR2(30):= 'CTN_PUB_CNTL_EXTRACT_PUBLISH';
g_vProcedureName VARCHAR2(30);
g_vTableName VARCHAR2(30);
g_nSqlCd NUMBER;
g_vErrorMessage VARCHAR2(2000);
/*****************************************************************************
* Global Public Variables
*****************************************************************************/
--Type declarations for GetCtnData procedure
TYPE g_tVCArrayTyp IS
TABLE OF VARCHAR2(32767)
INDEX BY BINARY_INTEGER;
g_tVarcharArray g_tVCArrayTyp;
TYPE g_tTblIDsTyp IS
TABLE OF NUMBER
INDEX BY BINARY_INTEGER;
PROCEDURE GetCtnData(p_nInCtnPubCntlID IN ctn_pub_cntl.ctn_pub_cntl_id%TYPE
, p_tOutVarCharArray OUT g_tVCArrayTyp
, pCount OUT NUMBER);
Package body:
CREATE OR REPLACE PACKAGE BODY CTNAPP.SANDEEP_XMLXTRACT IS
-- Local Variables
XMLctx DBMS_XMLGEN.CTXHANDLE;
XMLdoc xmldom.DOMDocument;
root_node xmldom.DOMNode;
child_node xmldom.DOMNode;
child_elmt xmldom.DOMElement;
leaf_node xmldom.DOMNode;
elmt_value xmldom.DOMText;
vStrSqlQuery VARCHAR2(32767);
nKiloByteLimit NUMBER(6) := 30000;
dCurrentDate TIMESTAMP := SYSTIMESTAMP;
-- Private Procedures
/************************************************************************
*NAME : BuildCPRHeader
*TYPE : FUNCTION
*INPUT :
*OUTPUT :
*DESCRIPTION :
*
*************************************************************************/
FUNCTION BuildCPRHeader RETURN VARCHAR2 IS
vpublishHdr VARCHAR2(2000) := NULL;
BEGIN
XMLdoc := xmldom.newdomdocument;
root_node := xmldom.makeNode(XMLdoc);
child_elmt := xmldom.createElement(XMLdoc, 'PUBLISH_HEADER');
child_node := xmldom.appendChild (root_node, xmldom.makeNode (child_elmt));
child_elmt := xmldom.createElement (XMLdoc, 'SOURCE_APLCTN_ID');
elmt_value := xmldom.createTextNode (XMLdoc, 'CTN');
leaf_node := xmldom.appendChild (child_node, xmldom.makeNode (child_elmt));
leaf_node := xmldom.appendChild (leaf_node, xmldom.makeNode (elmt_value));
child_elmt := xmldom.createElement (XMLdoc, 'SOURCE_PRGRM_ID');
elmt_value := xmldom.createTextNode (XMLdoc, g_vProgramName);
leaf_node := xmldom.appendChild (child_node, xmldom.makeNode (child_elmt));
leaf_node := xmldom.appendChild (leaf_node, xmldom.makeNode (elmt_value));
child_elmt := xmldom.createElement (XMLdoc, 'SOURCE_CMPNT_ID');
elmt_value := xmldom.createTextNode (XMLdoc, g_vPackageName);
leaf_node := xmldom.appendChild (child_node, xmldom.makeNode (child_elmt));
leaf_node := xmldom.appendChild (leaf_node, xmldom.makeNode (elmt_value));
child_elmt := xmldom.createElement (XMLdoc, 'PUBLISH_TMS');
elmt_value := xmldom.createTextNode (XMLdoc, TO_CHAR(dCurrentDate, 'YYYY-MM-DD HH24:MI:SS'));
leaf_node := xmldom.appendChild (child_node, xmldom.makeNode (child_elmt));
leaf_node := xmldom.appendChild (leaf_node, xmldom.makeNode (elmt_value));
xmldom.writetobuffer(XMLdoc, vPublishHdr);
RETURN vPublishHdr;
END BuildCPRHeader;
PROCEDURE GetCtnData(p_nInCtnPubCntlID IN ctn_pub_cntl.ctn_pub_cntl_id%TYPE,
p_tOutVarCharArray OUT g_tVCArrayTyp,
pCount OUT NUMBER)
IS
vTblName ctn_pub_cntl.table_name%TYPE;
vLastPubTms ctn_pub_cntl.last_pub_tms%TYPE;
l_clob CLOB;
nCount PLS_INTEGER;
nLength PLS_INTEGER;
noffset PLS_INTEGER;
table_not_found EXCEPTION;
PRAGMA EXCEPTION_INIT(table_not_found, -00942);
BEGIN
g_vProcedureName:='GetCtnData';
SELECT table_name, last_pub_tms
INTO vTblName, vLastPubTms
FROM CTN_PUB_CNTL
WHERE ctn_pub_cntl_id = p_nInCtnPubCntlID;
IF sql%ROWCOUNT = 0 THEN
RAISE no_data_found;
END IF;
DBMS_SESSION.SET_NLS('NLS_DATE_FORMAT','''YYYY:MM:DD HH24:MI:SS''');
vStrSqlQuery := 'SELECT * FROM ' || vTblName
|| ' WHERE record_update_tms <= TO_DATE(''' || TO_CHAR(vLastPubTms, 'MM/DD/YYYY HH24:MI:SS') || ''', ''MM/DD/YYYY HH24:MI:SS'')'
|| ' AND rownum < 2'
|| ' ORDER BY record_update_tms'
;
XMLctx := DBMS_XMLGEN.NEWCONTEXT(vStrSqlQuery);
DBMS_XMLGEN.SETNULLHANDLING(XMLctx, 2);
DBMS_XMLGEN.SETROWSETTAG(XMLctx, vTblName);
l_clob := DBMS_XMLGEN.GETXML(XMLctx);
l_clob := REPLACE(l_clob, '<?xml version="1.0"?>', '');
l_clob := '<?xml version="1.0"?>' || CHR(13) || CHR(10)
|| '<PUBLISH> ' || CHR(13) || CHR(10)
|| BuildCPRHeader
|| '<PUBLISH_BODY> '
|| l_clob
|| '</PUBLISH_BODY> ' || CHR(13) || CHR(10)
|| '</PUBLISH>';
-- l_clob := '<' || vTblName || '/>';
nLength := DBMS_LOB.getlength(l_clob);
nCount := CEIL(nLength / nKiloByteLimit);
noffset := 1;
IF (nCount > 0) THEN
FOR i in 1 .. nCount LOOP
p_tOutVarCharArray(i) := DBMS_LOB.SUBSTR(l_clob, nKiloByteLimit, noffset);
noffset := noffset + nKiloByteLimit;
dbms_output.put_line(p_tOutVarCharArray(i));
END LOOP;
END IF;
pCount := nCount;
EXCEPTION
WHEN table_not_found THEN
RAISE_APPLICATION_ERROR(-20001,'Table is missing from the database');
WHEN no_data_found THEN
CTNAPP_COMMON.write_log(g_vPackageName, g_vProcedureName, NULL,'INFORMATIONAL','XMLTOSAP');
--WHEN too_many_rows THEN
-- CTNAPP_COMMON.write_log(g_vPackageName, g_vProcedureName, NULL,'FATAL','XMLTOSAP');
WHEN others THEN
CTNAPP_COMMON.write_log(g_vPackageName, g_vProcedureName,NULL, 'ERROR','XMLTOSAP');
END GetCtnData;
and now calling my own procedure:
CREATE OR REPLACE PROCEDURE CTNAPP.SANDEEP_TEST_LAMXML
IS
l_tOutVarCharArray SANDEEP_XMLXTRACT.g_tVCArrayTyp;
nCount NUMBER(5);
BEGIN
SANDEEP_XMLXTRACT.GetCtnData(2617804, l_tOutVarCharArray,nCount);
END;
/
where in 2617804 is an id which has a table name called "sandeep" and this "sandeep" is being passed dynamically (as there could be someother table name in the future) into vStrSqlQuery variable.
Can anyone let me know where I am going wrong. I tried a small prototype with PRAGMA EXCEPTION_INIT(which worked successfully) before building the error handler for production program.
|
|
|
|
|
|
|
|
|
|
| Re: PRAGMA EXCEPTION_INIT not working as desired [message #637068 is a reply to message #637067] |
Sat, 09 May 2015 17:52  |
buggleboy007
Messages: 282
Registered: November 2010
Location: Canada
|
Senior Member |
|
|
Issue is addressed. Please don't spend any further time on this.
I just passed "EXECUTE IMMEDIATE vStrSqlQuery;" after the "vStrSqlQuery" and that fixed the issue. It straight away went to the error handler that I wanted it to (WHEN table_not_found THEN) as opposed to "When OTHERS".
For those of whom who took time to go through the code, Thanks a lot! Much appreciated.
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Fri Apr 19 19:56:22 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
