Oracle Database 10g SQL (Osborne ORACLE Press Series)

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