Forum Stats

  • 3,752,633 Users
  • 2,250,529 Discussions
  • 7,867,903 Comments

Discussions

How To Download Clob From Database From Forms To Client

littlecat
littlecat Member Posts: 98 Bronze Badge

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

Answers

  • Michael Ferrante-Oracle
    Michael Ferrante-Oracle Senior Principal Product Manager USMember Posts: 6,816 Employee

    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
    littlecat Member Posts: 98 Bronze Badge

    Hi

    Apologies on missing versions.

    Forms: 12.2.1.3.0

    Database: 12.1.0.2.0

  • Michael Ferrante-Oracle
    Michael Ferrante-Oracle Senior Principal Product Manager USMember Posts: 6,816 Employee

    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
    ben_g Member Posts: 26 Blue Ribbon

    @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
    littlecat Member Posts: 98 Bronze Badge

    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.