DBMS_UTILITY.EXEC_DDL_STATEMENT

I published a post recently about executing DDL from PL/SQL. You can also do this using dbms_utility.exec_ddl_statement as shown in the examples below, which I tested in Oracle 11: 

SQL> begin

  2    dbms_utility.exec_ddl_statement

  3    ('create table tab1(col1 number)');

  4  end;

  5  /

 

PL/SQL procedure successfully completed.

 

SQL> declare

  2    sql_statement varchar2(50);

  3  begin

  4    sql_statement := 'alter table tab1 add(col2 number)';

  5    dbms_utility.exec_ddl_statement(sql_statement);

  6  end;

  7  /

 

PL/SQL procedure successfully completed.

 

SQL> desc tab1

Name                       Null?    Type

-------------------------- -------- ------------------

COL1                                NUMBER

COL2                                NUMBER

 

SQL> 

It even seems to allow you to run DDL in a remote database: 

SQL> begin

  2    dbms_utility.exec_ddl_statement@remote_db

  3    ('create table tab1(col1 number)');

  4  end;

  5  /

 

PL/SQL procedure successfully completed.

 

SQL> desc tab1@remote_db

Name                       Null?    Type

-------------------------- -------- ------------------

COL1                                NUMBER

 

SQL>

ORA-01754

The first example below was tested on Oracle 11.2. If you try to have more than one long column in a table, you get an ORA-01754:

SQL> create table tab1 (col1 long, col2 long)

  2  /

create table tab1 (col1 long, col2 long)

                              *

ERROR at line 1:

ORA-01754: a table may contain only one column of type LONG

 

SQL> 

That was fairly obvious. Now here is another example, which I wrote at the start of 2012. I do not know which Oracle version I did it on. First I created a table with one column:

SQL> create table andrews_table

  2  (col1 varchar2(10))

  3  /

Table created.

SQL>

Then I added a long column to the table and described it to show the column I had just added:

SQL> alter table andrews_table

  2  add (col2 long)

  3  /

Table altered.

SQL> desc andrews_table

Name                      Null?    Type

-------------------------- -------- ------------------

COL1                                VARCHAR2(10)

COL2                                LONG

SQL>

Next I set the long column to unused. This does not restore the disk space used by the column:

SQL> alter table andrews_table
  2  set unused column col2
  3  /

Table altered.

SQL>

I described the table again to show that the long column had gone:

SQL> desc andrews_table
Name                      Null?    Type
-------------------------- -------- ------------------
COL1                                VARCHAR2(10)

SQL>

Then I tried to add another long column. This failed as you can only have 1 long column per table. How would you diagnose this problem if it happened to you in real life?

SQL> alter table andrews_table
  2  add (col3 long)
  3  /
add (col3 long)
    *
ERROR at line 2:
ORA-01754: a table may contain only one column of type
LONG

SQL>

You can get a clue by looking in user_unused_col_tabs, which confirms that the table has 1 unused column:

SQL> select count(*) from user_unused_col_tabs

  2  where table_name = 'ANDREWS_TABLE'

  3  /

  COUNT(*)

----------

         1

SQL>

Drop the unused column from the table. This frees up the disk space which the column occupied:

SQL> alter table andrews_table
  2  drop unused columns
  3  /

Table altered.

SQL>

The table no longer has an entry in user_unused_col_tabs:

SQL> select count(*) from user_unused_col_tabs
  2  where table_name = 'ANDREWS_TABLE'
  3  /

no rows selected

SQL>

And you can add another long column if you wish:

SQL> alter table andrews_table
  2  add (col3 long)
  3  /

Table altered.

SQL>

ORA-00030

This was tested on Oracle 11.2. When you run ALTER SYSTEM KILL SESSION ‘X,Y’; X must be a SID from V$SESSION and Y must be the SERIAL# from the same row. If not, you will get an ORA-00030: 

SQL> select count(*) from v$session

  2  where sid = 123

  3  and serial# = 321

  4  /

 

  COUNT(*)

----------

         0

 

SQL> alter system kill session '123,321'

  2  /

alter system kill session '123,321'

*

ERROR at line 1:

ORA-00030: User session ID does not exist.

 

SQL>

Hard Coded Variables in PL/SQL

Many applications need to use the same value in several different places. The examples below show a net value and a tax figure being added to create a gross amount. The tax figure is calculated by multiplying the net value by the tax rate. In the first example, the tax rate is hard-coded as 0.2. This is quite simple to implement but, if the tax rate changes, you will need to go through your whole application and amend every individual value: 

SQL> set serveroutput on

SQL> declare

  2    net number;

  3    tax number;

  4    gross number;

  5  begin

  6    net := 1;

  7    tax := 0.2;

  8    gross := net + (net * tax);

  9    dbms_output.put_line('Gross = '||to_char(gross,'$9.99'));

 10  end;

 11  /

Gross =  $1.20

 

PL/SQL procedure successfully completed.

SQL> 

The second example stores the tax rate as a constant in a package. When you need to know the tax rate, you fetch it from the package as shown below. This is slightly harder to implement but, if the tax rate changes, you only need to amend the value in one place: 

SQL> create or replace package special_values1

  2  is

  3    tax constant number := 0.2;

  4  end special_values1;

  5  /

 

Package created.

 

SQL> declare

  2    net number;

  3    tax number;

  4    gross number;

  5  begin

  6    net := 1;

  7    tax := special_values1.tax;

  8    gross := net + (net * tax);

  9    dbms_output.put_line('Gross = '||to_char(gross,'$9.99'));

 10  end;

 11  /

Gross =  $1.20

 

PL/SQL procedure successfully completed.

SQL>

The third example is similar to the second but, this time, the tax rate is returned by a function:

 

SQL> create or replace package special_values2

  2  is

  3    function tax

  4    return number;

  5  end special_values2;

  6  /

 

Package created.

 

SQL> create or replace package body special_values2

  2  is

  3    function tax

  4    return number

  5  is

  6  begin

  7    return 0.2;

  8  end;

  9  end special_values2;

 10  /

 

Package body created.

 

SQL> declare

  2    net number;

  3    tax number;

  4    gross number;

  5  begin

  6    net := 1;

  7    tax := special_values2.tax();

  8    gross := net + (net * tax);

  9    dbms_output.put_line('Gross = '||to_char(gross,'$9.99'));

 10  end;

 11  /

Gross =  $1.20

 

PL/SQL procedure successfully completed.

 

SQL>

The simplest method of all is possibly to store the tax rate as a value in a table. I won't insult your intelligence by showing an example using this method!

PL/SQL Example Using SUBTYPE and VALUE_ERROR

This example was tested on Oracle 11.2. First it creates a single-digit variable called SMALL_NUMBER. It then uses this to define a subtype called SINGLE_DIGIT, which has the same type as SMALL_NUMBER. The SINGLE_DIGIT subtype is then used to create another single-digit variable called MINUS_NINE_THROUGH_PLUS_NINE. Negative and positive single-digit values are successfully assigned to this variable but an attempt to assign a double-digit value fails. This is picked up and handled by VALUE_ERROR in the exception section of the PL/SQL block:

 

SQL> SET SERVEROUTPUT ON

SQL> DECLARE

  2   SMALL_NUMBER NUMBER(1,0);

  3   SUBTYPE SINGLE_DIGIT IS SMALL_NUMBER%TYPE;

  4   MINUS_NINE_THROUGH_PLUS_NINE SINGLE_DIGIT;

  5  BEGIN

  6   MINUS_NINE_THROUGH_PLUS_NINE := -9;

  7   DBMS_OUTPUT.PUT_LINE(MINUS_NINE_THROUGH_PLUS_NINE);

  8   MINUS_NINE_THROUGH_PLUS_NINE := 0;

  9   DBMS_OUTPUT.PUT_LINE(MINUS_NINE_THROUGH_PLUS_NINE);

 10   MINUS_NINE_THROUGH_PLUS_NINE := 9;

 11   DBMS_OUTPUT.PUT_LINE(MINUS_NINE_THROUGH_PLUS_NINE);

 12   MINUS_NINE_THROUGH_PLUS_NINE := 10;

 13   DBMS_OUTPUT.PUT_LINE(MINUS_NINE_THROUGH_PLUS_NINE);

 14  EXCEPTION

 15   WHEN VALUE_ERROR THEN

 16   DBMS_OUTPUT.PUT_LINE('Value out of range');

 17  END;

 18  /

-9

0

9

Value out of range

 

PL/SQL procedure successfully completed.

 

SQL>

ORA-00069

A colleague asked what the table_lock column in dba_tables was for. I did not know so I decided to investigate.First I looked at the distribution of table_lock values in one of our test databases:

SQL> select table_lock, count(*)
  2  from dba_tables
  3  group by table_lock
  4  /

TABLE_LOCK  COUNT(*)
---------- ----------
ENABLED          825

SQL>
 

This suggested to me that the default setting is enabled so I tried this out by creating a test table:

SQL> create table andrew (col1 number)
  2  /

Table created.

SQL> 

As expected, the table_lock column was set to enabled for this new table:

SQL> select table_lock from dba_tables
  2  where table_name = 'ANDREW'
  3  /

TABLE_LOCK
----------
ENABLED

SQL> 

You can alter this as follows:

SQL> alter table andrew disable table lock
  2  /

Table altered.

SQL>

And this sets the table_lock value to disabled:

SQL> select table_lock from dba_tables
  2  where table_name = 'ANDREW'
  3  /

TABLE_LOCK
----------
DISABLED

SQL> 

Setting table_lock to disabled stops you locking that table:

SQL> lock table andrew in share mode
  2  /
lock table andrew in share mode
*
ERROR at line 1:
ORA-00069: cannot acquire lock -- table locks
disabled for ANDREW

SQL> 

It also stops you running other DDL against it:

SQL> rename andrew to fred
  2  /
rename andrew to fred
*
ERROR at line 1:
ORA-00069: cannot acquire lock -- table locks
disabled for ANDREW

SQL>

And setting it back to enabled allows you to lock the table:

SQL> alter table andrew enable table lock
  2  /

Table altered.

SQL>

And run other DDL on the table:

SQL> rename andrew to fred
  2  /

Table renamed.

SQL>

So the main purpose of the table_lock column is to show whether you are allowed to lock a table or not.

How to Allow Updates and Inserts on Individual Columns

This was tested on Oracle 11.2. First I created a couple of users:

 

SQL> create user andrew identified by reid

  2  default tablespace users

  3  quota unlimited on users

  4  /

 

User created.

 

SQL> grant create session, create table to andrew

  2  /

 

Grant succeeded.

 

SQL> grant create session to fred

  2  identified by bloggs

  3  /

 

Grant succeeded.

 

SQL>

 

User Andrew created a table with two columns. Note that col1 could be null but col2 could not. You will see why later:

 

SQL> conn andrew/reid

Connected.

SQL> create table tab1

  2  (col1 number,

  3   col2 number not null)

  4  /

 

Table created.

 

SQL> insert into tab1 values (1,2)

  2  /

 

1 row created.

 

SQL>

 

Andrew then allowed Fred to update col1:

 

SQL> grant select, update (col1) on tab1 to fred

  2  /

 

Grant succeeded.

 

SQL>

 

Fred then logged in and updated col1 but, when he tried to update col2, Oracle returned an

ORA-01031:

 

SQL> conn fred/bloggs

Connected.

SQL> update andrew.tab1 set col1 = 3

  2  /

 

1 row updated.

 

SQL> update andrew.tab1 set col2 = 4

  2  /

update andrew.tab1 set col2 = 4

              *

ERROR at line 1:

ORA-01031: insufficient privileges

 

SQL> select * from andrew.tab1

  2  /

 

      COL1       COL2

---------- ----------

         3          2

 

SQL>

 

Andrew then allowed Fred to insert values into col1:

 

SQL> conn andrew/reid

Connected.

SQL> grant insert (col1) on tab1 to fred

  2  /

 

Grant succeeded.

 

SQL>

 

Fred tried to insert a value into col1 but Oracle returned an ORA-01400 because col2 could not be null. If you grant insert access to selected columns in this way, you must grant it to all the not null columns in the table:

 

SQL> conn fred/bloggs

Connected.

SQL> insert into andrew.tab1 (col1) values (5)

  2  /

insert into andrew.tab1 (col1) values (5)

*

ERROR at line 1:

ORA-01400: cannot insert NULL into

("ANDREW"."TAB1"."COL2")

 

SQL> select * from andrew.tab1

  2  /

 

      COL1       COL2

---------- ----------

         3          2

 

SQL>

 

Andrew then granted insert access to col2 instead. Note how the first revoke failed as you cannot revoke a privilege from a single column:

 

SQL> conn andrew/reid

Connected.

SQL> revoke insert (col1) on tab1 from fred

  2  /

revoke insert (col1) on tab1 from fred

              *

ERROR at line 1:

ORA-01750: UPDATE/REFERENCES may only be REVOKEd from

the whole table, not by column

 

SQL> revoke insert on tab1 from fred

  2  /

 

Revoke succeeded.

 

SQL> grant insert (col2) on tab1 to fred

  2  /

 

Grant succeeded.

 

SQL>

 

As col2 was the only not null column in the table, Fred was then able to insert a row successfully:

 

SQL> conn fred/bloggs

Connected.

SQL> insert into andrew.tab1 (col2) values (6)

  2  /

 

1 row created.

 

SQL> select * from andrew.tab1

  2  /

 

      COL1       COL2

---------- ----------

         3          2

                    6

 

SQL>

ORA-01792

I tested this example in Oracle 11.2. First I created a table with 1 column called COL1: 

SQL> create table tab1 (col1 number)

  2  /

 

Table created.

 

SQL>

 

Then I used a PL/SQL loop and EXECUTE IMMEDIATE to add COL2 through COL1000:

 

SQL> declare

  2    sql_statement varchar2(100);

  3  begin

  4  for i in 2..1000 loop

  5    sql_statement :=

  6    'alter table tab1 add (col'||i||' number)';

  7    execute immediate sql_statement;

  8  end loop;

  9  end;

 10  /

 

PL/SQL procedure successfully completed.

 

SQL>

 

I checked that TAB1 had 1000 columns:

 

SQL> select count(*) from user_tab_cols

  2  where table_name = 'TAB1'

  3  /

 

  COUNT(*)

----------

      1000

 

SQL>

 

Finally, I tried to add an extra column. This failed with an ORA-01792, proving that you cannot have more than 1000 columns in a table:

 

SQL> alter table tab1 add (col1001 number)

  2  /

alter table tab1 add (col1001 number)

                      *

ERROR at line 1:

ORA-01792: maximum number of columns in a table or

view is 1000

 

SQL>