Monthly Archives: June 2007

Transferring CLOBs Across Linked Oracle Databases

  By Daniel | June 15, 2007 - 2:04 pm | Oracle, PL/SQL Leave a comment

Linking databases in Oracle make it easy to share data, and can be useful for replication. However, there is a limitation in Oracle that prevents Character Large Objects (CLOBs) from coming across these links. The following technique uses stored procedures and a temporary table to pull CLOBs across a database link.

First, you’ll need the temporary table, which will hold a sequence number, the primary key for the table where you’ll want to reconstruct the CLOB, and some text. This table can reside in the source or destination database, but must be linked from the other one. For our purposes, it looks like this…

1
2
3
4
5
6
7
8
9
10
11

CREATE TABLE clob_xfer_area
(
  cxa_pk      NUMBER(12),
  cxa_number  NUMBER(12),
  cxa_text    varchar2(4000 byte)
);
ALTER TABLE clob_xfer_area ADD
(
  CONSTRAINT pk_cxa_id
    PRIMARY KEY (cxa_pk, cxa_number)
);

Second, you’ll need the procedure in the source database that breaks the CLOB apart and populates the temporary table.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49

SET serveroutput ON SIZE 1000000
SET LINES 1000
SET pages 0
SET tab off
SET feedback ON
 
CREATE OR REPLACE
PROCEDURE break_clobs_apart
IS
 
  v_line_number   NUMBER(3);
  v_text_piece    varchar2(4000);
  v_total_length  NUMBER(12);
 
  cursor clob_cur IS
    SELECT twc_pk, twc_clob_field
    FROM   table_with_clob;
 
BEGIN /* { */
 
  FOR clob_rec IN clob_cur loop /* { */
 
    v_total_length := 1;
    v_line_number  := 0;
 
    while (v_total_length <=
           DBMS_LOB.GETLENGTH(clob_rec.twc_clob_field)) loop /* { */
 
      v_line_number := v_line_number + 1;
      v_text_piece := DBMS_LOB.SUBSTR(clob_rec.twc_clob_field,
        3999, v_total_length);
      v_total_length := v_total_length + 3999;
 
      INSERT INTO clob_xfer_area (
        cxa_pk,
        cxa_number,
        cxa_text
      )
        VALUES (
          clob_rec.twc_pk, -- cxa_pk
          v_line_number,   -- cxa_number
          v_text_piece     -- cxa_text
        );
 
    END loop; /* } of while */
 
  END loop; /* } of clob_cur */
 
END; /* } of procedure break_clobs_apart */

Third, you’ll need a procedure in the destination database that puts the CLOB back together, and deletes the data from the temporary table.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46

SET serveroutput ON SIZE 1000000
SET LINES 1000
SET pages 0
SET feedback ON
SET tab off
 
CREATE OR REPLACE
PROCEDURE put_clobs_together
IS
  v_new_clob   CLOB;
 
  cursor pk_cur IS
    SELECT DISTINCT cxa_pk
    FROM   clob_xfer_area;
 
  cursor piece_cur(p_cxa_pk NUMBER) IS
    SELECT cxa_text
    FROM   clob_xfer_area
    WHERE  cxa_pk = p_cxa_pk
    ORDER BY cxa_number;
 
BEGIN /* { */
 
  FOR pk_rec IN pk_cur loop /* { */
 
    DBMS_LOB.CREATETEMPORARY(v_new_clob, TRUE);
    DBMS_LOB.OPEN(v_new_clob, DBMS_LOB.LOB_READWRITE);
 
    FOR piece_rec IN piece_cur(pk_rec.cxa_pk) loop /* { */
 
      DBMS_LOB.WRITEAPPEND(v_new_clob, LENGTH(piece_rec.cxa_text),
        piece_rec.cxa_text);
 
    END loop;  /* } of piece_cur */
 
    DBMS_LOB.CLOSE(v_new_clob);
 
    UPDATE dest_table_with_clob
      SET  migrated_clob = v_new_clob
      WHERE dtwc_pk = pk_rec.cxa_pk;
 
  END loop; /* } of pk_cur */
 
  DELETE FROM clob_xfer_area;
 
END; /* } of procedure put_clobs_together */

Finally, you’ll need a procedure that controls the whole thing. We’ll assume that this procedure is loaded in the destination database, and the source database is linked with the name “source”.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

SET LINES 1000
SET pages 0
SET feedback ON
SET tab off
 
CREATE OR REPLACE
PROCEDURE xfer_clobs
IS
 
BEGIN /* { */
 
  break_clobs_apart@SOURCE;
  put_clobs_together;
 
END; /* } */

(This does not include a commit – the changes will not be persistent unless they are committed.)

Of course, these processes could (and, to be useful, likely would) be integrated into other procedures and scripts. But, this framework will successfully transfer CLOBs across linked databases in Oracle.

Tagged , , , ,

Posting Source Code in WordPress, Take 2

  By Daniel | June 13, 2007 - 11:33 am | PHP, WordPress Leave a comment

In my searching, I have found another WordPress source code plugin, called wp-syntax. This one uses GeSHi, the Generic Syntax Highlighter. It features many languages, and is extensible to even more. (If I ever post a COBOL snippet, I’ll probably add COBOL support to it, and contribute it to the codebase.)

To use it, you simply put a pre tag, followed by “lang=[language]“. It will also do line numbering. Of course, with a single “language” parameter, the embedded language will not be highlighted as well. In this case, the previous plug-in works better; although the syntax highlighting has to be done manually, it can handle multiple languages.

Here is what the example from the “Category DropDowns in WordPress” post looks like with lang=”php”…

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30

<?php
/**
 * This creates a list of category links that can be used with a category dropdown
 */
$aCategories = get_all_category_ids();
$iMaxCat = 0;
foreach($aCategories as $iThisCat) {
    if ($iMaxCat < $iThisCat) {
        $iMaxCat = $iThisCat;
    }
}
$iMaxCat++;
?><div style="text-align:center;">
    <form name="categoryform" action="" style="text-align:center;">
    <script type="text/javascript">
        var aLink = new Array(<?php echo($iMaxCat); ?>);
<?php
foreach($aCategories as $iThisCat) {
    echo("aLink[$iThisCat] = \"" . get_category_link($iThisCat) . "\";\n");
} ?>
        function goCat() {
            window.location =
                aLink[document.getElementById('cat')[document.getElementById('cat').selectedIndex].text;
        }
    </script>
    <?php wp_dropdown_categories('class=sidebardropdown&orderby=name&show_count=1&hierarchical=1'); ?>
    <br />
    <button class="sidebarbutton" type="button" style="margin-top:5px;" onclick="goCat();">View Category</button>
    </form>
</div>

And, here’s what it looks like with lang=”javascript”…

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30

<?php
/**
 * This creates a list of category links that can be used with a category dropdown
 */
$aCategories = get_all_category_ids();
$iMaxCat = 0;
foreach($aCategories as $iThisCat) {
    if ($iMaxCat < $iThisCat) {
        $iMaxCat = $iThisCat;
    }
}
$iMaxCat++;
?><div style="text-align:center;">
    <form name="categoryform" action="" style="text-align:center;">
    <script type="text/javascript">
        var aLink = new Array(<?php echo($iMaxCat); ?>);
<?php
foreach($aCategories as $iThisCat) {
    echo("aLink[$iThisCat] = \"" . get_category_link($iThisCat) . "\";\n");
} ?>
        function goCat() {
            window.location =
                aLink[document.getElementById('cat')[document.getElementById('cat').selectedIndex].text;
        }
    </script>
    <?php wp_dropdown_categories('class=sidebardropdown&orderby=name&show_count=1&hierarchical=1'); ?>
    <br />
    <button class="sidebarbutton" type="button" style="margin-top:5px;" onclick="goCat();">View Category</button>
    </form>
</div>

This is another option, and is probably what I’ll use for single-language posts, or posts where the embedded language may not be crucial.

Tagged , , , ,