Example 6-1 Using Character and RAW Binds in INSERT and UPDATE Operations

The following example demonstrates using Character and RAW binds for LOB columns in INSERT and UPDATE operations

DECLARE
  bigtext VARCHAR2(32767);
  smalltext VARCHAR2(2000);
  bigraw RAW (32767);
BEGIN
  bigtext := LPAD('a', 32767, 'a');
  smalltext := LPAD('a', 2000, 'a');
  bigraw := utl_raw.cast_to_raw (bigtext);

  /* Multiple long binds for LOB columns are allowed for INSERT: */
  INSERT INTO print_media(product_id, ad_id, ad_sourcetext, ad_composite)
    VALUES (2004, 1, bigtext, bigraw);

  /* Single long bind for LOB columns is allowed for INSERT: */
  INSERT INTO print_media (product_id, ad_id, ad_sourcetext)
    VALUES (2005, 2, smalltext);  

  bigtext := LPAD('b', 32767, 'b');
  smalltext := LPAD('b', 20, 'a');
  bigraw := utl_raw.cast_to_raw (bigtext);

  /* Multiple long binds for LOB columns are allowed for UPDATE: */
  UPDATE print_media SET ad_sourcetext = bigtext, ad_composite = bigraw,
    ad_finaltext = smalltext;

  /* Single long bind for LOB columns is allowed for UPDATE: */
  UPDATE print_media SET ad_sourcetext = smalltext, ad_finaltext = bigtext;

  /* The following is NOT allowed because we are trying to insert more than
     4000 bytes of data in a LONG and a LOB column: */
  INSERT INTO print_media(product_id, ad_id, ad_sourcetext, press_release)
    VALUES (2030, 3, bigtext, bigtext);
  
  /* Insert of data into LOB attribute is allowed */
  INSERT INTO print_media(product_id, ad_id, ad_header)
     VALUES (2049, 4, adheader_typ(null, null, null, bigraw));

  /* The following is not allowed because we try to perform INSERT AS
     SELECT data INTO LOB */
  INSERT INTO print_media(product_id, ad_id, ad_sourcetext)
    SELECT 2056, 5, bigtext FROM dual;

END;
/

Example 6-2 Multiple Defines for LOBs in SELECT

The following example demonstrates performing a SELECT operation to retrieve multiple persistent or temporary CLOBs from a SQL query into a VARCHAR2 variable, or a BLOB to a RAW variable.

DECLARE
  ad_src_buffer    VARCHAR2(32000);
  ad_comp_buffer   RAW(32000);
BEGIN
  /* This retrieves the LOB columns if they are up to 32000 bytes, 
   * otherwise it raises an exception */
  SELECT ad_sourcetext, ad_composite INTO ad_src_buffer, ad_comp_buffer FROM print_media 
    WHERE product_id=2004 AND ad_id=5;

  /* This retrieves the temporary LOB produced by SUBSTR if it is up to 32000 bytes, 
   * otherwise it raises an exception */  
  SELECT substr(ad_sourcetext, 2) INTO ad_src_buffer FROM print_media 
    WHERE product_id=2004 AND ad_id=5;END;
/

Example 6-3 Implicit Conversions between BLOB and RAW

Implicit assignment works for variables declared explicitly and for variables declared by referencing an existing column type using the %TYPE attribute as show in the following example. The example assumes that column long_col in table t has been migrated from a LONG to a CLOB column.

CREATE TABLE t (long_col LONG); -- Alter this table to change LONG column to LOB
DECLARE
   a VARCHAR2(100);
   b t.long_col%type; -- This variable changes from LONG to CLOB
BEGIN
   SELECT * INTO b FROM t;
   a := b;  -- This changes from "VARCHAR2 := LONG to VARCHAR2 := CLOB
   b := a;  -- This changes from "LONG := VARCHAR2 to CLOB := VARCHAR2 
END;

Example 6-4 Calling PL/SQL and C Procedures from PL/SQL

You can call a PL/SQL or C procedure from PL/SQL. You can pass a CLOB as an actual parameter, where a VARCHAR2 is the formal parameter, or you can pass a VARCHAR2 as an actual parameter, where a CLOB is the formal parameter. The same holds good for BLOBs and RAWs. One example of when these cases can arise is when either the formal or the actual parameter is an anchored type, that is, the variable is declared using the table_name.column_name%type syntax. PL/SQL procedures or functions can accept a CLOB or a VARCHAR2 as a formal parameter. This holds for both built-in and user-defined procedures and functions.

The following example demonstrates implicit conversion during procedure calls:

CREATE OR REPLACE PROCEDURE foo(vvv IN VARCHAR2, ccc INOUT CLOB) AS
  ...
 BEGIN
  ...
  END;
  /
 DECLARE
  vvv VARCHAR2[32000] := rpad('varchar', 32000, 'varchar')
  ccc CLOB := rpad('clob', 32000, 'clob')
BEGIN
  foo(vvv, ccc); -- No implicit conversion needed here
  foo(ccc, vvv); -- Implicit conversion for both parameters done here
END;
/

Example 6-5 Implicit Conversion with PL/SQL built-in functions

The following example illustrates the use of CLOBs in PL/SQL built-in functions.

DECLARE
  my_ad CLOB;
  revised_ad CLOB;
  myGist VARCHAR2(100):= 'This is my gist.';
  revisedGist VARCHAR2(100);
BEGIN
  INSERT INTO print_media (product_id, ad_id, ad_sourcetext)
    VALUES (2004, 5, 'Source for advertisement 1');  

  -- select a CLOB column into a CLOB variable
  SELECT ad_sourcetext INTO my_ad FROM print_media 
    WHERE product_id=2004 AND ad_id=5;

  -- perform VARCHAR2 operations on a CLOB variable
  revised_ad := UPPER(SUBSTR(my_ad, 1, 20));
 
  -- revised_ad is a temporary LOB
  -- Concat a VARCHAR2 at the end of a CLOB
  revised_ad := revised_ad || myGist;

  -- The following statement raises an error if my_ad is
  -- longer than 100 bytes
  myGist := my_ad;
END;
/