Breaking News: Grepper is joining You.com. Read the official announcement!

pl sql dynamic sql drop doesnt work

JBTheOneAndOnly answered on May 16, 2022 Popularity 1/10 Helpfulness 8/10

answer pl sql dynamic sql drop doesnt work

pl sql dynamic sql drop doesnt work

Comment

/*Making Procedures Work on Arbitrarily Named Schema Objects

Suppose you need a procedure that accepts the name of any database table, then drops that table from your schema. Using dynamic SQL, you might write the following standalone procedure:
This doesn't work: */
CREATE PROCEDURE drop_table (table_name IN VARCHAR2) AS
BEGIN
   EXECUTE IMMEDIATE 'DROP TABLE :tab' USING table_name;
END;
/*
However, at run time, this procedure fails with an invalid table name error. That is because you cannot use bind arguments to pass the names of schema objects to a dynamic SQL statement. Instead, you must embed parameters in the dynamic string, then pass the names of schema objects to those parameters.

To debug the last example, you must revise the EXECUTE IMMEDIATE statement. Instead of using a placeholder and bind argument, you embed parameter table_name in the dynamic string, as follows:
*/
CREATE PROCEDURE drop_table (table_name IN VARCHAR2) AS
BEGIN
   EXECUTE IMMEDIATE 'DROP TABLE ' || table_name;
END;
/*
Now, you can pass the name of any database table to the dynamic SQL statement.
*/

Popularity 1/10 Helpfulness 8/10 Language sql

Source: Grepper

Tags: sql

Link to this answer
Share Copy Link

Contributed on May 16 2022

JBTheOneAndOnly

0 Answers Avg Quality 2/10

pl sql dynamic sql drop doesnt work

Contents

More Related Answers

pl sql dynamic sql drop doesnt work

Grepper

Documentation

Social

Legal

Contact

Oops, You will need to install Grepper and log-in to perform this action.