Previous
Previous
 
Next
Next


SQL PREPARE

Within a program, the SQL PREPARE command precompiles a SQL statement for later execution, in the same program using SQL EXECUTE. Typically, you use SQL PREPARE in programs to optimize the processing of SQL statements that are executed repeatedly, particularly those involving OLAP DML input expressions, such as INSERT, UPDATE, and DELETE.

Syntax

SQL PREPARE statement-name FROM sql-statement [insert-options]

Parameters

statement-name

A name that you assign to the executable code produced from sql-statement. You can redefine statement-name just by issuing another SQL PREPARE statement.

sql-statement

The SQL statement that you want to precompile for more efficient execution. It cannot contain ampersand substitution or variables that are undefined when the program is compiled.

insert-options

The following options are optional when sql-statement is an INSERT statement:

DIRECT=YES|NO specifies if the insert is a direct-path INSERT. This option must be the first option specified right aver the values phrase of the INSERT statement.

Setting this option to YES specifies that the insert is a direct-path INSERT. Direct-path INSERT enhances performance during INSERT operations and is similar to the functionality of Oracle's direct-path loader utility, SQL*Loader. 

The default value is NO which specifies a normal INSERT.

NOLOG=YES|NO specifies if logging occurs. Setting this option to YES specifies that the redo information is not recorded in the redo log files which makes load-time faster. The default value is NO which specifies logging mode.

PARTITION=(sub)partition-name specifies that only the segments related to the named partition or subpartition are locked. When you specify this option, another session can insert data to unrelated segments in the same table. When you do not specify this option (the default), other sessions cannot insert data into the same table.

Usage Notes

Using Direct-Path INSERT

When performing a direct-path INSERT, data is written directly into data files, bypassing the buffer cache, free space in the existing data is not reused, and the inserted data is appended after existing data in the table.

Restrictions When Using Direct-Path INSERT Direct-path INSERT is subject to several restrictions. When executing a direct-path INSERT using the OLAP DML, transactions in the session issuing the direct-path INSERT must be committed for the INSERT to execute successfully. (You can use the SQL or OLAP DML COMMIT to commit transactions.)

Additionally, the general restrictions that apply to using direct-path INSERT in SQL apply to preparing a direct-pathINSERT using OLAP DML PREPARE statements:

For more information on restrictions when using a direct-path INSERT, see the discussion of the INSERT statement in Oracle Database SQL Language Reference.

Data Type Conversions During Direct-Path Insertion Table: Automatic Data Type Conversion During Direct-Path Insertion shows the automatic data type conversion performed during direct-path insertion.

Automatic Data Type Conversion During Direct-Path Insertion

Oracle RDBMS Oracle OLAP DML

CHAR(n), VARCHAR(n)

TEXT

LONG

TEXT with WIDE option

CHAR(8), VARCHAR(8)

ID

DATE

DATE

NUMBER(x,x)

DECIMAL (SHORTDECIMAL)

INTEGER (or NUMBER(38)

INTEGER (SHORTINTEGER)

NUMBER(1)

BOOLEAN


Inserting OLAP Text Data into a Column with a DATE Data Type When inserting text data from Oracle OLAP into a column with a DATE data type, you must use the default date format of DD MMM YY. You can use slashes (/), hyphens (-), or spaces as separators. When the data is in a different format, you can use the Oracle TO_DATE function in a SQL INSERT statement.

Inserting Large Text Values into a CLOB or NCLOB Column To insert more than 2K bytes of text data from an analytic workspace into a CLOB or NCLOB column, use the WIDE keyword before the name of the OLAP DML input expression. When the data type of the OLAP DML input expression is TEXT, then the target data type is CLOB. When the data type of the input expression is NTEXT, then the target data type is NCLOB.

The following is the syntax of an OLAP DML input expression with the WIDE keyword. See Example: Using the WIDE Keyword for an example.

:WIDE input-expression

See Example: Using the WIDE Keyword for an example.

Note that the target table must conform to these guidelines:

The RDBMS imposes some restrictions on large data types. Oracle OLAP does not signal an error when you violate these restrictions. However, you might get unexpected results. Refer to the Oracle Application Developer's Guide for restrictions on large data types.

Calculating the Number of Characters

You can calculate the number of characters that are sent to a database table from an Oracle OLAP variable by using the following formula.

NUMCHARS(variable) + 2 * (NUMLINES(variable) - 1)

This formula counts the extra carriage return and line feed characters that Oracle OLAP inserts between lines when passing the text to the database.

Examples

Preparing a FOR Loop

To automatically add all the sales people from the salesperson dimension to the relational table, you could write a program and put the SQL INSERT statement in a FOR loop.

FOR salesperson
   SQL INSERT INTO Sales VALUES (:Salesperson, :Dollars) DIRECT=YES

When a statement includes OLAP DML input expressions and are executed repeatedly, such as in a FOR loop, you can make the statements more efficient by "preparing" the SQL statement first. The INSERT statement becomes part of a PREPARE statement.

SQL PREPARE s1 FROM INSERT INTO Sales VALUES -
   (:Salesperson, :Dollars) DIRECT=YES
FOR Salesperson
   DO
      SQL EXECUTE s1
      IF SQLCODE NE 0
      THEN BREAK
   DOEND

Updating a Table

The next example shows a simple update of a table using data stored in an analytic workspace. The market dimension is limited to one value at a time in the FOR loop. The SQL phrase WHERE S.Market=:market specifies that the sales value in the row for that market is the value that is changed.

FOR market
   SQL UPDATE Mkt SET Sales=:Mkt.Sales WHERE S.Market=:market

Like the INSERT statement in the previous example, an UPDATE statement should be used in a PREPARE statement and executed in an ACROSS statement or FOR loop.

SQL PREPARE s2 FROM UPDATE mkt -
   SET Sales=:mkt.sales WHERE s.market=:market
ACROSS market DO 'SQL EXECUTE s1'

Using the WIDE Keyword

In both of the following statements, WIDE indicates that the target value is CLOB when var1 is TEXT, or NCLOB when var1 is NTEXT.

SQL INSERT INTO CLOB_TEST values (:dim1 :WIDE var1)
SQL UPDATE CLOB_TEXT SET clob_col = :WIDE var1 WHERE key = 1