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 McCauleyEnterprise Analytics ManagerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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 McCauleyEnterprise 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
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook 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 McCauleyEnterprise 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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.