Dani's IT Blog

…moving from Oracle to Software Development

PL/SQL Basics for the DBA. Part IV (… or how to start having fun working as a DBA)

leave a comment »

Unbelievable but true. After talking about EXECUTE IMMEDIATE since 2 posts, I will finally show you how to use it. Basically EXECUTE IMMEDIATE takes a string as argument, interprets it and executes it as a statement, which is the same as what EVAL does in shell scripting.

It’s behavior can be tested with the following.

CONNECT t/t

SELECT table_name FROM user_tables;

DECLARE
  table_name VARCHAR2(50);
BEGIN
  table_name := 'exec_immediate_test_table';
  EXECUTE IMMEDIATE 'CREATE TABLE '||table_name||' (x NUMBER)';
END;
/

SELECT table_name FROM user_tables;

One thing you might consider a bit special is, you don’t need to use a semicolon or slash to terminate the statement. If we think about what this sings are used for, we realize, that they tell Oracle when a statement is finished and should be executed, then it makes sense. EXECUTE IMMEDIATE takes a string and executes it as a statement, so the end of the string is the end of the statement and no semicolon or slash is needed.

Now we can combine our knowledge and use it to drop all tables of a schema.

connect t/t;
SELECT table_name FROM user_tables;
SET SERVEROUTPUT ON

BEGIN
  FOR tables IN (SELECT 'DROP TABLE "'|| table_name ||'"' drop_statement FROM user_tables)
  LOOP
     --output to see what's going on
     dbms_output.put_line(tables.drop_statement);
     --execute the drop table statement
     EXECUTE IMMEDIATE tables.drop_statement;
  END LOOP;
END;
/

SELECT table_name FROM user_tables;

To test my code in your TEST database, please use the create schema code provided in the first part of this series.

Advertisements

Written by danirey

June 22, 2009 at 17:01

Posted in English, Oracle, PL/SQL Basics

Tagged with , ,

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

%d bloggers like this: