Working with Oracle XMLType and JPA over Weblogic
Posted: 03/11/2013 Filed under: EE, Java, Oracle, PL/SQL, Weblogic | Tags: Java EE, JDeveloper, JPA, Oracle Database, PL/SQL, Weblogic, XMLType Leave a comment »I’ve been working with the object type of Oracle databases for XML for a long time, but always in the context of PL/SQL programming. Last week, I developed a Java EE module that made use of JPA entities, supported by tables with SYS.XMLType columns, so I’d like to share my findings with an example.
First of all, here you have the creation sentence of a table that offers persistence to a simple messaging interface:
create table messages
(
message_id number constraint message_pk primary key,
reception_date date not null,
msg_content xmltype not null
constraint message_msg_content_ck check(
xmlisvalid(msg_content) = 1)
)
xmltype column msg_content store as clob xmlschema
"http://testdomain.com/Messages.xsd"
element "Messages";
This is an excerpt of a JPA entity that could represent the former table:
package com.wordpress.fcosfc.test.messaging;
...
import org.eclipse.persistence.annotations.Customizer;
@Entity
@Table(name = "MESSAGES")
@Customizer(XMLTypeAttributeCustomizer.class)
public class Messages implements Serializable {
@Id
@Column(name = "MESSAGE_ID", nullable = false)
private BigDecimal messageId;
@Temporal(TemporalType.TIMESTAMP)
@Column(name = "RECEPTION_DATE", nullable = false)
private Date receptionDate;
@Lob
@Column(nullable = false)
private String msgContent;
...
}
I’m sure you’ve realized that there is an special annotation Customizer, which makes reference to this class:
package com.wordpress.fcosfc.test.messaging;
import org.eclipse.persistence.config.DescriptorCustomizer;
import org.eclipse.persistence.descriptors.ClassDescriptor;
import org.eclipse.persistence.mappings.xdb.DirectToXMLTypeMapping;
public class XMLTypeAttributeCustomizer implements DescriptorCustomizer {
@Override
public void customize(final ClassDescriptor descriptor) throws Exception {
descriptor.removeMappingForAttributeName("msg_content");
final DirectToXMLTypeMapping mapping = new DirectToXMLTypeMapping();
mapping.setAttributeName("msg_content");
mapping.setFieldName("MSG_CONTENT");
mapping.getField().setColumnDefinition("sys.XMLTYPE");
descriptor.addMapping(mapping);
}
}
This class has a method that changes the default mapping of the EclipseLink JPA provider in order to use the proper type from Oracle: SYS.XMLType.
Finally, I’d like to point out that the first time I deployed my module over an Oracle Weblogic 10.3.5 application server I got a ClassNotFoundException: oracle/xdb/xmltype , so I had to extend my Weblogic domain in order to support Oracle XMType. Here you have my reference about how to do it, I just followed the instructions to support Oracle XDB.
References
Weblogic: Interoperating with Oracle AQ JMS. A tip for sending messages
Posted: 02/08/2013 Filed under: Advanced Queuing, EE, Java, Oracle, PL/SQL, Weblogic | Tags: AQ, Database, Java EE, JMS, Oracle, Oracle Database, Weblogic Leave a comment »I usually work with Oracle Streams Advanced Queuing (AQ) integrated with Weblogic. In PL/SQL programs you can set the exception queue where you want to move the messages that couldn’t be delivered to their destinations, when you send a message, for example:
declare
enq_msgid raw(16) ;
eopt dbms_aq.enqueue_options_t;
mprop dbms_aq.message_properties_t;
message sys.aq$_jms_text_message;
begin
mprop.priority := 1;
mprop.exception_queue := 'test_exception_q';
message := sys.aq$_jms_text_message.construct() ;
message.set_text('This is a Test') ;
dbms_aq.enqueue(queue_name => 'test_q',
enqueue_options => eopt,
message_properties => mprop,
payload => message,
msgid => enq_msgid) ;
commit;
end;
But, how to set the exception queue when you’re programming in Java, in the context of an integration between Oracle AQ and Weblogic? The solution I’ve found is to set the string property called JMS_OracleExcpQ to the message with the name of the Oracle AQ exception queue, for example:
...
javax.jms.TextMessage msg = jmsSession.createTextMessage();
msg.setStringProperty("JMS_OracleExcpQ", "TEST.TEST_EXCEPTION_Q");
msg.setText("This is a Test");
jmsProducer.send(msg);
...
An important advice: put the name in uppercase and preceded by the name of the schema.
Finally, I’d like to point out that this is a good solution in the context of an integration between Oracle AQ and Weblogic, but makes the code non portable, if you want to change the messaging provider of your Weblogic Application Server.
References
- Oracle Streams Advanced Queuing (AQ) integrated with Weblogic
- Oracle Streams Advanced Queuing User’s Guide
Fighting with sockets!
Posted: 08/23/2012 Filed under: Java, Oracle, PL/SQL, SE | Tags: Debug, Java SE, JDeveloper, Microsoft .NET, Oracle Database, Sockets Leave a comment »Last week, I was appointed to develop a Java program that should connect to an external secure socket, in order to get data provided by a partner company. Another requisite was that the module should be stored on an Oracle 11g Database, so I must use a 1.5 JDK. Easy, I thought!
First of all, I review Java Secure Socket Extension (JSSE) Reference Guide. Our company partner IT team provided me with the key store containing the certificate I should trust and I decide to program a custom SSL context:
...
KeyStore keyStoreTrust = KeyStore.getInstance("PKCS12");
keyStoreTrust.load(this.getClass().getResourceAsStream("KeyStoreTrust.pfx"),
"password".toCharArray());
TrustManagerFactory trustManagerFactory = TrustManagerFactory.getInstance("PKIX");
trustManagerFactory.init(keyStoreTrust);
SSLContext sslContext = SSLContext.getInstance("SSL");
sslContext.init(null, trustManagerFactory.getTrustManagers(), null);
...
The first problem arose when the server socket (developed in Microsoft .NET C#) unexpectedly closed the connection during the handshake, the support guy of my partner company said me that they got the following error message: “The client and server cannot communicate, because they do not possess a common algorithm”. Therefore, I delved into the problem and finally I realized that the server wanted to use a TLS_ECDHE_RSA_WITH_AES_128_CBC_SHA cipher suite, which wasn’t supported by the security providers shipped with the JDK 1.5 I’d like to point out that a key point to find out the source of the error was to activate the debug of the SSL connection:
System.setProperty("javax.net.debug", "ssl");
Hence, I decided to add to my program the well-known Bouncy Castle security provider, which supports the required cipher suite and it’s 1.5 compliant:
Security.addProvider(new BouncyCastleProvider());
Once I sorted out the problem, everything started to work properly, at least as an stand-alone client! So, I created a “Loadjava and Java Stored Procedures” profile in my JDeveloper IDE, in order to deploy the software to the Oracle Database 11.2, but when I tried to do it I got the following errors:
Invoking loadjava on connection 'Test11g_Paco' with arguments: -order -resolve -definer -thin -resolver ((* TEST) (* PUBLIC) (* -)) -synonym errors : class org/bouncycastle/jcajce/provider/asymmetric/ec/BCECPrivateKey ORA-29552: verification warning: java.lang.NoClassDefFoundError: java/security/interfaces/ECPrivateKey errors : class org/bouncycastle/jcajce/provider/asymmetric/ec/BCECPublicKey ORA-29552: verification warning: java.lang.NoClassDefFoundError: java/security/interfaces/ECPublicKey errors : class org/bouncycastle/jcajce/provider/asymmetric/ecgost/BCECGOST3410PrivateKey ORA-29552: verification warning: java.lang.NoClassDefFoundError: java/security/interfaces/ECPrivateKey errors : class org/bouncycastle/jcajce/provider/asymmetric/ecgost/BCECGOST3410PublicKey ORA-29552: verification warning: java.lang.NoClassDefFoundError: java/security/interfaces/ECPublicKey errors : class org/bouncycastle/jce/provider/JCEECPrivateKey ORA-29552: verification warning: java.lang.NoClassDefFoundError: java/security/interfaces/ECPrivateKey errors : class org/bouncycastle/jce/provider/JCEECPublicKey ORA-29552: verification warning: java.lang.NoClassDefFoundError: java/security/interfaces/ECPublicKey Loadjava finished.
I can’t understand the problem because the interfaces java.security.interfaces.ECPublicKey and java.security.interfaces.ECPrivateKey are available in 1.5 and the Oracle Database 11.2 JVM is supposed to be 1.5 compliant, but I couldn’t find any satisfactory solution.
SQL statement to get the weeks before the current date
Posted: 04/28/2012 Filed under: SQL | Tags: Database, Oracle, Oracle Database, SQL Leave a comment »I recently had to design a report to analyze the data of a week, so the user had to select that week using an input control. The data source of the combo box was the following SQL statement that runs in an Oracle Database:
select to_char(next_day(trunc(sysdate, 'DAY'), 'SATURDAY') - (level * 7) - 6,
'mm/dd/yyyy')
|| ' - '
|| to_char(next_day(trunc(sysdate, 'DAY'), 'SATURDAY') - (level * 7),
'mm/dd/yyyy') week
from dual
connect by level <= 52
order by level
This code employs the hierarchical query clause CONNECT BY to select the initial and the final day of the weeks before the current date, a period of a year more or less.
Debugging Java Stored Procedures
Posted: 06/15/2011 Filed under: Oracle, PL/SQL, SE | Tags: Debug, JDeveloper, Oracle Database, PL/SQL, SE, SQL*Plus, SQLDeveloper 1 Comment »I’ve been a fan of developing Java software stored in Oracle Databases for the last years, it’s the perfect partner of PL/SQL, where this structured language is difficult to deal with or simply can’t achieve the goal.
One of my first problems was remote debugging, a common issue for every programmer.
First of all, I asked my DBA for the DEBUG CONNECT SESSION system privilege. When I got it, I had to configure my JDeveloper project for remote debugging, I started using the version 10g of this powerful IDE, so I changed the project properties in order to listen for JPDA (Java Platform Debugger Architecture) connections:
Nowadays, I’m working with JDeveloper 11g, where I have to configure a Run/Debug/Profile within the project properties, clicking on the Remote Debugging check-box of the Launch Settings and adjusting the same parameters of the former version:
Later on, the next step was to set the breakpoints I needed in the code, so I could go to the menu Run → Debug, in order to listen for remote debugging sessions. At this point, I was asked for the details of the connection:
Once I started the listening process, I could check for it checking the Run Manager.
The next step was to run SQL*Plus (nowadays I use SQLDeveloper), logging on the Database and calling the procedure dbms_debug_jdwp.connect_tcp with two VARCHAR2 parameters: the IP direction of my PC and the port where my IDE was listening for JPDA connections.
After that, I started my debugging session calling the PL/SQL wrapper from SQL*Plus.
When I finished my debugging session, I ran the procedure dbms_debug_jdwp.disconnect from SQL*Plus and I used the Run Manager of JDeveloper to terminate the listening process.
Finally, I would like to talk of some problems I’ve suffered from: sometimes the debugger disconnects the session without any reason, others it disconnects when the Java code throws an exception in order to be managed by the calling PL/SQL, both times the Oracle Database connection is finished too.
