понедельник, 17 сентября 2012 г.

Ошибка "internal xpath error" в oraext:query-database и вариант её решения

Ошибка:
При использовании XPath-функции oraext:query-database, если в запросе используются функции БД (например: select max(salary) from employee) возникает ошибка следующего вида:
 <bpelFault>  
  <faultType>0</faultType>  
  <subLanguageExecutionFault xmlns="http://docs.oasis-open.org/wsbpel/2.0/process/executable">  
   <part name="summary">  
    <summary>An error occurs while processing the XPath expression; the  
         expression is oraext:query-database("select  
         max(salary) from employee",false(),false(),'jdbc/examplesDS').</summary>  
   </part>  
   <part name="detail">  
    <detail>XPath expression failed to execute. An error occurs while  
        processing the XPath expression; the expression is  
        oraext:query-database("select max(salary) from  
        employee",false(),false(),'jdbc/examplesDS'). The XPath  
        expression failed to execute; the reason was: internal xpath  
        error. Check the detailed root cause described in the exception  
        message text and verify that the XPath query is correct.</detail>  
   </part>  
   <part name="code">  
    <code>XPath expression failed to execute</code>  
   </part>  
  </subLanguageExecutionFault>  
 </bpelFault>  
А в логе managed-сервера:
 oracle.xml.sql.OracleXMLSQLException: Character ')' is not allowed in an XML tag name.  
     at oracle.xml.sql.core.OracleXMLConvert.getXML(OracleXMLConvert.java:1178)  
     at oracle.xml.sql.query.OracleXMLQuery.getXMLDOM(OracleXMLQuery.java:417)  
     at oracle.xml.sql.query.OracleXMLQuery.getXMLDOM(OracleXMLQuery.java:384)  
     at oracle.xml.sql.query.OracleXMLQuery.getXMLDOM(OracleXMLQuery.java:345)  
     at oracle.tip.pc.services.functions.ExtFunc.queryDatabase(ExtFunc.java:152)  
     at oracle.tip.pc.services.functions.ExtFuncFunction$QueryDatabaseFunction.call(ExtFuncFunction.java:359)
     ...
Причина:
XML SQL Utility (XSU) обрабатывает имя столбца как имя тега. В нашем случае имя столбца max(salary) рассматривается XSU как недопустимое имя тега, и происходит ошибка.

Вариант решения:
Необходимо добавить алиас для столбца в котором используются функции, т.е. в нашем случае:
 oraext:query-database("select max(salary) as salary from employee",
                                       false(),false(),'jdbc/examplesDS')  

суббота, 15 сентября 2012 г.

Хеширование SHA-1 и MD5 на PL/SQL и Java

  • Реализация на PL/SQL:
    • SHA-1:
       CREATE OR REPLACE  
        FUNCTION SHA1(plain_text VARCHAR2) RETURN VARCHAR2  
        AS  
        BEGIN  
         RETURN UTL_RAW.CAST_TO_VARCHAR2(UTL_ENCODE.BASE64_ENCODE(DBMS_CRYPTO.HASH (src => utl_i18n.string_to_raw (plain_text, 'AL32UTF8'), typ => DBMS_CRYPTO.HASH_SH1)));  
        END;  
      
    • MD5:
       CREATE OR REPLACE  
        FUNCTION MD5(plain_text VARCHAR2) RETURN VARCHAR2 IS  
        BEGIN  
         RETURN RAWTOHEX(DBMS_OBFUSCATION_TOOLKIT.MD5(INPUT => TL_RAW.CAST_TO_RAW(plain_text) ));  
        END;
      
  • Реализация на Java:
    import java.math.BigInteger;
    
    import java.security.MessageDigest;
    import java.security.NoSuchAlgorithmException;
    
    public class CryptoUtils {
    
        private final static char[] ALPHABET =
            "ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789+/".toCharArray();
    
        public static String sha1(String plain) {
            try {
                MessageDigest md = MessageDigest.getInstance("sha");
                md.update(plain.getBytes());
                byte[] digest = md.digest();
    
                return encode(digest);
                /* Альтернативные варианты:
                return javax.xml.bind.DatatypeConverter.printBase64Binary(digest);
                или
                return com.sun.org.apache.xml.internal.security.utils.Base64.encode(digest);
                */
            } catch (NoSuchAlgorithmException e) {
                e.printStackTrace();
                return null;
            }
        }
    
        public static String md5(String raw) {
            try {
                MessageDigest md = MessageDigest.getInstance("MD5");
                md.update(raw.getBytes(), 0, raw.length());
                return new BigInteger(1, md.digest()).toString(16);
            } catch (NoSuchAlgorithmException e) {
                e.printStackTrace();
                return null;
            }
        }
    
        public static String encode(byte[] buf) {
            int size = buf.length;
            char[] ar = new char[((size + 2) / 3) * 4];
            int a = 0;
            int i = 0;
            while (i < size) {
                byte b0 = buf[i++];
                byte b1 = (i < size) ? buf[i++] : 0;
                byte b2 = (i < size) ? buf[i++] : 0;
    
                int mask = 0x3F;
                ar[a++] = ALPHABET[(b0 >> 2) & mask];
                ar[a++] = ALPHABET[((b0 << 4) | ((b1 & 0xFF) >> 4)) & mask];
                ar[a++] = ALPHABET[((b1 << 2) | ((b2 & 0xFF) >> 6)) & mask];
                ar[a++] = ALPHABET[b2 & mask];
            }
            switch (size % 3) {
            case 1:
                ar[--a] = '=';
            case 2:
                ar[--a] = '=';
            }
            return new String(ar);
        }
    }
    

воскресенье, 29 июля 2012 г.

Управление структурой MDS с помощью MDS Explorer

  MDS Explorer - это OpenSource-проект, цель которого создание средства для доступа к Oracle MDS (Meta Data Services) репозиториям с помощью которого можно создавать, удалять, загружать, выгружать директории и файлы.
  На сайте проекта можно скачать исходники (в скомпилированном виде отсутствуют). В скомпилированном виде в одном jar-файле выкладываю здесь.
  Запуск:
  1. Выполнить следующую команду:
     java -Drepository.flavor=DB -jar MDSExplorer.jar  
    
    где DB - для доступа к репозиторию в БД;
        FILE - для доступа к репозиторию в файловой системе.
  2. Ввести параметры соединения со схемой MDS, затем нажать "Connect", выбрать нужный раздел (partition) и нажать "OK":
  3. В открывшемся окне можно выполнять действия по управлению структурой MDS:

пятница, 29 июня 2012 г.

Удаление метаданных из MDS

Все команды выполняются из wlst.cmd или wlst.sh находящегося в SOA_HOME, у меня например это:
 C:\Apps\Oracle\Weblogic\10.3.6\Oracle_SOA1\common\bin\wlst.cmd  
  • Удаление файлов
    Примечание: удаляются файлы, но не каталоги:
    1. Соединяемся с SOA-сервером:
      wls:/offline> 
       connect('weblogic','welcome1','t3://bpm-dev.mycompany.com:8001')  
      
      где weblogic - логин административного пользователя;
      welcome1 - пароль административного пользователя;
      bpm-dev.mycompany.com - хост SOA-сервера;
      8001 - порт SOA-сервера.
    2. Выполняем удаление файлов:
      wls:/soa_dev/serverConfig>
       deleteMetadata('soa-infra','soa_server1','/apps/MyComponents/**')  
      
      где soa_server1 - имя SOA-сервера;
      /apps/MyComponents - директория из которой будут удаляться файлы;
      ** - удаление в текущем каталоги в всех вложенных каталогах.
  • Удаление каталогов (рекурсивное)
    Примечание: если в удаляемом каталоге нет ни одного файла может произойти ошибка и каталог не будет удалён.
    wls:/offline>
     sca_removeSharedData('http://bpm-dev.mycompany.com:8001', 
                          'MyComponents',
                          'weblogic',
                          'welcome1')  
    
    где bpm-dev.mycompany.com - хост SOA-сервера;
    8001 - http-порт SOA-сервера;
    MyComponents - директория из корневой директории apps которую следует удалить;
    weblogic - логин административного пользователя;
    welcome1 - пароль административного пользователя.

суббота, 5 мая 2012 г.

Ошибка валидации SOA-композита при сборке на сервере Hudson

Ошибка:
При сборке композита возникает ошибка вида:
 scac:  
    [scac] Validating composite "/var/lib/hudson/jobs/SAPRetailRegistrationInventoryComments/workspace/tools/ant/../../composite.xml"  
    [scac] info: Validating composite "/var/lib/hudson/jobs/SAPRetailRegistrationInventoryComments/workspace/tools/ant/../../composite.xml"  
    [scac] info: Pass  
    [scac] error: location {/ns:composite}(12,61): Parse of component type files failed, check the adf-config.xml file : "oracle.fabric.common.FabricException: oracle.fabric.common.FabricException: javax.management.InstanceNotFoundException: com.bea:Name=RuntimeService,Type=weblogic.management.mbeanservers.runtime.RuntimeServiceMBean: com.bea:Name=RuntimeService,Type=weblogic.management.mbeanservers.runtime.RuntimeServiceMBean: javax.management.InstanceNotFoundException: com.bea:Name=RuntimeService,Type=weblogic.management.mbeanservers.runtime.RuntimeServiceMBean: com.bea:Name=RuntimeService,Type=weblogic.management.mbeanservers.runtime.RuntimeServiceMBean: oracle.fabric.common.FabricException: javax.management.InstanceNotFoundException: com.bea:Name=RuntimeService,Type=weblogic.management.mbeanservers.runtime.RuntimeServiceMBean: com.bea:Name=RuntimeService,Type=weblogic.management.mbeanservers.runtime.RuntimeServiceMBean: javax.management.InstanceNotFoundException: com.bea:Name=RuntimeService,Type=weblogic.management.mbeanservers.runtime.RuntimeServiceMBean: com.bea:Name=RuntimeService,Type=weblogic.management.mbeanservers.runtime.RuntimeServiceMBean"  
   
 BUILD FAILED  
 /var/lib/hudson/jobs/SAPRetailRegistrationInventoryComments/workspace/tools/ant/build.xml:28: The following error occurred while executing this line:  
 /u01/jdeveloper11.1.1.5/jdeveloper/bin/ant-sca-package.xml:46: The following error occurred while executing this line:  
 /u01/jdeveloper11.1.1.5/jdeveloper/bin/ant-sca-compile.xml:269: Java returned: 1 Check log file : /u01/tmp/SAPRetailRegistrationInventoryComments.err for errors  
Или следующего вида:
 scac:  
    [scac] Validating composite "/var/lib/hudson/jobs/SAPRetailRegistrationInventoryComments/workspace/tools/ant/../../composite.xml"  
    [scac] BPEL 2.0 validation of "RegistrationInventoryCommentsBPELProcess" took 391.7 milliseconds  
    [scac] >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>  
    [scac] >> modified xmlbean locale class in use  
    [scac] >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>  
    [scac] info: Validating composite "/var/lib/hudson/jobs/SAPRetailRegistrationInventoryComments/workspace/tools/ant/../../composite.xml"  
    [scac] info: Pass  
    [scac] error: location {/ns:composite/ns:import[@location='file:/var/lib/hudson/jobs/SAPRetailRegistrationInventoryComments/workspace/RegistrationInventoryCommentsBPELProcess.wsdl']}: Load of wsdl "ZINV_VERIF_receive.wsdl with Message part element undefined in wsdl [file:/var/lib/hudson/jobs/SAPRetailRegistrationInventoryComments/workspace/RegistrationInventoryCommentsBPELProcess.wsdl] part name = payload     type = {urn:sap-com:document:sap:idoc}ZINV_VERIF" failed  
    [scac] error: location {/ns:composite/ns:import[@location='file:/var/lib/hudson/jobs/SAPRetailRegistrationInventoryComments/workspace/CheckIfMsgExistsDBAdapter.wsdl']}: Load of wsdl "ZINV_VERIF_receive.wsdl with Message part element undefined in wsdl [file:/var/lib/hudson/jobs/SAPRetailRegistrationInventoryComments/workspace/CheckIfMsgExistsDBAdapter.wsdl] part name = OutputParameters     type = {http://xmlns.oracle.com/pcbpel/adapter/db/RTKESBPROTO/INTEGRATION_PKG/CHECK_IF_MSG_EXISTS/}OutputParameters" failed  
    [scac] error: location {/ns:composite/ns:import[@location='http://oracle-sb.tsretail.ru:9030/soa-infra/directWsdl/SAP/SAPRetailRegistrationCommon/RegistrationCommonService?resource=%2FRegistrationCommonService.wsdl']}(20,30): Load of wsdl "ZINV_VERIF_receive.wsdl with Message part element undefined in wsdl [http://oracle-sb.tsretail.ru:9030/soa-infra/directWsdl/SAP/SAPRetailRegistrationCommon/RegistrationCommonService?resource=%2FRegistrationCommonService.wsdl] part name = payload     type = {http://www.tsretail.ru/integration/sap/retail}registration_reply" failed  
    [scac] error: location {/ns:composite/ns:import[@location='file:/var/lib/hudson/jobs/SAPRetailRegistrationInventoryComments/workspace/RegistrationCommonServiceWrapper.wsdl']}: Load of wsdl "ZINV_VERIF_receive.wsdl with Message part element undefined in wsdl [http://oracle-sb.tsretail.ru:9030/soa-infra/directWsdl/SAP/SAPRetailRegistrationCommon/RegistrationCommonService?resource=%2FRegistrationCommonService.wsdl] part name = payload     type = {http://www.tsretail.ru/integration/sap/retail}registration_reply" failed  
    [scac] error: location {/ns:composite/ns:import[@location='http://oracle-sb.tsretail.ru:9030/soa-infra/directWsdl/SAP/SAPRetailRegistrationCommon/RegistrationCommonService?resource=%2FRegistrationCommonService.wsdl']}(20,30): Load of wsdl "ZINV_VERIF_receive.wsdl with Message part element undefined in wsdl [http://oracle-sb.tsretail.ru:9030/soa-infra/directWsdl/SAP/SAPRetailRegistrationCommon/RegistrationCommonService?resource=%2FRegistrationCommonService.wsdl] part name = payload     type = {http://www.tsretail.ru/integration/sap/retail}registration_reply" failed  
    [scac] error: location {/ns:composite/ns:import[@location='file:/var/lib/hudson/jobs/SAPRetailRegistrationInventoryComments/workspace/ZINV_VERIF_receive.wsdl']}: Load of wsdl "ZINV_VERIF_receive.wsdl with Message part element undefined in wsdl [file:/var/lib/hudson/jobs/SAPRetailRegistrationInventoryComments/workspace/ZINV_VERIF_receive.wsdl] part name = event_ZINV_VERIF     type = {urn:sap-com:document:sap:idoc}ZINV_VERIF" failed  
    [scac] error: in RegistrationInventoryCommentsBPELProcess.bpel(38): Error loading schema from file:/var/lib/hudson/jobs/SAPRetailRegistrationInventoryComments/workspace/RegistrationInventoryCommentsBPELProcess.wsdl [Cause=Error in getting XML input stream: oramds:/apps/core.xsd: Error encountered while creating the MDS Session]  
    [scac] error: in RegistrationInventoryCommentsBPELProcess.bpel(81): "element" is set to "ns4:error" on this <bpel:variable> but it cannot be resolved (check value of "element", imports, WSDLs or XSDs).  
    [scac] error: in RegistrationInventoryCommentsBPELProcess.bpel(94): "element" is set to "ns8:comments" on this <bpel:variable> but it cannot be resolved (check value of "element", imports, WSDLs or XSDs).  
    [scac] info: in RegistrationInventoryCommentsBPELProcess.bpel(100): Copy rule not checked - from-spec type-of "xsd:string", to-spec type-of "unspecified".  
    [scac] info: in RegistrationInventoryCommentsBPELProcess.bpel(104): Copy rule not checked - from-spec type-of "xsd:string", to-spec type-of "unspecified".  
    [scac] info: in RegistrationInventoryCommentsBPELProcess.bpel(108): Copy rule not checked - from-spec type-of "xsd:string", to-spec type-of "unspecified".  
    [scac] info: in RegistrationInventoryCommentsBPELProcess.bpel(112): Copy rule not checked - from-spec type-of "xsd:string", to-spec type-of "unspecified".  
    [scac] info: in RegistrationInventoryCommentsBPELProcess.bpel(116): Copy rule not checked - from-spec type-of "xsd:string", to-spec type-of "unspecified".  
    [scac] error: in RegistrationInventoryCommentsBPELProcess.bpel(146): Variable "OnMessage_process_InputVariable" does not have a part named "payload" - check expression segment "$OnMessage_process_InputVariable.payload"  
    [scac] error: in RegistrationInventoryCommentsBPELProcess.bpel(160): Variable "OnMessage_ZINV_VERIF_InputVariable" does not have a part named "event_ZINV_VERIF" - check expression segment "$OnMessage_ZINV_VERIF_InputVariable.event_ZINV_VERIF"  
    [scac] error: in RegistrationInventoryCommentsBPELProcess.bpel(160): The XPath segment "child::ns5:IDOC" cannot be resolved.  
    [scac] error: in RegistrationInventoryCommentsBPELProcess.bpel(161): Variable "InvokeCheckIfMsgExists_InputVariable" does not have a part named "InputParameters" - check expression segment "$InvokeCheckIfMsgExists_InputVariable.InputParameters"  
    [scac] error: in RegistrationInventoryCommentsBPELProcess.bpel(161): The XPath segment "child::ns6:P_IN_MSG_SYS_ID" cannot be resolved.  
    [scac] error: in RegistrationInventoryCommentsBPELProcess.bpel(165): Variable "InvokeCheckIfMsgExists_InputVariable" does not have a part named "InputParameters" - check expression segment "$InvokeCheckIfMsgExists_InputVariable.InputParameters"  
    [scac] error: in RegistrationInventoryCommentsBPELProcess.bpel(165): The XPath segment "child::ns6:P_IN_MSG_TYPE" cannot be resolved.  
    [scac] error: in RegistrationInventoryCommentsBPELProcess.bpel(177): Variable "InvokeCheckIfMsgExists_OutputVariable" does not have a part named "OutputParameters" - check expression segment "$InvokeCheckIfMsgExists_OutputVariable.OutputParameters"  
    [scac] error: in RegistrationInventoryCommentsBPELProcess.bpel(177): The XPath segment "child::ns6:P_ERR_CODE" cannot be resolved.  
    [scac] error: in RegistrationInventoryCommentsBPELProcess.bpel(184): Variable "InvokeCheckIfMsgExists_OutputVariable" does not have a part named "OutputParameters" - check expression segment "$InvokeCheckIfMsgExists_OutputVariable.OutputParameters"  
    [scac] error: in RegistrationInventoryCommentsBPELProcess.bpel(184): The XPath segment "child::ns6:P_ERR_MESSAGE" cannot be resolved.  
    [scac] error: in RegistrationInventoryCommentsBPELProcess.bpel(188): Variable "InvokeCheckIfMsgExists_OutputVariable" does not have a part named "OutputParameters" - check expression segment "$InvokeCheckIfMsgExists_OutputVariable.OutputParameters"  
    [scac] error: in RegistrationInventoryCommentsBPELProcess.bpel(188): The XPath segment "child::ns6:P_ERR_CODE" cannot be resolved.  
    [scac] error: in RegistrationInventoryCommentsBPELProcess.bpel(199): Variable "InvokeCheckIfMsgExists_OutputVariable" does not have a part named "OutputParameters" - check expression segment "$InvokeCheckIfMsgExists_OutputVariable.OutputParameters"  
    [scac] error: in RegistrationInventoryCommentsBPELProcess.bpel(199): The XPath segment "child::ns6:P_RESULT" cannot be resolved.  
    [scac] error: in RegistrationInventoryCommentsBPELProcess.bpel(210): Variable "OnMessage_ZINV_VERIF_InputVariable" does not have a part named "event_ZINV_VERIF" - check expression segment "$OnMessage_ZINV_VERIF_InputVariable.event_ZINV_VERIF"  
    [scac] warning: in RegistrationInventoryCommentsBPELProcess.bpel(211): The node <bpel:to> refers to a bpel:variable (via the attribute "variable") - this bpel:variable needs to be defined correctly.  
    [scac] error: in RegistrationInventoryCommentsBPELProcess.bpel(219): Variable "OnMessage_ZINV_VERIF_InputVariable" does not have a part named "event_ZINV_VERIF" - check expression segment "$OnMessage_ZINV_VERIF_InputVariable.event_ZINV_VERIF"  
    [scac] info: File to validate does not exist fault-policies.xml:/var/lib/hudson/jobs/SAPRetailRegistrationInventoryComments/workspace/tools/ant/../../fault-policies.xml (No such file or directory)  
    [scac] info: Checking validateMessages....testsuites/SAPRetailRegistrationInventoryComments/messages  
    [scac] info: Begin validateIncludes....testsuites/SAPRetailRegistrationInventoryComments/includes  
    [scac] info: Begin validateTests....testsuites/SAPRetailRegistrationInventoryComments/tests  
    [scac] info:      Check validateTestDocument....testsuites/SAPRetailRegistrationInventoryComments/tests/CommonRegistration-Test.xml  
    [scac] info:      Check doSchemaValidation....testsuites/SAPRetailRegistrationInventoryComments/tests/CommonRegistration-Test.xml  
    [scac] info:      Check isSetInitiate ....testsuites/SAPRetailRegistrationInventoryComments/tests/CommonRegistration-Test.xml  
    [scac] info:      Check WireActionsModel ....testsuites/SAPRetailRegistrationInventoryComments/tests/CommonRegistration-Test.xml  
    [scac] info:      Check WireActionsModel ....testsuites/SAPRetailRegistrationInventoryComments/tests/CommonRegistration-Test.xml  
    [scac] info:      validateTestDocument Pass  
    [scac] info: validateTests Pass  
   
 BUILD FAILED  
 /var/lib/hudson/jobs/SAPRetailRegistrationInventoryComments/workspace/tools/ant/build.xml:39: The following error occurred while executing this line:  
 /u01/jdeveloper11.1.1.5/jdeveloper/bin/ant-sca-package.xml:46: The following error occurred while executing this line:  
 /u01/jdeveloper11.1.1.5/jdeveloper/bin/ant-sca-compile.xml:269: Java returned: 1 Check log file : /u01/tmp/SAPRetailRegistrationInventoryComments.err for errors  
   
 Total time: 4 seconds  
 Recording test results  
 [DEBUG] Skipping watched dependency update for build: SAPRetailRegistrationInventoryComments #31 due to result: FAILURE  
 Finished: FAILURE  

Причина:
Проблема с доступом к MDS-репозиторию или к сервису через механизм прямого связывания.

Вариант решения:
Разобраться почему не удается получить доступ (лучший вариант) или убрать валидацию композита при сборке (но до этого убедится в успешной валидации на рабочей станции), для этого:
  1. Перейти в директорию в которой установлен Oracle JDeveloper:
     $ cd /u01/jdeveloper11.1.1.5/  
    
  2. Перейти в директорию с Ant-скриптами для SOA Suite:
     $ cd jdeveloper/bin  
    
  3. Забэкапить файл ant-sca-package.xml:
     $ cp ant-sca-package.xml ant-sca-package.xml.original
    
  4. Изменить файл ant-sca-package.xml:
     $ vi ant-sca-package.xml
  5. Найти в нём следующие строки:
      ...
        <target name="scac-validate" depends="init">  
          <echo message="Running scac-validate in ${scac.input}"/>  
          <antcall target="scac" inheritall="true"/>   
        </target>  
      ...
    
    Заменить их на следующие:
      ...
        <target name="scac-validate" depends="init">  
          <echo message="Running scac-validate in ${scac.input}"/>  
          <!--antcall target="scac" inheritall="true"/-->  
          <echo message="Skipping scac-validate in ${scac.input}"/>  
        </target>  
      ...