Please Visit Our New Website for Further Communication

Amazon

10 Apr 2013

EXECUTE IMMEDIATE and DBMS_SQL



DYNAMIC SQL 

Dynamic SQL is a SQL statement that contains variables that can change during runtime.Is written using either DBMS_SQL or native dynamic SQL (Execute Immediate).

EXECUTE IMMEDIATE: The Execute Immediate statement can perform dynamic single-row queries. Also, this is used for functionality such as objects and collections, which are not supported by DBMS_SQL. If the statement is a multi-row SELECT statement you use OPEN-FOR, FETCH and CLOSE statements.

DBMS_SQL: The DBMS_SQL is used to perform dynamically multi-row query. DBMS_SQL package is used to write dynamic SQL in stored procedures and to parse DDL statements. Some of the procedures and functions of the package include:
1)      OPEN_CURSOR: Opens a new cursor and assigns a cursor ID number.
2)      PARSE: Every SQL statement must be parsed. Parsing the statement includes checking the statements syntax and validating the statement , ensuring that all references to objects are correct and ensuring that the relevant privileges to those objects exist.
3)      BIND_VARIABLES: Binds the given value to the variable identified by its name in the parsed statement in the given cursor.
4)      EXECUTE: Executes the SQL statement and returns the number of row processed.
5)      FETCH_ROWS: Retrieves a row for the specified cursor (for multiple rows, call in a loop)
6)      CLOSE_CURSOR: Closes the specified cursor.
 
­­You can use the INTO clause for a single-row query, but you must use OPEN-FOR, FETCH and CLOSE for a multi row query.

Dynamic SQL supports all the SQL data types but does not supports PL/SQL specific types except PL/SQL record.

DBMS_JOB: Enables the scheduling and execution of PL/SQL programs.

No comments:

Post a Comment