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
Overview of control structures According to the structure theorem, any computer program can be written by using the basic control structures as shown in figure below. They can b

Parameter SELF in pl/sql The MEMBER methods recognize a built-in parameter named SELF that is an instance of the object type. Whether declared explicitly or implicitly, it is

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

Multiple Assignment- SQL SQL supports multiple assignment to local variables and also applies multiple assignment semantics in SET clauses of UPDATE statements, but does not

Application to Export Excel Data to MSSQL Server table I am having a table available in excel format and features the subsequent: - Some text is in Arabic (e.g. UTF-8 encodin

Number Types The Number types permit you to store the numeric data (real numbers, integers, and floating-point numbers), show quantities, and do computations. BINARY_INTEG

%NOTFOUND The %NOTFOUND is logical, opposite of the %FOUND. The %NOTFOUND yields FALSE if the last fetch returned a row, or TRUE when the final fetch failed to return a row. I

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 Similar to a cursor, cursor variable points to the current row in the result set of a multi-row query. But, dissimilar a cursor, a cursor variable can be opene

%ROWCOUNT When its cursor or cursor variable is opened, the %ROWCOUNT is zeroed. Before the first fetch, the %ROWCOUNT yields 0. Afterward, it yields the number of rows fetche