Native Dynamic SQL :Method 1

Run Below two statements for the initial Set up :

SELECT * FROM employees;

CREATE TABLE employees_nds AS
(
SELECT * FROM employees
);

Run Below statement to check employee count for department 20 in this table:

SELECT * FROM employees_nds WHERE department_id=20;

Method 1

Method 1 Code.PNG

Code for Copy Paste :

DECLARE
—-This Block explain the dymanic SQL for method 1
—-The SQL statement must not be query (SELECT STATEMENT)
—-AND must not hold any place holder for input host variable
—-DELETE FROM employees_nds WHERE department_id = 20

nds_string VARCHAR2(2000);

BEGIN

nds_string := ‘DELETE FROM employees_nds WHERE department_id = 20’ ;

EXECUTE IMMEDIATE nds_string;

EXCEPTION WHEN OTHERS THEN

dbms_output.put_line (‘SQLCODE :’ || SQLCODE || chr(13) || ‘ SQLERRM :’ || SQLERRM);

END;

Advertisements
This entry was posted in Uncategorized. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s