Execute dynamic SQL statements in form

The dynamic SQL times error is performed today in FORM Builder with execute immediate ' SQL Staments '.

It is found that the dynamic SQL is executed with the FORMS_DDL function in the form, and this function implies commit;

Detailed descriptions are as follows:

First, Forms_ddl function

FUNCTION FORMS_DDL (statement VARCHAR2);

For dynamic execution of SQL statements vs. PL statements

Precautions:

1, the maximum capacity of the parameter statement can not exceed 32K.

2. A DDL-type SQL statement that implies a commit command.

Second, Exec_sql bag

An SQL statement used to dynamically execute DDL types and DML types.

General use of Exec_sql packages:

First step: Connect to the database:

Exec_sql. Open_connection/exec_sql. Default_connection

Step two: Create a pointer variable (cursor):

Exec_sql. Open_cursor

Step three: Load an SQL statement (DDL, DML) for the pointer variable: exec_sql. Open_cursor

Fourth step: Execute the loaded SQL statement:

Exec_sql. Execute

Fifth step: Bulk PIN variable (cursor) and close database connection:

Exec_sql. Is_open, Exec_sql. Close_cursor, Exec_sql. Close_cursor, Exec_sql. Close_cursor

Iii. Summary

1. The FORMS_DDL function is primarily used for dynamically executing DDL-type SQL statements (which can be understood as SQL statements that do not use DDL types directly in a form.) )。 The FORMS_DDL function can only be used for the database to which the current form is connected.

2. Exec_sql package has two important uses:

First: Used to dynamically execute a SELECT statement and return a result set.

Second: Used for data access between different databases.

Execute dynamic SQL statements in form