Web Publisher Assistance

Logging into a database
To access the database that you want to get data from (query) to create your web page, Oracle Web Publishing Assistant asks you to enter the login information:

User Name
Type the User Name that you use to log in to the database you want to query. For example, a sample client user SCOTT is included with the starter database of your Oracle8 product.
Password
Enter the password. The password will appear encrypted for security. For example, the password associated with the user SCOTT is TIGER.
Database
Enter the service name or alias of the database you want to access. To connect to a local database (a database installed on the same machine as the Web Publishing Assistant), leave this field blank.

To use operating system authentication, do not enter the user name and password. The database uses your operating system user profile to log into the database.

See Chapter 3, “Working with Oracle Web Publishing Assistant” in Oracle Web Publishing Assistant for Windows NT for information on configuring Oracle Web Publishing Assistant to support operating system authentication

Oracle Database Configuration

Click on Start > All Programs > Oracle > Database Administration > Database Configuration Assistant

1ST PAGE

2ND PAGE

3RD PAGE

4TH PAGE

5TH PAGE

6TH PAGE

7TH PAGE

8TH PAGE

9TH PAGE

10TH PAGE

11TH PAGE

12TH PAGE

13TH PAGE

14TH PAGE

15TH PAGE

16TH PAGE

Triggers

A trigger defines an action the database should take when some database-related event occurs. The code within a trigger, called the trigger body, is made up of PL/SQL blocks. Triggers are executed by the database when specific types of data manipulation commands are performed on specific tables. Such commands may include inserts, updates, and deletes.

Required System Privileges
۞
To create a trigger on a table, you must be able to alter that table.

۞ Therefore, you must either own the table, have the ALTER privilege for the table, or have the ALTER ANY TABLE system privilege.

۞ You must have the CREATE TRIGGER system privilege.

۞ To create triggers in another user's account (also called a schema), you must have the CREATE ANY TRIGGER system privilege.

۞ The CREATE TRIGGER system privilege is part of the RESOURCE role provided with Oracle.

۞ To create a trigger on a database-level event, you must have the ADMINISTER DATABASE TRIGGER system privilege.


Types of Triggers
A trigger's type is defined by the type of triggering transaction and by the level at which the trigger is executed.


Row-Level Triggers
Row-level triggers execute once for each row in a transaction. For the LEDGER table auditing example described earlier, each row that is changed in the LEDGER table may be processed by the trigger.

Row-level triggers are created using the for each row clause in the create trigger command.


Statement-Level Triggers
Statement-level triggers execute once for each transaction. For example, if a single transaction inserted 500 rows into the LEDGER table, then a statement-level trigger on that table would only be executed once.

Statement-level triggers are the default type of trigger created via the create trigger command.


BEFORE and AFTER Triggers
Since the events that execute triggers include database transactions, triggers can be executed immediately before or after inserts, updates, and deletes.

Within the trigger, you can reference the old and new values involved in the transaction. "Old" refers to the data as it existed prior to the transaction; updates, and deletes usually reference old values. "New" values are the data values that are the transaction creates (such as the columns in an inserted record).

If you need to set a column value in an inserted row via your trigger, then you need to use a BEFORE INSERT trigger to access the "NEW" values. Using an AFTER INSERT trigger would not allow you to set the insert value, since the row will already have been inserted into the table.


INSTEAD OF Triggers
You can use INSTEAD OF triggers to tell Oracle whet to do instead of performing the actions that invoked the trigger. For example, you could use an INSTEAD OF trigger on a view to redirect inserts into a table or to update multiple tables that are part of a view. You can use INSTEAD OF triggers on either object views or relational views.

For example, if a view involves a join of two tables, your ability to use the update command on records in the view is limited. However, if you use an INSTEAD OF trigger, you can tell Oracle how to inserts, updates, and deletes records in the view's underlying tables when a user attempts to change values via the view. The code in the INSTEAD OF trigger is executed in place of the inserts, updates, or deletes command you enter.


Schema Triggers
As of Oracle8i, you can create triggers on schema operations. The allowable triggering events include create table, alter table and drop table. You can even create triggers to prevent users from dropping their own tables. For the most part, schema-level triggers provide two capabilities: preventing DDL operations and providing additional security monitoring when DDL operations occur.


Database-Level Triggers
You can create triggers to be fired on database events, including errors, logons, logoffs, shutdowns and startup. You can use this type of trigger to automate database maintenance or auditing actions.

PL/SQL

PL/SQL is Oracle's procedural language (PL) superset of the Structured Query Language (SQL). You can use PL/SQL to do such things as codify your business rules through the creation of stored procedures and packages, trigger database events to occur or add programming logic to the execution of SQL commands.

PL/SQL Overview
PL/SQL code is grouped into structures called blocks. If the block of PL/SQL code is not given a name, then it is called an anonymous block.

A block of PL/SQL code contains three sections. In Declaration section, you defines the variables and cursors that the block will use. The declaration section will start with the keyword declare and ends when the Executable Command section starts (keyword begin). The Executable Commands section is followed by the exception Handling Section. The PL/SQL block is terminated by the end keyword. Syntax:

declare

begin

exception

end;


Declaration Section
Example, the result is stored in a table named AREAS. The AREAS table has two columns, to store radius and area values.

declare
pi constant NUMBER(9,7) := 3.1415926 ;
radius INTEGER (5) ;
area NUMBER (14,2) ;
begin
radius := 3;
area := pi*power( radius, 2 );
insert into AREAS values (radius, area);
end;
/

The end; signals the end of the PL/SQL block and the / executes the PL/SQL block PL/SQL block is executed, you will receive the following response from Oracle:

PL/SQL procedure successfully completed.

In the following example, a cursor is declared to retrieve a record from the RADIUS_VALS table. RADIUS_VALS is a table with one column, named Radius, that holds radius value to be used in these examples. The cursor is declared in the Declaration section and a variable named rad_val is declared with a datatype based on the cursor's results.

declare
pi constant NUMBER(9,7) := 3.1415926 ;
area NUMBER (14,2) ;
cursor rad_cursor is select * from RADIUS_VALS;
rad_val rad_cursor%ROWTYPE;
begin
open rad_cursor;
fetch rad_cursor into rad_val;
area := pi*power( rad_val.radius, 2 );
insert into AREAS values (rad_val.radius, area);
close rad_cursor;
end;
/

The cursor definition consists of a cursor name (rad_cursor) and a query (select * from RADIUS_VALS;). A cursor holds the results of a query for processing by the other commands within the PL/SQL blocks.

rad_val rad_cursor%ROWTYPE;

The rad_val variable will be able to reference each column of the query's result set. In this example, the query only returns a single column, but if the table contained multiple columns, you would be able to reference all of them via the rad_val variable.

In addition to the %ROWTYPE declaration, you can use the %TYPE declaration to inherit datatype information. If you use the %ROWTYPE declaration, the variable inherits he column and datatype information for all the columns in the cursor's result set. If you use the %TYPE declaration, then the variable only inherits the definition of the column used to define it.


Executable Commands Section
In the executable Command section, you manipulate the variables and cursors declared in the Declarations section of your PL/SQL block. The Executable Commands section always starts with the keyword begin.

declare
pi constant NUMBER(9,7) := 3.1415926 ;
area NUMBER (14,2) ;
cursor rad_cursor is select * from RADIUS_VALS;
rad_val rad_cursor%ROWTYPE;
begin
open rad_cursor;
fetch rad_cursor into rad_val;
area := pi*power( rad_val.radius, 2 );
insert into AREAS values (rad_val.radius, area);
close rad_cursor;
end;
/

In the first command involving the cursor, the open command is used:

open rad_cursor;

ð When the rad_cursor cursor is opened, the query declared for that cursor is executed and the records to be retuned are identified.

fetch rad_cursor into rad_val;

ð In the Declarations section, the rad_var variable was declared to anchor its datatypes to the rad_cursor cursor.

cursor rad_cursor is select * from RADIUS_VALS;
rad_val rad_cursor%ROWTYPE;

ð When you fetch a record from the cursor into the rad_val variable, you can still address each column value selected via the cursor's querery. When the cursor's data is no longer needed, you can close the cursor as shown below,

close rad_cursor;


Conditional Logic

if, else and elsif syntax:

if
then
elsif
then
else
end if;

Nested if-else syntax:

if
then
if
then
end if;
else
end if;

Example,

declare
pi constant NUMBER(9,7) := 3.1415926 ;
area NUMBER (14,2) ;
cursor rad_cursor is select * from RADIUS_VALS;
rad_val rad_cursor%ROWTYPE;
begin
open rad_cursor;
fetch rad_cursor into rad_val;
area := pi*power( rad_val.radius, 2 );
if area > 30
then
insert into AREAS values (rad_val.radius , area);
end if;
close rad_cursor;
end;
/


Simple Loops
In the following listing, a simple loop is used to generate multiple rows in the AREAS table. The loop is started by the loop keyword and the exit when clause determines when the loop should be exited. An end loop clause signals the end of the loop.

Syntax,

loop
;
exit when ;
end loop;

Example,

declare
pi constant NUMBER(9,7) := 3.1415926 ;
radius INTEGER (5) ;
area NUMBER (14,2) ;
begin
radius := 3;
loop
area := pi*power( radius, 2 );
insert into AREAS values (radius, area);
radius := radius + 1 ;
exit when area > 100 ;
end loop;
end;
/



Simple Cursor Loops
You can use the attributes of a cursor - such as whether or not any rows are left to be fetched - as the exit criteria for a loop. In the following example, a cursor is executed until no more rows are returned by the query. To determine the status of the cursor, the cursor's attributes are checked. Cursor have four attributes you can use in your program:

Attribute Performance
%FOUND A record can be fetched from the cursor.
%NOTFOUND No more records can be fetched from the cursor.
%ISOPEN The cursor has been opened.
%ROWCOUNT The number of rows fetched from the cursor os far.

These attributes are Booleans; they are set to either TRUE or FALSE.

declare
pi constant NUMBER(9,7) := 3.1415926 ;
area NUMBER (14,2) ;
cursor rad_cursor is select * from RADIUS_VALS;
rad_val rad_cursor%ROWTYPE;
begin
open rad_cursor;
loop
fetch rad_cursor into rad_val;
exit when rad_cursor%NOTFOUND;
area := pi*power( rad_val.radius, 2 );
insert into AREAS values (rad_val.radius , area);
end loop;
close rad_cursor;
end;
/


FOR Loops
In a for loop, the loop executes a specified number of times. The for loop's start is indicated by the keyword for, followed by the criteria used to determine when the processing should exit the loop.

declare
pi constant NUMBER(9,7) := 3.1415926 ;
radius INTEGER (5) ;
area NUMBER (14,2) ;
begin
for radius in 1 . . 7 loop
area : = pi * power (radius , 2);
insert into AREAS values (radius, area);
end loop;
end;
/

SQL> SELECT * FROM AREAS ORDER BY radius;
RADIUS AREA
1 3.14
2 12.57
3 28.27
4 50.27
5 78.54
6 113.1
7 153.94

7 rows selected.



Cursor FOR Loop
In FOR loop, the loop executes a specified number of times. In a Cursor FOR loop, the results of query are used to dynamically determine the number of times the loop is executed.

declare
pi constant NUMBER(9,7) := 3.1415926 ;
area NUMBER (14,2) ;
cursor rad_cursor is select * from RADIUS_VALS;
rad_val rad_cursor%ROWTYPE;
begin
for rad_val in rad_cursor
loop
area := pi * power( rad_val.radius, 2 );
insert into AREAS values (rad_val.radius , area);
end loop;
end;
/


While Loops
In a WHILE loop, the loop is processed until an exit condition is met. Instead of specifying the exit condition via an exit command within the loop, the exit condition is specified in the while command that initiates the loop.

declare
pi constant NUMBER(9,7) := 3.1415926 ;
radius INTEGER (5) ;
area NUMBER (14,2) ;
begin
radius := 3;
while radius <= 7
loop
area : = pi * power (radius , 2);
insert into AREAS values (radius, area);
radius : = radius + 1;
end loop;
end;
/


Exception Handling Section
When user-defined or system-related exceptions (errors) are encountered, the control of the PL/SQL block shifts to the Exception Handling Section. If an exception is raised within the Exception Commands section of your PL/SQL block, the flow of commands immediately leaves the Executable Commands section and searches the Exception Handling section for an exception matching the error encountered.

Syntax:

declare

begin

exception

end;

In the following listing, the simple loop for calculating the area of a circle is shown, with two modifications (shown in bold). A new variable named some_variable is declared in the Declaration section and a calculation to determine the variable's value is created in the Executable Command section.

declare
pi constant NUMBER(9,7) := 3.1415926 ;
radius INTEGER (5) ;
area NUMBER (14,2) ;
some_variable NUMBER(14,2);
begin
radius := 3;
loop
some_variable := 1/ (radius - 4);
area : = pi * power (radius , 2);
insert into AREAS values (radius, area);
radius : = radius + 1;
exit when area > 100;
end loop;
end;
/

ð Because the calculation for some_variable involves division, you may encounter a situation in which the calculation attempts to divide by zero - an error condition.

declare
*
ERROR at line 1:
ORA-01476 : divisor is equal to zero
ORA-06512 : at line 9

Therefore you can add an Exception Section on your PL/SQL.

declare
pi constant NUMBER(9,7) := 3.1415926 ;
radius INTEGER (5) ;
area NUMBER (14,2) ;
some_variable NUMBER(14,2);
begin
radius := 3;
loop
some_variable := 1/ (radius - 4);
area : = pi * power (radius , 2);
insert into AREAS values (radius, area);
radius : = radius + 1;
exit when area > 100;
end loop;
exception
when ZERO_DIVIDE
then
insert into AREAS values (0, 0);
end;
/

ð When the PL/SQL block encounters an error, it scans the Exception Handling section for the defines exceptions. In this case, it finds the ZERO_DIVIDE exception, which is one of the system-defined exceptions available in PL/SQL. In addition to the system-defined exceptions and user-defined exceptions, you can use the when others clause to address all exception not defined within your Exception Handling section.

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.