Help Center> Data Warehouse Service (DWS)> Tool Guide> DSC> SQL Syntax Migration Reference> Oracle Syntax Migration> System Functions> LOB Functions
Updated on 2024-04-28 GMT+08:00
View PDF

LOB Functions

This section describes the following LOB functions:

DBMS_LOB.APPEND

DBMS_LOB.APPEND function appends the content of a source LOB to a specified LOB.

Input - DBMS_LOB.APPEND

[sys.]dbms_lob.append(o_menuxml, to_clob('DSJKSDAJKSFDA'));

Output

o_menuxml := CONCAT(o_menuxml, CAST('DSJKSDAJKSFDA' AS CLOB));

Input - DBMS_LOB.APPEND

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
 
CREATE
     OR REPLACE PROCEDURE append_example IS clobSrc CLOB ;
     clobDest CLOB ;
BEGIN
     SELECT
               clobData INTO clobSrc
          FROM
               myTable
          WHERE
               id = 2 ;
              SELECT
                    clobData INTO clobDest
               FROM
                    myTable
               WHERE
                    id = 1 ;
                    readClob ( 1 ) ;
               DBMS_LOB.APPEND ( clobDest ,clobSrc ) ;
          readClob ( 1 ) ;
END append_example ;
/

Output

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
 
CREATE
     OR REPLACE PROCEDURE append_example IS clobSrc CLOB ;
     clobDest CLOB ;
BEGIN
     SELECT
               clobData INTO clobSrc
         FROM
               myTable
          WHERE
               id = 2 ;
               SELECT
                    clobData INTO clobDest
               FROM
                    myTable
               WHERE
                    id = 1 ;
                    readClob ( 1 ) ;
               clobDest := CONCAT( clobDest ,clobSrc ) ;
          readClob ( 1 ) ;
end ;
/

DBMS_LOB.COMPARE

DBMS_LOB.COMPARE is an Oracle system function and is not implicitly supported by GaussDB(DWS).

This function is used to compare the full/partial content of two LOBs. To support this feature, use DSC to create a COMPARE function in the MIG_ORA_EXT schema. The migrated statements will use the new function MIG_ORA_EXT.MIG_CLOB_COMPARE, and the examples of using functions in SQL statements are shown as follows.

COMPARE in SQL

Input - DBMS_LOB.COMPARE in SQL

1
 
SELECT a.empno ,dbms_lob.compare ( col1 ,col2 ) FROM emp a ,emp b ;

Output

1
 
SELECT a.empno ,MIG_ORA_EXT.MIG_CLOB_COMPARE ( col1 ,col2 ) FROM emp a ,emp b ;

Input - DBMS_LOB.COMPARE in SQL with CREATE TABLE using 5 parameters

1
 
CREATE TABLE abc nologging AS SELECT dbms_lob.compare ( col1 ,col2 ,3 ,5 ,4 ) FROM emp a ,emp b ;

Output

1
 
CREATE UNLOGGED TABLE abc AS ( SELECT MIG_ORA_EXT.MIG_CLOB_COMPARE ( col1 ,col2 ,3 ,5 ,4 ) FROM emp a ,emp b ) ;

Input - DBMS_LOB.COMPARE in SQL of a function (NVL2)

1
 
SELECT REPLACE( NVL2( DBMS_LOB.COMPARE ( ENAME ,Last_name ) ,'NO NULL' ,'ONE NULL' ) ,'NULL' ) FROM emp ;

Output

1
 
SELECT REPLACE( DECODE ( MIG_ORA_EXT.MIG_CLOB_COMPARE ( ENAME ,Last_name ) ,NULL ,'ONE NULL' ,'NO NULL' ) ,'NULL' ,'' ) FROM emp ;

COMPARE in PL/SQL

Input - DBMS_LOB.COMPARE in PL/SQL

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
 
DECLARE v_clob clob;
        v_text varchar(1000);
        v_compare_res INT;
BEGIN
    v_clob := TO_CLOB('abcddedf');
    v_text := '123454';
    v_compare_res := dbms_lob.compare(v_clob, TO_CLOB(v_text));
    DBMS_OUTPUT.PUT_LINE(v_compare_res); 
end;
/

Output

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
 
DECLARE v_clob clob;
        v_text varchar(1000);
        v_compare_res INT;
BEGIN
    v_clob := CAST('abcddedf' AS CLOB);
    v_text := '123454';
    v_compare_res := MIG_ORA_EXT.MIG_CLOB_COMPARE(v_clob,cast(v_text as CLOB));
    DBMS_OUTPUT.PUT_LINE(v_compare_res); 
end;
/

DBMS_LOB.CREATETEMPORARY

The DBMS_LOB.CREATETEMPORARY function creates a temporary LOB and its corresponding index in the default temporary tablespace. DBMS_LOB.FREETEMPORARY is used to delete the temporary LOB and its index.

Input - DBMS_LOB.CREATETEMPORARY with DBMS_LOB.FREETEMPORARY

1
2
3
4
5
6
7
8
 
DECLARE v_clob clob;
BEGIN
    DBMS_LOB.CREATETEMPORARY(v_clob, TRUE, DBMS_LOB.SESSION);
    v_clob := TO_CLOB('abcddedf');
    DBMS_OUTPUT.PUT_LINE(v_clob);
    DBMS_LOB.FREETEMPORARY(v_clob);
end;
/

Output

1
2
3
4
5
6
7
8
9
 
DECLARE v_clob clob;
BEGIN
    -- DBMS_LOB.CREATETEMPORARY(v_clob, TRUE, DBMS_LOB.SESSION);
    v_clob := CAST('abcddedf' AS CLOB);
    DBMS_OUTPUT.PUT_LINE(CAST(v_clob AS TEXT));
    -- DBMS_LOB.FREETEMPORARY(v_clob);
    NULL;
end;
/

DBMS_LOB.FREETEMPORARY

The DBMS_LOB.FREETEMPORARY function frees the temporary BLOB or CLOB in the default temporary tablespace. After the call to FREETEMPORARY, the LOB locator that is freed is marked as invalid.

Input - DBMS_LOB.CREATETEMPORARY and DBMS_LOB.FREETEMPORARY

1
2
3
4
5
6
7
8
 
DECLARE v_clob clob;
BEGIN
    DBMS_LOB.CREATETEMPORARY(v_clob, TRUE, DBMS_LOB.SESSION);
    v_clob := TO_CLOB('abcddedf');
    DBMS_OUTPUT.PUT_LINE(v_clob);
    DBMS_LOB.FREETEMPORARY(v_clob);
end;
/

Output

1
2
3
4
5
6
7
8
9
 
DECLARE v_clob clob ;
BEGIN
          /*DBMS_LOB.CREATETEMPORARY(v_clob, TRUE, DBMS_LOB.SESSION);*/
          v_clob := cast( 'abcddedf' as CLOB ) ;
          DBMS_OUTPUT.PUT_LINE ( v_clob ) ;
          /* DBMS_LOB.FREETEMPORARY(v_clob); */
          null ;
     end ;
     /

DBMS_LOB.INSTR

DBMS_LOB.INSTR function returns the matching position of the nth occurrence of the pattern in the LOB, starting from the offset specified.

Input - DBMS_LOB.INSTR in SQL

1
2
3
 
SELECT expr1, …, DBMS_LOB.INSTR(str, septr, 1, 5)
  FROM tab1
 WHERE …;

Output

1
2
3
 
SELECT expr1, …, INSTR(str, septr, 1, 5)
  FROM tab1
 WHERE …

Input - DBMS_LOB.INSTR in PL/SQL

1
2
3
4
5
6
 
BEGIN
  …
       pos := DBMS_LOB.INSTR(str,septr,1, i);
  ...
END;
/

Output

1
2
3
4
5
6
 
BEGIN
  …
       pos := INSTR(str,septr,1, i);
  ...
END;
/

DBMS_LOB.SUBSTR

You can specify whether to migrate this function by configuring parameter MigDbmsLob.

Input - DBMS_LOB.SUBSTR when MigDbmsLob is set to true

If the value of MigDbmsLob is true, then migration happens. If the value is false, then migration does not happen.

Input

SELECT dbms_lob.substr('!2d3d4dd!',1,5);

Output

If the config param is true, it should be migrated as below:
select substr('!2d3d4dd!',5,1);

If false, it should be retained as it is:
select dbms_lob.substr('!2d3d4dd!',1,5);

Input

SELECT dbms_lob.substr('!2d3d4dd!',5);

Output

If the config param is true, it should be migrated as below:
select substr('!2d3d4dd!',1,5);

If false, it should be retained as it is:
select dbms_lob.substr('!2d3d4dd!',5);
Parent topic: System Functions