read XML file into stored procedure

[Former Member]

hi to all, i have this stored procedure :

create or replace

PROCEDURE INSERTXML(

p_xml_in CLOB,

p_table IN VARCHAR2 ) AS

insCtx DBMS_XMLStore.ctxType;

v_rows NUMBER;

BEGIN

insCtx := DBMS_XMLStore.newContext(p_table); -- get saved context

dbms_xmlstore.setRowTag(insCtx,'Row');

DBMS_XMLStore.clearUpdateColumnList(insCtx); -- clear the update settings

-- set the columns to be updated as a list of values

DBMS_XMLStore.setUpdateColumn(insCtx,'ORDERNUMBER');

DBMS_XMLStore.setUpdateColumn(insCtx,'PLANT');

DBMS_XMLStore.setUpdateColumn(insCtx,'MATERIAL');

DBMS_XMLStore.setUpdateColumn(insCtx,'LINENUMBER');

DBMS_XMLStore.setUpdatecolumn(insCtx,'NOMSPEED');

DBMS_XMLStore.setUpdatecolumn(insCtx,'STARTDATE');

DBMS_XMLStore.setUpdatecolumn(insCtx,'FINISHDATE');

DBMS_XMLStore.setUpdatecolumn(insCtx,'TARGETQTY');

DBMS_XMLStore.setUpdatecolumn(insCtx,'UNIT');

DBMS_XMLStore.setUpdatecolumn(insCtx,'SYSTEMSTATUS');

v_rows := DBMS_XMLStore.insertXML(insCtx, p_xml_in);

-- Close the context

DBMS_XMLStore.closeContext(insCtx);

END;

that passes xml file and puts in table but SAP Mii have the limitation that the maximun value of strings is 4000..how can i pass this limitation?!?! someone have some ideia?!?!...i have now to read XML file to stored procedure...could someone help me??

regards and thanks in advance

[Former Member]

Mario,

please have a look at the following thread:

[https://forums.sdn.sap.com/click.jspa?searchID=25190303&messageID=6841535]

I have gone through the implementation of the bulk insert using the Oracle XML SQL Utilities and described a possible solution.

There is a limit you will not be able to pass (32KB for PL/SQL), so you will have to devide your XML into chunks. But it works great and fast.

Hope this helps.

Michael

[Former Member]

i MIchael, do you know if we could break the xml file in SAP Mii?!?!

regards

Mário

[Former Member]

Mario,

are you building your xml file in MII?

I have build a solution where we construct the xml in MII, appending the rows into the xml one by one. After each loop I check the size of the xml using the stringlength function with the xml. If the size exceeds 30000, I call the query, init the xml and proceed with the remaining rows.

Michael

[Former Member]

yes, iam building the xml in Mii.

[Former Member]

Could you post were the query or send to email?!?!

regards and thanks a lot for the help

Mário

[Former Member]

Mario,

where exactly do you need help?

An example of the query is included in the thread I have mentioned above. The transaction which calls the query use the same repeater you use to build the xml. After appending a row to the xml it checks the stringlength and calls the query if it exceeds a defined size (30K).

Are you unsure how to extend your transaction/query, or do you get an error using it?

Michael

[Former Member]

hello Michael i need help in your stored procedure:

create or replace PROCEDURE INSERTXML4

(xmlDoc in CLOB, tableName in VARCHAR2, refcur in out

regards abd thanks for your help

Mário

[Former Member]

Mario,

we used to define a package where we define the ref cursor:

create or replace PACKAGE PKG_MII_BULK_CHANGE AS

  type return_cur IS ref CURSOR;

  procedure insertXMLdoc(
    xmlDoc IN CLOB, 
    tableName IN VARCHAR2, 
    myRC IN OUT return_cur
  );

END PKG_MII_BULK_CHANGE;

The package name is the definition you can use in your procedure which is defined in the package:

create or replace PROCEDURE INSERTXMLDOC 
(xmlDoc in CLOB, tableName in VARCHAR2, refcur in out return_cur)
...

Alternatively you can use the ref cursor with the Package name:

create or replace PROCEDURE INSERTXMLDOC 
(xmlDoc in CLOB, tableName in VARCHAR2, refcur in out PKG_MII_BULK_CHANGE.return_cur)
...

Hope this helps.

Michael

[Former Member]

hello Michael thanks for your help but appear to me some strange errors, i created this package in Oracle SQL Developer...

create or replace PACKAGE PACKAGE_XML AS

type return_cur IS ref CURSOR;

procedure INSERTXML4(

xmlDoc IN CLOB,

tableName IN VARCHAR2,

myRC IN OUT return_cur

);

END PACKAGE_XML;

and compile and everthing is ok, then i changed my SP and it looks like this:

create or replace PROCEDURE INSERTXML4

(xmlDoc in CLOB, tableName in VARCHAR2, refcur in out package_xml.return_cur)

AS

insCtx dbms_xmlsave.ctxType;

rowcnt number;

BEGIN

insCtx := dbms_xmlsave.newContext(tableName);

dbms_xmlsave.setignorecase(insCtx,1);

rowcnt := dbms_xmlsave.insertxml(insCtx, xmlDoc);

dbms_xmlsave.closecontext(insCtx);

OPEN refcur FOR SELECT rowcnt AS cnt FROM dual;

END INSERTXML4;

but i cant compile...it throws this errors:

Error(5,10): PLS-00201: identifier 'DBMS_XMLSAVE.CTXTYPE' must be declared

Error(5,10): PL/SQL: Item ignored

Error(8,3): PLS-00320: the declaration of the type of this expression is incomplete or malformed

Error(8,3): PL/SQL: Statement ignored

Error(9,3): PL/SQL: Statement ignored

Error(9,30): PLS-00320: the declaration of the type of this expression is incomplete or malformed

Error(10,3): PL/SQL: Statement ignored

Error(10,13): PLS-00201: identifier 'DBMS_XMLSAVE.INSERTXML' must be declared

Error(11,3): PL/SQL: Statement ignored

Error(11,29): PLS-00320: the declaration of the type of this expression is incomplete or malformed

do you could help..

regards

Mário

[Former Member]

Mario,

does you DB has the XMLSAVE installed? In your post above you used XMLSTORE (I guess you can use both).

If the error persists, please ask your DB Admin for help.

Good luck.

Michael