5 Replies Latest reply on Dec 11, 2012 2:56 PM by Hiten Vira

    CLOB in forms


      I have a table test that contains the following fields:
      id  NUMBER,
      resp  CLOB
      Now resp column contains huge data for a particular id.
      How do I show clob data(resp) in Oracle forms 6i?

      I have 2 solutions at hand:

      Solution 1) Create a base table block & set the resp item property to LONG with size as 240.
      Solution 2) Convert CLOB to VARCHAR2 & then display the VARCHAR2 in forms. However VARCHAR2 limits the data to 32767 in PLSQL.

      I need to display the data formatted as follows, so I cannot use Base table approach:
      id -> 3
      id -> 2
      id -> 1
      I need to display the CLOB data in forms in the above formatted manner in a multi-line text item.i,e Last entry in table is displayed first & so on.

      Is this possible with a Base Table?

        • 1. Re: CLOB in forms
          Gerd Volberg
          I wrote a solution in my blog:


          Try it
          • 2. Re: CLOB in forms
            Hi Gerd,

            Thank you so much for providing that soultion, it works beautifully!!

            I have a small question.

            I see that in your implementation you are using the two triggers on the block to set & reset the column & read new values.

            Now I need to read the response from the table as soon as I click on a button.
            I have no base table blocks. It's a simple read. I click a button & read the values in the table on to the form.

            Q) Where do I need to call the "PRU_EMP" procedure in this case?( Here we clear the CLOB column and fetch new data) ??

            In my button code this is what I am doing:
            I believe the PRU_EMP should be within the loop in POQ_EMP correct?(i,e to reset the CLOB column)

            Thanks a Lot.
            • 3. Re: CLOB in forms
              Gerd Volberg
              I typically name my routines like the firing triggers:

              So my POQ_EMP is reading the CLOB in a POST-QUERY-trigger
              and the PRU_EMP is writing the CLOB in a PRE-UPDATE-trigger

              I bet, that it's not a good idea to write a button which uses both procedures after each other.

              When you have no basetable in your block, how did you fill your block?
              • 4. Re: CLOB in forms
                Thanks for replying.

                This application is used to interact with ASCII Terminal devices.

                I send a command to a device & the device returns a response which gets updated to the database. From forms,I read the CLOB column which contains the device reponse.

                A user inputs a command into a text field and hits a "Send" button, which sends the command the device and the response from the device gets updated into the database. This response is stored in the CLOB column where it is displayed in forms.

                So there are no Base table blocks, just a simple UI to input a command and view the response from the table.

                I need to read the response from the table after after I send a command to the device. ( This is in the "Send" Button of my application )

                • 5. Re: CLOB in forms
                  Hiten Vira
                  If you are using Oracle Forms in Oracle E-Biz environment, then using OAF you can display your CLOB data without any size limitations. A detailed solution is described on the following link

                  Edited by: Hiten Vira on Dec 11, 2012 6:55 AM