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
Updating Objects: To change the attributes of objects in an object table, you can use the UPDATE statement, as the illustration below shows: BEGIN UPDATE persons p SET p

Explicit Cursors The set of rows returned by the query can include zero, one, or multiple rows, depending on how many rows meet your search criteria. Whenever a query returns

Question: (a) In the context of database security explain how the following database features help to enforce security in the database system: (i) Authorisation (ii) Access

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

Closest Approximation to Relational Union - SQL Actually, just as SQL has several varieties of JOIN, it also has several varieties of UNION, none of which is equivalent to th

1. Create a procedure called TAX_COST_SP to accomplish the tax calculation task. Keep in mind that the state and subtotal values are inputs into the procedure and the procedure is

THEORY OF SPONTANEOUS GENERATION - ABIOGENESIS OR AUTOGENESIS - According to this theory, the existing living communities have originated from non-living organic matter with

Package STANDARD The package named STANDARD defines the PL/SQL atmosphere. The package specification globally declares the exceptions, types, and subprograms that are available

Project Description: I want to write some SQL statements. The things I need are between pages 5-7. The only problem is that i want it till tomorrow. Skills required is SQL

We are seeking a freelance consultant that is familiar with Appgen applications. We require exporting all our data into a format appropriate for importing into SAP Business One. Pl