Returning a result from a loaded Java class in Oracle resultset

First off, I'm an MSSQL developer, so my apologies of I'm off on some of the terminology or basic understanding of this this works :)

I've been asked to extract some data from an Oracle database, but one of the columns I need is encrypted with a certificate. I received a Java class from the vendor that decrypts and writes the data to a file, but I want it returned inline, in my result set. Essentially, I want to do this:

SELECT MyKey, SomeValue1, SomeValue2, decryptThisValue(SomeEncryptedValue) as DecryptedValue
FROM MyTable

I have successfully used loadjava to load their library and map it to a function in Oracle, but it's doesn't do what I want - I can call the function, and it will successfully decrypt my value and write it to disk, but I want it returned as part of the query results. It's my understanding that the "main" in a java class always has to return "void", so I'm stuck here about how to get my value out.

In .Net/MSSQL, I can set the return type of my function call to whatever I want, so I can return the value as a bytestream or Base64 string or whatever, and I'm looking to do the same thing in java/Oracle. Any help is appreciated, and again, I apologize if I'm missing something really obvious here.
LVL 28
Ryan McCauleyData and Analytics ManagerAsked:
Who is Participating?
 
for_yanCommented:
In Java Stored proceures you can publish any static method of a class as Orcale SQL FUNCTION
and then you can use FUNCTION in your query
Say, see section 2.3.1.2. in
http://download.oracle.com/docs/html/A95261_01/jdgwork.htm
0
 
for_yanCommented:
But, I guess, you eed to have your decryption method as a static function
of your class. You actually even don't meed to have main method in such class
but staic method for what you want to do is essential
0
 
Ryan McCauleyData and Analytics ManagerAuthor Commented:
That makes sense - I was under the (mistaken) impression that you had to map your function to the Main method, but it would make sense that you could map the function to any of the methods in the class, as long as they're public and static.

I'll try this and let you all know. Thanks!
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
for_yanCommented:
yes, I once used Java Store Procedures (long time ago) - I rememeber I was using
static method
0
 
Suraj_MathewCommented:
Execute your query as usual...

SELECT MyKey, SomeValue1, SomeValue2, SomeEncryptedValue FROM MyTable

now retrieve the values of each row using resultSet , and store that in a class (Transfer Object) , which is having the respective variables , and their getter and setter methods.

So while setting the encrypted value , first decrypt using your java class and then store , as shown below...

You should extend your class containing the "decryptThisValue()" method.
.
.
.
SELECT MyKey, SomeValue1, SomeValue2, SomeEncryptedValue
FROM MyTable
.
.

resultSet = preparedStatement.executeQuery();

Collection outputCollection = new ArrayList();
OutputTransferObject oto = null;

while(resultSet.next()) {
    oto = new OutputTransferObject();

    oto.setKey(resultSet.getString("MyKey"));    
    oto.setValue1(resultSet.getString("SomeValue1"));    
    .
    .
   oto.setEncryptedValue(resultSet.getString("SomeEncryptedValue"));
   oto.setDecryptedValue(decryptThisValue(resultSet.getString("SomeEncryptedValue")));  
 
   outputCollection .add(oto) ;
}



 OutputTransferObject.java
0
 
Ryan McCauleyData and Analytics ManagerAuthor Commented:
I'm opening a related question since I'm not able to get the mapping statements correct, but I believe this problem (of returning a value at all) is resolved.

The follow-up is here (http://www.experts-exchange.com/Database/Oracle/Q_26951585.html) - any additional help is appreciated, and I'm happy to hand out more points.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.