How To Download Clob From Database From Forms To Client

littlecat

Hi

Apologies if this has asked recently, I am only able to find old discussions and wish to confirm no better approach than what am using.

Have clob data stored in table. Wish for user to download to file on client. In Forms procedure am selecting clob contents into variable. Then looping through clob with dbms_lob.read to chunk up, then writing each chunk to client using client_text_io.put (webutil).

Cannot use webutil_file_transfer.DB_To_Client_with_progress as this only handles blobs.

Current approach works, but not overly elegant in reading clob into Form. Just checking if better options. Alternative is to read clob into database package and have form call database package to get each chunk, but this not very modular.

Thanks

Michael Ferrante-Oracle

Exactly which Forms version and Database version are you using? Please do not say "11g", "12c", etc as these are not versions. These are product names.

littlecat

Hi

Apologies on missing versions.

Forms: 12.2.1.3.0

Database: 12.1.0.2.0

Michael Ferrante-Oracle

I don't believe there's an easy way to make this work. There are several problems and this is why I always ask for product versions.

The solution may differ depending on what you want to do with the data. For example, are you expecting to show it in a form (in a text or display item)? Or is your expectation to go directly from the DB to a text file on the user machine.

Personally, if your plan is to go from DB to file, I would generate the file on the middle tier (if it's possible to do that in client side plsql). Then use WebUtil to transfer the file from the mid tier to the user. I would not attempt to send all that data to the user tier and attempt to assemble the file there.

If there is a goal to display the text, this will be difficult unless you are using v12.2.1.4. In versions older than 12.2.1.4, text (and display) items are limited to holding 32k or less (less in versions older than 11.1). You can get to 64k with nchar. However, in 12.2.1.4, text and display items can now hold as much as 2M-1 (2097151). This means from a Forms data block you can directly populate a text/display item. However, there is a limitation. Because PLSQL has a 32k limit on the size of variables and specifically bind variables, you cannot manipulate more than 32k. In other words, you cannot fill a variable with 2M of characters in PLSQL then assign its value to a display item using bind syntax (e.g. :display_item1 := myvar).

The only way you could make this work in versions older than 12.2.1.4 is to obtain chunks of the data one at a time. Then send each chunk to a unique item.

ben_g

@littlecat we use a few approaches for this, here are some ideas for you.

1. Button which sends an email to the user with the CLOB as an attachment - if the size of the attachment is reasonable this is fast and easy to implement

2. Network share of a common location available to the users and database

Our DBs are hosted on linux so we use Samba to make directories mounted in the DB also available on the Windows network. We use this for uploading and downloading data and it is extremely fast. I got a 2+ hour loading process using WebUtil down to 20 seconds.

You can write a file to a directory in one step using DBMS_LOB.CLOB2FILE (or DBMS_XSLPROCESSOR.CLOB2FILE if on an older database). Our users collect their files from the Samba/export location but you could also copy it to their PC for them.

3. Custom utility invoked by WebUtil

We have a utility installed on each user's PC that connects to the database using their credentials and either uploads or downloads BLOBs to/from the database. We use this to move lots of images around, again because it is significantly faster than WebUtil especially for bulk operations. The database generates a thumbnail which is all the users ever see in their forms. Some of the images are 100MB and they are downloaded in the blink of an eye using this approach.

You could build a similar utility to download CLOBs directly from the database.

Ben

littlecat

Appreciate responses.

For now I am sticking with original approach, discussed in original post. This is working fine. Although I may look at Michael suggestion of using middle tier.

Thank you.