"Dan's Oracle7 Guide" - cp6Tab27Tab.sh
Date: 07 Sep 1993 07:52:07 GMT
Message-ID: <DBIKLE.93Sep7005207_at_alumni.cco.caltech.edu>
The following discussion is an excerpt from "Dan's Oracle7 Guide".
While the guide could not be described as a great literary work, it might prove useful to those DBA's and Application Developers who work with Oracle7.
The guide will be finished sometime in October.
If you want a copy, I'll put you on my mailing list.
The public domain, email, beta version is free.
If you have an extra $20, please send it to my favorite charity:
Amnesty International 322 8th ave. ny NY usa 10001.
All of the trademarks mentioned in this excerpt are owned by their respective owners.
Of course, everything I create has no warranty. If my software or ideas cause you problems, feel free to send me hate mail.
-Dan
Daniel B. Bikle
dbikle_at_alumni.caltech.edu
415/854-9542
P.O. BOX 'D'
MENLO PARK CA 94026
#! /bin/sh
# cp6Tab27Tab.sh
# Demo of selecting rows from a V6 table into an Oracle7 table
# using a database link.
# Perhaps the most important feature of this script is
# the demonstration that no password needs to be hard-coded
# into the database link. In V6, a password needs to be hard-coded
# into database links; a major security hole.
# assumptions:
# This script assumes that an ops$account for the UNIX user running this
# shell script has been created in both the V6 and Oracle7 dbs's.
# This script assumes that both dbs's reside on the same machine.
# If they reside on different machines only the TCP/IP link will work.
# This script assumes that sql*net is working, if it's not then
# the pipe link might still work; the TCP/IP link will not work.
# setup Oracle7 env
ORACLE_HOME=/q/o7
ORACLE_SID=x7
TWO_TASK=P:x7 # this specifies use of the pipe driver against the
# x7 database; explicitly set here to avoid getting it
# wrong from the parent shell
# Also note that this will spawn a "dedicated server"
# on the Oracle7 dbs
export ORACLE_SID ORACLE_HOME TWO_TASK
# create the link using a sql script embedded within this shell script
$ORACLE_HOME/bin/sqlplus / << EndOfsql
set echo on
rem let's spool a logfile so we know what happened spool cp6Tab27Tab
rem Demo of selecting rows from a V6 table into an Oracle7 table.
rem let's create a dbs link
rem maybe it's already there; let's drop it, then create it
DROP DATABASE LINK pipe_x6
/
CREATE DATABASE LINK pipe_x6 USING 'P:x6' /
rem let's create another one based on TCP/IP
DROP DATABASE LINK tcp_x6
/
CREATE DATABASE LINK tcp_x6 USING 'T:ava2:x6' /
rem create table in Oracle7 dbs
rem maybe it's already there; let's drop it, then create it
DROP TABLE V6DATAFILES
/
rem create an empty copy using WHERE 1=2 and the pipe driver link
CREATE TABLE V6DATAFILES
AS
SELECT * FROM SYS.DBA_DATA_FILES_at_pipe_x6
WHERE 1=2
/
rem Let's describe the new table to see what it looks like desc V6DATAFILES
rem Let's describe SYS.DBA_DATA_FILES_at_pipe_x6 to see what it looks like desc SYS.DBA_DATA_FILES_at_pipe_x6
rem select rows from the V6 table into the Oracle7 table
INSERT INTO V6DATAFILES
SELECT * FROM SYS.DBA_DATA_FILES_at_pipe_x6
/
rem select rows from the V6 table into the Oracle7 table
rem using the TCP/IP driver link
INSERT INTO V6DATAFILES
SELECT * FROM SYS.DBA_DATA_FILES_at_tcp_x6
/
rem Let's see what came through the link
SELECT * FROM V6DATAFILES
/
EndOfsql
#end of cp6Tab27Tab.sh
Output found in in the spool file is displayed below:
///////////////////////////////////////////////////// 18:28:35 OPS$DAN v7sql> 18:28:36 OPS$DAN v7sql> rem Demo of selecting rows from a V6 table into an Oracle7 table. 18:28:36 OPS$DAN v7sql> 18:28:36 OPS$DAN v7sql> rem let's create a dbs link 18:28:36 OPS$DAN v7sql> rem maybe it's already there; let's drop it, then create it18:28:36 OPS$DAN v7sql> DROP DATABASE LINK pipe_x6 18:28:36 2 /
Database link dropped.
18:28:36 OPS$DAN v7sql> 18:28:36 OPS$DAN v7sql> CREATE DATABASE LINK pipe_x6 USING 'P:x6' 18:28:36 2 /
Database link created.
18:28:36 OPS$DAN v7sql> 18:28:36 OPS$DAN v7sql> rem let's create another one based on TCP/IP 18:28:36 OPS$DAN v7sql> DROP DATABASE LINK tcp_x6 18:28:36 2 /
Database link dropped.
18:28:36 OPS$DAN v7sql> 18:28:36 OPS$DAN v7sql> CREATE DATABASE LINK tcp_x6 USING 'T:ava2:x6' 18:28:36 2 /
Database link created.
18:28:36 OPS$DAN v7sql> 18:28:36 OPS$DAN v7sql> rem create table in Oracle7 dbs 18:28:36 OPS$DAN v7sql> rem maybe it's already there; let's drop it, then create it 18:28:36 OPS$DAN v7sql> DROP TABLE V6DATAFILES 18:28:36 2 /
Table dropped.
18:28:37 OPS$DAN v7sql> 18:28:37 OPS$DAN v7sql> rem create an empty copy using WHERE 1=2 and the pipe driver link 18:28:37 OPS$DAN v7sql> CREATE TABLE V6DATAFILES 18:28:37 2 AS 18:28:37 3 SELECT * FROM SYS.DBA_DATA_FILES_at_pipe_x6 18:28:37 4 WHERE 1=2 18:28:37 5 /
Table created.
18:28:38 OPS$DAN v7sql> 18:28:38 OPS$DAN v7sql> rem Let's describe the new table to see what it looks like 18:28:38 OPS$DAN v7sql> desc V6DATAFILES Name Null? Type ------------------------------- -------- ---- FILE_NAME VARCHAR2(255) FILE_ID NUMBER TABLESPACE_NAME VARCHAR2(30) BYTES NUMBER BLOCKS NUMBER STATUS VARCHAR2(9) 18:28:39 OPS$DAN v7sql>
18:28:39 OPS$DAN v7sql> rem Let's describe SYS.DBA_DATA_FILES_at_pipe_x6 to see what it looks like 18:28:39 OPS$DAN v7sql> desc SYS.DBA_DATA_FILES_at_pipe_x6
Name Null? Type ------------------------------- -------- ---- FILE_NAME VARCHAR2(255) FILE_ID NOT NULL NUMBER TABLESPACE_NAME NOT NULL VARCHAR2(30) BYTES NUMBER BLOCKS NOT NULL NUMBER STATUS VARCHAR2(9) 18:28:39 OPS$DAN v7sql> 18:28:39 OPS$DAN v7sql> rem select rows from the V6 table into the Oracle7 table 18:28:39 OPS$DAN v7sql> INSERT INTO V6DATAFILES18:28:39 2 SELECT * FROM SYS.DBA_DATA_FILES_at_pipe_x6 18:28:39 3 /
1 row created.
18:28:39 OPS$DAN v7sql> 18:28:39 OPS$DAN v7sql> rem select rows from the V6 table into the Oracle7 table 18:28:39 OPS$DAN v7sql> rem using the TCP/IP driver link 18:28:39 OPS$DAN v7sql> INSERT INTO V6DATAFILES 18:28:39 2 SELECT * FROM SYS.DBA_DATA_FILES_at_tcp_x6 18:28:39 3 /
1 row created.
18:28:40 OPS$DAN v7sql>
18:28:40 OPS$DAN v7sql> rem Let's see what came through the link
18:28:40 OPS$DAN v7sql> SELECT * FROM V6DATAFILES
18:28:40 2 /
FILE_NAME
--------------------------------------------------------------------------------
FILE_ID TABLESPACE_NAME BYTES BLOCKS STATUS
---------- ------------------------------ ---------- ---------- ---------
/q/o6/dbs/dbsx6.dbf
1 SYSTEM 10485760 5120 AVAILABLE
/q/o6/dbs/dbsx6.dbf
1 SYSTEM 10485760 5120 AVAILABLE
18:28:40 OPS$DAN v7sql>
18:28:40 OPS$DAN v7sql>
Received on Tue Sep 07 1993 - 09:52:07 CEST