About DBMS_XMLSTORE

[dodolin]

environment : oracle 10g

I have a small function as following:

FUNCTION insertInRawTable (xmlDoc IN CLOB, tableName IN VARCHAR2) RETURN PLS_INTEGER IS
		
	CURSOR columnCursor (tableName VARCHAR2) IS
		SELECT column_name FROM all_tab_columns
		WHERE table_name = tableName;
		
	cursorRecord columnCursor%ROWTYPE;
	insCtx DBMS_XMLSTORE.ctxType;
	rows PLS_INTEGER;
	
BEGIN
	--DBMS_OUTPUT.PUT_LINE(tableName);
		
	IF DBMS_LOB.INSTR(xmlDoc, tableName, 1, 1) > 0 
	THEN
	    insCtx := DBMS_XMLSTORE.newContext(tableName); -- Get saved context
	   DBMS_XMLSTORE.clearUpdateColumnList(insCtx); -- Clear the update settings
	   DBMS_XMLSTORE.setRowTag(insCtx, tableName);
	-- Set the columns to be updated as la list of values
	   FOR cursorRecord IN columnCursor(tableName)
	   LOOP 
	       DBMS_XMLSTORE.setUpdateColumn(insCtx, cursorRecord.column_name);
	   END LOOP;
			
	    -- Insert the doc.
	   rows := DBMS_XMLSTORE.insertXML(insCtx, xmlDoc);

	    -- Close the context
	   DBMS_XMLSTORE.closeContext(insCtx);
        ELSE
	     rows := 0;
	END IF;
		
           RETURN rows;
END;

And I tried to run this function on SQL*PlUS with commands:

SQL>variable nb number;
SQL>execute :nb := insertInRawTable('8TestConfig7993999910001234', 
'RAWPLL');

The compilation is OK. And the total number of records inserted is 1.
But when I used

SELECT fecSlot FROM rawPLL;

I got the answer like:

FECSLOT
------------

There is NOTHING printed. It didn't show "no data found" but "NOTHING"!!!
Could you help me to understand what happened??

Thanks very much

[ales]

Try to post the XML document again. Seems that it was parsed in your former post. A description of the table would be helpful as well.

[dodolin]

XML doc is :

'<ROWSET><RAWPLL><fecSlot>8</fecSlot><fecHardId>TestConfig</fecHardId>7<ringSlot></ringSlot><ccuAddress>99</ccuAddress><mbType>3</mbType><i2cChannel>99</i2cChannel><i2cAddress>99</i2cAddress><delayCoarse>1000</delayCoarse><delayFine>1234</delayFine></RAWPLL></ROWSET>' 

And the table RawPll is:

CREATE GLOBAL TEMPORARY TABLE RawPll (
	fecSlot 		NUMBER,
	fecHardId 	VARCHAR2(200),
	ringSlot 		NUMBER,
	ccuAddress 	NUMBER,
	mbType 		VARCHAR2(200),
	i2cChannel 	NUMBER,
	i2cAddress 	NUMBER,
	delayCoarse         NUMBER,
	delayFine 	NUMBER
) ON COMMIT DELETE ROWS;

[ales]

The XML tag and column name matching is case sensitive. Either convert the tags to uppercase or double-quote the column names in the create table statement.
Both ways worked for me. I prefer using upercase tags.

[dodolin]

Thanks for your help.
The problem is fixed as following your indication.