Problem solve Get help with specific problems with your technologies, process and projects.

Viewing data stored in CLOB, part 1

I have migrated a database from MS Access to Oracle9i, which has three CLOB fields. Now when I try to view data, which has been stored in CLOB, I am only able to retrieve 4,000 KB of data in Form 6i. What should I do to retrieve data greater than 4,000 KB in Form 6i?
Form 6i does not support CLOB. But it supports LONG data types. With a LONG data type you can store up to 2 GB per row. CLOBs(Character Large Objects) can store data up to 4 GB in length per row. A VARCHAR2 field has the limitation of 4,000 Bytes. I am assuming that you are using a VARCHAR2 field and that you mean 4,000 bytes in your question.

You can achieve the task of displaying your CLOB data in Forms in many ways. For instance have routines written to convert CLOB to LONG for your form and vice versa but I do not recommend you use the LONG data type in your form in this case.

One solution can be as follows: Define a VARCHAR2 text item in your form and provide a button next to the item. The Button code will be used to display the entire CLOB field. The VARCHAR2 text item will display a SUBSTR of the CLOB field with ellipses (..) to show there is more data for this item.

Before you proceed further, let me tell you that you need to attach the following library to your form. ($ORACLE_HOMETOOLSDEVDEM60DEMOFORMS) to ensure that you can run windows API in your form trigger codes. Please ensure you remove reference to the PATH when prompted. The $ORACLE_HOME value can be obtained from your DBA or from the registry if you are in a Windows environment.


The code in the button will invoke the HOST command to invoke notepad as an editor for the CLOB field. Using the Windows API, you can get temporary directory information of the user and create a temporary file in the temporary directory to indirectly read from and write to the CLOB field.

You will also use Windows API to obtain signature of the file so that a time change to the file will be considered an update and you will have to update the database CLOB fields accordingly.

To display the CLOB field you may have a POST-QUERY trigger that takes a DBMS_LOB.SUBSTR (few hundred characters) of the CLOB field and concatenates the ellipses (if the value is more than 4,000 bytes long). You can get the length using the function dbms_lob.getlength(FIELD_NAME).

In your WHEN-BUTTON-PRESSED trigger for the button next to the field you may use the following code as a pointer and add your own business rules. However, your code should be as modular as possible. You should also make sure that you have proper error handling in your code, such as adequate EXCEPTION statements in your PL/SQL code.

Click for the PL/SQL code in part 2.

Dig Deeper on Using Oracle PL-SQL