Results for DBMS_SQL


1 to 20 of 143 results. Prev  Next
Results per page:   20  | 40 | 60


Supplied PL/SQL Packages and Types Reference, 9i Release 2 (9.2.0.2)

DBMS_SQL, 1 of 2

Using DBMS_SQLInterface 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 … release of the DBMS_SQL package does not provide CANCEL cursor procedures. Indicator variables are … DBMS_SQL

Supplied PL/SQL Packages and Types Reference, 9i Release 2 (9.2.0.2)

DBMS_SQL, 1 of 2

69 DBMS_SQLwhose name is not known until runtime. Additionally, DBMS_SQL enables you to parse any data … within a stored procedure by using the PARSE procedure supplied with the DBMS_SQL package. Note: Oracle8 … i introduces native dynamic SQL, an alternative to DBMS_SQL. Using native dynamic SQL, you can … replace DBMS_SQL.

Supplied PL/SQL Packages and Types Reference, 9i Release 2 (9.2.0.2)

DBMS_SQL, 2 of 2

Summary of DBMS_SQL SubprogramsTable 69-1 DBMS_SQL Subprograms Subprogram Description OPEN_CURSOR Function Returns cursor ID … Procedure Describes the columns for a cursor opened and parsed through DBMS_SQL. CLOSE_CURSOR Procedure

Application Developer's Guide - Fundamentals, 9i Release 2 (9.2.0.2)

Coding Dynamic SQL Statements

Advantages of the DBMS_SQL PackageThe DBMS_SQL package provides the following advantages over native dynamic SQL: DBMS_SQL is … Supported in Client-Side Programs The DBMS_SQL package is supported in client-side programs, but native … dynamic SQL is not. Every call to the DBMS_SQL package from the client-side program translates to a PL … variable,

Supplied PL/SQL Packages and Types Reference, 9i Release 2 (9.2.0.2)

DBMS_SQL, 1 of 2

Constants, Types, and Exceptions for DBMS_SQLConstants v6 constant INTEGER:= 0; native constant INTEGER:= 1; v7 constant INTEGER:= 2; Types TYPE varchar2s IS TABLE OF VARCHAR2(256) INDEX BY BINARY_INTEGER; TYPE desc_rec IS RECORD ( col_type BINARY_INTEGER:= 0, col_max_len BINARY_INTEGER:= 0, col_name VARCHAR2(32):= '', col_name_len BINARY_INTEGER:= 0, col_schema_name VARCHAR2(32):= '', col_schema_name_len BINARY_INTEGER:= 0, col_precision BINARY_INTEGER:=

Application Developer's Guide - Fundamentals, 9i Release 2 (9.2.0.2)

Coding Dynamic SQL Statements

Choosing Between Native Dynamic SQL and the DBMS_SQL PackageDBMS_SQL package. Native dynamic SQL lets you place dynamic SQL statements directly into PL/SQL code. These … information about the COMPATIBLE parameter. The DBMS_SQL package is a PL/SQL library that offers an … API to execute SQL statements dynamically. The DBMS_SQL package has procedures to open a cursor … , parse a cursor,

Application Developer's Guide - Fundamentals, 9i Release 2 (9.2.0.2)

Coding Dynamic SQL Statements

Examples of DBMS_SQL Package Code and Native Dynamic SQL Codethe DBMS_SQL package and native dynamic SQL. Specifically, the following types of examples are … table. TableĀ 8-2 shows sample code that accomplishes this query using the DBMS_SQL package and … native dynamic SQL. Table 8-2 Querying Using the DBMS_SQL Package and Native Dynamic SQL DBMS_SQL Query … this DML operation

Supplied PL/SQL Packages and Types Reference, 9i Release 2 (9.2.0.2)

DBMS_SQL, 2 of 2

OPEN_CURSOR FunctionThis procedure opens a new cursor. When you no longer need this cursor, you must close it explicitly by calling CLOSE_CURSOR. You can use cursors to run the same SQL statement repeatedly or to run a new SQL statement. When a cursor is reused, the contents of the corresponding cursor data area are reset when the new SQL statement is parsed. It is never necessary to close and reopen a cursor before

Supplied PL/SQL Packages and Types Reference, 9i Release 2 (9.2.0.2)

DBMS_SQL, 2 of 2

SyntaxDBMS_SQL.OPEN_CURSOR RETURN INTEGER;

Supplied PL/SQL Packages and Types Reference, 9i Release 2 (9.2.0.2)

DBMS_SQL, 2 of 2

ParametersTable 69-3 BIND_VARIABLE Procedure Parameters Parameter Description c ID number of the cursor to which you want to bind a value. name Name of the variable in the statement. value Value that you want to bind to the variable in the cursor. For IN and IN / OUT variables, the value has the same type as the type of the value being passed in for this parameter. out_value_size Maximum expected OUT value

Supplied PL/SQL Packages and Types Reference, 9i Release 2 (9.2.0.2)

DBMS_SQL, 2 of 2

Scalar and LOB Types for Collectionsnumber of rows into it using DBMS_SQL. (These are the same types as you can specify for the BIND_ARRAY

Supplied PL/SQL Packages and Types Reference, 9i Release 2 (9.2.0.2)

DBMS_SQL, 2 of 2

Pragmaspragma restrict_references(define_array,RNDS,WNDS); The subsequent FETCH_ROWS call fetch \"count\" rows. When the COLUMN_VALUE call is made, these rows are placed in positions indx, indx+1, indx+2, and so on. While there are still rows coming, the user keeps issuing FETCH_ROWS / COLUMN_VALUE calls. The rows keep accumulating in the table specified as an argument in the COLUMN_VALUE call.

Supplied PL/SQL Packages and Types Reference, 9i Release 2 (9.2.0.2)

DBMS_SQL, 2 of 2

EXECUTE_AND_FETCH FunctionThis function executes the given cursor and fetches rows. This function provides the same functionality as calling EXECUTE and then calling FETCH_ROWS. Calling EXECUTE_AND_FETCH instead, however, may reduce the number of network round-trips when used against a remote database. The EXECUTE_AND_FETCH function returns the number of rows actually fetched.

Supplied PL/SQL Packages and Types Reference, 9i Release 2 (9.2.0.2)

DBMS_SQL, 2 of 2

Pragmaspragma restrict_references(fetch_rows,WNDS);

Supplied PL/SQL Packages and Types Reference, 9i Release 2 (9.2.0.2)

DBMS_SQL, 2 of 2

ParametersTable 69-11 COLUMN_VALUE Procedure Parameters Parameter Description c ID number of the cursor from which you are fetching the values. position Relative position of the column in the cursor. The first column in a statement has position 1. value Returns the value at the specified column and row. If the row number specified is greater than the total number of rows fetched, then you receive an error message.

Supplied PL/SQL Packages and Types Reference, 9i Release 2 (9.2.0.2)

DBMS_SQL, 2 of 2

VARIABLE_VALUE ProcedureThis procedure returns the value of the named variable for a given cursor. It is used to return the values of bind variables inside PL/SQL blocks or DML statements with returning clause.

Supplied PL/SQL Packages and Types Reference, 9i Release 2 (9.2.0.2)

DBMS_SQL, 2 of 2

BIND_VARIABLE and BIND_ARRAY ProceduresThese two procedures bind a given value or set of values to a given variable in a cursor, based on the name of the variable in the statement. If the variable is an IN or IN / OUT variable or an IN collection, then the given bind value must be valid for the variable or array type. Bind values for OUT variables are ignored. The bind variables or collections of a SQL statement are identified by their

Supplied PL/SQL Packages and Types Reference, 9i Release 2 (9.2.0.2)

DBMS_SQL, 2 of 2

SyntaxDBMS_SQL.BIND_VARIABLE ( c IN INTEGER, name IN VARCHAR2, value IN ) Where can be any one of the following types: NUMBER DATE VARCHAR2 CHARACTER SET ANY_CS BLOB CLOB CHARACTER SET ANY_CS BFILE UROWID Notice that BIND_VARIABLE is overloaded to accept different datatypes. See Also: Oracle9i Application Developer's Guide - Large Objects (LOBs)

Supplied PL/SQL Packages and Types Reference, 9i Release 2 (9.2.0.2)

DBMS_SQL, 2 of 2

Pragmaspragma restrict_references(bind_variable,WNDS);

Supplied PL/SQL Packages and Types Reference, 9i Release 2 (9.2.0.2)

DBMS_SQL, 2 of 2

SyntaxDBMS_SQL.BIND_ARRAY ( c IN INTEGER, name IN VARCHAR2, IN [,index1 IN INTEGER, index2 IN INTEGER)] ); Where the and its corresponding can be any one of the following matching pairs: Number_Table Varchar2_Table Date_Table Blob_Table Clob_Table Bfile_Table Urowid_Table Notice that the BIND_ARRAY procedure is overloaded to accept different datatypes.


Was this page helpful?