DBMS_SQLThe DBMS_SQL package provides an interface to use dynamic SQL to parse any data manipulation language (DML) or data definition language (DDL) statement using PL/SQL. For example, you can enter a DROPTABLE statement from within a stored procedure by using the PARSE procedure supplied with the DBMS_SQL package. Using DBMS_SQL Overview. Oracle lets you write stored procedures and anonymous PL/SQL blocks that use dynamic SQL. Dynamic SQL statements are not embedded in your source program; rather, they are stored in character strings that are input to, or built by, the program at runtime. This enables you to create more general- purpose procedures. For example, dynamic SQL lets you create a procedure that operates on a table whose name is not known until runtime. Native Dynamic SQL is an alternative to DBMS_SQL that lets you place dynamic SQL statements directly into PL/SQL blocks. In most situations, Native Dynamic SQL is easier to use and performs better than DBMS_SQL. However, Native Dynamic SQL itself has certain limitations: There is no support for so- called Method 4 (for dynamic SQL statements with an unknown number of inputs or outputs)Also, there are some tasks that can only be performed using DBMS_SQL. The ability to use dynamic SQL from within stored procedures generally follows the model of the Oracle Call Interface (OCI). PL/SQL differs somewhat from other common programming languages, such as C. For example, addresses (also called pointers) are not user- visible in PL/SQL. ![]() Selects data from a table or multiple tables. GROUP BY groups the the result by the given expression(s). HAVING filter rows after grouping. ORDER BY sorts the result. As a result, there are some differences between the Oracle Call Interface and the DBMS_SQL package. These differences include the following: The OCI uses bind by address, while the DBMS_SQL package uses bind by value. With DBMS_SQL you must call VARIABLE_VALUE to retrieve the value of an OUT parameter for an anonymous block, and you must call COLUMN_VALUE after fetching rows to actually retrieve the values of the columns in the rows into your program. The current release of the DBMS_SQL package does not provide CANCEL cursor procedures. ![]() Indicator variables are not required, because NULLs are fully supported as values of a PL/SQL variable. A sample usage of the DBMS_SQL package follows. For users of the Oracle Call Interface, this code should seem fairly straightforward. Security Model. DBMS_SQL is a SYS- owned package compiled with AUTHIDCURRENT_USER. Any DBMS_SQL subprogram called from an anonymous PL/SQL block is run using the privileges of the current user. Removes one or more rows from a table or view in SQL Server. Specifies the temporary named result set, also known as common table expression, defined within the scope. 100 DBMS_SQL. The DBMS_SQL package provides an interface to use dynamic SQL to parse any data manipulation language (DML) or data definition language (DDL) statement. In Oracle Database 1. Release 1 (1. 1. 1), Oracle introduces a number of enhancements to DBMS_SQL to improve the security of the package. Preventing Malicious or Accidental Access of Open Cursor Numbers. An error, ORA- 2. DBMS_SQL subprogram is called with a cursor number that does not denote an open cursor. When the error is raised, an alert is issued to the alert log and DBMS_SQL becomes inoperable for the life of the session. If the actual value for the cursor number in a call to the IS_OPEN Function denotes a cursor currently open in the session, the return value is TRUE. If the actual value is NULL, then the return value is FALSE. Otherwise, this raises an ORA- 2. Note that the OPEN_CURSOR Function is the only DBMS_SQL subprogram that has no formal parameter for the cursor number; rather, it returns a cursor number. Therefore it is not within the scope of these rules. Preventing Inappropriate Use of a Cursor. Cursors are protected from security breaches that subvert known existing cursors. Checks are made when binding and executing. Optionally, checks may be performed for every single DBMS_SQL subprogram call. The check is: The current_user is the same on calling the subprogram as it was on calling the most recent parse. The enabled roles on calling the subprogram must be a superset of the enabled roles on calling the most recent parse. Consistent with the use of definer's rights subprograms, roles do not apply. If either check fails, and ORA- 2. The mechanism for defining when checks are performed is a new overload for the OPEN_CURSOR subprogram which takes a formal parameter, security_level, with allowed values NULL, 1 and 2. When security_level = 1 (or is NULL), the checks are made only when binding and executing. When security_level = 2, the checks are always made. Upgrade Considerations. This security regime is stricter than those in Oracle Database 1. Release 2 (1. 0. 2) and previous releases. As a consequence, users of DBMS_SQL may encounter runtime errors on upgrade. While these security enhancements make for more secure applications, users may wish to relax the security checks temporarily as they migrate to Oracle Database 1. Release 1 (1. 1. 1). If so, please consult with Oracle Support on steps to relax the above security restrictions. Constants. The constants described in Table 1. PARSE Procedures. Table 1. 22- 1 DBMS_SQL Constants. Name. Type. Value. Description. V6. INTEGER0. Specifies Oracle database version 6 behavior. NATIVEINTEGER1. Specifies normal behavior for the database to which the program is connected. V7. INTEGER2. Specifies Oracle database version 7 behavior. Exceptions. inconsistent_type EXCEPTION. This exception is raised by the COLUMN_VALUE Procedure or the VARIABLE_VALUE Procedures when the type of the given OUT parameter (for where to put the requested value) is different from the type of the value. Operational Notes. Execution Flow. OPEN_CURSORPARSEBIND_VARIABLE or BIND_ARRAYDEFINE_COLUMN, DEFINE_COLUMN_LONG, or DEFINE_ARRAYEXECUTEFETCH_ROWS or EXECUTE_AND_FETCHVARIABLE_VALUE, COLUMN_VALUE, or COLUMN_VALUE_LONGCLOSE_CURSOROPEN_CURSORTo process a SQL statement, you must have an open cursor. When you call the OPEN_CURSOR Function function, you receive a cursor ID number for the data structure representing a valid cursor maintained by Oracle. These cursors are distinct from cursors defined at the precompiler, OCI, or PL/SQL level, and are used only by the DBMS_SQL package. PARSEEvery SQL statement must be parsed by calling the PARSE Procedures. Parsing the statement checks the statement's syntax and associates it with the cursor in your program. You can parse any DML or DDL statement. DDL statements are run on the parse, which performs the implied commit. Note. When parsing a DDL statement to drop a package or a procedure, a deadlock can occur if you're still using a procedure in the package. After a call to a procedure, that procedure is considered to be in use until execution has returned to the user side. Any such deadlock times out after five minutes. The execution flow of DBMS_SQL is shown in Figure 1. BIND_VARIABLE or BIND_ARRAYMany DML statements require that data in your program be input to Oracle. When you define a SQL statement that contains input data to be supplied at runtime, you must use placeholders in the SQL statement to mark where data must be supplied. For each placeholder in the SQL statement, you must call one of the bind procedures, the BIND_ARRAY Procedures or the BIND_VARIABLE Procedures, to supply the value of a variable in your program (or the values of an array) to the placeholder. When the SQL statement is subsequently run, Oracle uses the data that your program has placed in the output and input, or bind, variables. DBMS_SQL can run a DML statement multiple times — each time with a different bind variable. The BIND_ARRAY procedure lets you bind a collection of scalars, each value of which is used as an input variable once for each EXECUTE. This is similar to the array interface supported by the OCI. DEFINE_COLUMN, DEFINE_COLUMN_LONG, or DEFINE_ARRAYThe columns of the row being selected in a SELECT statement are identified by their relative positions as they appear in the select list, from left to right. For a query, you must call one of the define procedures (DEFINE_COLUMN, DEFINE_COLUMN_LONG, or DEFINE_ARRAY) to specify the variables that are to receive the SELECT values, much the way an INTO clause does for a static query. Use the DEFINE_COLUMN_LONG procedure to define LONG columns, in the same way that DEFINE_COLUMN is used to define non- LONG columns. You must call DEFINE_COLUMN_LONG before using the COLUMN_VALUE_LONG procedure to fetch from the LONG column. Use the DEFINE_ARRAY procedure to define a PL/SQL collection into which you want to fetch rows in a single SELECT statement. DEFINE_ARRAY provides an interface to fetch multiple rows at one fetch. You must call DEFINE_ARRAY before using the COLUMN_VALUE procedure to fetch the rows. EXECUTECall the EXECUTE function to run your SQL statement.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. Archives
November 2017
Categories |