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

Application to export excel data to mssql server table, Application to Expo...

Application to Export Excel Data to MSSQL Server table I am having a table available in excel format and features the subsequent: - Some text is in Arabic (e.g. UTF-8 encodin

Assignment of variable - updating a variable, Assignment of Variable - Upda...

Assignment of Variable - Updating a Variable Syntax: SET SN = SID ('S2'); This can obviously be read as "set the variable SN to be equal in value to SID ( 'S2' )".

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

Package - pl/sql programming, What Is a Package? The package is a sch...

What Is a Package? The package is a schema object that group logically related PL/SQL items, types, and subprograms. The Packages usually have 2 parts, a specification & a bo

Albeit simple method , Albeit simple method : These all the truth tabl...

Albeit simple method : These all the truth tables give us our first as albeit simple method for proving a theorem: where check whether it can be written in propositional logic

Miller-urey''s experimental procedure, MILLER-UREY' S EXPERIMENTAL PROCEDU...

MILLER-UREY' S EXPERIMENTAL PROCEDURES - They recreated the probable conditions on the primitive earth in the laboratory. An atmosphere containing hydrogen, ammonia, me

Commit statement in pl sql, COMMIT Statement The COMMIT statement expli...

COMMIT Statement The COMMIT statement explicitly makes everlasting changes to the database during the present transaction. The Changes made to the database are not considered e

Sql scripts, The SQL ‘CREATE TABLE' scripts for all the tables you have imp...

The SQL ‘CREATE TABLE' scripts for all the tables you have implemented. Note that your tables must correspond exactly to the ERD you have provided in 1. above, or you will lose ma

Second step at defining type sid in sql, Second Step at defining type SID i...

Second Step at defining type SID in SQL CREATE TYPE SID AS VARCHAR(5) ; Explanation: TYPE SID announces that a type named SID is being defined to the system.

I want to build website for funding, Project Description: I want to rebu...

Project Description: I want to rebuild it and add better content to it It will include up to 5 forms The data will be saved on SQL server and the data access layer could b

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