|
|
This is an implementation of the xpath for groovy.util.Node.
Prerequisites:
Java 6
Groovy 1.7.5
Jaxen 1.1.3
Idea and example:
def x=new groovy.util.XmlParser().parseText('''
test1
test2
test3
test4
''');
//print out our xml
println( groovy.xml.XmlUtil.serialize(x) );
def nsCtx=['sab':'http://sab/'];
println( "/sab:foo/bar/sab:baz[@t='xx']/@f == "+GXPath.valueOf( x, "/sab:foo/bar/sab:baz[@t='xx']/@f", nsCtx ) );
println( "/*/bar/baz[@t='xx']/@f == "+GXPath.valueOf( x, "/*/bar/baz[@t='xx']/@f", nsCtx ) );
println( "sum(//*[local-name()='baz']/@f) == "+GXPath.valueOf( x, "sum(//*[local-name()='baz']/@f)", nsCtx ) );
println( "concat(/sab:foo/@f,'-t') == "+GXPath.valueOf( x, "concat(/sab:foo/@f,'-t')", nsCtx ) );
println( "//*[local-name()='foo']/@f == "+GXPath.valueOf( x, "//*[local-name()='foo']/@f", nsCtx ) );
println( "//*[local-name()='baz'] == "+GXPath.getNode( x, "//*[local-name()='baz']", nsCtx ) );
Result:
/sab:foo/bar/sab:baz[@t='xx']/@f == 1
/*/bar/baz[@t='xx']/@f == 2
sum(//*[local-name()='baz']/@f) == 3
concat(/sab:foo/@f,'-t') == root-t
//*[local-name()='foo']/@f == root
//*[local-name()='baz'] == {http://sab/}baz[attributes={t=xx, f=1}; value=[test0]]
The groovy class to do that:
/* XPath for groovy node */
import org.jaxen.XPath;
import org.jaxen.XPathSyntaxException;
import org.jaxen.JaxenException;
import org.jaxen.BaseXPath;
import org.jaxen.JaxenException;
import org.jaxen.DefaultNavigator;
import org.jaxen.FunctionCallException;
import org.jaxen.NamedAccessNavigator;
import org.jaxen.Navigator;
import org.jaxen.XPath;
import java.util.HashMap;
import org.jaxen.util.SingleObjectIterator;
import org.jaxen.JaxenConstants;
public class GXPath extends DefaultNavigator implements NamedAccessNavigator {
/*-------- HELPER METHODS --------*/
public static org.jaxen.XPath parse(String path, java.util.HashMap nsContext=null){
org.jaxen.XPath xpath=instance.parseXPath(path);
if(nsContext!=null) {
for(i in nsContext){
xpath.addNamespace(i.getKey(), i.getValue());
}
}
return xpath;
}
public static String valueOf(groovy.util.Node node, String path, java.util.HashMap nsContext=null){
org.jaxen.XPath xpath=GXPath.parse(path,nsContext);
return xpath.stringValueOf(node);
}
public static Object getNode(groovy.util.Node node, String path, java.util.HashMap nsContext=null){
org.jaxen.XPath xpath=GXPath.parse(path,nsContext);
return xpath.selectSingleNode(node);
}
public static List getNodes(groovy.util.Node node, String path, java.util.HashMap nsContext=null){
org.jaxen.XPath xpath=GXPath.parse(path,nsContext);
return xpath.selectNodes(node);
}
/*-------- NAVIGATOR IMPLEMENTATION --------*/
private static GXPath instance = new GXPath();
public static Navigator getInstance() {
return instance;
}
//i guess it should return empty itarator to work faster
//i can't understand why it's used for node comparison
public Iterator getFollowingSiblingAxisIterator(Object contextNode) throws org.jaxen.UnsupportedAxisException {
return JaxenConstants.EMPTY_ITERATOR;
}
java.util.Iterator getAttributeAxisIterator(Object obj, String localName, String namespacePrefix, String namespaceURI){
if ( obj instanceof groovy.util.Node ) {
def name=localName;
if(namespaceURI!=null&&namespaceURI.length()>0)name=new groovy.xml.QName(namespaceURI,localName,'x');
def ret=obj.attribute(name);
if(ret!=null){
return new SingleObjectIterator( ret );
}
}
return JaxenConstants.EMPTY_ITERATOR;
}
public Iterator getChildAxisIterator(Object obj, String localName, String namespacePrefix, String namespaceURI){
Iterator result = null;
def name=localName;
if(namespaceURI!=null&&namespaceURI.length()>0)name=new groovy.xml.QName(namespaceURI,localName,'x');
if ( obj instanceof GDoc ) {
if( name.equals( obj.getRoot().name() ) ){
result = new SingleObjectIterator( obj.getRoot() );
}
}else if ( obj instanceof groovy.util.Node ) {
result = obj[name]?.iterator();
}
if (result != null) return result;
return JaxenConstants.EMPTY_ITERATOR;
}
public Iterator getChildAxisIterator(Object obj) {
Iterator result = null;
if ( obj instanceof GDoc ) {
return new SingleObjectIterator( obj.getRoot() );
}else if ( obj instanceof groovy.util.Node ) {
result = obj.iterator();
}
if (result != null) return result;
return JaxenConstants.EMPTY_ITERATOR;
}
public Iterator getParentAxisIterator(Object contextNode) {
Iterator result = null;
if ( contextNode instanceof groovy.util.Node ) {
groovy.util.Node node = (groovy.util.Node) contextNode;
groovy.util.Node parent = node.parent();
if(parent!=null)return new SingleObjectIterator( parent );
}
return JaxenConstants.EMPTY_ITERATOR;
}
public Object getDocumentNode(Object obj) {
if ( obj instanceof groovy.util.Node ) {
groovy.util.Node node = (groovy.util.Node) obj;
while(node.parent()!=null){
node=node.parent();
}
return new GDoc(node);
}
return null;
}
public Object getParentNode(Object contextNode) {
if ( contextNode instanceof groovy.util.Node ) {
groovy.util.Node node = (groovy.util.Node) contextNode;
return node.parent();
}
return null;
}
public boolean isElement(Object obj) {
return obj instanceof groovy.util.Node;
}
public boolean isComment(Object obj) {
return false; //not supported by groovy node
}
public boolean isText(Object obj){
return ( obj instanceof String );
}
public boolean isAttribute(Object obj) {
return obj instanceof java.util.Map.Entry;
}
public boolean isProcessingInstruction(Object obj) {
return false;
}
public boolean isDocument(Object obj) {
if(obj instanceof groovy.util.Node){
if(obj.parent()==null)return true;
}
return false;
}
public boolean isNamespace(Object obj) {
return obj instanceof groovy.xml.Namespace;
}
public String getElementName(Object obj){
groovy.util.Node elem = (groovy.util.Node) obj;
Object name = elem.name();
if(name instanceof String)return name;
return name.getLocalPart();
}
public String getElementNamespaceUri(Object obj) {
String uri=null;
groovy.util.Node elem = (groovy.util.Node) obj;
Object name = elem.name();
if(name instanceof groovy.xml.QName){
uri = name.getNamespaceURI();
}
if ( uri != null && uri.length() == 0 ) return null;
return uri;
}
public String getElementQName(Object obj) {
groovy.util.Node elem = (groovy.util.Node) obj;
return elem.name().getQualifiedName();
}
public String getAttributeName(Object obj) {
java.util.Map.Entry attr=(java.util.Map.Entry)obj;
if(attr.getKey() instanceof String)return attr.getKey();
if(attr.getKey() instanceof groovy.xml.QName )return attr.getKey().getLocalPart();
return ""; //?exception
}
public String getAttributeNamespaceUri(Object obj) {
java.util.Map.Entry attr=(java.util.Map.Entry)obj;
if(attr.getKey() instanceof groovy.xml.QName )return attr.getKey().getNamespaceURI();
return "";
}
public String getAttributeQName(Object obj) {
java.util.Map.Entry attr=(java.util.Map.Entry)obj;
if(attr.getKey() instanceof String)return attr.getKey();
if(attr.getKey() instanceof groovy.xml.QName )return attr.getQualifiedName();
return ""; //?exception
}
public Iterator getAttributeAxisIterator(Object contextNode) {
if ( contextNode instanceof groovy.util.Node ) {
groovy.util.Node node = (groovy.util.Node) contextNode;
return node.attributes().entrySet().iterator();
}
return JaxenConstants.EMPTY_ITERATOR;
}
public XPath parseXPath (String xpath) throws org.jaxen.saxpath.SAXPathException {
return new BaseXPath(xpath,instance);
}
public String getTextStringValue(Object obj) {
if ( obj instanceof String ) {
return obj;
}
return null;
}
public String getElementStringValue(Object obj) {
if ( obj instanceof groovy.util.Node ) {
groovy.util.Node node = (groovy.util.Node) obj;
return node.text();
}
return null;
}
public String getAttributeStringValue(Object obj) {
if ( obj instanceof java.util.Map.Entry ) {
return obj.getValue();
}
return null;
}
public String getNamespaceStringValue(Object obj) {
groovy.xml.Namespace ns = (groovy.xml.Namespace) obj;
return ns.getUri();
}
public String getNamespacePrefix(Object obj) {
groovy.xml.Namespace ns = (groovy.xml.Namespace) obj;
return ns.getPrefix();
}
public String getCommentStringValue(Object obj) {
return null;
}
public Iterator getNamespaceAxisIterator(Object contextNode) {
if ( contextNode instanceof groovy.util.Node ) {
groovy.util.Node node = (groovy.util.Node) contextNode;
Map ns=new HashMap();
for( i in node.depthFirst() ){
if( i.getKey() instanceof groovy.xml.QName && ns.containsKey( i.getKey().getPrefix() ) ){
ns.put(i.getKey().getPrefix(), new groovy.xml.Namespace(i.getKey().getNamespaceURI(), i.getKey().getPrefix()) );
}
}
return ns.values().iterator();
}
return JaxenConstants.EMPTY_ITERATOR;
}
public String translateNamespacePrefixToUri(String prefix, Object obj) {
if ( obj instanceof groovy.util.Node && prefix!=null) {
groovy.util.Node node = (groovy.util.Node) obj;
for( i in node.depthFirst() ){
if( i.getKey() instanceof groovy.xml.QName && prefix.equals(i.getKey().getPrefix()) ){
return i.getKey().getNamespaceURI();
}
}
}
return null;
}
public Object getDocument(String uri) throws FunctionCallException {
try {
return groovy.util.XmlParser.parse( uri );
} catch (Exception e) {
throw new FunctionCallException("Failed to parse document for URI: " + uri, e);
}
}
public class GDoc{
private groovy.util.Node root;
public GDoc(groovy.util.Node n){
root=n;
}
public groovy.util.Node getRoot(){
return root;
}
}
}
Problems:
In Jaxen 1.1.3 there is a strange code that sorts the result nodes in own strange way...
So, when you try to get single node with GXPath, you will receive the last one instead of first one.
This is a method of fast and simple webservice call using groovy.
Prerequisites:
Java 6
Groovy 1.7.5
Log4j (you can comment it out from code)
SoapUI (or other tool that could call webservice and show the request and response)
Idea and call example:
//Next line ignores all SSL restrictions when working with HTTPS
//Comment it if SSL certificates must be checked
WSCallout.trustAll();
//the endpoint of your service (could be loa=cated in wsdl)
String endpoint="https://esb-test:8243/services/echo.echoHttpsSoap11Endpoint";
//Usernametoken authentication (null if the service does not require it)
def auth = WSCallout.getUsernameToken("admin","FooPass");
//Soap action (unique operation name) could be located in wsdl
String action="urn:echoString";
//Payload of the message
def payload = XML.builder()."echo:echoString"("xmlns:echo":"http://test"){
"in" ("fooooo-data")
};
/*
At his moment payload will contain the following XML in groovy.util.Node:
fooooo-data
*/
//Now call web service and get a result in groovy.util.Node
def result = WSCallout.callSoap11(endpoint, payload, auth, action);
//print the result
println( XML.toString( result ) );
The groovy class to do that:
/**
* web service caller
*/
package groovy.abi;
//import javax.xml.namespace.*
import javax.xml.ws.*
import javax.xml.soap.*;
import java.net.HttpURLConnection;
//TRUST ALL
import javax.net.ssl.HostnameVerifier;
import javax.net.ssl.HttpsURLConnection;
import javax.net.ssl.SSLSession;
import javax.net.ssl.SSLSocketFactory;
import javax.net.ssl.SSLContext;
import javax.net.ssl.TrustManager;
import javax.net.ssl.X509TrustManager;
import java.security.cert.X509Certificate;
import java.security.SecureRandom;
import javax.net.ssl.KeyManagerFactory;
import org.apache.log4j.Logger;
import org.apache.log4j.Level;
public class WSCallout{
public static groovy.util.Node callSoap11(endpointUrl,payload,headers,action,throwFault=true,timeout=40000,Logger logger=null){
groovy.util.NodeBuilder builder = groovy.util.NodeBuilder.newInstance();
def envelop = builder."soapenv:Envelope"("xmlns:soapenv": "http://schemas.xmlsoap.org/soap/envelope/") {
"soapenv:Header"()
"soapenv:Body"()
};
def msgId=Integer.toHexString(payload.hashCode());
if(headers!=null)envelop["soapenv:Header"][0].append(headers);
if(payload!=null)envelop["soapenv:Body"][0].append(payload);
if(logger!=null) {
if(logger.isEnabledFor(Level.TRACE)){
logger.trace("WSCallout.send msgId::="+msgId+" envelop::="+XML.toString(envelop));
}else if(logger.isDebugEnabled()){
logger.debug("WSCallout.send msgId::="+msgId+" body::="+XML.toString(payload));
}
}
def method = action;
if(method==null || method.trim().length()==0){
method = XML.localName(payload);
}
if(method==null || method.trim().length()==0){
method = "undefined";
}
try {
HttpURLConnection conn = (new URL(endpointUrl)).openConnection();
conn.setConnectTimeout(3000);
conn.setReadTimeout(timeout);
conn.addRequestProperty("Content-Type","text/xml;charset=UTF-8");
conn.addRequestProperty("Accept","text/xml");
if(action!=null)conn.addRequestProperty("SOAPAction",'"'+action+'"');
conn.setDoOutput(true);
ByteArrayOutputStream baos=new ByteArrayOutputStream();
groovy.xml.XmlUtil.serialize(envelop,baos);
conn.setFixedLengthStreamingMode( baos.size() );
OutputStream ost = conn.getOutputStream();
ost.write( baos.toByteArray() );
ost.flush();
// Get the response
def statusCode = conn.getResponseCode();
InputStream ist=null;
try{
ist=conn.getInputStream();
}catch(java.io.IOException e){
try{
ist=conn.getErrorStream();
}catch(Exception){}
if(ist==null)throw new SOAPException("IO Error receiving data from server: "+e.getMessage(),e);
}
groovy.util.XmlParser parser = new XmlParser();
byte[] resp=ist.getBytes();
ost.close();
ist.close();
try{
envelop = parser.parse(new java.io.ByteArrayInputStream(resp));
}catch(Exception e){
throw new java.text.ParseException("Expected soap envelop as response for "+method+", but received: \n"+new String(resp,"UTF-8"),-1 );
}
} catch (java.net.SocketTimeoutException e) {
throw new java.io.IOException("Failed to call remote service \""+method+"\" in "+(timeout/1000)+" seconds.",e);
}
def soapenv = new groovy.xml.Namespace("http://schemas.xmlsoap.org/soap/envelope/", "soapenv");
def body = envelop[soapenv.Body][0];
if(body==null)throw new java.text.ParseException("Expected soap envelop as response for "+method+", but received: \n"+XML.toString(envelop),-1 );
payload = body.children()[0];
if(logger!=null) {
if(logger.isEnabledFor(Level.TRACE)){
logger.trace("WSCallout.recv msgId::="+msgId+" envelop::="+XML.toString(envelop));
}else if(logger.isDebugEnabled()){
logger.debug("WSCallout.recv msgId::="+msgId+" body::="+XML.toString(payload));
}
}
if( throwFault && payload.name().equals( new groovy.xml.QName("http://schemas.xmlsoap.org/soap/envelope/","Fault") ) ){
def msg=payload["faultstring"].text();
throw new SOAPException("Remote service ["+method+"] error: "+msg);
}
return payload;
}
public static groovy.util.Node getUsernameToken(String user,String pass){
groovy.util.NodeBuilder builder = groovy.util.NodeBuilder.newInstance();
def id="UT-"+Long.toHexString( System.currentTimeMillis() );
def header = builder."wsse:Security"("xmlns:wsse":"http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-secext-1.0.xsd") {
"wsse:UsernameToken"( "wsu:Id":id, "xmlns:wsu":"http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-utility-1.0.xsd" ) {
"wsse:Username" ( user )
"wsse:Password" ( Type:"http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-username-token-profile-1.0#PasswordText", pass )
}
};
}
public static groovy.util.Node getUsernameToken2002(String user,String pass){
groovy.util.NodeBuilder builder = groovy.util.NodeBuilder.newInstance();
def id="UT-"+Long.toHexString( System.currentTimeMillis() );
def header = builder."wsse:Security"("xmlns:wsse":"http://schemas.xmlsoap.org/ws/2002/07/secext") {
"wsse:UsernameToken" {
"wsse:Username" ( user )
"wsse:Password" ( Type:"http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-username-token-profile-1.0#PasswordText", pass )
}
};
}
public static String getSoap11Fault(obj){
String msg;
String details="";
if(obj instanceof String){
msg=obj;
}else if(obj instanceof Throwable){
msg=obj.getMessage();
if(msg==null||msg.trim().length()==0||"null".equalsIgnoreCase(msg))msg=""+obj;
for(StackTraceElement i in obj.getStackTrace()){
if(i.getFileName()?.matches(".*\\.groovy")){
details=(details.length()==0?'':details+"\r\n\t\t")+i.getMethodName()+" "+i.getFileName()+":"+i.getLineNumber();
}
}
}else{
msg=""+obj;
}
def builder = groovy.util.NodeBuilder.newInstance();
def body = builder."soapenv:Fault"("xmlns:soapenv": "http://schemas.xmlsoap.org/soap/envelope/") {
"faultcode" ("soapenv:Server")
"faultstring" (msg)
"detail" (details)
};
return groovy.xml.XmlUtil.serialize(body);
}
private static HostnameVerifier allowAllHostsVerifier = new HostnameVerifier() {
public boolean verify(String urlHostName, SSLSession session) {
return true;
}
};
public static void allowAllHosts(con){
if(con instanceof HttpsURLConnection){
con.setHostnameVerifier(allowAllHostsVerifier);
}
}
//TRUST ALL
private static TrustManager[] trustAllCerts = null;
public static void trustAll() {
try {
HttpsURLConnection.setDefaultHostnameVerifier(allowAllHostsVerifier);
//init certificates
if( trustAllCerts==null ){
trustAllCerts=new TrustManager[1];
trustAllCerts[0] = new X509TrustManager() {
public void checkClientTrusted( final X509Certificate[] chain, final String authType ) {}
public void checkServerTrusted( final X509Certificate[] chain, final String authType ) {}
public X509Certificate[] getAcceptedIssuers() {return null;}
} ;
}
//KeyManagerFactory kmf=
SSLContext sslContext = SSLContext.getInstance( "TLS" );
sslContext.init( null, trustAllCerts, new java.security.SecureRandom() );
SSLSocketFactory sslSockFact=sslContext.getSocketFactory();
HttpsURLConnection.setDefaultSSLSocketFactory(sslSockFact);
} catch(Exception e) {
throw new IllegalStateException(e.getMessage());
}
}
}
public class XML{
public static String toString(groovy.util.Node node){
if(node==null)return null;
return groovy.xml.XmlUtil.serialize(node);
}
public static void toStream(groovy.util.Node node, java.io.OutputStream os){
groovy.xml.XmlUtil.serialize(node, os);
}
public static groovy.util.NodeBuilder builder(){
return groovy.util.NodeBuilder.newInstance();
}
public static groovy.util.Node parse(java.io.InputStream stream){
return new groovy.util.XmlParser().parse(stream);
}
public static groovy.util.Node parse(String uri){
return new groovy.util.XmlParser().parse(uri);
}
public static groovy.util.Node parseText(String s){
return new groovy.util.XmlParser().parseText(s);
}
public static groovy.xml.Namespace ns(String uri, String prefix){
return new groovy.xml.Namespace(uri, prefix);
}
public static groovy.xml.Namespace ns(String uri){
return new groovy.xml.Namespace(uri);
}
public static groovy.xml.Namespace ns(groovy.util.Node node){
if( node.name() instanceof groovy.xml.QName ){
return new groovy.xml.Namespace( node.name().getNamespaceURI(), node.name().getPrefix() );
}
return new groovy.xml.Namespace();
}
public static String localName(groovy.util.Node node){
def name=node.name();
if(name instanceof String){
return name.replaceFirst(".*:(.*)","\$1");
}else if(name instanceof groovy.xml.QName){
return name.getLocalPart();
}
return null;
}
}
I need this to export regularly objects from command line and add them into svn
to monitor shanges in oracle database.
Idea:
Create sql file for sqlplus oracle tool that will create temporary file
that will do export
Name: oradump.sql
WHENEVER SQLERROR EXIT 1
SET SQLBLANKLINES ON
SET HEADING OFF
SET FEEDBACK OFF
SET TRIMSPOOL ON
--hope nobody produce one line longer then 2k
SET LINESIZE 2000
SET LONGCHUNKSIZE 2000
--hope this will be enough to export views...
SET LONG 64000
SET PAGESIZE 0
SET VERIFY OFF
--the schema to export
define p_schema="&1"
--the schema to export
define p_mask="&2"
--the target directory
define p_dir="&3"
define p_pcr="CREATE OR REPLACE"
select 'prepare export '||chr(10)||'objects like "&p_mask" from schema "&p_schema" into "&p_dir"' from dual
/
ALTER SESSION SET CURRENT_SCHEMA = &p_schema
/
--create temp export sctipt
SET TERMOUT OFF
spool tmp.sql
/
prompt WHENEVER SQLERROR EXIT 1
prompt SET SQLBLANKLINES ON
prompt SET HEADING OFF
prompt SET FEEDBACK OFF
prompt SET TRIMSPOOL ON
prompt SET LINESIZE 2000
prompt SET LONGCHUNKSIZE 2000
prompt SET LONG 64000
prompt SET PAGESIZE 0
prompt SET VERIFY OFF
prompt SET TERMOUT OFF
prompt ALTER SESSION SET CURRENT_SCHEMA = &p_schema
prompt /
--create out dirs for sources
select 'host md "&p_dir'||chr(92)||type||'"'
from user_source
where type like '&p_mask'
group by type
order by type
/
select 'spool "&p_dir'||chr(92)||type||chr(92)||name||'.sql"'||chr(10)
||'select decode(line,1,''&p_pcr '','''')||text from user_source where name='''||name||''' and type='''||type||''' order by line'||chr(10)
||'/'||chr(10)
||'spool off'
from user_source
where type like '&p_mask'
group by name,type
order by type,name
/
--create out dirs for views
select 'host md "&p_dir'||chr(92)||'VIEW'||'"'
from dual
where 'VIEW' like '&p_mask'
/
select 'spool "&p_dir'||chr(92)||'VIEW'||chr(92)||v.view_name||'.sql"'||chr(10)
||'select ''&p_pcr VIEW '||v.view_name||' (''||chr(10) from dual'
||chr(10)||'/'||chr(10)
||'select '' ''||column_name||decode(column_id,'||to_char(vc.colcount)||','''','','') '
||'from user_tab_cols where table_name='''||v.view_name||''' order by column_id'
||chr(10)||'/'||chr(10)
||'select '') AS''||chr(10) from dual'
||chr(10)||'/'||chr(10)
||'select text from user_views where view_name='''||v.view_name||''''
||chr(10)||'/'||chr(10)
||'select chr(10)||''/''||chr(10) from dual'
||chr(10)||'/'||chr(10)
||'spool off'
from user_views v,(select max(column_id) colcount,table_name from user_tab_cols group by table_name) vc
where v.view_name=vc.table_name
and 'VIEW' like '&p_mask'
and v.view_name like 'VACC%'
order by v.view_name
/
prompt exit
spool off
exit
/
Now let's create command file to start this script:
Name: oradump.cmd
@echo off
cd "%~dp0"
set oradump_srv=MY_SERVER
set oradump_uid=MY_USER
set oradump_sch=MY_SCHEMA
set oradump_dir=.\output
set oradump_obj=%%
if "%oradump_pwd%"=="" set /P oradump_pwd=PASS (visible)
sqlplus -L -S %oradump_uid%/%oradump_pwd%@%oradump_srv% "@oradump.sql" %oradump_sch% "%oradump_obj%" "%oradump_dir%"
echo do export
sqlplus -L -S %oradump_uid%/%oradump_pwd%@%oradump_srv% "@tmp.sql"
del tmp.sql 2>nul
exit 0
Now create "output" directory,
define correct values for variables in oradump.cmd
and you can export objects (types, packages, procedures, functions, views)
into separate files
Let we have table-like xml with custom namespace and we want to transform it to the table (resultset)
to be used in select query.
Let we have function getXML() that returns the following XMLType value:
<datas1:employmenttypeslist xmlns:datas1="http://mynamespace/">
<datas1:employmenttypes>
<datas1:id>676</datas1:id>
<datas1:code>NOT_WORK</datas1:code>
<datas1:text>Not working</datas1:text>
</datas1:employmenttypes>
<datas1:employmenttypes>
<datas1:id>677</datas1:id>
<datas1:code>OWN_BUSINESS</datas1:code>
<datas1:text>Own business</datas1:text>
</datas1:employmenttypes>
<datas1:employmenttypes>
<datas1:id>678</datas1:id>
<datas1:code>PENSIONER</datas1:code>
<datas1:text>Pensioner</datas1:text>
</datas1:employmenttypes>
</datas1:employmenttypeslist>
So, to transform the XMLType into a table we can use several methods.
Method #1
select
extractValue(xmlt.COLUMN_VALUE,'*/p:id','xmlns:p="http://mynamespace/"') as id,
extractValue(xmlt.COLUMN_VALUE,'*/p:code','xmlns:p="http://mynamespace/"') as code,
extractValue(xmlt.COLUMN_VALUE,'*/p:text','xmlns:p="http://mynamespace/"') as text
from
TABLE(XMLSEQUENCE(
EXTRACT(
(
select getXML() from dual
),
'/*/*'
)
)) xmlt
Method #2
select *
FROM XMLTABLE(XMLNAMESPACES ('http://mynamespace/' AS "p"),'/*/*'
PASSING
getXML()
COLUMNS
--describe columns and path to them:
id varchar2(10) PATH '/*/p:id',
code varchar2(10) PATH '/*/p:code',
text varchar2(255) PATH '/*/p:text'
) xmlt
;
There are a lot of samples how to call web service from Oracle.
but after a small research I make this choice because of speed and simplicity.
So, I create a package and here is a code example how to use it:
select ws_tools.callws(
--endpoint url
'http://myhost:9763/services/Dictionary.DictionaryHttpSoap11Endpoint/',
--soap message payload
XMLElement(
"ns1:getEducationTypes",
XMLAttributes('http://mynamespace/' as "xmlns:ns1"),
XMLElement("ns1:id",null)
)
--optional WSSecurity UsernameToken parameters:
'theusername',
'thepassword'
) from dual
The package:
CREATE OR REPLACE PACKAGE WS_TOOLS
IS
FUNCTION callWS(p_url IN VARCHAR2,p_method IN XMLTYPE,p_user in VARCHAR2 :=null,p_pass in VARCHAR2 :=null) RETURN XMLTYPE;
END;
/
CREATE OR REPLACE PACKAGE BODY WS_TOOLS
IS
--internal function to post http(s) request
FUNCTION httpPost
(
p_url IN VARCHAR2,
p_data IN CLOB,
p_timeout IN BINARY_INTEGER DEFAULT 60
)
RETURN CLOB
IS
--
v_request utl_http.req;
v_response utl_http.resp;
v_buffer CLOB;
v_chunk VARCHAR2(4000);
v_length NUMBER;
v_index NUMBER;
BEGIN
v_index := 1;
v_length := nvl(length(p_data), 0);
-- configure HTTP
utl_http.set_response_error_check(enable => FALSE);
utl_http.set_detailed_excp_support(enable => FALSE);
utl_http.set_transfer_timeout(p_timeout);
-- send request
v_request := utl_http.begin_request(p_url, 'POST','HTTP/1.0');
utl_http.set_header(v_request, 'Content-Type', 'text/xml');
utl_http.set_header(v_request, 'Content-Length', v_length);
WHILE v_index <= v_length LOOP
utl_http.write_text(v_request, substr(p_data, v_index, 4000));
v_index := v_index + 4000;
END LOOP;
-- check HTTP status code for error
IF v_response.status_code <> utl_http.http_ok THEN
raise_application_error(-20400,v_response.status_code || ' - ' || v_response.reason_phrase);
END IF;
-- get response
dbms_lob.createtemporary(v_buffer, FALSE);
v_response := utl_http.get_response(v_request);
BEGIN
LOOP
utl_http.read_text(v_response, v_chunk, 4000);
dbms_lob.writeappend(v_buffer, length(v_chunk), v_chunk);
END LOOP;
EXCEPTION
WHEN utl_http.end_of_body THEN NULL;
END;
utl_http.end_response(v_response);
RETURN v_buffer;
END;
FUNCTION callWS
(
p_url IN VARCHAR2,
p_method IN XMLTYPE,
p_user in VARCHAR2 :=null,
p_pass in VARCHAR2 :=null
--p_timeout IN NUMBER := 60
)
RETURN XMLTYPE
IS
-- calls the given SOAP service
--cn_procedure_name CONSTANT VARCHAR2(30) := 'invoke';
--
v_envelope XMLTYPE;
v_header XMLTYPE;
v_response CLOB;
v_fault XMLTYPE;
v_sqlerrm VARCHAR2(2000);
BEGIN
if p_user is not null then
--prepare header
SELECT
XMLElement(
"soap:Header",
XMLElement(
"wsse:Security",
XMLAttributes(
'http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-secext-1.0.xsd' AS "xmlns:wsse"
),
XMLElement(
"wsse:UsernameToken",
XMLAttributes(
'UsernameToken-1' AS "wsu:Id",
'http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-utility-1.0.xsd' AS "xmlns:wsu"
),
XMLElement(
"wsse:Username",
'cfront'
),
XMLElement(
"wsse:Password",
XMLAttributes(
'http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-username-token-profile-1.0#PasswordText' AS "Type"
),
'cfrontpass'
)
)
)
)
INTO
v_header
FROM
dual;
end if;
-- wrap method in SOAP envelope
SELECT
XMLElement(
"soap:Envelope",
XMLAttributes(
'http://schemas.xmlsoap.org/soap/envelope/' AS "xmlns:soap"
),
v_header,
XMLElement(
"soap:Body",
p_method
)
)
INTO
v_envelope
FROM
dual;
-- POST request
v_response := httpPost(
p_url,
v_envelope.getClobVal(),
60 -- 60 seconds
);
IF v_response IS NULL THEN
raise_application_error(-20400,'empty response');
END IF;
-- parse response
BEGIN
v_envelope := XMLType(v_response);
EXCEPTION
WHEN OTHERS THEN
raise_application_error(-20400,'can''t parse xml: '||SQLERRM);
END;
-- check for a fault
v_fault := v_envelope.extract(
'/soap:Envelope/soap:Body/soap:Fault',
'xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/"'
);
IF v_fault IS NOT NULL THEN
v_sqlerrm := v_fault.extract('.//faultstring/text()').getStringVal();
raise_application_error(-20400,'remote error: '||v_sqlerrm);
END IF;
-- the actual response is the child of the "soap:Body" element
RETURN v_envelope.extract(
'/soap:Envelope/soap:Body/*[position() = 1]',
'xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/"'
);
END;
END;
|
