• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Extract Blob in Oracle Procedure

 
Ranch Hand
Posts: 138
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi to All,

We are facing an issue.
We have a table with column of type BLOB.
The column is inserted with java Object (A DVO contains geeters and setters) of byte[] data type from other of source which developed in Java program .


Now in the procedure I need to retrieve this BLOB Object and retrive the data fir futher procedure.
So, we need help that how can we retrieve Oracle Blob Object from that column and retrieve the data from that Object in Oracle procedure.
 
Bartender
Posts: 1971
17
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Did you do some searches online for code that's already written for this?

For example:

https://docs.oracle.com/javase/tutorial/jdbc/basics/blob.html

http://www.java2s.com/Code/Java/Database-SQL-JDBC/GetBLOBdatafromresultset.htm

Please post the code you have so far so we can help you.

-- mike
 
Rancher
Posts: 4801
50
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
When you say "in Oracle procedure", do you mean you want to handle this in PL/SQL?
 
naga eswar
Ranch Hand
Posts: 138
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Dave Tolls wrote:When you say "in Oracle procedure", do you mean you want to handle this in PL/SQL?



Thank  you Dave Tolls for your reply. I need handle in PL/SQL Procedures.
 
naga eswar
Ranch Hand
Posts: 138
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Mike London wrote:Did you do some searches online for code that's already written for this?

For example:

https://docs.oracle.com/javase/tutorial/jdbc/basics/blob.html

http://www.java2s.com/Code/Java/Database-SQL-JDBC/GetBLOBdatafromresultset.htm

Please post the code you have so far so we can help you.

-- mike



Thank You Mike. But this is in java code how to retrieve or Insert Blob or Clob Object which we know already.
Here the main problem is that we need to retrieve the Blob Object which contains DVO Object in Byte[] form and use in the PL/SQL Procedures.
We need help.
 
Dave Tolls
Rancher
Posts: 4801
50
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Assuming this "Java Object" is a serialised instance of your DVO then you are going to need to write some Java code to convert that back to a Java object and then pick out the data you want.
I don't know what state Java in Oracle is in, but that would be the place to do it and call that Java stored procedure from your PL/SQL to get the info you need.

As for how to write that, you'll need to dig up the Oracle documentation for your version.
 
naga eswar
Ranch Hand
Posts: 138
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Dave Tolls wrote:Assuming this "Java Object" is a serialised instance of your DVO then you are going to need to write some Java code to convert that back to a Java object and then pick out the data you want.
I don't know what state Java in Oracle is in, but that would be the place to do it and call that Java stored procedure from your PL/SQL to get the info you need.

As for how to write that, you'll need to dig up the Oracle documentation for your version.




Dave Tolls wrote:Assuming this "Java Object" is a serialised instance of your DVO then you are going to need to write some Java code to convert that back to a Java object and then pick out the data you want.
I don't know what state Java in Oracle is in, but that would be the place to do it and call that Java stored procedure from your PL/SQL to get the info you need.

As for how to write that, you'll need to dig up the Oracle documentation for your version.



Thanks Dave. Yours Assuming is right. "Java Object" is a serialised instance of DVO and it is inserting in table. Below is the code.


       Finally  arrByteArrOpStrm is inserting in blob column of a table and Yes, java code is there is convert back.
       But we need handle this in PL/SQL Procedure.
 
Dave Tolls
Rancher
Posts: 4801
50
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Converting the serialised BLOB back into meaningful data in PL/SQL is going to be very hard.
You'd need to look into what the structure of the data is (how Java serialises things).

You really need to use Java.
 
naga eswar
Ranch Hand
Posts: 138
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Dave Tolls wrote:Converting the serialised BLOB back into meaningful data in PL/SQL is going to be very hard.
You'd need to look into what the structure of the data is (how Java serialises things).

You really need to use Java.



Thanks Dave. Yes, I googled the and till I found the  same thing.
Trying for if any alterate.
 
Dave Tolls
Rancher
Posts: 4801
50
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
The alternative is not to store serialised Java objects in the database.
They should (at the least) be stored in a structured format (eg JSON or XML).

That's the root cause of your problem.
Whoever decided this was a Good Idea was probably just taking a shortcut and a decent review should have resulted in it being rejected as an idea.
 
Don't get me started about those stupid light bulbs.
reply
    Bookmark Topic Watch Topic
  • New Topic