SET operators in Oracle

SET operators in Oracle

You can combine multiple queries using the set operators UNION, UNION ALL, INTERSECT, and MINUS. All set operators have equal precedence. If a SQL statement contains multiple set operators, then Oracle Database evaluates them from the left to right unless parentheses explicitly specify another order.

The following list briefly describes the four set operations supported by Oracle SQL:

UNION ALL : Combines the results of two SELECT statements into one result set.

UNION : Combines the results of two SELECT statements into one result set, and then eliminates any duplicate rows from that result set.

MINUS : Takes the result set of one SELECT statement, and removes those rows that are also returned by a second SELECT statement.

INTERSECT : Returns only those rows that are returned by each of two SELECT statements

SQL statements containing these set operators are referred to as compound queries, and each SELECT statement in a compound query is referred to as a component query. Two SELECTs can be combined into a compound query by a set operation only if they satisfy the following two conditions:

  1. The result sets of both the queries must have the same number of columns.  
  2. The datatype of each column in the second result set must match the datatype of its corresponding column in the first result set.

Rules and Restrictions on Set Operations:

  • Column names for the result set are derived from the first SELECT.
  • If we want to use ORDER BY in a query involving set operations, we must place the ORDER BY at the end of the entire statement. The ORDER BY clause can appear only once at the end of the compound query.
  • Component queries are executed from top to bottom. If we want to alter the sequence of execution, use parentheses appropriately.
  • Set operations are not permitted on columns of type BLOB, CLOB, BFILE, and VARRAY, nor are set operations permitted on nested table columns.
  • Since UNION, INTERSECT, and MINUS operators involve sort operations, they are not allowed on LONG columns. However, UNION ALL is allowed on LONG columns.

Invoking SQL*Loader

Invoking SQL*Loader

On Unix systems, the command used to invoke SQL*Loader is sqlldr. On Windows systems running Oracle8i, release 8.1 or higher, the command is also sqlldr.

The command to execute SQL*Loader is as follows:

sqlldr USERID = <username>/<password> CONTROL = <control file name> LOG = <log file name>….

Issuing the sqlldr command by itself results in a list of valid command-line parameters being displayed. Command-line parameters are usually keyword/value pairs, and may be any combination of the following:

USERID = {username[/password] [@net_service_name]|/} Specifies the username and password to use when connecting to the database.

CONTROL = control_ file_name  Specifies the name, which may include the path, of the control file. The default extension is .ctl.

LOG = path_ file_name Specifies the name of the log file to generate for a load session. You may include a path as well. By default, the log file takes on the name of the control file, but with a .log extension, and is written to the same directory as the control file.

BAD = path_ file_name Specifies the name of the bad file. You may include a path as part of the name. By default, the bad file takes the name of the control file, but with a .bad extension, and is written to the same directory as the control file.

DATA = path_ file_name Specifies the name of the file containing the data to load. You may include a path as part of the name. By default, the name of the control file is used, but with the .dat extension.

DISCARD = path_ file_name Specifies the name of the discard file. You may include a path as part of the name. By default, the discard file takes the name of the control file, but it has a .dis extension.

Other Parameters are:















My Previous Posts:

Registering SQL*Loader as a Concurrent Program

Registering SQL*Loader as a Concurrent Program

The following steps will describe the process to register a SQL*Loader program as a Concurrent Program in Oracle Apps.

Step 1]

Create the SQL*Loader Control and Data file and place them in Server(ex: $CUSTOM_TOP/bin). Create or check the interface table structures in the backend.

Control file: test.ctl

Data file: test.dat

(deptno NUMBER(2) NOT NULL,
 dname VARCHAR2(14),
 loc VARCHAR2(13));


Step 2]

Go to Application Developer > Concurrent > Executables. Define a Concurrent Program Executable. Choose the Execution Method as SQL*Loader and give the Execution File Name as the name of the SQL*Loader control file. Save your work.

Step 3]

Go to Application Developer > Concurrent > Program. Define the Concurrent Program. Attach the executable defined above.

Step 4]

Go to parameters of the concurrent program. Create a parameter to take the server path of the data file. You can also place the default value.

Step 5]

Attach the Concurrent program to a Responsibility through a Request Group.

Step 6]

Go to that Responsibility and Run the Concurrent Program. If successful check the output file that have all data uploading information.

Number to load: ALL
Number to skip: 0
Errors allowed: 50
Bind array: 64 rows, maximum of 256000 bytes
Continuation: none specified
Path used: Conventional

Table TESTDEPT, loaded from every logical record.
Insert option in effect for this table: INSERT

   Column Name                  Position   Len  Term Encl Datatype
—————————— ———- —– —- —- ———————
DEPTNO                               FIRST     *   ,  O(“) CHARACTER           
DNAME                                NEXT     *   ,  O(“) CHARACTER           
LOC                                     NEXT     *   ,  O(“) CHARACTER           

  7 Rows successfully loaded.
  0 Rows not loaded due to data errors.
  0 Rows not loaded because all WHEN clauses were failed.
  0 Rows not loaded because all fields were null.

Space allocated for bind array:49536 bytes(64 rows)
Read   buffer bytes: 1048576

Total logical records skipped:           0
Total logical records read:               7
Total logical records rejected:          0
Total logical records discarded:        0

Run began on Thu Aug 12 09:41:55 2010
Run ended on Thu Aug 12 09:41:56 2010

Elapsed time was:      00:00:00.11
CPU time was:           00:00:00.01

Step 7]

Check in the backend whether the tables got updated or not.



The Bad and Discard files will be created in /conc/out file of the server.

SQL*Loader Basics

SQL*Loader Basics

SQL*Loader is an Oracle-supplied utility that allows you to load data from a flat file into one or more database tables. It has a powerful data parsing engine that supports data present in any format in the data files. It can load data from multiple datafiles during the same load session as well as can load data into multiple tables during the same load session. SQL*Loader can manipulate the data before loading it, using SQL functions.

SQL*Loader’s Capabilities:

  • SQL*Loader can read from multiple input files in a single load session.
  • SQL*Loader can handle files with fixed-length records, variable-length records, and stream-oriented data.
  • SQL*Loader supports a number of different datatypes, including text, numeric, zoned decimal, packed decimal, and various machine-specific binary types.
  • Not only can SQL*Loader read from multiple input files, but it can load that data into several different database tables, all in the same load session.
  • SQL*Loader allows you to use Oracle’s built-in SQL functions to manipulate the data being read from the input file.
  • SQL*Loader includes functionality for dealing with whitespace, delimiters, and null data.
  • In addition to standard relational tables, SQL*Loader can load data into object tables, varying arrays (VARRAYs), and nested tables.
  • SQL*Loader can load data into large object (LOB) columns.
  • SQL*Loader can handle character set translation between the input data file and the database.

The Oracle DUAL table

The Oracle DUAL table

The DUAL Dummy table (as it is sometimes called) is an automatically-generated table assigned to SYS, but accessible to all users. It has a single column “DUMMY” of type VARCHAR2(1) which has a single row with a value of ‘X’.



Name                        Null?                Type
DUMMY                                             VARCHAR2(1)

What is it used for?

It is useful because it always exists, and has a single row, which is handy for select statements with constant expressions. You could just as easily do this with any other table with a single row, but using DUAL makes it portable among all Oracle installations.




Why is it called “DUAL”?

The DUAL table was created by Chuck Weiss of Oracle corporation to provide a table for joining in internal views:

“I created the DUAL table as an underlying object in the Oracle Data Dictionary. It was never meant to be seen itself, but instead used inside a view that was expected to be queried. The idea was that you could do a JOIN to the DUAL table and create two rows in the result for every one row in your table. Then, by using GROUP BY, the resulting join could be summarized to show the amount of storage for the DATA extent and for the INDEX extent(s). The name, DUAL, seemed apt for the process of creating a pair of rows from just one.” Chuck Weiss, Oracle

The original DUAL table had two rows in it (hence its name), but subsequently it only had one row.

Note: Although it is possible to delete the one record, or insert additional records, one really should not do that!.

Views:Basic Concepts

1.A view is a predefined query on one or more tables.
2.Retrieving information from a view is done in the same manner as retrieving from a table.
3.With some views you can also perform DML operations (delete, insert, update) on the base tables.
4.Views don’t store data, they only access rows in the base tables.
5.user_tables, user_sequences, and user_indexes are all views.
6.View Only allows a user to retrieve data.
7.view can hide the underlying base tables.
8.By writing complex queries as a view, we can hide complexity from an end user.
9.View only allows a user to access certain rows in the base tables.

Advantages of Views

• To restrict data access
• To make complex queries easy
• To provide data independence
• To present different views of the same data

Creating and Using a View

You create a view using CREATE VIEW , which has the following simplified syntax:

[(alias_name[, alias_name...])] AS subquery

1.OR REPLACE specifies the view is to replace an existing view if present.
2.FORCE specifies the view is to be created even if the base tables don’t exist.
3.NOFORCE specifies the view is not to be created if the base tables don’t exist; NOFORCE is the default.
4.alias_name specifies the name of an alias for an expression in the subquery.
5.There must be the same number of aliases as there are expressions in the subquery.
6.subquery specifies the subquery that retrieves from the base tables.
7.If you’ve supplied aliases, you can use those aliases in the list after the SELECT clause.
8.WITH CHECK OPTION specifies that only the rows that would be retrieved by the subquery can be inserted, updated, or deleted.
9.By default, rows are not checked that they are retrievable by the subquery before they are inserted, updated, or deleted.
10.constraint_name specifies the name of the WITH CHECK OPTION or READ ONLY constraint.
11.WITH READ ONLY specifies that rows may only read from the base tables.

There are two basic types of views:

Simple views:
–Derives data from only one table
–Contains no functions or groups of data
–Can perform DML operations through the view

Complex views:
–Derives data from many tables
–Contains functions or groups of data
–Does not always allow DML operations through the view

Creating a View with a CHECK OPTION Constraint

You can specify that DML operations on a view must satisfy the subquery by adding a CHECK OPTION constraint to the view.

   FROM employee
   WHERE id  INSERT INTO myView (id) VALUES (0);

1 row created.

INSERT INTO myView (id) VALUES (7);
INSERT INTO myView (id) VALUES (7)
ERROR at line 1:
ORA-01402: view WITH CHECK OPTION where-clause violation

Creating a View with a READ ONLY Constraint

You can make a view read only by adding a READ ONLY constraint to the view.

  FROM employee
  WITH READ ONLY CONSTRAINT my_view_read_only;

View created.

INSERT INTO myView (id) VALUES (1);
INSERT INTO myView (id) VALUES (1)
ERROR at line 1:
ORA-01733: virtual column not allowed here

Performing an INSERT Using a View

You can only perform DML operations with simple views.Complex views don’t support DML.

CREATE VIEW employee_view AS
  SELECT id, first_name, last_name
  FROM employee;

View created.

INSERT INTO employee_view (id, first_name, last_name) VALUES (
                             13, 'New','Western');

1 row created.

1 row is created in the employee table.

Modifying a View

You can completely replace a view using CREATE OR REPLACE. You can alter the constraints on a view using ALTER VIEW.

  FROM employee
  WITH READ ONLY CONSTRAINT my_view_read_only;

View created.

DROP CONSTRAINT my_view_read_only;

View altered.

Create a complex view with group by and having clause

  SELECT city, AVG(salary) average_salary
  FROM employee
  GROUP BY city
  HAVING AVG(salary)>50000;

Removing a View
You can remove a view without losing data because a view is based on underlying tables in the database.

DROP VIEW myview;

Inline Views

• An inline view is a subquery with an alias (or correlation name) that you can use within a SQL statement.
• A named subquery in the FROM clause of the main query is an example of an inline view.

Materialized Views

A materialized view is a database object that contains the results of a query. The FROM clause of the query can name tables, views, and other materialized views. Collectively these objects are called master tables (a replication term) or detail tables (a data warehousing term). This reference uses “master tables” for consistency. The databases containing the master tables are called the master databases.

When you create a materialized view, Oracle Database creates one internal table and at least one index, and may create one view, all in the schema of the materialized view. Oracle Database uses these objects to maintain the materialized view data. You must have the privileges necessary to create these objects.