Accessing Remote Data

How a Database Link Works
Database links allow users to treat a group of distributed databases as if they were a single, integrated database. Database links tell Oracle how to get one database to another. It requires that Net8 be running on each of the machines (hosts) involved in the remote database access. Net8 is usually started by the database administrator (DBA) or the system manager. The following figure shows two hosts, each running Net8. There is a database on each of the hosts. A database link establishes a connection from the first database (named LOCAL) to the second database (named REMOTE). The database links software that is located in the local database.

Database links specify the following connection information:
→The communications protocol (such as TCP/IP) to use during the connection.
→The host on which the remote database resides.
→The name of the database on the remote host.
→The name of a valid account in the remote database.
→The password for that account.

When used, a database link actually logs in as a user in the remote database and then logs out when the remote data access is complete. A database link can be preivate, owned by a single user or public in which case all users in the local database can use the link.

Using a Database Link for Remote Queries
If you are a user in the local database, you can access objects in the remote database via a database link. For remote tables, use a database link named REMOTE_CONNECT. In the from clause, reference the table name followed by @REMOTE_CONNECT:

SQL> SELECT * FROM worker@REMOTE_CONNECT;

Note: The maximum number of database links that can be used in a single query is set via the OPEN_LINKS parameter in the database's init.ora initialization file. This parameter usually defaults to four.

There are restrictions to the queries that are executed using database links. You should avoid using database links in queries that use the connect by, start with and prior keywords. Some queries using these keywords will work, but most users of tree-structured queries will fail when using database links.

Using a Database Link for Synonyms and Views
You may create local synonyms and views that reference remote objects. To do this reference the database link name, preceded by an @ sign, wherever you refer to a remote table.

SQL> CREATE SYNONYM worker_syn for worker@remote_connect ;

SQL> CREATE SYNONYM worker_skill_syn for Talbot.worker_skill@remote_connect ;

SQL> CREATE VIEW local_employee_view AS SELECT * FROM worker@remote_connect WHERE Lodging = 'ROSE HILL' ;

Using a Database Link for Remote Updates

SQL> UPDATE worker@remote_connect SET Lodging = 'CRANMER' WHERE Lodging = 'ROSE HILL';

ð This update command will use the remote_connect database link to log in to the remote database. It will then update the worker table in that database, based on the set and where conditions specified.

SQL> UPDATE worker@remote_connect
SET Lodging = (SELECT Lodging FROM lodging@remote_connect WHERE Manager = 'KEN MULLER')
WHERE Lodging = 'ROSE HILL';

ð Both table are in remote database.

SQL> UPDATE worker@remote_connect
SET Lodging = (SELECT Lodging FROM lodging WHERE Manager = 'KEN MULLER')
WHERE Lodging = 'ROSE HILL';

ð Here, the updated table is in remote database and lodging table is in local database.

Syntax for Database Links
You can create a database link with the following command:

CREATE [PUBLIC] DATABASE LINK REMOTE_CONNECT
CONNECT TO [Current_User | Username IDENTIFIED BY password]
USING 'Connect String';

Note: To create a database link, you must have CREATE DATABASE LINK system privilege. The account to which you will be connecting in the remote database must have CREATE SESSION privilege. Both of these system privilege are included as part of the CONNECT role in Oracle.

Public and Private Database Links
A public database link is available to the all users in a database. By contrast, a private database link is only available to the user who created it. It is not possible for one user to grant access on a private database link to another user.

SQL> CREATE PUBLIC DATABASE LINK REMOTE_CONNECT
CONNECT TO username IDENTIFIED BY password
USING 'connect string';

Note: To create a public database link, you must have CREATE PUBLIC DATABASE LINK system privilege. This privilege is included in the DBA role in Oracle.

.

Default and Explicit Logins
In place of the connect to ... identified by ... clause, you can use connect to current_user when create a database link. If you use the crrent_user option, then when that link is used, it will attempt to open a session in the remote database that has the same username and password as the local database account. This is called a default login, since the username/password combination will default to the combination in the local database.

SQL> CREATE PUBLIC DATABASE LINK REMOTE_CONNECT
CONNECT TO current_user
USING 'connect string';

An explicit login specifies a username and password that the database link will use while connecting to the remote database.

SQL> CREATE PUBLIC DATABASE LINK REMOTE_CONNECT
CONNECT TO warehouse IDENTIFIED BY warehouse
USING 'connect string';

ð In the remote database, a user named warehouse was created and was given the password warehouse.

Connect String Syntax
The connection details for these service names are contained in files that are distributed to each host in the network. When a service name is encountered, Oracle checks the local Net8 configuration file (called tnsnames.ora) to determine which protocol, host name and database name to use during the connection.
When using Net8, you must know the name of the service that points to the remote database. For example, if the service name HQ specifies the connection parameters for the database you need, then HQ should be used as the connect string in the create database link command.

SQL> CREATE PUBLIC DATABASE LINK REMOTE_CONNECT

CONNECT TO current_user
USING 'HQ';

A typical entry in the tnsnames.ora file (for a network using the TCP/IP protocol) is shown in the following listing:

HQ = ( DESCRIPTION =
(ADDRESS_LIST =
( ADDRESS = ( PROTOCOL = TCP )
(HOST = host1)
(PORT = 1521) )
)
( CONNECT DATA =
(SERVICE_NAME = HQ.host1)

)
)
ð In this listing, the HQ service name is mapped to a connect descriptor that tells the database which protocol to use (TCP/IP), and which host (host1) and database (HQ) to connect to.

Different protocols will have different keywords, but they all must convey the same content.

Using the User Pseudo-column in Views
The user pseudo-column is very useful when you are using remote data access methods. For example, you may not want all remote users to see all records in a table.

In this example, the name table is queried. If the value of its Name column is the same as the name of the user entering the query, then records will be returned.

SQL> CREATE or REPLACE VIEW restricted_name AS SELECT * FROM name WHERE Name = User;

ð When restricting remote access to the rows of your table, you should first consider which columns would be the best to use for the restriction.

For another example, assume the departments are called NORTH, EAST, SO

UTH and WEST. For each of the departments, a specific database link would be created.

SQL> CREATE DATABASE LINK SOUTH_LINK CONNECT TO SOUTH IDENTIFIED BY PASSWD USING 'HQ';

SQL> CREATE or REPLACE VIEW restricted_worker AS SELECT * FROM worker WHERE Department = User;

ð A user who connects via the SOUTH_LINK database link - and thus is logged in as the south user - would only be able to see the worker records that have a Department value equal to 'south';

Dynamic Links: Using the SQLPLUS Copy Command
To break the transaction into smaller entries, use the SQLPLUS copy command, which has the following syntax:

SQL> COPY FROM
[REMOTE username/remote password@connect string]
[TO username/password@connect string]
TABLE name USING subquery;

ð If the current account is to be the destination of the copied data, then the word to plus the local username, password, and connect string are not necessary. If the current account is to be the source

of the copied data, then the remote connection information for the data source is not necessary.

To set the transaction entry size, use the SQLPLUS set command to set a value for the arraysize parameter. This determines the number of records that will be retrieved in each batch. The copycommit parameter tells SQLPLUS how many batches should be committed at one time.

In this example, the data is committed after every 1,000 records. This reduces the transaction's rollback segment entry size needed form 100MB to 1MB.

SET COPYCOMMIT 1

SET ARRAYSIZE 1000

COPY FROM TALBOT/LEDGER@HQ -

CREATE WORKER -

USING -

SELECT * FROM WORKER

Note: Except for the last line, each line in the copy command must be terminated with a bash (-), since this is a SQLPLUS command.

Data options for the COPY command are given below,

Option	Description
APPEND	Inserts the rows into the description table. Automatically creates the table if it does not exist.
CREATE	Create the table and then inserts the rows.
INSERT	Inserts the rows into the destination table if it exists; otherwise, returns an error. When using INSERT, all columns must be specified in the using subquery.
REPLACE	Drops the existing destination table and replaces it with a new table containing the copied data.

Connecting to a Remote Database
You can go directly to a remote database. To do this, enter your username and password along with the Net8 connect string for the remote database:

SQLPLUS username/password@HQ

ð This cmmand will log you in directly to the HQ database.

As figure shows, there are very few hardware requirements for the Branch host. All it support is the front-end tool and Net8 - a typical configuration for client-server applications.

Using SQL*Loader to Load Data

Two additional data-movement utilities, Expert and Import, are covered. SQL*Loader loads data from external files into tables in the Oracle database. SQL*Loader requires two primary files: the data file, which contains the information to be loaded and the control file, which contains information on the format of the data, the records and fields within the file, the order in which they are to be loaded and even when needed, the names of the multiple files that will be used for data. You can also combine the control file information into the data file itself, although the two are usually separated to make it easier to reuse the control file.

When executed, SQL*Loader will aromatically create a log file and a "bad" file. The log file records the status of the load, such as the number of rows processed and the number of rows committed. The "bad" file will contain all the rows that were rejected during the loader due to data errors, such as no unique values in primary key columns.

Within the control file, you can specify additional commands to govern the load criteria. If these criteria are not met by a row, the row will be written to a "discard" file. The log, bad and discard files will have the extensions .log, .bad and .dsc respectively. Control files are typically given the extension .ctl.

The Control File
The control file tells Oracle how to read and load the data. The control file tells SQL*Loader where to find the source data for the load and the tables into which to load the data, along with any other rules that must be applied during the load processing. The control file is created at the operating system level, using any text editor that enables you to save plain text files. The following listing shows a sample control file for loading data into the lodging table:

C:> EDIT control_file.ctl
LOAD DATA
INFILE 'lodging.dat'
INTO TABLE lodging
(Lodging POSITION(01:15) CHAR,
Longname POSITION(01:15) CHAR,
Manager POSITION(01:15) CHAR,
Address POSITION(01:15) CHAR)

ð In this example, data is loaded from the file lodging.dat into the lodging table. The lodging.dat file contains the data for all four of the lodging columns with white space padding out the unused characters in those fields. Thus, the Longname column value always begins at space 16 in the file, even if the lodging value is less than 15 characters.

Loading Variable-Length Data
In the following example, a comma separates the input values:

C:> EDIT control_file.ctl
LOAD DATA
INFILE 'lodging.dat'
BADFILE 'c:\user\load\lodging.bad'
TRUNCATE
INTO TABLE lodging
FIELDS TERMINATED BY ","
(Lodging, Longname, Manager, Address)

ð The field terminated by "," clause tells SQL*Loader that during the load, each column value will be terminated by a comma. In this example, the name of the bad file is specified by the badfile clause. In general, the name of the bad file is only given when you wish to redirect the file to a different directory. Here, also shows the use of the truncate clause within a control file. When this control file is executed by SQL*Loader, the lodging table will be truncated before the start of the load. In addition to truncate, you can use the following options:

append	Use to add rows to the table.
insert	Use to add rows to an empty table. If the table is not empty, the load will abort with an error.
replace	Use to empty the table and then add the new rows. The user must have DELETE privilege on the table.

Starting the Load
SQL*Loader is started via the SQLLDR command at the operating system prompt. When you execute SQLLDR, you need to specify the control file, username/password and other critical load information as shown below:

If the userid keyword is omitted, you will be asked for it. If a slash is given after the equal sign, an OPS$ login default ID and password will be used. You also can use a Net8 database specification string to log on to a remote database and load the data into it. For example, your command may start:

C:\> SQLLDR userid=usernm/mypass@dev

The data to be loaded is in a file called lodging.dat and consists of two records:

C:|> EDIT lodging.dat
GoodLodging, Good Lodging Record, John Goodman, 123 Okeedokee Drive
GoodLodging 1, Lodging Record Ok, Mark Jon, 155 Okeedokee Drive

ð The data is separated by commas and we don't wish to delete the data previously loaded into lodging table, so the control file will look like this:

C:> EDIT lodging.ctl
LOAD DATA
INFILE 'lodging.dat'
APPEND
INTO TABLE lodging
FIELDS TERMINATED BY ","
(Lodging, Longname, Manager, Address)

Next, run SQLLDR and tell it to use the control file:

C:> SQLLDR practice/practice control=lodging.ctl log=lodging.log

Control File Syntax Notes
The syntax for the into table clause includes a when clause. The when clause, show in the preceding listing, services as a filter applied to rows prior to their insertion into the table. For example, you can specify

WHEN Manager='TOM CRANMER'

When you load DATA date datatype values, you can specify a date mask.

HireDate POSITION (1:11) DATE "Mon-DD-YYYY"

Insert, Update and Delete

Insert
The SQL command insert lets you place a row of information directly into a table.

SQL> DESCRIBE comfort;
Name Null? Type
City VARCHAR2(13)
Sampledate DATE
Noon NUMBER
Midnight NUMBER
Precipitation NUMBER

SQL> INSERT INTO comfort VALUES ('WALPOLE', TO_DATE('21-MAR-1999','DD-MON-YYYY'), 56.7, 43.8, 0);
1 row created.

ð The word values must precede the list of data to be inserted. A character string must be in single quotation marks. Numbers can stand by themselves. Each field is separated by commas and the fields must be in the same order as the columns are when the table is described.

You can also insert a NULL. This simple means the column will be left empty for this row, as shown in the following:

SQL> INSERT INTO comfort (SampleDate, Precipitation, City, Noon, Midnight)
VALUES (TO_DATE('21-MAR-1999','DD-MON-YYYY'), NULL, 'WALPOLE', 86.3, 72.1);
1 row created.

SQL> INSERT INTO studentinfo VALUES (&id,'&firstname','&lastname',TO_DATE('&admitdate','DD-MON-YYYY'),'&course' );
Enter value for id: 00002
Enter value for firstname: Shovon
Enter value for lastname: Roy
Enter value for admitdate: 02-JUN-2005
Enter value for couese: Economics
old 1: insert into studentinfo values (&id,'&firstname','&lastname',TO_DATE('&admitdate','DD-MON-Y
new 1: insert into studentinfo values (00002,'Shovon','Roy',TO_DATE('02-JUN-2005','DD-MON-YYYY'),'
1 row created.

SQL> /

ð It will repeat the previous command again to help operator to work quickly.

Inset with Select

SQL> INSERT INTO comfort (SampleDate, Precipitation, City, Noon, Midnight)
SELECT TO_DATE('22-DEC-1999','DD-MON-YYYY'), Precipitation, 'WALPOLE', Noon, Midnight FROM comfort
WHERE City = 'KEENE' AND Sampledate = TO_DATE('22-DEC-1999','DD-MON-YYYY');
1 row created.

ð You can't use the insert into .... select from syntax with LONG datatypes unless you are using the TO_LOB function to insert the LONG data into a LOB column.

SQL> SELECT * FROM comfort WHERE City = 'WALPOLE';
CITY SAMPLEDATE NOON MIDNIGHT PERCIPITATION
WALPOLE 21-MAR-99 56.7 43.8 0
WALPOLE 22-JUN-99 56.7 43.8 0
WALPOLE 23-SEP-99 86.3 72.1
WALPOLE 22-DEC-99 -7.2 -1.2 3.9

An Aside About Performance
For insert statement, Oracle tries to insert each new record into an existing block of data already allocated to the table. This execution plan optimizes the use of space required to store the data. You can override the execution plan by using the APPEND hint to improve the performance of large inserts.

SQL> INSERT /*+ APPEND */ INTO WORKER (Name)
SELECT Name FROM Prospect;

ð The records from the prospect table will be inserted into the worker table. Instead of attempting to reuse previously used space within the worker table, the new records will be placed at the end of the table's physical storage space. In general, you should use the APPEND hint only when inserting large volumes of data into tables with little reusable space.

Rollback, Commit and Autocommit
When you inset, update or delete data from the database, you can reverse or roll back the work you've done. This can be very important when an error is discovered. The process of committing or rolling back work is controlled by two SQL PLUS commands, commit and rollback. This is controlled by the autocommit feature of set. Like other set features, you can show it, like this,

SQL> SHOW AUTOCOMMIT;
autocommit OFF.

ð OFF is the default. You can also specify a number for the autocommit value; this value will determine the number of commands after which Oracle will issue a commit.

SQL> COMMIT;
Commit complete.

ð Until you commit, only you can see how your work affects the tables. Anyone else with access to these tables will continue to get the old information.

You can do quite a large number of inserts, updates and deletes and still undo the work by issuing this command:

SQL> ROLLBACK;
rollback complete.

ð However, the message 'rollback complete' can be misleading. It means only that Oracle has rolled back any work that hasn't been committed.

If you've complete a series of inserts, updates or deletes, but have not yet explicitly or implicitly committed them and you experience serious difficulties, such as a computer failure, Oracle will automatically roll back any uncommitted work.

Delete
Removing a rows from a table requires the delete command. The where clause is essential to removing only the rows you intend. delete without a where clause will empty the table completely.

SQL> DELETE FROM comfort WHERE City = 'WALPOLE' ;
4 rows deleted.

SQL> SELECT # FROM comfort WHERE City = 'WALPOLE' ;
no rows selected.

Now rollback the delete an run the same query:

SQL> ROLLBACK ;
rollback complete.

SQL> SELECT # FROM comfort WHERE City = 'WALPOLE' ;
CITY SAMPLEDATE NOON MIDNIGHT PERCIPITATION
WALPOLE 21-MAR-99 56.7 43.8 0
WALPOLE 22-JUN-99 56.7 43.8 0
WALPOLE 23-SEP-99 86.3 72.1
WALPOLE 22-DEC-99 -7.2 -1.2 3.9

This illustrates that recovery is possible, so long as a commit hasn't occurred.

An additional command for deleting records, truncate, does not behave the same as delete. Whereas delete allows you to commit or rollback the deletion, truncate automatically deleted all records from the table. The action of the truncate command cannot be rolled back or committed; the truncated records are unrecoverable.

Update
Update requires setting specific values for each column you wish to change and specifying which row or rows you wish to affect by using a carefully constructed where clause.

SQL> UPDATE comfort SET Precipitation = .5, Midnight = 73.1
WHERE City = 'WALPOLE' AND Sampledate = TO_DATE('22-DEC-1999','DD-MON-YYYY');

Note: Always set feedback on when doing updates, and look at the feedback to be sure the number of rows updated is what you expected it to be.

Update with Embedded Select
It is possible to set value in an update by embedding a select statement right in the middle of it. Note that this select has its own where clause, picking out the temperature from the City of MANCHESTER from the WEATHER table, and the update has its own where clause to affect just the City of WALPOLE on a certain day:

SQL> UPDATE comfort SET Midnight = (
SELECT Temperature FROM weather WHERE City = 'MANCHESTER')
WHERE City = 'WALPOLE' AND Sampledate = TO_DATE('22-DEC-1999','DD-MON-YYYY');
1 row updated.

You also can use an embedded select to update multiple columns at once.

SQL> UPDATE comfort SET (Noon, Midnight) = (
SELECT Humidity, Temperature FROM weather WHERE City = 'MANCHESTER')
WHERE City = 'WALPOLE' AND Sampledate = TO_DATE('22-DEC-1999','DD-MON-YYYY');
1 row updated.

Update with NULL
You also can update a table and set a column equal to NULL.

SQL> UPDATE comfort SET Noon = NULL
WHERE City = 'WALPOLE' AND Sampledate = TO_DATE('22-DEC-1999','DD-MON-YYYY');
1 row updated.