Benefit of the dynamic sql pl sql, PL-SQL Programming

Benefit of the dynamic SQL:

This part shows you how to take full benefit of the dynamic SQL and how to keep away from some of the common pitfalls.

Passing the Names of Schema Objects:

Assume that you require a procedure which accepts the name of any database table, and then drop that table from your schema. By using the dynamic SQL, you may write the stand-alone procedure as shown below:

CREATE PROCEDURE drop_table (table_name IN VARCHAR2) AS

BEGIN

EXECUTE IMMEDIATE 'DROP TABLE :tab' USING table_name;

END;

Though, at run time, this procedure fails with an invalid table name error. That is as you cannot use the bind arguments to pass the names of the schema objects to a dynamic SQL statement. Rather, you should embed parameters in the dynamic string, and then pass the names of the schema objects to those parameters.

To debug the last illustration, you should revise the EXECUTE IMMEDIATE statement. Rather of using the placeholder and bind the argument, you can use the concatenation operator to embed the parameter table_name in the dynamic string, which is as shown below:

CREATE PROCEDURE drop_table (table_name IN VARCHAR2) AS

BEGIN

EXECUTE IMMEDIATE 'DROP TABLE ' || table_name;

END;

Posted Date: 10/6/2012 8:30:21 AM | Location : United States







Related Discussions:- Benefit of the dynamic sql pl sql, Assignment Help, Ask Question on Benefit of the dynamic sql pl sql, Get Answer, Expert's Help, Benefit of the dynamic sql pl sql Discussions

Write discussion on Benefit of the dynamic sql pl sql
Your posts are moderated
Related Questions
LAWS / RULES - Dollo's Law                     :           Living organisms do exhibit evolutionary irreversibility or evolution is irreversible. Williston's Law

Implicit Cursors The Oracle implicitly opens a cursor to process each SQL statement not related with an explicitly declared cursor. The PL/SQL lets you refer to the most recen

Selecting Objects: Suppose that you have run the SQL*Plus script below that creates object type Person and object table persons, and that you have settled the table: CREATE

Aggregate Assignment The %ROWTYPE declaration cannot include an initialization clause. Though, there are two ways to assign values to all fields in a record at once. At First, t

Keyword and Parameter Description: label_name: This is an undeclared identifier which labels an executable statement or the PL/SQL block. You can use a GOTO statement to

What is Cursors how to use it in Real time application ?

SQL outer join SELECT * FROM IS_CALLED NATURAL LEFT JOIN IS_ENROLLED_ON Note that adding LEFT to an invocation of CROSS JOIN has no effect unless the right-hand operand

Opening a Cursor Variable The OPEN-FOR statement relates a cursor variable with the multi-row query, executes the query, and then identifies the result set. The syntax for ope

Bitmap Join Indexes - This feature will increase the performance and detains the size and format of your databases in data Character Semantics and Globalization -This featur

When Are Constraints Checked Under the model constraints are conceptually checked at all statement boundaries (and only at statement boundaries). By default the same is true