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
Implicit Cursor Attributes The Implicit cursor attributes returns the information about the execution of an INSERT, DELETE, UPDATE, or SELECT INTO statement. The cursor attribu

Keys in SQL SQL support for keys in the following respects: SQL does not require at least one key for every base table. If no key is explicitly declared, then KEY {ALL B

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

Parameter and Keyword Description: select_item: This select_item is a value returned by the SELECT statement, and then assigned to the equivalent variable or field in the

Subprograms The PL/SQL has two types of subprograms known as the procedures and functions that can take parameters and be invoked. As the following example represents, a subp

I have a Pascal Source file that needs to be compiled into a Service. In addition, there are various functions (Pascal Procedures I guess) that need to be created to Read and Write

On occasion, some of Brewbean's customers mistakenly leave an item out of a basket already checked out, so they create a new basket containing the missing items. However, they requ

Inserting Objects: You can use the INSERT statement to add objects to an object table. In the illustration below, you insert a Person object into the object table persons:

Using the student and faculty tables create a select query that outputs all students for a specific advisor. Generate the execution plan, select out the explain plan . Create an

Data Types in SQL - Character CHARACTER or, synonymously, CHAR, for character strings. When this type is to be the declared type of something (e.g., a column), the permissible