Home » Developer & Programmer » JDeveloper, Java & XML » PLS-00307: too many declarations of 'INSERTXML' match this call (Oracle Database, 10.2.0.1.0 , Widows 2000 server)
PLS-00307: too many declarations of 'INSERTXML' match this call [message #343366] Wed, 27 August 2008 01:25 Go to next message
tarikkuet@yahoo.com
Messages: 3
Registered: August 2008
Junior Member
Dear All,

I need help to create a procedure. I am describing the scenario below.

I have a table which is

xml_clob(
doc_id number,
doc clob
)

The above table contain an xml file in doc column. What i need is read the doc column and parse and insert the xml data to a table. For this purpose I am using a built-in package in oracle database 10g name dbms_xmlsave. To full fill my purpose i am going to create a procedure which is getting error. I am giving the procedure code

CREATE OR REPLACE PROCEDURE loadxml1 AS
fil clob;
buffer varchar2(1000);
len INTEGER;
insrow INTEGER;
BEGIN

SELECT doc INTO fil FROM xml_clob WHERE doc_id=1;

len := DBMS_LOB.GETLENGTH(fil);
DBMS_OUTPUT.PUT_LINE('length '||len);

DBMS_LOB.READ(fil,len,1,buffer);
--xmlgen.resetOptions;
--insrow := xmlgen.insertXML('xml_doc',buffer);

insrow := dbms_xmlsave.insertXML('xml_doc',buffer);

--insrow := dbms_xmlsave.insertXML('xml_doc',fil);
DBMS_OUTPUT.PUT_LINE('length ins '||insrow);

EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('In Exception');
DBMS_OUTPUT.PUT_LINE(SQLERRM(SQLCODE));

end;
/

i am getting the error with the bold line

insrow := dbms_xmlsave.insertXML('xml_doc',buffer);

PLS-00307: too many declarations of 'INSERTXML' match this call

I need help on this. Here the argument xml_doc is the table name where i want to insert data from xml.

I am giving u the code of dbms_smlsave package which is at sys user.

CREATE OR REPLACE PACKAGE DBMS_XMLSAVE AUTHID CURRENT_USER AS

SUBTYPE ctxType IS NUMBER; /* context type */

DEFAULT_ROWTAG CONSTANT VARCHAR2(3) := 'ROW'; /* rowtag */
DEFAULT_DATE_FORMAT CONSTANT VARCHAR2(21):= 'YYYY-MM-DD HH24:MI:SS';

MATCH_CASE CONSTANT NUMBER := 0; /* match case */
IGNORE_CASE CONSTANT NUMBER := 1; /* ignore case */




--------------------------------------------------------------------------------
constructor/destructor functions
--------------------------------------------------------------------------------
FUNCTION newContext(targetTable IN VARCHAR2) RETURN ctxType;
PROCEDURE closeContext(ctxHdl IN ctxType);


--------------------------------------------------------------------------------
parameters to the save (XMLtoDB) engine
--------------------------------------------------------------------------------
PROCEDURE setXSLT(ctxHdl IN ctxType,uri IN VARCHAR2,ref IN VARCHAR2 := null);
PROCEDURE setXSLT(ctxHdl IN ctxType, stylesheet IN CLOB, ref IN VARCHAR2 := null);
PROCEDURE setXSLTParam(ctxHdl IN ctxType,name IN VARCHAR2,value IN VARCHAR2);
PROCEDURE removeXSLTParam(ctxHdl IN ctxType, name IN VARCHAR2);

PROCEDURE setRowTag(ctxHdl IN ctxType, tag IN VARCHAR2);
PROCEDURE setSQLToXMLNameEscaping(ctxHdl IN ctxType, flag IN BOOLEAN := true);
PROCEDURE setPreserveWhitespace(ctxHdl IN ctxType, flag IN BOOLEAN := true);
PROCEDURE setIgnoreCase(ctxHdl IN ctxType, flag IN NUMBER);

PROCEDURE setDateFormat(ctxHdl IN ctxType, mask IN VARCHAR2);

PROCEDURE setBatchSize(ctxHdl IN ctxType, batchSize IN NUMBER);
PROCEDURE setCommitBatch(ctxHdl IN ctxType, batchSize IN NUMBER);

-- set the columns to update. Relevant for insert and update routines..
PROCEDURE setUpdateColumn(ctxHdl IN ctxType, colName IN VARCHAR2);
PROCEDURE clearUpdateColumnList(ctxHdl IN ctxType);

-- set the key column name to be used for updates and deletes.
PROCEDURE setKeyColumn(ctxHdl IN ctxType, colName IN VARCHAR2);
PROCEDURE clearKeyColumnList(ctxHdl IN ctxType);


--------------------------------------------------------------------------------
save
--------------------------------------------------------------------------------
-- insertXML
FUNCTION insertXML(ctxHdl IN ctxType, xDoc IN VARCHAR2) RETURN NUMBER;
FUNCTION insertXML(ctxHdl IN ctxType, xDoc IN CLOB) RETURN NUMBER;
-- updateXML
FUNCTION updateXML(ctxHdl IN ctxType, xDoc IN VARCHAR2) RETURN NUMBER;
FUNCTION updateXML(ctxHdl IN ctxType, xDoc IN CLOB) RETURN NUMBER;
-- deleteXML
FUNCTION deleteXML(ctxHdl IN ctxType, xDoc IN VARCHAR2) RETURN NUMBER;
FUNCTION deleteXML(ctxHdl IN ctxType, xDoc IN CLOB) RETURN NUMBER;


--------------------------------------------------------------------------------
misc
--------------------------------------------------------------------------------
PROCEDURE propagateOriginalException(ctxHdl IN ctxType, flag IN BOOLEAN);
PROCEDURE getExceptionContent(ctxHdl IN ctxType, errNo OUT NUMBER, errMsg OUT VARCHAR2);
PROCEDURE useDBDates(ctxHdl IN ctxType, flag IN BOOLEAN := true);

-------private method declarations------------------------------------------
-- we must do this as a bug workaround; otherwise we get ora-600 kgmexchi11
PROCEDURE p_useDBDates(ctxHdl IN ctxType, flag IN NUMBER);
PROCEDURE p_setXSLT(ctxHdl IN ctxType, uri IN VARCHAR2, ref IN VARCHAR2);
PROCEDURE p_setXSLT(ctxHdl IN ctxType, stylesheet CLOB, ref IN VARCHAR2);
PROCEDURE p_propagateOriginalException(ctxHdl IN ctxType, flag IN NUMBER);
PROCEDURE p_setSQLToXMLNameEsc(ctxHdl IN ctxType, flag IN NUMBER);
PROCEDURE p_setPreserveWhitespace(ctxHdl IN ctxType, flag IN NUMBER);

END dbms_xmlsave;
/

CREATE OR REPLACE PACKAGE BODY DBMS_XMLSAVE AS

FUNCTION insertXML(ctxHdl IN ctxType, xDoc IN VARCHAR2) RETURN NUMBER
as LANGUAGE JAVA NAME
'oracle.xml.sql.dml.OracleXMLStaticSave.insertXML(int, java.lang.String) return int';

FUNCTION insertXML(ctxHdl IN ctxType, xDoc IN CLOB) RETURN NUMBER
as LANGUAGE JAVA NAME
'oracle.xml.sql.dml.OracleXMLStaticSave.insertXML(int, oracle.sql.CLOB) return int';
END DBMS_XMLSAVE;
/
Re: PLS-00307: too many declarations of 'INSERTXML' match this call [message #343398 is a reply to message #343366] Wed, 27 August 2008 03:33 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Quote:

SUBTYPE ctxType IS NUMBER; /* context type */

dbms_xmlsave.insertXML('xml_doc',buffer);

FUNCTION insertXML(ctxHdl IN ctxType, xDoc IN VARCHAR2) RETURN NUMBER;


I am not sure what you are doing trying to do here. May be something like this.
http://www.oracle.com/technology/oramag/oracle/05-sep/o55xml.html
Trying to re-implement what is already done by oracle or you are customising it for your needs. But I think the error is more detailed enough to tell what the problem is
Quote:

PLS-00307: too many declarations of "string" match this call
Cause: The declaration of a subprogram or cursor name is ambiguous because there was no exact match between the declaration and the call and more than one declaration matched the call when implicit conversions of the parameter datatypes were used. The subprogram or cursor name might be misspelled, its declaration might be faulty, or the declaration might be placed incorrectly in the block structure.
Action: Check the spelling and declaration of the subprogram or cursor name. Also confirm that its call is correct, its parameters are of the right datatype, and, if it is not a built-in function, that its declaration is placed correctly in the block structure.



Regards

Raj

P.S : Please format the post from next time
Re: PLS-00307: too many declarations of 'INSERTXML' match this call [message #343415 is a reply to message #343366] Wed, 27 August 2008 04:27 Go to previous messageGo to next message
Michel Cadot
Messages: 58327
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
CLOB and VARCHAR2 are same kind of datatype, Oracle does not know which one to use.
Remove VARCHAR2 one.

Regards
Michel
Re: PLS-00307: too many declarations of 'INSERTXML' match this call [message #343661 is a reply to message #343366] Wed, 27 August 2008 14:58 Go to previous messageGo to next message
Barbara Boehmer
Messages: 7927
Registered: November 2002
Location: California, USA
Senior Member
SCOTT@orcl_11g> CREATE TABLE xml_clob
  2    (doc_id	NUMBER,
  3  	doc	clob)
  4  /

Table created.

SCOTT@orcl_11g> INSERT INTO xml_clob
  2  SELECT 1, DBMS_XMLGEN.GETXML ('SELECT * FROM dept') FROM DUAL
  3  /

1 row created.

SCOTT@orcl_11g> SELECT * FROM xml_clob
  2  /

    DOC_ID DOC
---------- --------------------------------------------------------------------------------
         1 <?xml version="1.0"?>
           <ROWSET>
            <ROW>
             <DEPTNO>10</DEPTNO>
             <DNAME>ACCOUNTING</DNAME>
             <LOC>NEW YORK</LOC>
            </ROW>
            <ROW>
             <DEPTNO>20</DEPTNO>
             <DNAME>RESEARCH</DNAME>
             <LOC>DALLAS</LOC>
            </ROW>
            <ROW>
             <DEPTNO>30</DEPTNO>
             <DNAME>SALES</DNAME>
             <LOC>CHICAGO</LOC>
            </ROW>
            <ROW>
             <DEPTNO>40</DEPTNO>
             <DNAME>OPERATIONS</DNAME>
             <LOC>BOSTON</LOC>
            </ROW>
           </ROWSET>


SCOTT@orcl_11g> CREATE TABLE xml_doc AS SELECT * FROM dept WHERE 1 = 2
  2  /

Table created.

SCOTT@orcl_11g> SELECT * FROM xml_doc
  2  /

no rows selected

SCOTT@orcl_11g> CREATE OR REPLACE PROCEDURE loadxml1
  2  AS
  3    fil     CLOB;
  4    insrow  INTEGER;
  5    ctx     DBMS_XMLSAVE.CTXTYPE;
  6  BEGIN
  7    SELECT doc INTO fil FROM xml_clob WHERE doc_id = 1;
  8    ctx := DBMS_XMLSAVE.NEWCONTEXT ('xml_doc');
  9    insrow := DBMS_XMLSAVE.INSERTXML (ctx, fil);
 10  END loadxml1;
 11  /

Procedure created.

SCOTT@orcl_11g> SHOW ERRORS
No errors.
SCOTT@orcl_11g> EXEC loadxml1

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> SELECT * FROM xml_doc
  2  /

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

SCOTT@orcl_11g>

Re: PLS-00307: too many declarations of 'INSERTXML' match this call [message #343855 is a reply to message #343661] Thu, 28 August 2008 06:20 Go to previous messageGo to next message
tarikkuet@yahoo.com
Messages: 3
Registered: August 2008
Junior Member
Thankx for reply.

But still i have some problem.

What i need is

CREATE TABLE xml_clob
2 (doc_id NUMBER,
3 doc clob)

in xml_clob table i want to insert data to doc colum from an os xml file say located as c:\xml_dir\test.xml.

For this i have tried the procedure given below

CREATE OR REPLACE PROCEDURE insertXML (dirname IN VARCHAR2,
filename IN VARCHAR2)
IS
xmlfile BFILE;
myclob CLOB;
BEGIN
INSERT INTO xml_clob (doc_id, doc)
VALUES (filename, empty_clob())
RETURNING doc into myclob;

-- get a handle to the xml file on the OS
xmlfile := Bfilename(dirname,filename);

-- open the file
DBMS_LOB.fileOpen(xmlfile);

-- copy the contents of the file into the empty clob
DBMS_LOB.loadFromFile(myclob, xmlfile, dbms_lob.getLength(xmlfile));

END insertXML;
/

I have created directory

create or replace directory xml_dir as 'c:\xml_dir'

and my xml file is test.xml located at c:\xml_dir

i am executing the procedure

execute insertXML ('dir_xml','test.xml') ;

but facing the errors.

Error on line 0
begin
insertXML ('dir_xml','test.xml');
end;

ORA-22285: non-existent directory or file for FILEOPEN operation
ORA-06512: at "SYS.DBMS_LOB", line 523
ORA-06512: at "AB_070808.INSERTXML", line 15
ORA-06512: at line 2


What may be the solution or any other way to insert a file content to a clob colum. Pls response. It will help me so much.

Thanking u in advance
Re: PLS-00307: too many declarations of 'INSERTXML' match this call [message #344107 is a reply to message #343855] Thu, 28 August 2008 15:31 Go to previous messageGo to next message
Barbara Boehmer
Messages: 7927
Registered: November 2002
Location: California, USA
Senior Member
You have created xml_dir, but passed a different name, dir_xml as a parameter. Also, Oracle directory objects are by default created in upper case, just like table names and other objects, so you must reference them in upper case when they are enclosed in quotes. So, you need to pass 'XML_DIR' in upper case as a parameter, not 'xml_dir' in lower case. Please see the demonstration below.

SCOTT@orcl_11g> CREATE TABLE xml_clob
  2    (doc_id	VARCHAR2 (8),
  3  	doc	CLOB)
  4  /

Table created.

SCOTT@orcl_11g> -- XML_DIR IS CREATED IN UPPER CASE:
SCOTT@orcl_11g> CREATE OR REPLACE DIRECTORY xml_dir AS 'c:\xml_dir'
  2  /

Directory created.

SCOTT@orcl_11g> -- lower case xml_dir not found:
SCOTT@orcl_11g> SELECT directory_name FROM all_directories WHERE directory_name = 'xml_dir'
  2  /

no rows selected

SCOTT@orcl_11g> -- UPPER CASE XML_DIR FOUND:
SCOTT@orcl_11g> SELECT directory_name FROM all_directories WHERE directory_name = 'XML_DIR'
  2  /

DIRECTORY_NAME
------------------------------
XML_DIR

SCOTT@orcl_11g> CREATE OR REPLACE PROCEDURE insertxml
  2    (dirname  IN VARCHAR2,
  3  	filename IN VARCHAR2)
  4  AS
  5    xmlfile	    BFILE;
  6    myclob	    CLOB;
  7  BEGIN
  8    INSERT INTO xml_clob (doc_id, doc)
  9    VALUES (filename, EMPTY_CLOB())
 10    RETURNING doc INTO myclob;
 11    xmlfile := BFILENAME (dirname, filename);
 12    DBMS_LOB.OPEN (xmlfile);
 13    DBMS_LOB.LOADFROMFILE (myclob, xmlfile, DBMS_LOB.GETLENGTH (xmlfile));
 14    DBMS_LOB.CLOSE (xmlfile);
 15  END insertxml;
 16  /

Procedure created.

SCOTT@orcl_11g> SHOW ERRORS
No errors.
SCOTT@orcl_11g> BEGIN
  2    insertxml ('XML_DIR', 'test.xml'); -- XML_DIR MUST BE IN UPPER CASE
  3  END;
  4  /

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> SELECT * FROM xml_clob
  2  /

DOC_ID
--------
DOC
--------------------------------------------------------------------------------
test.xml
<?xml version="1.0"?>
<ROWSET>
 <ROW>
  <DEPTNO>10</DEPTNO>
  <DNAME>ACCOUNTING</DNAME>
  <LOC>NEW YORK</LOC>
 </ROW>
 <ROW>
  <DEPTNO>20</DEPTNO>
  <DNAME>RESEARCH</DNAME>
  <LOC>DALLAS</LOC>
 </ROW>
 <ROW>
  <DEPTNO>30</DEPTNO>
  <DNAME>SALES</DNAME>
  <LOC>CHICAGO</LOC>
 </ROW>
 <ROW>
  <DEPTNO>40</DEPTNO>
  <DNAME>OPERATIONS</DNAME>
  <LOC>BOSTON</LOC>
 </ROW>
</ROWSET>



SCOTT@orcl_11g> CREATE TABLE xml_doc AS SELECT * FROM dept WHERE 1 = 2
  2  /

Table created.

SCOTT@orcl_11g> CREATE OR REPLACE PROCEDURE loadxml1
  2  AS
  3    fil     CLOB;
  4    insrow  INTEGER;
  5    ctx     DBMS_XMLSAVE.CTXTYPE;
  6  BEGIN
  7    SELECT doc INTO fil FROM xml_clob WHERE doc_id = 'test.xml';
  8    ctx := DBMS_XMLSAVE.NEWCONTEXT ('xml_doc');
  9    insrow := DBMS_XMLSAVE.INSERTXML (ctx, fil);
 10  END loadxml1;
 11  /

Procedure created.

SCOTT@orcl_11g> SHOW ERRORS
No errors.
SCOTT@orcl_11g> EXEC loadxml1

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> SELECT * FROM xml_doc
  2  /

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

SCOTT@orcl_11g>

Re: PLS-00307: too many declarations of 'INSERTXML' match this call [message #344114 is a reply to message #343855] Thu, 28 August 2008 15:50 Go to previous message
Barbara Boehmer
Messages: 7927
Registered: November 2002
Location: California, USA
Senior Member
The following puts it all together and loads directly from the bfile to the table, eliminating the unnecessary extra step of loading into the intermediary table.

SCOTT@orcl_11g> CREATE TABLE xml_doc AS SELECT * FROM dept WHERE 1 = 2
  2  /

Table created.

SCOTT@orcl_11g> CREATE OR REPLACE DIRECTORY xml_dir AS 'c:\xml_dir'
  2  /

Directory created.

SCOTT@orcl_11g> CREATE OR REPLACE PROCEDURE bfile_to_table_xml
  2    (dirname  IN VARCHAR2,
  3  	filename IN VARCHAR2)
  4  AS
  5    xmlfile	    BFILE;
  6    myclob	    CLOB;
  7    insrow	    INTEGER;
  8    ctx	    DBMS_XMLSAVE.CTXTYPE;
  9  BEGIN
 10    xmlfile := BFILENAME (dirname, filename);
 11    DBMS_LOB.OPEN (xmlfile);
 12    DBMS_LOB.CREATETEMPORARY (myclob, TRUE);
 13    DBMS_LOB.LOADFROMFILE (myclob, xmlfile, DBMS_LOB.GETLENGTH (xmlfile));
 14    DBMS_LOB.CLOSE (xmlfile);
 15    ctx := DBMS_XMLSAVE.NEWCONTEXT ('xml_doc');
 16    insrow := DBMS_XMLSAVE.INSERTXML (ctx, myclob);
 17    DBMS_LOB.FREETEMPORARY (myclob);
 18  END bfile_to_table_xml;
 19  /

Procedure created.

SCOTT@orcl_11g> SHOW ERRORS
No errors.
SCOTT@orcl_11g> BEGIN
  2    bfile_to_table_xml ('XML_DIR', 'test.xml');
  3  END;
  4  /

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> SELECT * FROM xml_doc
  2  /

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

SCOTT@orcl_11g>

Previous Topic: java.security.AccessControlException - the Permission has not been granted
Next Topic: session parameters - java app vs sql plus and sqlldr
Goto Forum:
  


Current Time: Fri Jul 11 04:06:48 CDT 2014

Total time taken to generate the page: 0.19624 seconds