Oracle Database 10g Enhancements to Large Objects

Oracle Database 10 g Enhancements to  Large  Objects

In this section, you ll learn about the following enhancements made to large objects in Oracle Database 10 g :

• Implicit conversion between CLOB and NCLOB objects

• Use of the :new attribute when using LOBs in a trigger

  Note  

  The various DDL statements that create the items shown in this section are contained in the lob_schema_10g.sql script. The script connects as lob_user with a password of lob_password , so if you re using a different password (or you re using a service name ), you ll need to edit the script first. Also, you must have already run lob_schema.sql before you attempt to run lob_schema_10g.sql .

Implicit Conversion Between CLOB and NCLOB Objects

With the advent of global business, you might have to convert between Unicode and a national language character set. In versions of the database beneath Oracle Database 10 g , you can explicitly convert between Unicode and a national character set using the TO_CLOB() and TO_NCLOB() functions (conversion functions were covered in Chapter 3).

Oracle Database 10 g allows implicit conversion between CLOB and NCLOB objects, which saves you from using TO_CLOB() and TO_NCLOB() . You can use this implicit conversion for IN and OUT variables in queries and DML statements, as well as for PL/SQL method parameters and variable assignments.

Let s take a look at an example. The following statement creates a table named nclob_content that contains an NCLOB column named nclob_column :

 CREATE TABLE nclob_content (id INTEGER PRIMARY KEY,  nclob_column NCLOB); 

The following nclob_example() procedure performs the following tasks :

• Creates a CLOB named clob_var . and sets its content to the string It is the east and Juliet is the sun .

• Creates an NCLOB named nclob_var .

• Inserts clob_var into nclob_column , thus demonstrating that an implicit conversion between a CLOB and an NCLOB is performed.

• Selects nclob_column into clob_var , thus demonstrating that an implicit conversion between an NCLOB and a CLOB is performed.

   CREATE OR REPLACE PROCEDURE nclob_example AS  clob_var CLOB := 'It is the east and Juliet is the sun';  nclob_var NCLOB; BEGIN  -- insert clob_var into nclob_column   INSERT INTO nclob_content (id, nclob_column) VALUES (1, clob_var);  -- select nclob_column into clob_var   SELECT nclob_column  INTO clob_var  FROM nclob_content  WHERE id = 1;  -- display the CLOB  DBMS_OUTPUT.PUT_LINE('clob_var = '  clob_var); END nclob_example; / 

The following example connects as lob_user , turns server output on, and calls nclob_example() :

  CONNECT lob_user/lob_password   SET SERVEROUTPUT ON   CALL nclob_example();  clob_var = It is the east and Juliet is the sun 

Use of the :new Attribute When Using LOBs in a Trigger

You can use the :new attribute when using LOBs in a BEFORE UPDATE or BEFORE INSERT row level trigger. The following example creates a trigger named before_clob_content_update that displays the length of clob_column when the clob_content table is updated. Notice that :new is used when accessing clob_column :

 CREATE OR REPLACE TRIGGER before_clob_content_update BEFORE UPDATE ON clob_content FOR EACH ROW BEGIN  DBMS_OUTPUT.PUT_LINE('clob_content changed');  DBMS_OUTPUT.PUT_LINE('Length = '  DBMS_LOB.GETLENGTH(:new.clob_column)); END before_clob_content_update; / 

The following example connects as lob_user and updates the clob_content table (which causes the trigger to be fired ):

  CONNECT lob_user/lob_password   SET SERVEROUTPUT ON   UPDATE clob_content   SET clob_column = 'Creeps in this petty pace'   WHERE id = 1;  clob_content changed Length = 25