| Oracle PL/SQL trying to use XSL and XMLSAVE [message #295241] |
Mon, 21 January 2008 19:48  |
cakask
Messages: 11
Registered: January 2008
|
Junior Member |
|
|
We are using Oracle 9.2.0.8.0
I am trying to execute the following code to update the view - which actually means executing the INSTEAD OF trigger. . . But while it is executing without "error" (blowing up), it makes the rowcount = 0 and the INSTEAD OF trigger does NOT get executed.
I am going crazy trying to figure out how to TEST this code - and the point was to verify that the XSLT was correct.
One other big question is what is the correct format of
the clause: (in the example here - the XML tags have the '-' dashes and the ORacle Columns have the '_' Underscores)
<BATCH-UNIQUE-ID><xsl:value-of select="GVN-RECORDS/Header/BATCH_UNIQUE_ID"/></BATCH-UNIQUE-ID>
SO I am assuming - because I cannot find ANYTHING after hours of searching - that the format is
<TAG><xsl:value-of select="path/COLUMNNAME"/></TAG>
The body of the code is here:
l_ctx := DBMS_XMLSave.newContext('V_VEND_XML_H');
DBMS_XMLSave.setXSLT(l_ctx, GC_ifmis_vend_h_xsl_to_canonic, null);
DBMS_XMLSave.setDateFormat(l_ctx,'YYYYMMDD');
DBMS_XMLSave.setIgnoreCase(l_ctx, 1); --IGNORE_CASE.
DBMS_XMLSave.clearKeyColumnList(l_ctx);
DBMS_XMLSave.clearUpdateColumnList(l_ctx);
DBMS_XMLSave.setKeyColumn(l_ctx,'BATCH_UNIQUE_ID');
DBMS_XMLSave.setUpdateColumn(l_ctx,'ADD_COUNT');
DBMS_XMLSave.setUpdateColumn(l_ctx,'MODIFY_COUNT');
DBMS_XMLSave.setUpdateColumn(l_ctx,'DELETE_COUNT');
DBMS_XMLSave.setUpdateColumn(l_ctx,'REACTIVATE_COUNT');
DBMS_XMLSave.setUpdateColumn(l_ctx,'TRANSACTION_DATE');
DBMS_XMLSave.setUpdateColumn(l_ctx,'ACTION_TIME');
DBMS_XMLSave.setCommitBatch(l_ctx, 0);
V_rowcount := DBMS_XMLSave.updateXML(l_ctx, v_clob);
A sample segment of the XML is shown here...
<?xml version = '1.0'?>
<GVN-RECORDS>
<Header>
<BATCH-UNIQUE-ID>ABCDE</BATCH-UNIQUE-ID>
<ADD-COUNT>0</ADD-COUNT>
<MODIFY-COUNT>2</MODIFY-COUNT>
<DELETE-COUNT>0</DELETE-COUNT>
<REACTIVATE-COUNT>9</REACTIVATE-COUNT>
<TRANSACTION-DATE>20071227</TRANSACTION-DATE>
<ACTION-TIME>100805</ACTION-TIME>
<Detail>
<TRAN-TYPE>M</TRAN-TYPE>
</Detail>
</Header>
</GVN-RECORDS>
And my XSL is shown here:
GC_ifmis_vend_h_xsl_to_canonic constant clob :=
'<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:output method="xml" indent="yes"/>
<xsl:template match="/">
<ROWSET>
<ROW>
<BATCH-UNIQUE-ID><xsl:value-of select="GVN-RECORDS/Header/BATCH_UNIQUE_ID"/></BATCH-UNIQUE-ID>
<ADD-COUNT><xsl:value-of select="GVN-RECORDS/Header/ADD_COUNT"/></ADD-COUNT>
<MODIFY-COUNT><xsl:value-of select="GVN-RECORDS/Header/MODIFY_COUNT"/></MODIFY-COUNT>
<DELETE-COUNT><xsl:value-of select="GVN-RECORDS/Header/DELETE_COUNT"/></DELETE-COUNT>
<REACTIVATE-COUNT><xsl:value-of select="GVN-RECORDS/Header/REACTIVATE_COUNT"/></REACTIVATE-COUNT>
<TRANSACTION-DATE><xsl:value-of select="GVN-RECORDS/Header/TRANSACTION_DATE"/></TRANSACTION-DATE>
<ACTION-TIME><xsl:value-of select="GVN-RECORDS/Header/ACTION_TIME"/></ACTION-TIME>
</ROW>
</ROWSET>
</xsl:template>
</xsl:stylesheet>';
I hope I have supplied enough info - but I would be happy to add dtls if needed
=====================
New info - on my way to my stroke  , I tried one more thing. In my test file, I changed the XML tags to make all of the dashes(-) into underscores (_) and then changed the references in the PL/SQL code (in the XSL) to also only have underscores. FINALLY that worked. . BUT I had been instructed that I HAD to use the tags as they were defined - meaning with the dashes(-).
So I guess I still need to know - as I have been unable to find any examples after HOURS of searching - of how to code the XSL / Oracle column mapping in the example shown above.
Thanks so much for any ideas.
[Updated on: Mon, 21 January 2008 21:04] Report message to a moderator |
|
|
|
| Re: Oracle PL/SQL trying to use XSL and XMLSAVE [message #295875 is a reply to message #295241] |
Wed, 23 January 2008 15:50  |
cakask
Messages: 11
Registered: January 2008
|
Junior Member |
|
|
Ok this is the OP - More info.
After DAYS of searching - I find that the for XMLSAVE, it appears that the TAG Names MUST match the COLUMN Names.(one line, just thrown into what appeared to be an irrelevant section in one of the dozens of documents I looked at) Therefore what I had would not work.
I tested by changing the tags to have Underscores instead of Dashes, and changed the XSL and PL/SQL to all have Underscores - as is the case in the Oracle Table.
NOW - My problem is that I STILL AM required to keep the Tags as they are defined - with DASHES and not Underscores.
Sooo, I was able to create the View with column names that MATCH the tags see sample below - BUT I am having a Devil of a time trying to get the INSTEAD OF Trigger to compile and Accept the dashes!
(I have slightly altered this because of privacy issues with my client.)
CREATE OR REPLACE VIEW V_VEND_XML_H
AS
SELECT UNIQUE_KEY "BATCH-UNIQUE-ID",
sum( ADD_COUNT ) "ADD-COUNT",
sum( MODIFY_COUNT ) "MODIFY-COUNT",
sum( DELETE_COUNT ) "DELETE-COUNT",
sum( REACTIVATE_COUNT ) "REACTIVATE-COUNT",
sysdate "TRANSACTION-DATE",
to_char(sysdate,'HH24MISS') "ACTION-TIME"
FROM ORIGINAL_TABLE;
1. So again if there an OracleFunction where I can JUST translate the tags to have Underscores instead of Dashes - note that I have to change ONLY the tags, my data may contain Dashes.
2. Does anyone know whether (how to) you can use Column Names with embedded dashes in this combination - The View, DBMS_XMLSAVE, and the (Instead of) Trigger.
Again thanks for ideas.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
