Posts categorized “Databases”


June 15, 2007
  Transferring CLOBs Across Linked Oracle Databases

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

Categorized under , , , ,
Tagged , , , ,


September 8, 2004
  Apache and MySQL Are Back

I was finally able to resolve my problems with Apache and MySQL. When I decided to mount my FAT32 drive under /home/summersd, I inadvertently caused myself some problems. From talking to a Linux guy at work, I found that no processes that weren’t running under my user ID could access those files. The reason is that Linux looks up the entire diretory tree, back to /, to determine if you can access the file. So, although I had -rwxrwxrwx summersd summersd on every file, /home/summersd was drwx—— summersd summersd, and /home was drwxr-xr-x. The permissions on /home/summersd was keeping Apache from seeing /home/summersd/drive_d/wwwroot, and MySQL from seeing or writing to /home/summersd/drive_d/mysql/data. I moved the drive to /mnt/drive_d, with the mount point being owned by “root”, still mounting the drive with my user name, and everything worked.

In the process of reconfiguring Thunderbird, I believe I may have found out how to share the address book across operating systems. The file ~/.thunderbird/default.[something]/prefs.js has a listing of all the preferences and settings. I modified this file to change the location of my mail files, and there is a setting there for an address book (which isn’t shown in the configuration dialog - after all, it is 0.7.3…) I’ll play with that later - right now I’m just elated to have Apache and MySQL working again.

Categorized under , , , , ,


September 5, 2004
  Success with Wine & Diagnostics

At work, we use an editor called Visual SlickEdit (VSlick). It’s got a lot of features, and supports color-coding for many different languages. I decided that I’d give wine another shot, as we only have the Windows version of this program. I installed wine and winesetuptk, used winesetuptk to configure the installation, then ran the installation program. Everything installed, and the program ran up to a point, when it started complaining about a missing DLL. I booted to WXP, found the DLL, copied it to the FAT32 drive, rebooted to Linux, and copied the DLL into the “fake windows” system directory. Soon, it was working great! I can’t believe it - success with wine!

I also have made little headway towards getting Apache and MySQL to working. I changed the process that Apache uses to run as “summersd”, and I was able to see pages (although any pages that relied on a database didn’t work). I still haven’t figured this one out yet…

I’m still getting kernel panics from time to time, and it seems to be whenever I access networking. A suggestion from one of the folks on the WBEL users list was to download the Ultimate Boot CD, filled with diagnostic programs. I downloaded it, burned it, and ran some memory checks. Those checked out, so I’m going to run a “CPU Burn-In” program to see if it can detect errors from the CPU. It runs for up to 7 days, but I think I’ll just run it overnight - folding@home didn’t take nearly that long to crash it before.

Categorized under , , , , , ,


September 4, 2004
  Back to WBEL

Today, I reinstalled WBEL 3.0. I was able to compile ndiswrapper (as I kept that on my FAT32 drive), and get the network card working smoothly very quickly. (In fact, it seems to be more reliable under Linux than WXP!) With the network up, it was easy to download Firefox, Thunderbird, and OpenOffice, and installing them was a breeze. (I decided to put them under /opt this time, trying to stick with the FHS.) I decided to mount my FAT32 drive under my home directory, as /home/summersd/drive_d. E-mail works fine, but Apache gives me a 403 (Permission Denied) error. MySQL doesn’t seem to be working either - I’ll have to play with that later.

Categorized under , , ,


August 31, 2004
  A Month in Summary

Well, the last month has been interesting. I was able to get my Windows and Linux installations synchronized by creating a mount point for my second drive under /mnt/drive_d. Under that, I created a directory called /thunderbird for my e-mail, and moved my e-mail and newsgroup folders over there. (The first time, I missed the “newsrc” file, which is important - it tells what newsgroups you’ve subscribed to and which messages you’ve read.) Under Windows, I pointed it to D:\thunderbird\pop3.knology.net, and under Linux, it was configured to /mnt/drive_d/thunderbird/pop3.knology.net. I then moved the wwwroot directory from C:\Inetpub to drive D:, and pointed IIS to the new location. Under Linux, I did something a little different. As “root”, I deleted the directory /var/www/html, and instead created /var/www/html as a symbolic link to /mnt/drive_d/wwwroot (the actual command is ln -s /mnt/drive_d/wwwroot /var/www/html). That worked great as well.

MySQL was more complicated, but I was eventually able to get it working as well. I created the directory D:\mysql\data for the data, then configured /etc/my.cnf under Linux to look at /mnt/drive_d/mysql/data. I kept getting “Could not connect to server using socket /var/lib/mysql/mysql.sock”. After some digging, it appeared to be a permissions problem. All the documentation said that the default socket was /tmp/mysql.sock, so I changed my.cnf to point there instead, restarted mysqld, and it worked! So, I have no idea what a Unix socket it, but I know that now I have one! :)

I was also able to get DVDs playing using xine, compiling it myself, and using libdvdcss, I can even watch commercial DVDs. I’m really impressed with xine - it handles all kinds of media out of the box, including DivX and up to version 8 of WMV files. You can add codecs to it as well, to support almost anything you want to do from an audio or video perspective. Compiling the player took around 20 minutes, and compiling the front end took another 5. And, it was simple - download the .tar.gz file, do tar xvfz [name].tar.gz, cd [name], ./configure, make install. The ./configure script is the key in the whole process - it looks at what you have installed, and creates make files that will work with your compiler.

Everything started going south, though, when I started having freezes. Eventually, I got to where I could not boot without a kernel panic, and then boot errors (which I detailed in this e-mail to the WBEL user’s list. Encouraged by my success over the past month, I decided to return to WBEL - it’s supposed to be more stable than FC2, and I bet that I can get ndiswrapper, the dual-booting web server, the common e-mail, and maybe even some other stuff working again.

Categorized under , , , , , ,


August 1, 2004
  Wow

Today I installed Fedora Core 2. This thing is slick! WBEL looked a lot like RH8, which I had seen before my renewed Linux learning began. FC2 has a graphical loader that hides a lot of the background stuff (unless an error occurs) - that’s cool. During the install, I skipped OpenOffice.org and MySQL, although I installed PHP with MySQL support. The reason for that is that I wanted to get the latest and greatest versions of those two products. We’ll see if this proves to be a good decision or not.

The wireless network card still wasn’t recognized (phooey). I did some more searching, armed with the knowledge that I have an adm8211 chipset. One of the first hits under Google’s Linux search for “adm8211” pointed me to a project called NDISwrapper. This is a “wrapper” that uses the vendor’s Windows DLL file, and converts the hooks from Windows to Linux. Doing this, this driver can (in theory) support most any network card, especially those that aren’t in the Linux Hardware Compatibility List (HCL). I downloaded it, compiled it, and followed the directions to install my driver under it. I still wasn’t able to create a connection, but on a hunch, I restarted the computer. NDISwrapper is also a kernel module, and I know that often those are only read at startup. Once the computer was restarted, I was able to create a connection, and now my network card works! YEA!!! (And it was only one night’s worth of work - much better.)

Now that I have networking working under both operating systems, I plan to try to get four things working the same, whether I’m booted to WXP or Linux - E-mail (using Mozilla Thunderbird), PHP (using Apache on Linux, IIS on WXP), MySQL (using the exact same version on both), and a web server that uses the same html root directory (again, Apache on Linux, IIS on WXP). If I didn’t already have IIS up, running, and configured under WXP, I’d probably just do Apache on both, but this will be interesting - it should work, as I don’t have many creative permission rules.

Categorized under , , , , , , , ,