SQL*Loader Control File Reference

Control File Contents

The SQL*Loader control file is a text file that contains data definition language (DDL) instructions. DDL is used to control the following aspects of a SQL*Loader session:

• Where SQL*Loader will find the data to load
• How SQL*Loader expects that data to be formatted
• How SQL*Loader will be configured (memory management, rejecting records, interrupted load handling, and so on) as it loads the data
• How SQL*Loader will manipulate the data being loaded

To create the SQL*Loader control file, use a text editor such as vi or xemacs.create. In general, the control file has three main sections, in the following order:

• Session-wide information
• Table and field-list information
• Input data (optional section)

Example Sample Control File

1    -- This is a sample control file
2    LOAD DATA
3    INFILE 'sample.dat'
4    BADFILE 'sample.bad'
5    DISCARDFILE 'sample.dsc'
6    APPEND
7    INTO TABLE emp
8    WHEN (57) = '.'
9    TRAILING NULLCOLS
10  (hiredate SYSDATE,
      deptno POSITION(1:2)  INTEGER EXTERNAL(2)
              NULLIF deptno=BLANKS,
       job    POSITION(7:14)  CHAR  TERMINATED BY WHITESPACE
              NULLIF job=BLANKS  "UPPER(:job)",
       mgr    POSITION(28:31) INTEGER EXTERNAL 
              TERMINATED BY WHITESPACE, NULLIF mgr=BLANKS,
       ename  POSITION(34:41) CHAR 
              TERMINATED BY WHITESPACE  "UPPER(:ename)",
       empno  POSITION(45) INTEGER EXTERNAL 
              TERMINATED BY WHITESPACE,
       sal    POSITION(51) CHAR  TERMINATED BY WHITESPACE
              "TO_NUMBER(:sal,'$99,999.99')",
       comm   INTEGER EXTERNAL  ENCLOSED BY '(' AND '%'
              ":comm * 100"
    )

In this sample control file, the numbers that appear to the left would not appear in a real control file. They are keyed in this sample to the explanatory notes in the following list:

1. This is how comments are entered in a control file. 
2. The LOAD DATA statement tells SQL*Loader that this is the beginning of a new data load.
3. The INFILE clause specifies the name of a datafile containing data that you want to load. 
4. The BADFILE parameter specifies the name of a file into which rejected records are placed. 
5. The DISCARDFILE parameter specifies the name of a file into which discarded records are placed. 
6. The APPEND parameter is one of the options you can use when loading data into a table that is not empty. 
   
   
   To load data into a table that is empty, you would use the INSERT parameter. 
7. The INTO TABLE clause allows you to identify tables, fields, and datatypes. It defines the relationship between records in the datafile and tables in the database. 
8. The WHEN clause specifies one or more field conditions. SQL*Loader decides whether or not to load the data based on these field conditions.
9. The TRAILING NULLCOLS clause tells SQL*Loader to treat any relatively positioned columns that are not present in the record as null columns. 
10. The remainder of the control file contains the field list, which provides information about column formats in the table being loaded. 

INFILE  Clause:

The following list shows different ways you can specify INFILE syntax:

Data contained in the control file itself:

INFILE  *

Data contained in a file named foo with a default extension of .dat:

INFILE  foo

Data contained in a file named datafile.dat with a full path specified:

INFILE 'c:/topdir/subdir/datafile.dat'

Specifying Multiple Data Files:

To load data from multiple datafiles in one SQL*Loader run, use an INFILE statement for each datafile. Datafiles need not have the same file processing options, although the layout of the records must be identical. For example, two files could be specified with completely different file processing options strings, and a third could consist of data in the control file.

You can also specify a separate discard file and bad file for each datafile. In such a case, the separate bad files and discard files must be declared immediately after each datafile name. For example, the following excerpt from a control file specifies four datafiles with separate bad and discard files:

INFILE  mydat1.dat  BADFILE  mydat1.bad  DISCARDFILE mydat1.dis 
INFILE  mydat2.dat 
INFILE  mydat3.dat  DISCARDFILE  mydat3.dis 
INFILE  mydat4.dat  DISCARDMAX  10 0

• For mydat1.dat, both a bad file and discard file are explicitly specified. Therefore both files are created, as needed.
• For mydat2.dat, neither a bad file nor a discard file is specified. Therefore, only the bad file is created, as needed. If created, the bad file has the default filename and extension mydat2.bad. The discard file is notcreated, even if rows are discarded.
• For mydat3.dat, the default bad file is created, if needed. A discard file with the specified name (mydat3.dis) is created, as needed.
• For mydat4.dat, the default bad file is created, if needed. Because the DISCARDMAX option is used, SQL*Loader assumes that a discard file is required and creates it with the default name mydat4.dsc.

Identifying Data in the Control File with BEGINDATA

If the data is included in the control file itself, then the INFILE clause is followed by an asterisk rather than a filename. The actual data is placed in the control file after the load configuration specifications.

Specify the BEGINDATA parameter before the first data record. The syntax is:

BEGINDATA 
data

Another Sample control file with in-line data formatted as fix length records. The trick is to specify "*" as the name of the data file, and use BEGINDATA to start the data section in the control file:

load data

 infile *

 replace

 into table departments

 (  dept     position (02:05) char(4),

    deptname position (08:27) char(20)

 )

begindata

COSC  COMPUTER SCIENCE

ENGL  ENGLISH LITERATURE

MATH  MATHEMATICS

POLY  POLITICAL SCIENCE

Keep the following points in mind when using the BEGINDATA parameter:

• If you omit the BEGINDATA parameter but include data in the control file, SQL*Loader tries to interpret your data as control information and issues an error message. If your data is in a separate file, do not use theBEGINDATA parameter.
• Do not use spaces or other characters on the same line as the BEGINDATA parameter, or the line containing BEGINDATA will be interpreted as the first line of data.
• Do not put comments after BEGINDATA, or they will also be interpreted as data.

Table-Specific Loading Method

When you are loading a table, you can use the INTO TABLE clause to specify a table-specific loading method (INSERT, APPEND, REPLACE, or TRUNCATE) that applies only to that table. That method overrides the global table-loading method. The global table-loading method is INSERT, by default, unless a different method was specified before any INTO TABLE clauses. The following sections discuss using these options to load data into empty and nonempty tables.

Loading Data into Empty Tables

If the tables you are loading into are empty, use the INSERT option.

INSERT
This is SQL*Loader's default method. It requires the table to be empty before loading. SQL*Loader terminates with an error if the table contains rows.

Loading Data into Nonempty Tables

If the tables you are loading into already contain data, you have three options:

• APPEND
• REPLACE
• TRUNCATE

APPEND
If data already exists in the table, SQL*Loader appends the new rows to it. If data does not already exist, the new rows are simply loaded. You must have SELECT privilege to use the APPEND option.

REPLACE

With REPLACE, all rows in the table are deleted and the new data is loaded. The table must be in your schema, or you must have DELETE privilege on the table

TRUNCATE

The SQL TRUNCATE statement quickly and efficiently deletes all rows from a table or cluster, to achieve the best possible performance. For the TRUNCATE statement to operate, the table's referential integrity constraints must first be disabled. If they have not been disabled, SQL*Loader returns an error.

INTO table Clause:

Among its many functions, the INTO TABLE clause allows you to specify the table into which you load data. To load multiple tables, you include one INTO TABLE clause for each table you wish to load.

To begin an INTO TABLE clause, use the keywords INTO TABLE, followed by the name of the Oracle table that is to receive the data.

The syntax is as follows:

intotab ::= 
  INTO TABLE name [ ( { PARTITION name | SUBPARTITION name } ) ] 
  { INSERT | REPLACE | TRUNCATE | APPEND }

The table must already exist. The table name should be enclosed in double quotation marks if it is the same as any SQL or SQL*Loader reserved keyword, if it contains any special characters, or if it is case sensitive.

INTO TABLE scott."CONSTANT"
INTO TABLE scott."Constant" 
INTO TABLE scott."-CONSTANT" 

WHEN Clause:

You can choose to load or discard a logical record by using the WHEN clause to test a condition in the record.

The WHEN clause appears after the table name and is followed by one or more field conditions. 

The syntax for field_condition is as follows:

fld_cond ::= 

  [(] 
  { full_fieldname | pos_spec } operator { 'char_string' | X'hex_string' | BLANKS } 
  [)] AND
  [(]
  { full_fieldname | pos_spec } operator { 'char_string' | X'hex_string' | BLANKS }
  [)]...

For example, the following clause indicates that any record with the value "q" in the fifth column position should be loaded:

WHEN (5) = 'q' 

A WHEN clause can contain several comparisons, provided each is preceded by AND. Parentheses are optional, but should be used for clarity with multiple comparisons joined by AND, for example:

WHEN (deptno = '10') AND (job = 'SALES') 

TRAILING NULLCOLS Clause

The TRAILING NULLCOLS clause tells SQL*Loader to treat any relatively positioned columns that are not present in the record as null columns.

For example, consider the following data:

10 Accounting 


Assume that the preceding data is read with the following control file and the record ends after dname:

INTO TABLE dept 

    TRAILING NULLCOLS 

( deptno CHAR TERMINATED BY " ", 

dname  CHAR TERMINATED BY WHITESPACE, 

loc    CHAR TERMINATED BY WHITESPACE 

) 


In this case, the remaining loc field is set to null. Without the TRAILING NULLCOLS clause, an error would be generated due to missing data.

Field List Reference

This chapter describes the field-list portion of the SQL*Loader control file.

Field List Contents

The field-list portion of a SQL*Loader control file provides information about fields being loaded, such as position, datatype, conditions, and delimiters.

Example Field List Section of Sample Control File

     (hiredate  SYSDATE,
      deptno  POSITION(1:2)  INTEGER EXTERNAL(2)
              NULLIF deptno=BLANKS,
       job   POSITION(7:14)  CHAR  TERMINATED BY WHITESPACE
              NULLIF job=BLANKS  "UPPER(:job)",
       mgr    POSITION(28:31) INTEGER EXTERNAL 
              TERMINATED BY WHITESPACE, NULLIF mgr=BLANKS,
       ename  POSITION(34:41) CHAR 
              TERMINATED BY WHITESPACE  "UPPER(:ename)",
       empno  POSITION(45) INTEGER EXTERNAL 
              TERMINATED BY WHITESPACE,
       sal    POSITION(51) CHAR  TERMINATED BY WHITESPACE
              "TO_NUMBER(:sal,'$99,999.99')",
      comm   INTEGER EXTERNAL  ENCLOSED BY '(' AND '%'
              ":comm * 100"
    )

SYSDATE sets the column to the current system date
POSITION specifies the position of a data field.
The NULLIF clause is one of the clauses that can be used to specify field conditions. In this sample, the field is being compared to blanks, using the BLANKS parameter.
The TERMINATED BY WHITESPACE clause is one of the delimiters it is possible to specify for a field.
The ENCLOSED BY clause is another possible field delimiter.

Examples of Using POSITION
siteid  POSITION (*) SMALLINT 
siteloc POSITION (*) INTEGER 


If these were the first two column specifications, siteid would begin in column 1, and siteloc would begin in the column immediately following.
ename  POSITION (1:20)  CHAR 
empno  POSITION (22-26) INTEGER EXTERNAL 
allow  POSITION (*+2)   INTEGER EXTERNAL TERMINATED BY "/" 


Column ename is character data in positions 1 through 20, followed by column empno, which is presumably numeric data in columns 22 through 26. Column allow is offset from the next position (27) after the end ofempno by +2, so it starts in column 29 and continues until a slash is encountered.

Shared Pool Concept and Efficient Utilization

For every statement issued against the server, Oracle searches the shared pool to see if the statement has already been parsed.  If an exact text match of the statement is already present in the shared pool a soft parse is performed as the execution plan for the statement has already been created and can be reused.  If the statement is not found in the shared pool a hard parse must be performed to determine the optimal execution path.

The important thing to remember from the previous paragraph is the term “exact text match”, as different numbers of spaces, literal values and case will result in a failure to find a text match, such that the following statements are considered different.

SELECT 1 FROM dual WHERE dummy = ‘X’;

SELECT 1 FROM dual WHERE dummy = ‘Y’;
SELECT 1 FROM DUAL WHERE dummy = ‘X’;

SELECT 1 FROM dual WHERE  dummy = ‘X’;

The first two statements only differ by the value of the search criteria, specified using a literal.  In these situations exact text matches can be achieved by replacing the literal values with bind variables that have the correct values bound to them.  Using the previous example the statement passed to the server might look like this.

SELECT 1 FROM dual WHERE dummy = :B1;

For every execution the bind variable may have a different value, but the text sent to the server is the same allowing for an exact text, which results in a soft parse. 

There are two main problems associated with applications that do not use bind variables:

• Parsing SQL statements is a CPU intensive process, so reparsing similar statements constantly represents a waste of CPU cycles.
• Parsed statements are stored in the shared pool until they are aged out.  By not using bind variables the shared pool can rapidly become filled with similar statements, which waste memory and make the instance less efficient.

The main components of the shared pool are the library cache, the dictionary cache, and, depending on your configuration, the server result cache. The library cache stores the executable (parsed or compiled) form of recently referenced SQL and PL/SQL code. The dictionary cache stores data referenced from the data dictionary. The server result cache stores the results of queries and PL/SQL function results.

Many of the caches in the shared pool automatically increase or decrease in size, as needed, including the library cache and the dictionary cache. Old entries are aged out to accommodate new entries when the shared pool does not have free space. A cache miss on the data dictionary cache or library cache is more expensive than a miss on the buffer cache. For this reason, the shared pool should be sized to ensure that frequently used data is cached.

Several features make large memory allocations in the shared pool: for example, the shared server, parallel query, or Recovery Manager. Oracle recommends segregating the SGA memory used by these features by configuring a distinct memory area, called the large pool.

Allocation of memory from the shared pool is performed in chunks. This chunking enables large objects (over 5 KB) to be loaded into the cache without requiring a single contiguous area. In this way, the database reduces the possibility of running out of enough contiguous memory due to fragmentation.

 Dictionary Cache Concepts

Information stored in the data dictionary cache includes usernames, segment information, profile data, tablespace information, and sequence numbers. The dictionary cache also stores descriptive information, or metadata, about schema objects. Oracle Database uses this metadata when parsing SQL cursors or during the compilation of PL/SQL programs.

Library Cache Concepts

The library cache holds executable forms of SQL cursors, PL/SQL programs, and Java classes. This section focuses on tuning as it relates to cursors, PL/SQL programs, and Java classes. These are collectively referred to as application code.

When application code is run, Oracle Database attempts to reuse existing code if it has been executed previously and can be shared. If the parsed representation of the statement does exist in the library cache and it can be shared, then the database reuses the existing code. This is known as a soft parse, or a library cache hit. If Oracle Database cannot use existing code, then the database must build a new executable version of the application code. This is known as a hard parse, or a library cache miss.

Now we need to understand when a SQL and PL/SQL statements can be shared.

SQL Sharing Criteria

Oracle Database automatically determines whether a SQL statement or PL/SQL block being issued is identical to another statement currently in the shared pool.

Oracle Database performs the following steps to compare the text of the SQL statement to existing SQL statements in the shared pool:

1. The text of the statement is hashed. If there is no matching hash value, then the SQL statement does not currently exist in the shared pool, and a hard parse is performed.
   
   
2. If there is a matching hash value for an existing SQL statement in the shared pool, then Oracle Database compares the text of the matched statement to the text of the statement hashed to see if they are identical. The text of the SQL statements or PL/SQL blocks must be identical, character for character, including spaces, case, and comments. For example, the following statements cannot use the same shared SQL area:
   SELECT * FROM employees;

   SELECT * FROM Employees;
   
   SELECT *  FROM employees;
   

   Usually, SQL statements that differ only in literals cannot use the same shared SQL area. For example, the following statements do not resolve to the same SQL area:

   
   
   
   SELECT count(1) FROM employees WHERE manager_id = 121;
   
   SELECT count(1) FROM employees WHERE manager_id = 247;
   

   The only exception to this rule is when the parameter CURSOR_SHARING has been set to SIMILAR or FORCE. Similar statements can share SQL areas when the CURSOR_SHARING is set to SIMILAR or FORCE. The costs and benefits involved in using CURSOR_SHARING are explained in "When to Set CURSOR_SHARING to a Nondefault Value".
   
   
3. The objects referenced in the issued statement are compared to the referenced objects of all existing statements in the shared pool to ensure that they are identical.
   References to schema objects in the SQL statements or PL/SQL blocks must resolve to the same object in the same schema. For example, if two users each issue the following SQL statement and they each have their own employees table, then this statement is not considered identical, because the statement references different tables for each user:

   SELECT * FROM employees;
   

4. Bind variables in the SQL statements must match in name, data type, and length.
   For example, the following statements cannot use the same shared SQL area, because the bind variable names differ:

   
   SELECT * FROM employees WHERE department_id = :department_id;
   
   SELECT * FROM employees WHERE department_id = :dept_id;
   

   Many Oracle products, such as Oracle Forms and the precompilers, convert the SQL before passing statements to the database. Characters are uniformly changed to uppercase, white space is compressed, and bind variables are renamed so that a consistent set of SQL statements is produced.
   
   
5. The session's environment must be identical. For example, SQL statements must be optimized using the same optimization goal.

CURSOR_SHARING parameter

CURSOR_SHARING determines what kind of SQL statements can share the same cursors.

It has following Values:

• FORCE
  Allows the creation of a new cursor if sharing an existing cursor, or if the cursor plan is not optimal.
• SIMILAR
  Causes statements that may differ in some literals, but are otherwise identical, to share a cursor, unless the literals affect either the meaning of the statement or the degree to which the plan is optimized.
• EXACT
  Only allows statements with identical text to share the same cursor.

How Similar Statements Can Share SQL Areas

When SQL statements use literals rather than bind variables, a nondefault setting for CURSOR_SHARING enables the database to replace literals with system-generated bind variables. Using this technique, the database can sometimes reduce the number of parent cursors in the shared SQL area.

When CURSOR_SHARING is set to a nondefault value, the database performs the following steps during the parse:

1. Searches for an identical statement in the shared pool
   If an identical statement is found, then the database skips to Step 3. Otherwise, the database proceeds to the next step.
2. Searches for a similar statement in the shared pool
   If a similar statement is not found, then the database performs a hard parse. If a similar statement is found, then the database proceeds to the next step.
3. Proceeds through the remaining steps of the parse phase to ensure that the execution plan of the existing statement is applicable to the new statement
   If the plan is not applicable, then the database performs a hard parse. If the plan is applicable, then the database proceeds to the next step.
4. Shares the SQL area of the statement

When to Set CURSOR_SHARING to a Nondefault Value

The best practice is to write sharable SQL and use the default of EXACT for CURSOR_SHARING. However, for applications with many similar statements, setting CURSOR_SHARING can significantly improve cursor sharing, resulting in reduced memory usage, faster parses, and reduced latch contention. Consider this approach when statements in the shared pool differ only in the values of literals, and when response time is poor because of a very high number of library cache misses.

Setting CURSOR_SHARING to FORCE or SIMILAR has the following drawbacks:

• The database must perform extra work during the soft parse to find a similar statement in the shared pool.
• There is an increase in the maximum lengths (as returned by DESCRIBE) of any selected expressions that contain literals in a SELECT statement. However, the actual length of the data returned does not change.
• Star transformation is not supported.
• If stored outlines were generated with CURSOR_SHARING set to EXACT, then the database does not use stored outlines generated with literals. To avoid this problem, generate outlines with CURSOR_SHARING set to FORCE or SIMILAR and use the CREATE_STORED_OUTLINES parameter.

When deciding whether to set CURSOR_SHARING to FORCE or SIMILAR, consider the performance implications of each setting. When CURSOR_SHARING is set to FORCE, the database uses one parent cursor and one child cursor for each distinct SQL statement. The database uses the same plan for each execution of the same statement. When set to SIMILAR, database behavior depends on the presence of histograms:

• Histogram absent for column with system-generated bind value.
  Only one parent cursor and one child cursor exists for each distinct SQL statement. In this case, all executions of a SQL statement use the same plan.
• Histogram present for column with system-generated bind value
  If the same SQL statement is executed multiple times, each execution has its own child cursor. In this case, the database peeks at bind variable values and create a new child cursor for each distinct value. Thus, each statement execution uses a plan based on the specific literals in the statement.

For example, consider the following statement:

SELECT * FROM hr.employees WHERE employee_id = 101;

If FORCE is used, or if SIMILAR is used when no histogram exists, then the database optimizes this statement as if it contained a bind variable and uses bind peeking to estimate cardinality. Statements that differ only in the bind variable share the same execution plan. If SIMILAR is used, and if a histogram does exist, then the database does not treat the statement as if a bind variable were used. The same query for a different employee may not use the same plan.

If you set CURSOR_SHARING, then Oracle recommends the FORCE setting unless you are in a DSS environment. FORCE limits the growth of child cursors that can occur when the setting is SIMILAR. Also, function-based indexes may not work when using SIMILAR because the database converts index parameters to bind variables. For example, if the index is SUBSTR(id,1,3), then the database converts it to SUBSTR("ID",:SYS_B_0,:SYS_B_1)=:id, rendering the index invalid.

Setting CURSOR_SHARING to either SIMILAR or FORCE allows similar statements to share SQL. The difference between SIMILAR and FORCE is that SIMILAR forces similar statements to share the SQL area without deteriorating execution plans. Setting CURSOR_SHARING to FORCE forces similar statements to share the executable SQL area, potentially deteriorating execution plans. Hence, FORCE should be used as a last resort, when the risk of suboptimal plans is outweighed by the improvements in cursor sharing.

Cursor Variable with Dynamic SQL

OPEN-FOR-USING Statement
The OPEN-FOR-USING statement associates a cursor variable with a query, executes the query, identifies the result set, positions the cursor before the first row in the result set, then zeroes the rows-processed count kept by %ROWCOUNT.

Because this statement can use bind variables to make the SQL processing more efficient, use the OPEN-FOR-USING statement when building a query where you know the WHERE clauses in advance. Use the OPEN-FOR statement when you need the flexibility to process a dynamic query with an unknown number of WHERE clauses.

Syntax:

cursor_variable_name

A weakly typed cursor variable (one without a return type) previously declared within the current scope.

bind_argument

An expression whose value is passed to the dynamic SELECT statement.

dynamic_string

A string literal, variable, or expression that represents a multi-row SELECT statement.

host_cursor_variable_name

A cursor variable declared in a PL/SQL host environment and passed to PL/SQL as a bind variable. The datatype of the host cursor variable is compatible with the return type of any PL/SQL cursor variable. Host variables must be prefixed with a colon.

USING ...

This optional clause specifies a list of bind arguments. At run time, bind arguments in the USING clause replace corresponding placeholders in the dynamic SELECT statement.

You use three statements to process a dynamic multi-row query: OPEN-FOR-USING, FETCH, and CLOSE. First, you OPEN a cursor variable FOR a multi-row query. Then, you FETCH rows from the result set. When all the rows are processed, you CLOSE the cursor variable.

The dynamic string can contain any multi-row SELECT statement (without the terminator). The string can also contain placeholders for bind arguments. However, you cannot use bind arguments to pass the names of schema objects to a dynamic SQL statement.

Every placeholder in the dynamic string must be associated with a bind argument in the USING clause. Numeric, character, and string literals are allowed in the USING clause, but Boolean literals (TRUE, FALSE, NULL) are not.

Any bind arguments in the query are evaluated only when the cursor variable is opened. To fetch from the cursor using different bind values, you must reopen the cursor variable with the bind arguments set to their new values.

Dynamic SQL supports all the SQL datatypes. For example, bind arguments can be collections, LOBs, instances of an object type, and refs. As a rule, dynamic SQL does not support PL/SQL-specific types. For instance, bind arguments cannot be Booleans or index-by tables.

Example
The following example declares a cursor variable, then associates it with a dynamic SELECT statement:
DECLARE
TYPE EmpCurTyp IS REF CURSOR; -- define weak REF CURSOR type
emp_cv EmpCurTyp; -- declare cursor variable
my_ename VARCHAR2(15);
my_sal NUMBER := 1000;
BEGIN
OPEN emp_cv FOR -- open cursor variable
'SELECT ename, sal FROM emp WHERE sal > :s' USING my_sal;
...
END;

Example  Dynamic SQL Fetching into a Record

As the following example shows, you can fetch rows from the result set of a dynamic multi-row query into a record:

DECLARE
TYPE EmpCurTyp IS REF CURSOR;
emp_cv EmpCurTyp;
emp_rec emp%ROWTYPE;
sql_stmt VARCHAR2(200);
my_job VARCHAR2(15) := 'CLERK';
BEGIN
sql_stmt := 'SELECT * FROM emp WHERE job = :j';
OPEN emp_cv FOR sql_stmt USING my_job;
LOOP
FETCH emp_cv INTO emp_rec;
EXIT WHEN emp_cv%NOTFOUND;
-- process record
END LOOP;
CLOSE emp_cv;
END;

/

Example Dynamic SQL with BULK COLLECT INTO Clause

You can bind define variables in a dynamic query using the BULK COLLECT INTO clause. As the following example shows, you can use that clause in a bulk FETCH or bulk EXECUTE IMMEDIATE statement:

DECLARE
TYPE EmpCurTyp IS REF CURSOR;
TYPE NumList IS TABLE OF NUMBER;
TYPE NameList IS TABLE OF VARCHAR2(15);
emp_cv EmpCurTyp;
empnos NumList;
enames NameList;
sals NumList;
BEGIN
OPEN emp_cv FOR 'SELECT empno, ename FROM emp';
FETCH emp_cv BULK COLLECT INTO empnos, enames;
CLOSE emp_cv;
EXECUTE IMMEDIATE 'SELECT sal FROM emp'
BULK COLLECT INTO sals;
END;

/

Using Cursor Variables (REF CURSORs)

Like a cursor, a cursor variable points to the current row in the result set of a multi-row query. A cursor variable is more flexible because it is not tied to a specific query. You can open a cursor variable for any query that returns the right set of columns. 

You pass a cursor variable as a parameter to local and stored subprograms. Opening the cursor variable in one subprogram, and processing it in a different subprogram, helps to centralize data retrieval. This technique is also useful for multi-language applications, where a PL/SQL subprogram might return a result set to a subprogram written in a different language.

Cursor variables are available to every PL/SQL client. For example, you can declare a cursor variable in a PL/SQL host environment such as an OCI or Pro*C program, then pass it as an input host variable (bind variable) to PL/SQL. Application development tools such as Oracle Forms and Oracle Reports, which have a PL/SQL engine, can use cursor variables entirely on the client side. Or, you can pass cursor variables back and forth between a client and the database server through remote procedure calls.

What Are Cursor Variables (REF CURSORs)?

Cursor variables are like pointers to result sets. You use them when you want to perform a query in one subprogram, and process the results in a different subprogram (possibly one written in a different language). A cursor variable has datatype REF CURSOR, and you might see them referred to informally as REF CURSORs. 

Unlike an explicit cursor, which always refers to the same query work area, a cursor variable can refer to different work areas. You cannot use a cursor variable where a cursor is expected, or vice versa.

Why Use Cursor Variables?

You use cursor variables to pass query result sets between PL/SQL stored subprograms and various clients. PL/SQL and its clients share a pointer to the query work area in which the result set is stored. For example, an OCI client, Oracle Forms application, and Oracle database server can all refer to the same work area.

A query work area remains accessible as long as any cursor variable points to it, as you pass the value of a cursor variable from one scope to another. For example, if you pass a host cursor variable to a PL/SQL block embedded in a Pro*C program, the work area to which the cursor variable points remains accessible after the block completes.

If you have a PL/SQL engine on the client side, calls from client to server impose no restrictions. For example, you can declare a cursor variable on the client side, open and fetch from it on the server side, then continue to fetch from it back on the client side. You can also reduce network traffic by having a PL/SQL block open or close several

host cursor variables in a single round trip.

Declaring REF CURSOR Types and Cursor Variables

To create cursor variables, you define a REF CURSOR type, then declare cursor variables of that type. You can define REF CURSOR types in any PL/SQL block, ubprogram, or package. In the following example, you declare a REF CURSOR type that represents a result set from the DEPARTMENTS table:

DECLARE

TYPE DeptCurTyp IS REF CURSOR RETURN departments%ROWTYPE;

REF CURSOR types can be strong (with a return type) or weak (with no return type).

Strong REF CURSOR types are less error prone because the PL/SQL compiler lets you associate a strongly typed cursor variable only with queries that return the right set of

columns. Weak REF CURSOR types are more flexible because the compiler lets you associate a weakly typed cursor variable with any query.

Because there is no type checking with a weak REF CURSOR, all such types are interchangeable. Instead of creating a new type, you can use the predefined type SYS_REFCURSOR.

Once you define a REF CURSOR type, you can declare cursor variables of that type in any PL/SQL block or subprogram.

DECLARE

TYPE EmpCurTyp IS REF CURSOR RETURN emp%ROWTYPE; -- strong

TYPE GenericCurTyp IS REF CURSOR; -- weak

cursor1 EmpCurTyp;

cursor2 GenericCurTyp;

my_cursor SYS_REFCURSOR; -- didn't need to declare a new type above

The following example declares the cursor variable dept_cv:

DECLARE

TYPE DeptCurTyp IS REF CURSOR RETURN dept%ROWTYPE;

dept_cv DeptCurTyp; -- declare cursor variable

To avoid declaring the same REF CURSOR type in each subprogram that uses it, you can put the REF CURSOR declaration in a package spec. You can declare cursor variables of that type in the corresponding package body, or within your own procedure or function.

Example 1 Cursor Variable Returning %ROWTYPE

In the RETURN clause of a REF CURSOR type definition, you can use %ROWTYPE to refer

to a strongly typed cursor variable:

DECLARE

TYPE TmpCurTyp IS REF CURSOR RETURN employees%ROWTYPE;

tmp_cv TmpCurTyp; -- declare cursor variable

TYPE EmpCurTyp IS REF CURSOR RETURN tmp_cv%ROWTYPE;

emp_cv EmpCurTyp; -- declare cursor variable

BEGIN

NULL;

END;

/

Example 2 Cursor Variable Returning %TYPE

You can also use %TYPE to provide the datatype of a record variable:

DECLARE

dept_rec departments%ROWTYPE; -- declare record variable

TYPE DeptCurTyp IS REF CURSOR RETURN dept_rec%TYPE;

dept_cv DeptCurTyp; -- declare cursor variable

BEGIN

NULL;

END;

/

Example 3 Cursor Variable Returning Record Type

This example specifies a user-defined RECORD type in the RETURN clause:

DECLARE

TYPE EmpRecTyp IS RECORD (

employee_id NUMBER,

last_name VARCHAR2(30),

salary NUMBER(7,2));

TYPE EmpCurTyp IS REF CURSOR RETURN EmpRecTyp;

emp_cv EmpCurTyp; -- declare cursor variable

BEGIN

NULL;

END;

/

Passing Cursor Variables As Parameters

You can declare cursor variables as the formal parameters of functions and procedures.

The following example defines a REF CURSOR type, then declares a cursor variable of that type as a formal parameter:

DECLARE

TYPE EmpCurTyp IS REF CURSOR RETURN employees%ROWTYPE;

emp EmpCurTyp;

-- Once we have a result set, we can process all the rows

-- inside a single procedure rather than calling a procedure

-- for each row.

PROCEDURE process_emp_cv (emp_cv IN EmpCurTyp) IS

person employees%ROWTYPE;

BEGIN

dbms_output.put_line('-----');

dbms_output.put_line('Here are the names from the result set:');

LOOP

FETCH emp_cv INTO person;

EXIT WHEN emp_cv%NOTFOUND;

dbms_output.put_line('Name = ' || person.first_name ||

' ' || person.last_name);

END LOOP;

END;

/

BEGIN

-- First find 10 arbitrary employees.

OPEN emp FOR SELECT * FROM employees WHERE ROWNUM < 11;

process_emp_cv(emp);

CLOSE emp;

-- Then find employees matching a condition.

OPEN emp FOR SELECT * FROM employees WHERE last_name LIKE 'R%';

process_emp_cv(emp);

CLOSE emp;

END;

/

Controlling Cursor Variables: OPEN-FOR, FETCH, and CLOSE 

You use three statements to control a cursor variable: OPEN-FOR, FETCH, and CLOSE.

First, you OPEN a cursor variable FOR a multi-row query. Then, you FETCH rows from

the result set. When all the rows are processed, you CLOSE the cursor variable.

Opening a Cursor Variable

The OPEN-FOR statement associates a cursor variable with a multi-row query, executes

the query, and identifies the result set.

OPEN {cursor_variable | :host_cursor_variable} FOR

{ select_statement

| dynamic_string [USING bind_argument[, bind_argument]...] };

The cursor variable can be declared directly in PL/SQL, or in a PL/SQL host environment such as an OCI program.

The SELECT statement for the query can be coded directly in the statement, or can be a string variable or string literal. When you use a string as the query, it can include placeholders for bind variables, and you specify the corresponding values with a USING clause.

Note: This section discusses the static SQL case, in which select_statement is

used. 

Unlike cursors, cursor variables take no parameters. Instead, you can pass whole queries (not just parameters) to a cursor variable. The query can reference host variables and PL/SQL variables, parameters, and functions.

The example below opens a cursor variable. Notice that you can apply cursor attributes (%FOUND, %NOTFOUND, %ISOPEN, and %ROWCOUNT) to a cursor variable.

DECLARE

TYPE EmpCurTyp IS REF CURSOR RETURN employees%ROWTYPE;

emp_cv EmpCurTyp;

BEGIN

IF NOT emp_cv%ISOPEN THEN

/* Open cursor variable. */

OPEN emp_cv FOR SELECT * FROM employees;

END IF;

CLOSE emp_cv;

END;

/

Other OPEN-FOR statements can open the same cursor variable for different queries. You need not close a cursor variable before reopening it. (Recall that consecutive OPENs of a static cursor raise the predefined exception CURSOR_ALREADY_OPEN.)

When you reopen a cursor variable for a different query, the previous query is lost.

Example 4 Stored Procedure to Open a Ref Cursor

Typically, you open a cursor variable by passing it to a stored procedure that declares an IN OUT parameter that is a cursor variable. For example, the following procedure opens a cursor variable:

CREATE PACKAGE emp_data AS

TYPE EmpCurTyp IS REF CURSOR RETURN employees%ROWTYPE;

PROCEDURE open_emp_cv (emp_cv IN OUT EmpCurTyp);

END emp_data;

/

CREATE PACKAGE BODY emp_data AS

PROCEDURE open_emp_cv (emp_cv IN OUT EmpCurTyp) IS

BEGIN

OPEN emp_cv FOR SELECT * FROM employees;

END open_emp_cv;

END emp_data;

/

DROP PACKAGE emp_data;

You can also use a standalone stored procedure to open the cursor variable. Define the REF CURSOR type in a package, then reference that type in the parameter declaration for the stored procedure.

Example 4 Stored Procedure to Open Ref Cursors with Different Queries

To centralize data retrieval, you can group type-compatible queries in a stored procedure. In the example below, the packaged procedure declares a selector as one of its formal parameters. When called, the procedure opens the cursor variable emp_cv for the chosen query.

CREATE PACKAGE emp_data AS

TYPE EmpCurTyp IS REF CURSOR RETURN emp%ROWTYPE;

PROCEDURE open_emp_cv (emp_cv IN OUT EmpCurTyp, choice INT);

END emp_data;

CREATE PACKAGE BODY emp_data AS

PROCEDURE open_emp_cv (emp_cv IN OUT EmpCurTyp, choice INT) IS

BEGIN

IF choice = 1 THEN

OPEN emp_cv FOR SELECT * FROM emp WHERE comm IS NOT NULL;

ELSIF choice = 2 THEN

OPEN emp_cv FOR SELECT * FROM emp WHERE sal > 2500;

ELSIF choice = 3 THEN

OPEN emp_cv FOR SELECT * FROM emp WHERE deptno = 20;

END IF;

END;

END emp_data;

Example 5 Cursor Variable with Different Return Types

For more flexibility, a stored procedure can execute queries with different return types:

CREATE PACKAGE admin_data AS

TYPE GenCurTyp IS REF CURSOR;

PROCEDURE open_cv (generic_cv IN OUT GenCurTyp, choice INT);

END admin_data;

/

CREATE OR REPLACE PACKAGE BODY admin_data AS

PROCEDURE open_cv (generic_cv IN OUT GenCurTyp, choice INT) IS

BEGIN

IF choice = 1 THEN

OPEN generic_cv FOR SELECT * FROM employees;

ELSIF choice = 2 THEN

OPEN generic_cv FOR SELECT * FROM departments;

ELSIF choice = 3 THEN

OPEN generic_cv FOR SELECT * FROM countries;

END IF;

END;

END admin_data;

/

Fetching from a Cursor Variable

The FETCH statement retrieves rows from the result set of a multi-row query. It works the same with cursor variables as with explicit cursors.

Example 6 Fetching from a Cursor Variable into a Record

The following example fetches rows one at a time from a cursor variable into a record:

DECLARE

TYPE EmpCurTyp IS REF CURSOR RETURN employees%ROWTYPE;

emp_cv EmpCurTyp;

emp_rec employees%ROWTYPE;

BEGIN

OPEN emp_cv FOR SELECT * FROM employees WHERE salary < 3000;

LOOP

/* Fetch from cursor variable. */

FETCH emp_cv INTO emp_rec;

EXIT WHEN emp_cv%NOTFOUND; -- exit when last row is fetched

-- process data record

dbms_output.put_line('Name = ' || emp_rec.first_name || ' ' ||

emp_rec.last_name);

END LOOP;

CLOSE emp_cv;

END;

/

Example 7 Fetching from a Cursor Variable into Collections

Using the BULK COLLECT clause, you can bulk fetch rows from a cursor variable into

one or more collections:

DECLARE

TYPE EmpCurTyp IS REF CURSOR;

TYPE NameList IS TABLE OF employees.last_name%TYPE;

TYPE SalList IS TABLE OF employees.salary%TYPE;

emp_cv EmpCurTyp;

names NameList;

sals SalList;

BEGIN

OPEN emp_cv FOR SELECT last_name, salary FROM employees WHERE salary < 3000;

FETCH emp_cv BULK COLLECT INTO names, sals;

CLOSE emp_cv;

-- Now loop through the NAMES and SALS collections.

FOR i IN names.FIRST .. names.LAST

LOOP

dbms_output.put_line('Name = ' || names(i) || ', salary = ' ||

sals(i));

END LOOP;

END;

/

Any variables in the associated query are evaluated only when the cursor variable is opened. To change the result set or the values of variables in the query, reopen the cursor variable with the variables set to new values. You can use a different INTO clause on separate fetches with the same cursor variable. Each fetch retrieves another row from the same result set.

PL/SQL makes sure the return type of the cursor variable is compatible with the INTO clause of the FETCH statement. If there is a mismatch, an error occurs at compile time if the cursor variable is strongly typed, or at run time if it is weakly typed. At run time, PL/SQL raises the predefined exception ROWTYPE_MISMATCH before the first fetch. If you trap the error and execute the FETCH statement using a different (compatible)

INTO clause, no rows are lost.

When you declare a cursor variable as the formal parameter of a subprogram that fetches from the cursor variable, you must specify the IN or IN OUT mode. If the subprogram also opens the cursor variable, you must specify the IN OUT mode. If you try to fetch from a closed or never-opened cursor variable, PL/SQL raises the predefined exception INVALID_CURSOR.

Closing a Cursor Variable

The CLOSE statement disables a cursor variable and makes the associated result set undefined. Close the cursor variable after the last row is processed.

When declaring a cursor variable as the formal parameter of a subprogram that closes the cursor variable, you must specify the IN or IN OUT mode.

If you try to close an already-closed or never-opened cursor variable, PL/SQL raises the predefined exception INVALID_CURSOR.

Reducing Network Traffic When Passing Host Cursor Variables to PL/SQL

When passing host cursor variables to PL/SQL, you can reduce network traffic by grouping OPEN-FOR statements. For example, the following PL/SQL block opens multiple cursor variables in a single round trip:

/* anonymous PL/SQL block in host environment */

BEGIN

OPEN :emp_cv FOR SELECT * FROM employees;

OPEN :dept_cv FOR SELECT * FROM departments;

OPEN :loc_cv FOR SELECT * FROM locations;

END;

This technique might be useful in Oracle Forms, for instance, when you want to populate a multi-block form.

When you pass host cursor variables to a PL/SQL block for opening, the query work areas to which they point remain accessible after the block completes, so your OCI or Pro*C program can use these work areas for ordinary cursor operations. In the following example, you open several such work areas in a single round trip:

BEGIN

OPEN :c1 FOR SELECT 1 FROM dual;

OPEN :c2 FOR SELECT 1 FROM dual;

OPEN :c3 FOR SELECT 1 FROM dual;

END;

/

The cursors assigned to c1, c2, and c3 behave normally, and you can use them for any purpose. When finished, release the cursors as follows:

BEGIN

CLOSE :c1;

CLOSE :c2;

CLOSE :c3;

END;

Avoiding Errors with Cursor Variables

If both cursor variables involved in an assignment are strongly typed, they must have exactly the same datatype (not just the same return type). If one or both cursor variables are weakly typed, they can have different datatypes.

If you try to fetch from, close, or refer to cursor attributes of a cursor variable that does not point to a query work area, PL/SQL raises the INVALID_CURSOR exception. You can make a cursor variable (or parameter) point to a query work area in two ways:

■ OPEN the cursor variable FOR the query.

■ Assign to the cursor variable the value of an already OPENed host cursor variable or PL/SQL cursor variable.

If you assign an unopened cursor variable to another cursor variable, the second one remains invalid even after you open the first one.

Be careful when passing cursor variables as parameters. At run time, PL/SQL raises ROWTYPE_MISMATCH if the return types of the actual and formal parameters are

incompatible.

Restrictions on Cursor Variables

Currently, cursor variables are subject to the following restrictions:

■ You cannot declare cursor variables in a package spec. For example, the following declaration is not allowed:

CREATE PACKAGE emp_stuff AS

TYPE EmpCurTyp IS REF CURSOR RETURN emp%ROWTYPE;

emp_cv EmpCurTyp; -- not allowed

END emp_stuff;

■ You cannot pass cursor variables to a procedure that is called through a database link.

■ If you pass a host cursor variable to PL/SQL, you cannot fetch from it on the server side unless you also open it there on the same server call.

■ You cannot use comparison operators to test cursor variables for equality, inequality, or nullity.

■ You cannot assign nulls to a cursor variable.

■ Database columns cannot store the values of cursor variables. There is no equivalent type to use in a CREATE TABLE statement.

■ You cannot store cursor variables in an associative array, nested table, or varray.

■ Cursors and cursor variables are not interoperable; that is, you cannot use one where the other is expected. For example, you cannot reference a cursor variable in a cursor FOR loop.