|
-
i cannot find the DBMS_XMLGEN and dbms_xmlquery packages.
i am using oracle 8.1.7 in linux.
Do i have to run any script file.
i even searched for the dbmsxsu.sql file .but it is not found.
can anyone say me how to install those packages
Good Judgement comes from Experience.
Experience comes from Bad Judgement
-
here you go
Code:
Rem
Rem $Header: dbmsxsu.sql 25-aug-2000.13:32:24 vnimani Exp $
Rem
Rem dbmsxsu.sql
Rem
Rem Copyright (c) Oracle Corporation 2000. All Rights Reserved.
Rem
Rem NAME
Rem dbmsxsu.sql -
Rem
Rem DESCRIPTION
Rem
Rem
Rem NOTES
Rem
Rem
Rem MODIFIED (MM/DD/YY)
Rem vnimani 08/24/00 - revert to no encoding tag by default
Rem vnimani 06/23/00 - caching to true in dbms_lob.createtemporary
Rem vnimani 06/21/00 - fix call to setDataHeader
Rem vnimani 06/13/00 - add xslt support
Rem vnimani 05/17/00 - Created
Rem
CREATE OR REPLACE PACKAGE DBMS_XMLQUERY AUTHID CURRENT_USER AS
-- types
SUBTYPE ctxType IS NUMBER; /* context type */
DEFAULT_ROWSETTAG CONSTANT VARCHAR2(6) := 'ROWSET'; /* rowsettag */
DEFAULT_ERRORTAG CONSTANT VARCHAR2(5) := 'ERROR'; /* error tag */
DEFAULT_ROWIDATTR CONSTANT VARCHAR2(3) := 'NUM'; /* Row ID attr */
DEFAULT_ROWTAG CONSTANT VARCHAR2(3) := 'ROW'; /* rowtag */
DEFAULT_DATE_FORMAT CONSTANT VARCHAR2(21):= 'MM/dd/yyyy HH:mm:ss';
ALL_ROWS CONSTANT NUMBER := -1; /* NO MAX, render all */
NONE CONSTANT NUMBER := 0; /* NO META */
DTD CONSTANT NUMBER := 1; /* META = DTD */
LOWER_CASE CONSTANT NUMBER := 1; /* LOWER case */
UPPER_CASE CONSTANT NUMBER := 2; /* UPPER case */
-- used to signal that the DB encoding is to be used
DB_ENCODING CONSTANT VARCHAR2(1) := '_';
-------------------- constructor/destructor functions ---------------------
FUNCTION newContext(sqlQuery IN VARCHAR2) RETURN ctxType;
FUNCTION newContext(sqlQuery IN CLOB) RETURN ctxType;
PROCEDURE closeContext(ctxHdl IN ctxType);
-------------------- parameters to the XML generation engine ----------------
PROCEDURE setRowsetTag(ctxHdl IN ctxType, tag IN VARCHAR2);
PROCEDURE setRowTag(ctxHdl IN ctxType, tag IN VARCHAR2);
PROCEDURE setErrorTag(ctxHdl IN ctxType, tag IN VARCHAR2);
PROCEDURE setRowIdAttrName(ctxHdl IN ctxType, attrName IN VARCHAR2);
PROCEDURE setRowIdAttrValue(ctxHdl IN ctxType, colName IN VARCHAR2);
PROCEDURE setCollIdAttrName(ctxHdl IN ctxType, attrName IN VARCHAR2);
PROCEDURE useNullAttributeIndicator(ctxHdl IN ctxType, flag IN BOOLEAN);
PROCEDURE setTagCase(ctxHdl IN ctxType, tCase IN NUMBER);
PROCEDURE setDateFormat(ctxHdl IN ctxType, mask IN VARCHAR2);
PROCEDURE setMaxRows (ctxHdl IN ctxType, rows IN NUMBER);
PROCEDURE setSkipRows(ctxHdl IN ctxType, rows IN NUMBER);
PROCEDURE setStylesheetHeader(ctxHdl IN ctxType, uri IN VARCHAR2, type IN VARCHAR2 := 'text/xsl');
PROCEDURE setXSLT(ctxHdl IN ctxType, uri IN VARCHAR2, ref IN VARCHAR2 := null);
PROCEDURE setXSLT(ctxHdl IN ctxType, stylesheet CLOB, ref IN VARCHAR2 := null);
PROCEDURE setEncodingTag(ctxHdl IN ctxType, enc IN VARCHAR2 := DB_ENCODING);
PROCEDURE setBindValue(ctxHdl IN ctxType, bindName IN VARCHAR2, bindValue IN VARCHAR2);
PROCEDURE setMetaHeader(ctxHdl IN ctxType, header IN CLOB := null);
PROCEDURE setDataHeader(ctxHdl IN ctxType, header IN CLOB := null, tag IN VARCHAR2 := null);
PROCEDURE setRaiseException(ctxHdl IN ctxType, flag IN BOOLEAN);
PROCEDURE setRaiseNoRowsException(ctxHdl IN ctxType, flag IN BOOLEAN);
PROCEDURE propagateOriginalException(ctxHdl IN ctxType, flag IN BOOLEAN);
PROCEDURE getExceptionContent(ctxHdl IN ctxType, errNo OUT NUMBER, errMsg OUT VARCHAR2);
------------------- generation ----------------------------------------------
FUNCTION getDTD(ctxHdl IN ctxType, withVer IN BOOLEAN := false) RETURN CLOB;
PROCEDURE getDTD(ctxHdl IN ctxType, xDoc IN CLOB, withVer IN BOOLEAN := false);
FUNCTION getXML(ctxHdl IN ctxType, metaType IN NUMBER := NONE) RETURN CLOB;
PROCEDURE getXML(ctxHdl IN ctxType, xDoc IN CLOB, metaType IN NUMBER := NONE);
END;
/
show errors
CREATE OR REPLACE PACKAGE BODY DBMS_XMLQUERY AS
FUNCTION newContext(sqlQuery IN VARCHAR2) return ctxType
as LANGUAGE JAVA NAME
'oracle.xml.sql.query.OracleXMLStaticQuery.newContext(java.lang.String) return int';
FUNCTION newContext(sqlQuery IN CLOB) return ctxType
as LANGUAGE JAVA NAME
'oracle.xml.sql.query.OracleXMLStaticQuery.newContext(oracle.sql.CLOB) return int';
PROCEDURE closeContext(ctxHdl IN ctxType)
as LANGUAGE JAVA NAME
'oracle.xml.sql.query.OracleXMLStaticQuery.closeContext(int)';
PROCEDURE setRowsetTag(ctxHdl IN ctxType, tag IN VARCHAR2)
as LANGUAGE JAVA NAME
'oracle.xml.sql.query.OracleXMLStaticQuery.setRowsetTag(int, java.lang.String)';
PROCEDURE setRowTag(ctxHdl IN ctxType, tag IN VARCHAR2)
as LANGUAGE JAVA NAME
'oracle.xml.sql.query.OracleXMLStaticQuery.setRowTag(int, java.lang.String)';
PROCEDURE setErrorTag(ctxHdl IN ctxType, tag IN VARCHAR2)
as LANGUAGE JAVA NAME
'oracle.xml.sql.query.OracleXMLStaticQuery.setErrorTag(int, java.lang.String)';
PROCEDURE setRowIdAttrName(ctxHdl IN ctxType, attrName IN VARCHAR2)
as LANGUAGE JAVA NAME
'oracle.xml.sql.query.OracleXMLStaticQuery.setRowIdAttrName(int, java.lang.String)';
PROCEDURE setRowIdAttrValue(ctxHdl IN ctxType, colName IN VARCHAR2)
as LANGUAGE JAVA NAME
'oracle.xml.sql.query.OracleXMLStaticQuery.setRowIdAttrValue(int, java.lang.String)';
PROCEDURE setCollIdAttrName(ctxHdl IN ctxType, attrName IN VARCHAR2)
as LANGUAGE JAVA NAME
'oracle.xml.sql.query.OracleXMLStaticQuery.setCollIdAttrName(int, java.lang.String)';
PROCEDURE private_useNullAttrInd(ctxHdl IN ctxType, flag IN NUMBER)
as LANGUAGE JAVA NAME
'oracle.xml.sql.query.OracleXMLStaticQuery.useNullAttributeIndicator(int, byte)';
PROCEDURE useNullAttributeIndicator(ctxHdl IN ctxType, flag IN BOOLEAN) is
begin
if flag = true then
private_useNullAttrInd(ctxHdl, 1);
else
private_useNullAttrInd(ctxHdl, 0);
end if;
end;
PROCEDURE setTagCase(ctxHdl IN ctxType, tCase IN NUMBER)
as LANGUAGE JAVA NAME
'oracle.xml.sql.query.OracleXMLStaticQuery.setTagCase(int, byte)';
PROCEDURE setDateFormat(ctxHdl IN ctxType, mask IN VARCHAR2)
as LANGUAGE JAVA NAME
'oracle.xml.sql.query.OracleXMLStaticQuery.setDateFormat(int, java.lang.String)';
PROCEDURE setMaxRows (ctxHdl IN ctxType, rows IN NUMBER)
as LANGUAGE JAVA NAME
'oracle.xml.sql.query.OracleXMLStaticQuery.setMaxRows(int, int)';
PROCEDURE setSkipRows(ctxHdl IN ctxType, rows IN NUMBER)
as LANGUAGE JAVA NAME
'oracle.xml.sql.query.OracleXMLStaticQuery.setSkipRows(int, int)';
PROCEDURE p_setStylesheetHeader(ctxHdl IN ctxType, uri IN VARCHAR2, type IN VARCHAR2)
as LANGUAGE JAVA NAME
'oracle.xml.sql.query.OracleXMLStaticQuery.setStylesheetHeader(int, java.lang.String, java.lang.String)';
PROCEDURE setStylesheetHeader(ctxHdl IN ctxType, uri IN VARCHAR2, type IN VARCHAR2 := 'text/xsl') is
begin
p_setStylesheetHeader(ctxHdl, uri, type);
end;
PROCEDURE p_setXSLT(ctxHdl IN ctxType, uri IN VARCHAR2, ref IN VARCHAR2)
as LANGUAGE JAVA NAME
'oracle.xml.sql.query.OracleXMLStaticQuery.setXSLT(int, java.lang.String, java.lang.String)';
PROCEDURE setXSLT(ctxHdl IN ctxType, uri IN VARCHAR2, ref IN VARCHAR2 := null) IS
begin
p_setXSLT(ctxHdl, uri, ref);
end;
PROCEDURE p_setXSLT(ctxHdl IN ctxType, stylesheet CLOB, ref IN VARCHAR2)
as LANGUAGE JAVA NAME
'oracle.xml.sql.query.OracleXMLStaticQuery.setXSLT(int, oracle.sql.CLOB, java.lang.String)';
PROCEDURE setXSLT(ctxHdl IN ctxType, stylesheet CLOB, ref IN VARCHAR2 := null) IS
begin
p_setXSLT(ctxHdl, stylesheet, ref);
end;
PROCEDURE private_setEncodingTag(ctxHdl IN ctxType, enc IN VARCHAR2)
as LANGUAGE JAVA NAME
'oracle.xml.sql.query.OracleXMLStaticQuery.setEncodingTag(int, java.lang.String)';
PROCEDURE setEncodingTag(ctxHdl IN ctxType,enc IN VARCHAR2 := DB_ENCODING) is
begin
private_setEncodingTag(ctxHdl, enc);
end;
PROCEDURE setBindValue(ctxHdl IN ctxType, bindName IN VARCHAR2, bindValue IN VARCHAR2)
as LANGUAGE JAVA NAME
'oracle.xml.sql.query.OracleXMLStaticQuery.setBindValue(int, java.lang.String, java.lang.String)';
PROCEDURE p_setMetaHeader(ctxHdl IN ctxType, header IN CLOB)
as LANGUAGE JAVA NAME
'oracle.xml.sql.query.OracleXMLStaticQuery.setMetaHeader(int, oracle.sql.CLOB)';
PROCEDURE setMetaHeader(ctxHdl IN ctxType, header IN CLOB := null) IS
begin
p_setMetaHeader(ctxHdl, header);
end;
PROCEDURE p_setDataHeader(ctxHdl IN ctxType, header IN CLOB, tag IN VARCHAR2)
as LANGUAGE JAVA NAME
'oracle.xml.sql.query.OracleXMLStaticQuery.setDataHeader(int, oracle.sql.CLOB, java.lang.String)';
PROCEDURE setDataHeader(ctxHdl IN ctxType, header IN CLOB := null, tag IN VARCHAR2 := null) is
begin
p_setDataHeader(ctxHdl, header, tag);
end;
PROCEDURE private_setRaiseException(ctxHdl IN ctxType, flag IN NUMBER)
as LANGUAGE JAVA NAME
'oracle.xml.sql.query.OracleXMLStaticQuery.setRaiseException(int, byte)';
PROCEDURE setRaiseException(ctxHdl IN ctxType, flag IN BOOLEAN) is
begin
if flag = true then
private_setRaiseException(ctxHdl, 1);
else
private_setRaiseException(ctxHdl, 0);
end if;
end;
PROCEDURE private_setRaiseNoRowsExc(ctxHdl IN ctxType, flag IN NUMBER)
as LANGUAGE JAVA NAME
'oracle.xml.sql.query.OracleXMLStaticQuery.setRaiseNoRowsException(int, byte)';
PROCEDURE setRaiseNoRowsException(ctxHdl IN ctxType, flag IN BOOLEAN) is
begin
if flag = true then
private_setRaiseNoRowsExc(ctxHdl, 1);
else
private_setRaiseNoRowsExc(ctxHdl, 0);
end if;
end;
PROCEDURE private_propOrigExc(ctxHdl IN ctxType, flag IN NUMBER)
as LANGUAGE JAVA NAME
'oracle.xml.sql.query.OracleXMLStaticQuery.propagateOriginalException(int, byte)';
PROCEDURE propagateOriginalException(ctxHdl IN ctxType, flag IN BOOLEAN) is
begin
if flag = true then
private_propOrigExc(ctxHdl, 1);
else
private_propOrigExc(ctxHdl, 0);
end if;
end;
PROCEDURE getExceptionContent(ctxHdl IN ctxType, errNo OUT NUMBER, errMsg OUT VARCHAR2)
as LANGUAGE JAVA NAME
'oracle.xml.sql.query.OracleXMLStaticQuery.getExceptionContent(int, int[], java.lang.String[])';
------------------- generation ----------------------------------------------
FUNCTION getDTD(ctxHdl IN ctxType, withVer IN BOOLEAN := false) RETURN CLOB IS
clb CLOB;
begin
dbms_lob.createtemporary(clb, true, DBMS_LOB.SESSION);
getDTD(ctxHdl, clb, withVer);
return clb;
end;
PROCEDURE p_getDTD(ctxHdl IN ctxType, xDoc IN CLOB, withVer IN NUMBER)
as LANGUAGE JAVA NAME
'oracle.xml.sql.query.OracleXMLStaticQuery.getDTD(int, oracle.sql.CLOB, byte)';
PROCEDURE getDTD(ctxHdl IN ctxType, xDoc IN CLOB, withVer IN BOOLEAN := false) IS
begin
if withVer = true then
p_getDTD(ctxHdl, xDoc, 1);
else
p_getDTD(ctxHdl, xDoc, 0);
end if;
end;
FUNCTION getXML(ctxHdl IN ctxType, metaType IN NUMBER := 0) RETURN CLOB IS
clb CLOB;
begin
dbms_lob.createtemporary(clb, true, DBMS_LOB.SESSION);
getXML(ctxHdl, clb, metaType);
return clb;
end;
PROCEDURE p_getXML(ctxHdl IN ctxType, xDoc IN CLOB, metaType IN NUMBER)
as LANGUAGE JAVA NAME
'oracle.xml.sql.query.OracleXMLStaticQuery.getXML(int, oracle.sql.CLOB, byte)';
PROCEDURE getXML(ctxHdl IN ctxType, xDoc IN CLOB, metaType IN NUMBER := 0) IS
begin
p_getXML(ctxHdl, xDoc, metaType);
end;
END DBMS_XMLQUERY;
/
show errors;
GRANT EXECUTE ON DBMS_XMLQUERY TO PUBLIC;
--=============================================================================
--=============================================================================
--=============================================================================
--=============================================================================
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 setRowTag(ctxHdl IN ctxType, tag IN VARCHAR2);
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);
END;
/
show errors
CREATE OR REPLACE PACKAGE BODY DBMS_XMLSAVE AS
FUNCTION newContext(targetTable IN VARCHAR2) RETURN ctxType
as LANGUAGE JAVA NAME
'oracle.xml.sql.dml.OracleXMLStaticSave.newContext(java.lang.String) return int';
PROCEDURE closeContext(ctxHdl IN ctxType)
as LANGUAGE JAVA NAME
'oracle.xml.sql.dml.OracleXMLStaticSave.closeContext(int)';
PROCEDURE setRowTag(ctxHdl IN ctxType, tag IN VARCHAR2)
as LANGUAGE JAVA NAME
'oracle.xml.sql.dml.OracleXMLStaticSave.setRowTag(int, java.lang.String)';
PROCEDURE setIgnoreCase(ctxHdl IN ctxType, flag IN NUMBER)
as LANGUAGE JAVA NAME
'oracle.xml.sql.dml.OracleXMLStaticSave.setIgnoreCase(int, byte)';
PROCEDURE setDateFormat(ctxHdl IN ctxType, mask IN VARCHAR2)
as LANGUAGE JAVA NAME
'oracle.xml.sql.dml.OracleXMLStaticSave.setDateFormat(int, java.lang.String)';
PROCEDURE setBatchSize(ctxHdl IN ctxType, batchSize IN NUMBER)
as LANGUAGE JAVA NAME
'oracle.xml.sql.dml.OracleXMLStaticSave.setBatchSize(int, int)';
PROCEDURE setCommitBatch(ctxHdl IN ctxType, batchSize IN NUMBER)
as LANGUAGE JAVA NAME
'oracle.xml.sql.dml.OracleXMLStaticSave.setCommitBatch(int, int)';
PROCEDURE setUpdateColumn(ctxHdl IN ctxType, colName IN VARCHAR2)
as LANGUAGE JAVA NAME
'oracle.xml.sql.dml.OracleXMLStaticSave.setUpdateColumn(int, java.lang.String)';
PROCEDURE clearUpdateColumnList(ctxHdl IN ctxType)
as LANGUAGE JAVA NAME
'oracle.xml.sql.dml.OracleXMLStaticSave.clearUpdateColumnList(int)';
PROCEDURE setKeyColumn(ctxHdl IN ctxType, colName IN VARCHAR2)
as LANGUAGE JAVA NAME
'oracle.xml.sql.dml.OracleXMLStaticSave.setKeyColumn(int, java.lang.String)';
PROCEDURE clearKeyColumnList(ctxHdl IN ctxType)
as LANGUAGE JAVA NAME
'oracle.xml.sql.dml.OracleXMLStaticSave.clearKeyColumnList(int)';
------------------- save ----------------------------------------------------
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';
FUNCTION updateXML(ctxHdl IN ctxType, xDoc IN VARCHAR2) RETURN NUMBER
as LANGUAGE JAVA NAME
'oracle.xml.sql.dml.OracleXMLStaticSave.updateXML(int, java.lang.String) return int';
FUNCTION updateXML(ctxHdl IN ctxType, xDoc IN CLOB) RETURN NUMBER
as LANGUAGE JAVA NAME
'oracle.xml.sql.dml.OracleXMLStaticSave.updateXML(int, oracle.sql.CLOB) return int';
FUNCTION deleteXML(ctxHdl IN ctxType, xDoc IN VARCHAR2) RETURN NUMBER
as LANGUAGE JAVA NAME
'oracle.xml.sql.dml.OracleXMLStaticSave.deleteXML(int, java.lang.String) return int';
FUNCTION deleteXML(ctxHdl IN ctxType, xDoc IN CLOB) RETURN NUMBER
as LANGUAGE JAVA NAME
'oracle.xml.sql.dml.OracleXMLStaticSave.deleteXML(int, oracle.sql.CLOB) return int';
------------------- misc ----------------------------------------------------
PROCEDURE private_propOrigExc(ctxHdl IN ctxType, flag IN NUMBER)
as LANGUAGE JAVA NAME
'oracle.xml.sql.save.OracleXMLStaticSave.propagateOriginalException(int, byte)';
PROCEDURE propagateOriginalException(ctxHdl IN ctxType, flag IN BOOLEAN) is
begin
if flag = true then
private_propOrigExc(ctxHdl, 1);
else
private_propOrigExc(ctxHdl, 0);
end if;
end;
PROCEDURE getExceptionContent(ctxHdl IN ctxType, errNo OUT NUMBER, errMsg OUT VARCHAR2)
as LANGUAGE JAVA NAME
'oracle.xml.sql.save.OracleXMLStaticSave.getExceptionContent(int, int[], java.lang.String[])';
END DBMS_XMLSAVE;
/
show errors;
GRANT EXECUTE ON DBMS_XMLSAVE TO PUBLIC;
-
hai thankz .
I have this sql file in my windows DB.
But what about the jar files.
i tried to load it but it is throwing errors.
can you give me the proper procedure to do it one by one.
what are the jar files and the .sql files
Good Judgement comes from Experience.
Experience comes from Bad Judgement
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|
Reply With Quote