Re: JVM in the database

From: Tim Hall <tim_at_oracle-base.com>
Date: Tue, 10 Dec 2019 14:27:25 +0000
Message-ID: <CAP=5zEgO0b0JcTjRAoUZLkDYHsSQ1+ya=QQ5AssuWv=gVma1sQ_at_mail.gmail.com>



Having said that, maybe I'm not guilty. My example uses,

int size = myBlob.getBufferSize();

According to the docs, this does the following.

getBufferSize(): Returns the ideal buffer size, according to calculations by the JDBC driver, to use in reading and writing BLOB data. This value is a multiple of the chunk size (see getChunkSize() below) and is close to 32K.

I'll consider myself off the hook. :)

Cheers

Tim...

On Tue, Dec 10, 2019 at 2:18 PM Tim Hall <tim_at_oracle-base.com> wrote:

> Hi.
>
> That looks strangely familiar.
>
> https://oracle-base.com/articles/8i/export-blob
>
> At it seems it's my fault your vendor didn't have a limit, because they
> probably copied me and I don't. :)
>
> From 9i onward you could do it with UTL_FILE instead of a Java stored
> procedure..
>
> https://oracle-base.com/articles/9i/export-blob-9i
>
> Cheers
>
> Tim...
>
> On Mon, Dec 9, 2019 at 11:08 PM Noveljic Nenad <
> nenad.noveljic_at_vontobel.com> wrote:
>
>> You‘ve done it correctly by limiting the buffer size for larger blobs.
>> But our vendor, unfortunately, didn‘t, so we‘re getting OutOfMemoryError
>> for the blobs larger than ~512M. Curiously, this error can‘t be fixed by
>> just configuring more memory, as it’s a consequence of some undocumented
>> hard-coded limits specific to the database JVM:
>>
>> https://nenadnoveljic.com/blog/troubleshooting-java-lang-outofmemoryerror-in-the-oracle-database/
>>
>> Thanks,
>> Nenad
>>
>>
>>
>> *Von: *Jeff Chirco <backseatdba_at_gmail.com>
>> *Datum *Montag, 09. Dez. 2019, 9:43 PM
>> *An: *Noveljic Nenad <nenad.noveljic_at_vontobel.com>
>> *Cc: *ORACLE-L (oracle-l_at_freelists.org) <oracle-l_at_freelists.org>
>> *Betreff: *Re: JVM in the database
>>
>> Here you go. I found this on the internet years ago so I take no credit
>> no do I understand much about it. But it works great.
>>
>> create or replace and compile java source named blobhandler as
>> import java.lang.*;
>> import java.sql.*;
>> import oracle.sql.*;
>> import java.io.*;
>> public class BlobHandler {
>> public static void ExportBlob(String myFile, BLOB myBlob) throws
>> Exception {
>> File binaryFile = new File(myFile);
>> FileOutputStream outStream = new FileOutputStream(binaryFile);
>> InputStream inStream = myBlob.getBinaryStream();
>> int size;
>> if (myBlob.length()> 20000000) { // tune this to whatever
>> appropriate value
>> size = 20000000;
>> } else {
>> size = (int)myBlob.length();
>> }
>> byte[] buffer = new byte[size];
>> int length = -1;
>> while ((length = inStream.read(buffer)) != -1)
>> {
>> outStream.write(buffer, 0, length);
>> outStream.flush();
>> }
>> inStream.close();
>> outStream.close();
>> }
>> }
>>
>> CREATE OR REPLACE PROCEDURE ExportBlobJava (p_file IN VARCHAR2,
>>
>> p_blob IN BLOB)
>> AS
>> LANGUAGE JAVA
>> NAME 'BlobHandler.ExportBlob(java.lang.String, oracle.sql.BLOB)';
>>
>>
>>
>> On Fri, Dec 6, 2019 at 7:21 AM Noveljic Nenad <
>> nenad.noveljic_at_vontobel.com> wrote:
>>
>>> Hi Jeff,
>>>
>>>
>>>
>>> Have you ever processed files larger than 536870895 bytes?
>>>
>>>
>>>
>>> Could you share the snippet which writes the BLOB into a file? Do you
>>> possibly stream it to an InputStream object, buffer it, and then stream it
>>> to the file with the FileOutputStream object?
>>>
>>>
>>>
>>> If yes, what’s the buffer size? The code to allocate the buffer should
>>> look similar to this:
>>>
>>>
>>>
>>> byte[] buffer = new byte[bufferSize];
>>>
>>>
>>>
>>> Best regards,
>>>
>>>
>>>
>>> Nenad
>>>
>>>
>>>
>>> https://nenadnoveljic.com/blog/
>>>
>>>
>>>
>>> I use a java procedure to dump blob files to an actual file. We store
>>> some PDF's in the database as blob and at times they need to be dumped
>>> to a files. Usually its when they want hundreds at time. We found doing
>>> this in a loop with PL/SQL was really slow and slower over time. I found a
>>> Java procedure to do this and is is super quick.
>>>
>>> We also have another to get file and directory information. It returned
>>> back more information that PL/SQL and also was really fast.
>>>
>>>
>>>
>>> ____________________________________________________
>>>
>>> Please consider the environment before printing this e-mail.
>>>
>>> Bitte denken Sie an die Umwelt, bevor Sie dieses E-Mail drucken.
>>>
>>>
>>> Important Notice
>>> This message is intended only for the individual named. It may contain
>>> confidential or privileged information. If you are not the named addressee
>>> you should in particular not disseminate, distribute, modify or copy this
>>> e-mail. Please notify the sender immediately by e-mail, if you have
>>> received this message by mistake and delete it from your system.
>>> Without prejudice to any contractual agreements between you and us which
>>> shall prevail in any case, we take it as your authorization to correspond
>>> with you by e-mail if you send us messages by e-mail. However, we reserve
>>> the right not to execute orders and instructions transmitted by e-mail at
>>> any time and without further explanation.
>>> E-mail transmission may not be secure or error-free as information could
>>> be intercepted, corrupted, lost, destroyed, arrive late or incomplete. Also
>>> processing of incoming e-mails cannot be guaranteed. All liability of
>>> Vontobel Holding Ltd. and any of its affiliates (hereinafter collectively
>>> referred to as "Vontobel Group") for any damages resulting from e-mail use
>>> is excluded. You are advised that urgent and time sensitive messages should
>>> not be sent by e-mail and if verification is required please request a
>>> printed version. Please note that all e-mail communications to and from the
>>> Vontobel Group are subject to electronic storage and review by Vontobel
>>> Group. Unless stated to the contrary and without prejudice to any
>>> contractual agreements between you and Vontobel Group which shall prevail
>>> in any case, e-mail-communication is for informational purposes only and is
>>> not intended as an offer or solicitation for the purchase or sale of any
>>> financial instrument or as an official confirmation of any transaction.
>>> The legal basis for the processing of your personal data is the
>>> legitimate interest to develop a commercial relationship with you, as well
>>> as your consent to forward you commercial communications. You can exercise,
>>> at any time and under the terms established under current regulation, your
>>> rights. If you prefer not to receive any further communications, please
>>> contact your client relationship manager if you are a client of Vontobel
>>> Group or notify the sender. Please note for an exact reference to the
>>> affected group entity the corporate e-mail signature. For further
>>> information about data privacy at Vontobel Group please consult
>>> www.vontobel.com.
>>>
>>
>> Important Notice
>> This message is intended only for the individual named. It may contain
>> confidential or privileged information. If you are not the named addressee
>> you should in particular not disseminate, distribute, modify or copy this
>> e-mail. Please notify the sender immediately by e-mail, if you have
>> received this message by mistake and delete it from your system.
>> Without prejudice to any contractual agreements between you and us which
>> shall prevail in any case, we take it as your authorization to correspond
>> with you by e-mail if you send us messages by e-mail. However, we reserve
>> the right not to execute orders and instructions transmitted by e-mail at
>> any time and without further explanation.
>> E-mail transmission may not be secure or error-free as information could
>> be intercepted, corrupted, lost, destroyed, arrive late or incomplete. Also
>> processing of incoming e-mails cannot be guaranteed. All liability of
>> Vontobel Holding Ltd. and any of its affiliates (hereinafter collectively
>> referred to as "Vontobel Group") for any damages resulting from e-mail use
>> is excluded. You are advised that urgent and time sensitive messages should
>> not be sent by e-mail and if verification is required please request a
>> printed version. Please note that all e-mail communications to and from the
>> Vontobel Group are subject to electronic storage and review by Vontobel
>> Group. Unless stated to the contrary and without prejudice to any
>> contractual agreements between you and Vontobel Group which shall prevail
>> in any case, e-mail-communication is for informational purposes only and is
>> not intended as an offer or solicitation for the purchase or sale of any
>> financial instrument or as an official confirmation of any transaction.
>> The legal basis for the processing of your personal data is the
>> legitimate interest to develop a commercial relationship with you, as well
>> as your consent to forward you commercial communications. You can exercise,
>> at any time and under the terms established under current regulation, your
>> rights. If you prefer not to receive any further communications, please
>> contact your client relationship manager if you are a client of Vontobel
>> Group or notify the sender. Please note for an exact reference to the
>> affected group entity the corporate e-mail signature. For further
>> information about data privacy at Vontobel Group please consult
>> www.vontobel.com.
>>
>

--
http://www.freelists.org/webpage/oracle-l
Received on Tue Dec 10 2019 - 15:27:25 CET

Original text of this message