Category Archive: SQL

Subcategories: PL/SQL 

Posts related to SQL programming

Oracle SQL Developer Debian Package

  By Daniel | October 29, 2008 - 7:17 am | MySQL, Oracle, PostgreSQL, SQL, SQL Server Leave a comment

Oracle SQL Developer is a Java-based tool that provides a graphical interface to a database. While it’s main focus is Oracle (of course), it can be hooked up, via JDBC, to many other databases, such as MySQL, PostgreSQL, and SQL Server. It’s similar to Toad, but is provided by Oracle at no cost.

Oracle provides SQL Developer in either an RPM, or a generic binary install. I like the ability to manage packages, but I’ve never had much luck at getting RPM to run on Ubuntu. I downloaded the RPM file, and, using alien, I converted the package to a .deb package (Debian package format) and installed it. It worked like a charm!

I haven’t tested it with gcj, but using Sun’s Java 6 update 7 from the Ubuntu repositories, it ran just fine. After you install the package, do a directory list on /usr/lib/jvm. You’re looking for the Sun JDK – if it’s installed, you’ll have a symlink java-6-sun that points to java-6-sun-1.6.0.07. Once you’ve determined the location of the JDK, run “sqldeveloper” from the command line – the program will prompt you for the path to your JDK. Enter it (probably “/usr/lib/jvm/java-6-sun”) and you’re good to go. (You have to install the package as root – but, for the rest of these steps, use your normal user, not root, as this puts settings in a .sqldeveloper directory off your home directory.) The package installs an icon in the “Programming” or “Development” group. Once you’ve told it where the JDK is, you can use this to launch it.

Download SQL Developer 1.5.1 Debian Package

Tagged , , , , ,

Algorithm for One-to-Many Child Table Updates

  By Daniel | March 28, 2008 - 8:13 pm | Databases, PHP, SQL Leave a comment

While working on the Not So Extreme Makeover: Community Edition site, I came up with an algorithm that simplifies anything else I’ve ever written to deal with this condition. I’ll set the scenario, explain the algorithm, share how I implemented it in PHP, and provide a modification if the scenario is a bit more complicated.

Scenario – You have two parent tables, and a child table with a many-to-one relationship with both parent tables, used to map entries in the two parent tables to each other. For this example, we’ll use these three tables…

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

CREATE TABLE volunteer (
    vol_id  INTEGER  NOT NULL,
    vol_last_name  VARCHAR(50)  NOT NULL,
    ...etc...
  PRIMARY KEY (vol_id)
);
 
CREATE TABLE r_volunteer_area (
    rva_id  INTEGER  NOT NULL,
    rva_description  VARCHAR(255)  NOT NULL,
  PRIMARY KEY (rva_id)
);
 
CREATE TABLE volunteer_area (
    va_volunteer_id  INTEGER  NOT NULL,
    va_area_id  INTEGER  NOT NULL,
  PRIMARY KEY (va_volunteer_id, va_area_id),
  FOREIGN KEY (va_volunteer_id) REFERENCES volunteer (vol_id),
  FOREIGN KEY (va_area_id) REFERENCES r_volunteer_area (rva_id)
);

Algorithm – The three-step algorithm is as follows…

  1. Create a comma-delimited string of IDs for the child table.
  2. Delete the IDs from the child table that are not in the list.
  3. Insert the IDs into the child table that are not there already.

Implementation – In PHP, if you have an array, it’s easy to come up with comma-delimited list. To get an array of values back in a post, define your fields with “[]” after the name…

1
2
3
4

<input type="checkbox" name="area[]" id="chkArea1" value="1" />
<label for="chkArea1">Do Something</label><br />
<input type="checkbox" name="area[]" id="chkArea7" value="7" />
<label for="chkArea7">Do Something Else</label>

Here’s the PHP code, using PHP Data Objects (PDO) as the database interface, behind a helper class that creates the statement, appends the parameters, and executes it. (The “quoting” escapes the statement to avoid potential SQL injection attacks – putting it in its own class would make the implementation here much cleaner.)

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

/**
 * STEP 1
 *    Create a comma-delimited list of IDs.
 */
 
// Quote will return the string as '2,3,4' - since we're using this
// as an IN clause of integers, we'll strip the quotes off.
$sAreas = $pdo->quote(join(",", $_POST["area"]));
$sAreas = substr($sAreas, 1, strlen($sAreas) - 1);
 
// Quote the volunteer ID.
$iVol = $pdo->quote($_POST["vol"], PDO::PARAM_INT);
 
/**
 * STEP 2
 *    Delete the IDs that are no longer in the list.
 */
$dbService->executeCommand(
    "DELETE FROM volunteer_area
    WHERE   va_volunteer_id = ?
        AND va_area_id NOT IN ($sAreas)",
    array($iVol);
 
/**
 * STEP 3
 *    Insert the IDs that are not yet in the list.
 */
$dbService->executeCommand(
    "INSERT INTO volunteer_area
        SELECT $iVol, rva_id
        FROM r_volunteer_area
        WHERE   rva_id IN ($sAreas)
            AND rva_id NOT IN
            (SELECT va_area_id
 
            FROM volunteer_area
            WHERE va_volunteer_id = ?)",
    array($iVol));

Modification – Suppose that now you accepted comments along with each of the checkboxes, so a simple two-integer insert/delete is no longer sufficient. You would still only need to break step 3 into two steps.

  1. Get a list of IDs to update.
  2. For each ID in the posted list
    1. If the ID exists in the update list, update it.
    2. Otherwise, insert it.

The implementation would then be able to use this list to make the decision without hitting the database every time.

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

// Assume this returns an associative array of IDs.
$aUpdates = $dbService->performSelect(
    "SELECT va_area_id
    FROM volunteer_area
    WHERE   va_volunteer_id = ?
        AND va_area_id IN ($sAreas)",
    array($iVol));
 
foreach($_POST["area"] as $iArea) {
    if (in_array($iArea, $aUpdates)) {
        // Update the table
        ...etc...
    }
    else {
        // Insert into the table
        ...etc...
    }
}

I think you’ll agree that this is much better than spinning through a loop, doing a count on each ID to see if it exists, then either doing an update or an insert based on the count. And, while the implementation here is PHP, it could easily be implemented in any language that supports arrays and database access.

Tagged , , , , ,

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 , , , ,