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:

DECLARE

TYPE EmpCurTyp IS REF CURSOR;

emp_cv EmpCurTyp;

my_ename VARCHAR2(15);

my_sal NUMBER := 1000;

BEGIN

OPEN emp_cv FOR

'SELECT ename, sal FROM emp

WHERE sal > :s' USING my_sal;

...

END;

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
Structure of an Object Type: Similar to package, an object type has 2 parts: the specification and the body. The specification is the interface to your applications; it declar

Product-specific Packages The Oracle and different Oracle tools are supplied with the product-specific packages which help you to build the PL/SQL-based applications. For illus

Cursor Variables:   To execute the multi-row query, the Oracle opens an unnamed work region that stores the processing information. You can use an explicit cursor that names

Sequential Control Dissimilar to the IF and LOOP statements, the GOTO and NULL statements are not important to the PL/SQL programming. The configuration of PL/SQL is such that th

THEO R Y OF PANSPERMIA - Arrhenius (1908) postulated the cosmic panspermia theory that claims that organisms existed throughout the universe, and their spores, etc., could

Renaming Columns - SQL SQL has no direct counterpart of RENAME. To derive the table on the right in Figure 4.4 from the table on the left, Tutorial D has IS_CALLED RENAME ( St

Calling Constructors: The Calls to a constructor are allowed wherever the function calls are allowed. Similarly to the functions, a constructor is called as a section of an ex

UNION and OR - SQL SQL supports UNION explicitly but differently from the way it supports JOIN explicitly. As we have seen, JOIN is used exclusively within the FROM clause, su

Entering and Exiting If you enter the executable part of an autonomous routine, the major transaction suspends. When you exit the routine, the major transaction resumes. To ex

At times, customers make mistakes in submitting their orders and call to cancel the order. Brewbean’s wants to create a trigger that automatically updates the stock level of all pr