Skip to Main Content
  • Questions

  • Utilizing CLOB data type from store procedures in Oracle Forms

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Jim.

Asked: October 21, 2009 - 2:55 pm UTC

Last updated: October 21, 2009 - 3:01 pm UTC

Version: 10.2.0

Viewed 1000+ times

You Asked


In the Oracle Database (Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production) I have a table like this:
CREATE TABLE testing_text (testing_text_id NUMBER (9), test_text CLOB);


If I manually insert data from the SQL prompt it is fine. When I query that data on the from level using the store procedures it is fine (the forms defaults to a LONG data type).

The issue comes about when inserting or updating from the from through the stored procedure to the table. I can not insert/update data as the LONG data type on the form does not correspond to data type in the reference cursor (CLOB) in the stored procedure. (Forms [32 Bit] Version 10.1.2.0.2 (Production)

Oracle forms DOES NOT support all PL/SQL database types (does not natively support CLOB or NCLOB). AND oracle strongly recommends NOT using the LONG data type in tables. So what to do?

If I change my record group in my stored procedure to a LONG instead of a CLOB it seems to work. Oracle Forms supports the LONG data type and is able to pass the known data type to the stored procedure.

   TYPE rec_def IS RECORD
   (
      testing_text_id   testing_text.testing_text_id%TYPE,
      test_text         long -- testing_text.test_text%TYPE
   );


I would like to get away from using (or declaring) the LONG data type as it appears Oracle it trying to phased this data type out. Is there any work around solution that would allow Forms to use CLOB columns from a store procedure while avoiding any references to the LONG data types on the form and stored procedure?










and Tom said...

in plsql - long is just shorthand for

subtype LONG is VARCHAR2(32760);


it is not really a long, it is a varchar2 in disguise. PLSQL strings cannot exceed 32k in size basically.


If you exceed 32k - you will have to "chunk it up" - that is, the lob locator would live in a package outside of a function/procedure ( it would be global ) and you could ask for any 32k chunk of it by using dbms_lob.substr - but you would not be able to deal with the clob as a varchar2 type using anything above 32k at a time.

You might try the forms forum on otn.oracle.com - there you will find people that use forms every day (unlike me who has literally not touched it since 1995) and they might be able to advise you on the best way to deal with clobs and such in your forms environment.

Is this answer out of date? If it is, please let us know via a Comment
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

CLOB/BLOB/etc

Complete documentation on Securefiles and Large Objects here