![]() Previous |
![]() Next |
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
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.
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.
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:
The target table cannot be index organized or clustered.
The target table cannot contain object type or LOB columns.
The target table cannot have any triggers or referential integrity constraints defined on it.
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:
Any number and combination of CLOB and NCLOB columns
No LONG columns
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'