Use native dynamic sql - improve performance of application, PL-SQL Programming

Use Native Dynamic SQL

A few programs (a normal-purpose report writer for illustration) should build and process a variety of SQL statements at run time. Therefore, their full text is unknown until then. These statements can, and probably will, change from execution to execution. Therefore, they are known as the dynamic SQL statements.

Previously, to execute the dynamic SQL statements, you have to use the supply package DBMS_SQL. Now, within the PL/SQL, you can execute any type of dynamic SQL statement using an interface known as the native dynamic SQL.

The Native dynamic SQL is easier to use and much faster than the package DBMS_SQL. In the illustration below, you declare a cursor variable, then relate it with a dynamic SELECT statement that returns rows from database table emp:



emp_cv EmpCurTyp;

my_ename VARCHAR2(15);

my_sal NUMBER := 1000;


OPEN emp_cv FOR

'SELECT ename, sal FROM emp

WHERE sal > :s' USING my_sal;



Posted Date: 10/5/2012 4:56:32 AM | Location : United States

Related Discussions:- Use native dynamic sql - improve performance of application, Assignment Help, Ask Question on Use native dynamic sql - improve performance of application, Get Answer, Expert's Help, Use native dynamic sql - improve performance of application Discussions

Write discussion on Use native dynamic sql - improve performance of application
Your posts are moderated
Related Questions
Example of Table Literal - SQL Example: A Table Literal (correct version) VALUES ('S1', 'C1', 'Anne'), ('S1', 'C2', 'Anne'), ('S2', 'C1', 'Boris'), ('S3', 'C3'

Using TRIM This process has two forms. The TRIM removes an element from the end of the collection. The TRIM(n) removes the n elements from the end of the collection. For e.g.

Cause of Indeterminacy in SQL One root cause of indeterminacy in SQL lies in its implementation of comparison for equality. For certain system-defined types it is possible for

"Not Enforced" Table Constraints A constraint that is not enforced is not really a constraint within the meaning of the act, but SQL does have such a concept and it needs to b

Parameter and Keyword Description: record_type_name: This identifies the user-defined type specifier that is used in the subsequent declarations of the records. NOT N

Using EXISTS The EXISTS(n) returns TRUE if the nth element in a collection exist. Or else, EXISTS(n) returns FALSE. Primarily, you use EXISTS with DELETE to maintain the spars

Deriving Predicates from Predicates in SQL The corresponding section in the theory book describes how predicates can be derived from predicates using (a) the logical connectiv

BETWEEN and NOT BETWEEN Operator in SQL Example: Restricting exam marks to between 0 and 100 CREATE ASSERTION Marks_between_0_and_100 CHECK (NOT EXISTS (SELECT * FROM

Challenge 1 You are required to do the project and write a test plan for it. Demo 4 is a check writer program for employees. In it, the user enters all information about the

Project Description: I want a small relational database to be built. I want the database to have the subsequent information tables: Employee Information Document storage