• 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 all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Devaka Cooray
  • Knute Snortum
  • Paul Clapham
  • Tim Cooke
Sheriffs:
  • Liutauras Vilda
  • Jeanne Boyarsky
  • Bear Bibeault
Saloon Keepers:
  • Tim Moores
  • Stephan van Hulst
  • Ron McLeod
  • Piet Souris
  • Frits Walraven
Bartenders:
  • Ganesh Patekar
  • Tim Holloway
  • salvin francis

Extract Blob in Oracle Procedure  RSS feed

 
Ranch Hand
Posts: 138
  • Mark post as helpful
  • send pies
  • 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: 1664
17
  • Mark post as helpful
  • send pies
  • 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
 
Master Rancher
Posts: 4072
47
  • Mark post as helpful
  • send pies
  • 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
  • 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
  • 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
Master Rancher
Posts: 4072
47
  • Mark post as helpful
  • send pies
  • 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
  • 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
Master Rancher
Posts: 4072
47
  • Mark post as helpful
  • send pies
  • 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
  • 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
Master Rancher
Posts: 4072
47
  • Mark post as helpful
  • send pies
  • 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.
 
They gave me pumpkin ice cream. It was not pumpkin pie ice cream. Wiping my tongue on this tiny ad:
ScroogeXHTML - small and flexible RTF to HTML converter library
https://coderanch.com/t/710903/ScroogeXHTML-RTF-HTML-XHTML-converter
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!