Using Large Objects

Note: This document is recognized for including obsolete content and is due for being updated. IFS/Design and Rational Rose are no longer used in development of IFS Applications 8. IFS Windows Client and IFS Client Developer are not in use.

The need for the possibility to save large amount of data in IFS Applications Logical Units (LU) attributes has become bigger and bigger the last years. Up until IFS Applications 2004 we have used two data types for storing Large Objects, these are Long Raw and Long. Long Raw and Long is data types that have been deprecated by Oracle, meaning that one should not use them anymore and that support for them can be removed in any forthcoming Oracle release. For that reason we have decided to support the new data types Character Large Object (CLOB) and Binary Large Object (BLOB).

Contents

• IFS_Design
• LOB_tablespace
• LOBs_and_NULL
• Granting
• Restrictions
• Example_in_PL/SQL_server_code
• Example_in_IFS_Windows_Client_form

LOB tablespace

We will introduce a new tablespace IFSAPP_LOB which by default will store all LOB data. To have a specific tablespace for LOB's makes it easier for DBA's to manage LOB data. Using IFSAPP_LOB is the default behavior for IFS Applications, but this can be changed by the person designing the new LOB attributes in the LU.

This tablespace, as well as all other tablespaces, is defined if you create a new Oracle instance using the included Oracle DBCA templates. If you are upgrading you database, IFSAPP_LOB will be added when preparing your database since this process verify that all default tablespaces exist in the database.

LOB's and NULL

A lob in IFS Applications will never be null, so it will never be possible to compare a LOB with a null in a SQL statement. Instead we will give the LOB the default value of empty LOB, by the functions empty_clob() or empty_blob().

Granting

Since we will use a view for retrieving LOB data and a PL/SQL procedure for writing data normal Foundation1 security is used. This means that end-users must be granted Select on the view and Execute on the package and also Execute on the method.

When adding LOB support to a form it is recommended to scan the form to get the new database grants to the Presentation Object. The database grants is needed for making it possible for end users to run the form by granting the Presentation Object to the end user.

Restrictions

The following restriction will is valid for LOB's in IFS Applications 2004-1 SP4:

• We will only support CLOB and BLOB datatypes.
  No support for BFILE or NCLOB.
• No support in IFS Design and code generation of server and client code.
• Joined views are not supported since we need to access data through pseudocolumn rowid.
  When using joined views you will get an error if using rowid without prefix or alias.
• Objversion can be updated but is not done automatically by the framework
  This can lead to locking problems or make it possible for one user to update the LOB without having the right version of the row.
• Today we must read the whole LOB into the client form then we stream it down to the server.
  This means that client memory must be able to handle LOB internally and with clients with small memory this can lead to paging and bad performance.
• The LOB is updated through a temporary LOB
  Temporary LOB's uses the users Temporary tablespace to allocate space for the LOB. This means that large LOB's must be able to allocate the same amount on the Temporary tablespace. This also means that for a short time we need to allocate twice the amount of a LOB (both on Temporary and LOB tablespace).
• The only support available in the client framework are private methods for reading and writing the LOB separately (apart from the other attributes in the row).
  If LOB's should be read and written during normal Populate/Save operation, that must be implemented by the developer of the form. This means that there is no framework support available for automatically include LOB's in client framework Populate/Save methods.

We will try to remove some of these restrictions in the next core release.

Example in PL/SQL server code

This is a example of an LU that originally had two attributes Id and Description. The LU has been extended with tow LOB's, one CLOB called Text and one BLOB called Picture. So now the LU includes four attributes:

• Id, internal identification
• Description, description of the Id
• Text, a CLOB describing the Id
• Picture, a picture showing the Id

Example of Create Table statement (changes in bold):

CREATE TABLE lob_tab (
   id 		VARCHAR2(10)	NOT NULL,
   description	VARCHAR2(100)	NOT NULL,
   text		CLOB		NOT NULL,
   picture	BLOB		NOT NULL)
   lob (text)    STORE AS (TABLESPACE ifsapp_lob CHUNK 4096 DISABLE STORAGE IN ROW)
   lob (picture) STORE AS (TABLESPACE ifsapp_lob CHUNK 4096 DISABLE STORAGE IN ROW) 
TABLESPACE for the table ...;

or

ALTER TABLE lob_tab 
      ADD text CLOB
      LOB (text)
      STORE AS (TABLESPACE ifsapp_lob CHUNK 4096 DISABLE STORAGE IN ROW)
/
UPDATE lob_tab
SET    text = empty_clob()
WHERE  text IS NULL
/
ALTER TABLE lob_tab MODIFY text NOT NULL
/

ALTER TABLE lob_tab 
      ADD picture BLOB
      LOB (picture)
      STORE AS (TABLESPACE ifsapp_lob CHUNK 4096 DISABLE STORAGE IN ROW)
/
UPDATE lob_tab
SET    picture = empty_blob()
WHERE  picture IS NULL
/
ALTER TABLE lob_tab MODIFY picture NOT NULL
/

Example of view (changes in bold):

CREATE OR REPLACE VIEW LOB AS
SELECT id          id,
       description description,
       picture     picture,
       text        text,
       rowid       objid, 
       ltrim(lpad(to_char(rowversion,'YYYYMMDDHH24MISS'),2000)) objversion
FROM lob_tab 
WITH read only

Example of Insert___ method (changes in bold):

PROCEDURE Insert___ (
   objid_	OUT    VARCHAR2,
   objversion_	OUT    VARCHAR2,
   newrec_	IN OUT LOB_TAB%ROWTYPE,
   attr_	IN OUT VARCHAR2 )
IS
BEGIN
   newrec_.rowversion := sysdate;
   objversion_ := to_char(newrec_.rowversion,'YYYYMMDDHH24MISS');
   INSERT
   INTO lob_tab (
      id,
      description,
      text,
      picture,
      rowversion)
   VALUES (
      newrec_.id,
      newrec_.description,
      empty_clob(),
      empty_blob(),
      newrec_.rowversion)
   RETURNING &OBJID INTO objid_;
EXCEPTION
   WHEN dup_val_on_index THEN
      Error_SYS.Record_Exist(lu_name_);
END Insert___;

Example of Update___ method (no changes required):

PROCEDURE Update___ (
   objid_	IN VARCHAR2,
   oldrec_	IN LOB_TAB%ROWTYPE,
   newrec_	IN OUT LOB_TAB%ROWTYPE,
   attr_	IN OUT VARCHAR2,
   objversion_	IN OUT VARCHAR2,
   by_keys_	IN BOOLEAN DEFAULT FALSE )
IS
BEGIN
   newrec_.rowversion := sysdate;
   objversion_ := to_char(newrec_.rowversion,'YYYYMMDDHH24MISS');
   IF by_keys_ THEN
      UPDATE lob_tab
      SET id = newrec_.id,
          description = newrec_.description,
          rowversion = newrec_.rowversion
      WHERE id = newrec_.id;
   ELSE
      UPDATE lob_tab
      SET id = newrec_.id,
          descripiton = newrec_.description,
          rowversion = newrec_.rowversion
      WHERE rowid = objid_;
   END IF;
EXCEPTION
   WHEN dup_val_on_index THEN
      Error_SYS.Record_Exist(lu_name_);
END Update___;

Example of Delete___ method (no changes required):

PROCEDURE Delete___ (
   objid_	IN VARCHAR2,
   remrec_	IN LOB_TAB%ROWTYPE )
IS
   key_ VARCHAR2(2000);
BEGIN
   key_ := remrec_.result_key || '^';
   Reference_SYS.Do_Cascade_Delete(lu_name_, key_);
   DELETE
   FROM lob_tab
   WHERE rowid = objid_;
END Delete___;

Example of Write BLOB procedure:

PROCEDURE Write_Picture__(
   objversion_ IN OUT VARCHAR2,
   rowid_      IN ROWID, 
   blob_loc_   IN BLOB ) 
IS
   rec FND_LOG_TAB%ROWTYPE;
BEGIN
   General_SYS.Init_Method(lu_name_, 'LOB_API', 'Write_Picture__'); 
   rec := Lock_By_Id___(rowid_, objversion_);
   UPDATE lob_tab
   SET picture = blob_loc_,
       rowversion = SYSDATE
   WHERE rowid = rowid_
   RETURNING to_char(rowversion,'YYYYMMDDHH24MISS') INTO objversion_;
END Write_Picture__;

Example of Write CLOB procedure:

PROCEDURE Write_Text__(
   objversion_ IN OUT VARCHAR2,
   rowid_      IN ROWID, 
   clob_loc_   IN CLOB ) 
IS
   rec FND_LOG_TAB%ROWTYPE;
BEGIN
   General_SYS.Init_Method(lu_name_, 'LOB_API', 'Write_Text__'); 
   rec := Lock_By_Id___(rowid_, objversion_);
   UPDATE lob_tab
   SET text = clob_loc_,
       rowversion = SYSDATE
   WHERE rowid = rowid_
   RETURNING to_char(rowversion,'YYYYMMDDHH24MISS') INTO objversion_;
END Write_Text__;

Example in IFS Enterprise Explorer Application Forms