Re: V6 export to V5 DBMS

From: Dave Lockwood <dave_at_msb.com>
Date: Wed, 4 Mar 92 00:36:35 GMT
Message-ID: <1992Mar4.003635.17936_at_msb.com>


If you use oraperl, you write a program to export the data to a flat file using tabs to separate fields, and a companion to "import" the data on the destination machine. It can, of course, be all in one program if both databases live on the same machine.

Remember that 0-length strings are a no-no, so for any column that can contain a null value you must:

select nvl(column, 'NULL') /* or some such impossible value */

then:

insert into table values(decode(:1, 'NULL', NULL, :1))

If both databases are on one machine, and in both cases, the username is an "automatic login", here's the oraperl code.

#! /usr/oracle/bin/oraperl

# define oracle SIDs for the two databases.
# sid1 is the source, sid2 is the destination
$sid1 = 'oracle1';
$sid2 = 'oracle2';

# login to both databases and setup LDA. (login or bust)
$lda1 = &ora_login($sid1, '', '') || die $ora_errstr; $lda2 = &ora_login($sid2, '', '') || die $ora_errstr;

# open oracle cursor to read DEPT data

$getdepts = &ora_open($lda1, "
SELECT NVL(deptno, 999), NVL(dname, 'NULL'), NVL(loc, 'NULL')   FROM dept
 ORDER BY deptno
") || die $ora_errstr;

# open cursor for insert into destination database
$insdept = &ora_open($lda2, "
INSERT INTO dept
VALUES(decode(:1, 999, NULL, :1), decode(:2, 'NULL', NULL, :2),

       decode(:3, 'NULL', NULL, :3))
") || die $ora_errstr;

# loop to read each dept record fetched from getdepts cursor
while (($deptno, $dname, $loc) = &ora_fetch($getdepts)) {

  # bind variables to cursor insdept causing insert to occur   ora_bind($insdept, $deptno, $dname, $loc) || die $ora_errstr;

}

# close cursors

&ora_close($insdept) || die "Can't close insdept cursor.\n"; &ora_close($getdepts) || die "Can't close getdepts cursor.\n";

# log off oracle databases

&ora_logoff($lda1) || die "Can't log off database $sid1.\n"; &ora_logoff($lda2) || die "Can't log off database $sid2.\n";

Cheers!

-- 
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-  | dave_at_msb.com (Dave Lockwood)
"It's like I've always said: If you want | UUCP: ...!uupsi!mpoint!dave
to find something weird, you have to go  | Sysop of Meetpoint Station BBS
downtown." - Wally (Gremlins II)         | cat flames | mail santa_at_north.pole
Received on Wed Mar 04 1992 - 01:36:35 CET

Original text of this message