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
Example of ADD CONSTRAINT in SQL Example: Alternative formulation for MAX_ENROLMENTS ALTER TABLE IS_ENROLLED_ON ADD CONSTRAINT MAX_ENROLMENTS CHECK ((SELECT COUNT (*)

Advantages of Packages The benefits of the Packages are as shown below: Modularity The Packages encapsulate logically associated items, types, and subprograms in the

Logical Operators The logical operators AND, NOT, and OR follow the tri-state logic shown in table below. The AND and OR are binary operators; NOT is a unary operator.

Keyword and Parameter Description: table_reference: This specifies a table or view that should be accessible when you execute the DELETE statement, and for that you must

Table Comparison - SQL The following definitions for relation comparisons: Let r1 and r2 be relations having the same heading. Then: r1 ⊆ r2 is true if every tuple of r1


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

How Transactions Guard Your Database The transaction is a sequence of SQL data manipulation statements which does a logical unit of work. The Oracle treats the sequence of SQL

Parameter and Keyword Description: record_type_name: This identifies the user-defined type specifier that is used in the subsequent declarations of the records. NOT N

Table Represents an Extension - SQL It describes how each tuple in a relation represents a true instantiation of some predicate and each true instantiation is represented by s