Re: V6 export to V5 DBMS
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.poleReceived on Wed Mar 04 1992 - 01:36:35 CET