[Solved] IF COL_LENGTH(‘EMP_NUM’,’EMPLOYEE’) IS NOT NULL – EQUIVALENT IN ORACLE [closed]

Question

In Oracle, you have to use dynamic SQL as you can’t execute DDL in PL/SQL as is. Why PL/SQL? Because IF-THEN-ELSE is PL/SQL, not SQL.

Presuming this is part of your PL/SQL procedure, you’d then

if col_length('EMP_NUM', 'EMPLOYEE') is not null then
  execute immediate 'alter table employee modify emp_num not null';
end if;

That’s for the ALTER part. However, as Oracle doesn’t have COL_LENGTH function (nor anything like that, as far as I can tell), you have to do it yourself. Here’s an example:

A table whose column allows nulls:

SQL> create table employee (emp_num number);

Table created.

Check the Null? column – empty, so yes – it allows nulls.

SQL> desc employee;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 EMP_NUM                                            NUMBER

Query data dictionary to find column’s length:

SQL> select data_length from user_tab_columns
  2  where table_name="EMPLOYEE"
  3    and column_name="EMP_NUM";

DATA_LENGTH
-----------
         22

Finally, your future PL/SQL procedure:

SQL> declare
  2    l_len number;
  3  begin
  4    select data_length
  5      into l_len
  6      from user_tab_columns
  7      where table_name="EMPLOYEE"
  8        and column_name="EMP_NUM";
  9
 10    if l_len is not null then
 11       execute immediate 'alter table employee modify emp_num not null';
 12    end if;
 13  end;
 14  /

PL/SQL procedure successfully completed.

SQL> desc employee;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 EMP_NUM                                   NOT NULL NUMBER

SQL>

As you can see, the Null? column now contains NOT NULL which means that ALTER TABLE was successfully executed.

Additionally, you can even create your own function to query column’s data length and then use it in your procedure:

SQL> create or replace function col_length(par_table in varchar2, par_column in varchar2)
  2    return number
  3  is
  4    retval user_tab_columns.data_length%type;
  5  begin
  6    select data_length
  7      into retval
  8      from user_tab_columns
  9      where table_name = par_table
 10        and column_name = par_column;
 11    return retval;
 12  end;
 13  /

Function created.

SQL> select col_length('EMPLOYEE', 'EMP_NUM') from dual;

COL_LENGTH('EMPLOYEE','EMP_NUM')
--------------------------------
                              22

SQL>

Finally:

SQL> drop table employee;

Table dropped.

SQL> create table employee (emp_num number);

Table created.

SQL> begin
  2    if col_length('EMPLOYEE', 'EMP_NUM') is not null then
  3       execute immediate 'alter table employee modify emp_num not null';
  4    end if;
  5  end;
  6  /

PL/SQL procedure successfully completed.

SQL> desc employee;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 EMP_NUM                                   NOT NULL NUMBER

SQL>

Accepted Answer