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

Assignment Help:

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;


Related Discussions:- Benefit of the dynamic sql pl sql

Definition of cross join - sql, Definition of CROSS JOIN - SQL Let s ...

Definition of CROSS JOIN - SQL Let s = t1 CROSS JOIN t2, where t1 and t2 are table expressions optionally accompanied by range variables. Then: Note: Here T denotes Table

Using invoker rights in pl sql, Using Invoker Rights: By default, the ...

Using Invoker Rights: By default, the stored procedure executes with the privileges of its definer, not its invoker. These procedures are bound to the schema in which they inh

Parameter modes - pl sql, Parameter Modes: You do not require to speci...

Parameter Modes: You do not require to specify a parameter mode for the input bind arguments (those used, for illustration, in the WHERE clause) as the mode defaults to IN. Th

Constants and variables in pl sql, Constants and Variables:   You can...

Constants and Variables:   You can declare the constants and variables in the declarative section of any PL/SQL subprogram, block, or package. The Declarations allot the stor

Disjunction - sql, Disjunction (OR, ∨) Again we have nine rows instead...

Disjunction (OR, ∨) Again we have nine rows instead of just four and again, when unknown is not involved, the rows are as for 2VL. Also, when anything is paired with true, t

Maintain an audit trail of product table changes, The accuracy of product t...

The accuracy of product table data is critical and the Brwebean's. owner wants to have an audit file that contains information regarding all DML activity on the BB_PRODUCT table. T

Data abstraction, Data Abstraction The Data abstraction extracts the im...

Data Abstraction The Data abstraction extracts the important properties of data while ignoring the not necessary details. Once you design a data structure, you can fail to reme

Rephrase conditional control statements, Rephrase Conditional Control State...

Rephrase Conditional Control Statements When computing a logical expression, the PL/SQL uses short-circuit evaluation. That is, the PL/SQL stops evaluating the expression as s

%found - implicit cursor attributes, %FOUND Until the SQL data manipul...

%FOUND Until the SQL data manipulation statement is executed, the %FOUND yields NULL. Afterward, the %FOUND yields TRUE, when an INSERT, UPDATE, or DELETE statement affected o

Effect of anonymous columns, Effect of Anonymous Columns Now, recall t...

Effect of Anonymous Columns Now, recall that a VALUES expression denotes a table with undefined column names. If an initial value is to be specified when a base table is creat

Write Your Message!

Captcha
Free Assignment Quote

Assured A++ Grade

Get guaranteed satisfaction & time on delivery in every assignment order you paid with us! We ensure premium quality solution document along with free turntin report!

All rights reserved! Copyrights ©2019-2020 ExpertsMind IT Educational Pvt Ltd