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  |
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  |
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 #343855 is a reply to message #343661] |
Thu, 28 August 2008 06:20 |
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 |
 |
Barbara Boehmer
Messages: 9101
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 |
|
Barbara Boehmer
Messages: 9101
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>
|
|
|
|
Goto Forum:
Current Time: Tue Dec 17 22:39:09 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
