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
BIOCHE M ICA L ORIGIN OF LIFE - It is generally agreed by astronomers, geologists and biologists that the earth is approximately 4500-5000 million years old. It is an

Example of Tables within a Table - SQL Example: Obtaining C_ER from COURSE and EXAM_MARK SELECT CourseId, CAST (TABLE (SELECT DISTINCT StudentId, Mark FROM EXAM_MARK AS EM

Boolean Values Only the values TRUE, FALSE, & NULL can be assigned to a Boolean variable. For illustration, given the declaration DECLARE done BOOLEAN; the following statements

Table Represents an Extension - SQL It describes how each tuple in a relation represents a true instantiation of some predicate and each true instantiation is represented by s

Semidifference via NOT IN and a subquery SELECT StudentId FROM IS_CALLED WHERE Name = 'Devinder' AND StudentId NOT IN (SELECT StudentId FROM IS_ENROLLED_ON WHER

1. a. Write a trigger that fires when a part's price is updated. The trigger will write a record into a table called PriceUpdates. The record should contain the information of

Ensuring Backward Compatibility   The PL/SQL Version 2 permits some abnormal behavior which Version 8 disallows. Particularly, Version 2 permits you to (i) Make the forw

Example of WHEN or THEN Constraints A concrete example showing how SQL supports WHEN/THEN constraints CREATE TABLE SAL_HISTORY (EmpNo CHAR (6), Salary INTEGER NOT NULL,

Transaction Visibility As the figure shows, the changes made by an autonomous transaction become visible to another transaction whenever the autonomous transaction commits. Th

Benefit of the dynamic SQL: This part shows you how to take full benefit of the dynamic SQL and how to keep away from some of the common pitfalls. Passing the Names of Sc