Recreate sequences with new starting numbers

The following script is useful in certain deployment scenarios.

For example, where the sequence names are created by CREATE SEQUENCE statements and the tables and columns, which the sequences are associated with have been imported with data.

A further scenario might be, data was loaded and the existing sequence, for whatever reason, was not used in the load process. The sequence would have to be dropped and recreated with a starting number that is in synch with the data loaded for the application to behave as per prior to the load.

In both of the above scenarios it might be useful to obtain the maximum number either from

1)the table column associated with the sequence
2)known gap between loaded data and last sequence number


set verify off
set feedback off
set head off

declare
  SEQ_NOT_EXIST   exception;
  TBL_NOT_EXIST   exception;
  pragma          exception_init(SEQ_NOT_EXIST, -2289);
  pragma          exception_init(TBL_NOT_EXIST, -942);
  l_Sequence_Name varchar2(30) := 'SQ_T1';
  l_Tbl_Name      varchar2(30) := 'T1';

begin

  begin

    execute immediate 'drop sequence ' || l_Sequence_Name;
    -- ignore if sequence does not exist, but 
    -- raise exception otherwise
    exception
      when SEQ_NOT_EXIST then
        null;
  end;

  begin
    execute immediate 'drop table ' || l_Tbl_Name;
    -- ignore if table does not exist, but 
    -- raise exception otherwise
    exception
      when TBL_NOT_EXIST then
        null;
  end;

end;
/

-- Create a table and sequence to test the script with
create table t1 (a number);
create sequence sq_t1;

set verify on
set feedback on
set head on

-- Insert some data using the sequence
insert into t1 values (sq_t1.nextval);
commit;

select * from t1;

set verify off
set feedback off
set head off

Drop and recreate sequence with starting number one more than the current value of the table/column that is associated with the sequence.

set serveroutput on
declare
  l_Sequence_Name varchar2(30) := 'SQ_T1';
  l_Tbl_Name      varchar2(30) := 'T1';
  l_Col_Name      varchar2(30) := 'A';
  l_Cnt           number;
  SEQ_NOT_EXIST   exception;
  pragma          exception_init(SEQ_NOT_EXIST, -2289);
begin

  execute immediate 'begin
                      select  max(' || l_Col_Name || ')'
                   || ' into :1 from '
                   || l_Tbl_Name || '; end;' using out l_Cnt;

  begin
    execute immediate 'drop sequence ' || l_Sequence_Name;
    exception
      when SEQ_NOT_EXIST then
        null; -- ignore if sequence does not exist, but raise exception in other cases
  end;

  begin

    execute immediate 'create sequence ' || l_Sequence_Name
                                         || ' start with '
                                         || to_char(l_Cnt + 1);

    dbms_output.put_line('Sequence ' || l_Sequence_Name
                                     || ' sucessfully recreated');
  end;
end;
/

set verify on
set feedback on
set head on

insert into t1 values (sq_t1.nextval);
commit;

select * from t1;

An alternative scenario would be to use the LAST_NUMBER column in the USER_SYNONYMS dictionary view and the if the gap(i.e. difference between max in data and LAST_NUMBER) is a known quantity, then simply specify as a parameter.

This might be useful for sequences which are not necessarily associated with a table column.
For example:

undefine GAP
set serveroutput on
declare
  l_Sequence_Name varchar2(30) := 'SQ_T1';
  l_Gap           number := to_number('&&GAP');
  l_Cnt           number;
  SEQ_NOT_EXIST   exception;
  pragma          exception_init(SEQ_NOT_EXIST, -2289);
begin

  begin
    select  last_number+l_Gap
    into    l_Cnt
    from    user_sequences
    where   sequence_name = l_Sequence_name;
  end;

  begin
    execute immediate 'drop sequence ' || l_Sequence_Name;

    -- ignore if sequence does not exist, but 
    -- raise exception in other cases    
    exception
      when SEQ_NOT_EXIST then
        null;
  end;

  execute immediate 'create sequence ' || l_Sequence_Name
                                       || ' start with '
                                       || to_char(l_Cnt);

  dbms_output.put_line('Sequence ' || l_Sequence_Name
                                   || ' sucessfully recreated');
end;
/

Describing a ref cursor using the DBMS_SQL API

The following example illustrates how to describe the columns and values of a reference cursor, created from a dynamic DML statement using the DBMS_SQL API.

Firstly, create a test table with some sample data;

create table Trade_Deal

(Deal_ID      number

,Deal_Type    varchar2(10)

,Deal_Version integer

,Created      timestamp

,Trade_Risk   integer

,GBP_Amnt     number

,USD_Amnt     number);

insert all

  into Trade_Deal values(1,'SPOT',   1,current_timestamp,1,10,null)

  into Trade_Deal values(2,'FORWARD',1,current_timestamp,1,12,null)

  into Trade_Deal values(3,'DEPOSIT',1,current_timestamp,1,11,null)

  into Trade_Deal values(4,'SPOT',   1,current_timestamp,1,22,null)

select 1

from dual;

commit;

Next, create a procedure to test the conversion of a result set

to a reference cursor;

create or replace

procedure Get_Deals(p_Deal_Type in varchar2

                   ,p_Ref      out sys_refcursor) as

   v_SQL     clob;

   v_Cursor  binary_integer := dbms_sql.open_cursor;

   v_Ref     sys_refcursor;

   v_Exec    binary_integer;

begin

  -- Note,there is no reason to execute the following SELECT as a

  -- dynamic statement, the reason for doing so is simply

  -- to illustrate that a reference cursor can be pointed to a

  -- result set using the DBMS_SQL API.

   v_SQL := 'select  td.Deal_ID

                    ,td.Deal_Type

                    ,td.Created

                    ,td.Trade_Risk

                    ,td.GBP_Amnt

              from   Trade_Deal td

              where  td.Deal_Type = :DT';

   dbms_sql.parse(v_Cursor, v_SQL, dbms_sql.native);

   dbms_sql.bind_variable(v_Cursor, 'DT', p_Deal_Type);

   v_Exec := dbms_sql.execute(v_Cursor);

   -- Convert to a REF cursor after execution

   v_Ref := dbms_sql.to_refcursor(v_Cursor);

   p_Ref := v_Ref;

end;

/

Now create a procedure, which describes the cursor which

was converted in the previous procedure.

create or replace

procedure Show_Ref_Cursor (p_Ref in sys_refcursor) as

   v_Ref              sys_refcursor := p_Ref;

   -- Do not use the "dbms_sql.desc_tab" type as it is deprecated

   v_Ref_Desc         dbms_sql.desc_tab2;

   v_Ref_Cols         binary_integer;

   v_Cursor           binary_integer;

begin

  -- Convert cursor, passed as a parameter, to a DBMS_SQL cursor

  v_Cursor := dbms_sql.to_cursor_number(v_Ref);

  -- Get a description of the cursor

  dbms_sql.describe_columns2(v_Cursor, v_Ref_Cols, v_Ref_Desc);

  dbms_sql.close_cursor(v_Cursor);

  /*

  Show a description of columns within cursor.

  The column type integer, for all columns that are

  of type SQL NCHAR types (NCHAR, NVARCHAR2, NCLOB),

  is the same as for (CHAR, VARCHAR2, CLOB).

  In such cases refer to the ".COL_CHARSETID" attribute.

  */

  for i in 1 .. v_Ref_Cols loop

    dbms_output.put('Column ' || i || ': '

    || rpad(v_Ref_Desc(i).col_name,10)

    || '; Type: ' ||  case v_Ref_Desc(i).col_type

                        when 1 then

                          'VARCHAR2'

                        when 2 then

                          'NUMBER'

                        when 8 then

                          'LONG'

                        when 11 then

                          'ROWID'

                        when 12 then

                          'DATE'

                        when 23 then

                          'RAW'

                        when 96 then

                          'CHAR'

                        when 100 then

                          'BINARY_FLOAT'

                        when 101 then

                          'BINARY_DOUBLE'

                        when 112 then

                          'CLOB'

                        when 113 then

                          'BLOB'

                        when 114 then

                          'BFILE'

                        when 180 then

                          'TIMESTAMP'

                        when 181 then

                          'TIMESTAMP WITH TIME ZONE'

                        when 182 then

                          'INTERVAL YEAR TO MONTH'

                        when 231 then

                          'TIMESTAMP WITH LOCAL TIME ZONE'

                        else

                          'UNDEFINED'

                      end);

      dbms_output.new_line;

   end loop;

end;

/

Now for the test;

SQL> variable test_Ref refcursor;

SQL> set serveroutput on

SQL> begin

  2    Get_Deals('SPOT', :Test_Ref);

  3  end;

  4  /

PL/SQL procedure successfully completed.

A useful query you may want to often use when dealing with

dynamic SQL (especially long running SQL) is to view

the bind variables and their respective values.

This is an example query to extract the bind variable

submitted in procedure GET_DEALS, which was executed

in the previous step.

col SQL_Text        format a50

col Bin_Var_Name    format a20

col Value_String    format a20

col Datatype_String format a20

SQL> select substr(SQL_Text,1,50) SQL_Text

  2        ,Bind_Var_Name

  3        ,Value_String

  4        ,Datatype_String

  5  from

  6    (

  7      select sq.sql_text          as SQL_Text

  8            ,sbc.name             as Bind_Var_name

  9            ,sbc.Value_String     as Value_String

 10            ,sbc.datatype_string  as Datatype_String

 11      from

 12            v$sql_bind_capture sbc

 13      inner join v$sql sq on sq.sql_id = sbc.sql_id

 14      -- modify predicate to obtain the SQL you looking for

 15      where sq.sql_text like 'select%'

 16      and   sq.sql_text like '%Trade_Deal%'

 17      order by sbc.last_captured desc

 18    )

 19  where rownum = 1

 20  /

SQL_TEXT      BIND_VAR_NAME VALUE_STRING DATATYPE_STRING

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

select  td.nc :DT           SPOT         VARCHAR2(32)

Verify the Reference cursor is populated;

SQL> print :Test_Ref;

DEAL_ID DEAL_TYPE CREATED                   TRADE_RISK GBP_AMNT

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

      1 SPOT      16-NOV-11 11.04.57.957000          1       10

      4 SPOT      16-NOV-11 11.04.57.957000          1       22

Rerun the procedure to open the reference cursor, since

the "print :Test_Ref" closes the cursor.

variable test_Ref refcursor;

set serveroutput on

begin

  Get_Deals('SPOT', :Test_Ref);

end;

/

Finally, Describe the cursor using the DBMS_SQL API

SQL> exec Show_Ref_Cursor(:Test_Ref);

Column 1: DEAL_ID   ; Type: NUMBER

Column 2: DEAL_TYPE ; Type: VARCHAR2

Column 3: CREATED   ; Type: TIMESTAMP

Column 4: TRADE_RISK; Type: NUMBER

Column 5: GBP_AMNT  ; Type: NUMBER

PL/SQL procedure successfully completed.

Note, a bug exists in 11g Release 1 and 2 whereby the DBMS_SQL.DESCRIBE does not output the correct precision for a NUMBER column that was added onto a table using the ALTER statement with a DEFAULT value.

The bug reference on Metalink is;
Bug 9040420: PLEASE RE-REVIEW BUG 7489902

The bug has been fixed in 11.2.0.2

As a workaround for earlier versions of 11g, use the following;

alter system set "_add_col_optim_enabled"=FALSE;

--------------------------------------------------------------
For columns that are User defined (NESTED TABLES, TYPES), use
the DBMS_SQL.DESC_TAB3 along with DBMS_SQL.DESCRIBE_COLUMNS3,
data type and procedure, instead of DBMS_SQL.DESC_TAB2
and DBMS_SQL.DESCRIBE_COLUMNS2.

DBMS_SQL.DESC_TAB3 along with DBMS_SQL.DESCRIBE_COLUMNS3 hold
two additional attributes COL_TYPE_NAME and COL_TYPE_NAME_LEN
to describe user defined types.

The full list of attributes in DESC_TAB3 is;
type desc_rec3 is record
(
  col_type            binary_integer  := 0,
  col_max_len         binary_integer  := 0,
  col_name            varchar2(32767) := '',
  col_name_len        binary_integer  := 0,
  col_schema_name     varchar2(32)    := '',
  col_schema_name_len binary_integer  := 0,
  col_precision       binary_integer  := 0,
  col_scale           binary_integer  := 0,
  col_charsetid       binary_integer  := 0,
  col_charsetform     binary_integer  := 0,
  col_null_ok         boolean         := true,
  col_type_name       varchar2(32767) := '',
  col_type_name_len   binary_integer  := 0
);

The example that follows describes a user defined type;

Create a sample user defined type first;

create or replace type ty_Deal_Type as object
(Deal_Type varchar2(10)
,Business_Unit  varchar2(10)
,Location varchar2(10)
);
/

create or replace type tb_Deal_Type as table of ty_Deal_Type;
/

Add a column, tb_DT, as a user defined type "TB_DEAL_TYPE"
in the Trade_Deal table;

SQL> alter table trade_deal add tb_DT tb_Deal_Type
  2  nested table tb_DT store as tb_Deal_Type_Col;
Table altered.

SQL> desc trade_deal;
 Name                          Null?    Type
 ----------------------------- -------- ------------
 DEAL_ID                                NUMBER
 DEAL_TYPE                              VARCHAR2(10)
 DEAL_VERSION                           NUMBER(38)
 CREATED                                TIMESTAMP(6)
 TRADE_RISK                             NUMBER(38)
 GBP_AMNT                               NUMBER
 USD_AMNT                               NUMBER
 TB_DT                                  TB_DEAL_TYPE

Modify the GET_DEALS procedure to include the tb_DT in the output;

create or replace
procedure Get_Deals(p_Deal_Type in varchar2
                   ,p_Ref      out sys_refcursor) as

   v_SQL     clob;
   v_Cursor  binary_integer := dbms_sql.open_cursor;
   v_Ref     sys_refcursor;
   v_Exec    binary_integer;

begin

   v_SQL := 'select  td.Deal_ID
                    ,td.Deal_Type
                    ,td.Created
                    ,td.Trade_Risk
                    ,td.GBP_Amnt
                    ,td.tb_DT
              from
                    Trade_Deal td
              where
                    td.Deal_Type = :DT';

   dbms_sql.parse(v_Cursor, v_SQL, dbms_sql.native);

   dbms_sql.bind_variable(v_Cursor, 'DT', p_Deal_Type);

   v_Exec := dbms_sql.execute(v_Cursor);

   -- Convert to a REF cursor after execution
   v_Ref := dbms_sql.to_refcursor(v_Cursor);

   p_Ref := v_Ref;

end Get_Deals;
/

Next, modify the SHOW_REF_CURSOR procedure to accommodate
for user defined types;

create or replace
procedure Show_Ref_Cursor (p_Ref in sys_refcursor) as

   v_Ref              sys_refcursor := p_Ref;
   -- Do not use the "dbms_sql.desc_tab" type as it is deprecated
   v_Ref_Desc         dbms_sql.desc_tab3;
   v_Ref_Cols         binary_integer;
   v_Cursor           binary_integer;

begin

  -- Convert cursor, passed as a parameter, to a DBMS_SQL cursor
  v_Cursor := dbms_sql.to_cursor_number(v_Ref);

  -- Get a description of the cursor
  dbms_sql.describe_columns3(v_Cursor, v_Ref_Cols, v_Ref_Desc);
  dbms_sql.close_cursor(v_Cursor);

  for i in 1 .. v_Ref_Cols loop
    dbms_output.put('Column ' || i || ': '
    ||rpad(v_Ref_Desc(i).col_name,10)
    ||'; Type: '||case v_Ref_Desc(i).col_type
                    when 1 then
                      'VARCHAR2'
                    when 2 then
                      'NUMBER'
                    when 8 then
                      'LONG'
                    when 11 then
                      'ROWID'
                    when 12 then
                      'DATE'
                    when 23 then
                      'RAW'
                    when 96 then
                      'CHAR'
                    when 100 then
                      'BINARY_FLOAT'
                    when 101 then
                      'BINARY_DOUBLE'
                    when 112 then
                      'CLOB'
                    when 113 then
                      'BLOB'
                    when 114 then
                      'BFILE'
                    when 180 then
                      'TIMESTAMP'
                    when 181 then
                      'TIMESTAMP WITH TIME ZONE'
                    when 182 then
                      'INTERVAL YEAR TO MONTH'
                    when 231 then
                      'TIMESTAMP WITH LOCAL TIME ZONE'
                    when 109 then
                    -- the "col_type_name" attribute is only
                    -- available when using the DESC_TAB3 type
                      'USER DEFINED: '||v_Ref_Desc(i).col_type_name
                  end);
      dbms_output.new_line;
   end loop;

end Show_Ref_Cursor;
/

Run the test again to view output for the user defined type;
variable Test_Ref refcursor;


set serveroutput on
variable Test_ref refcursor;
begin
  Get_Deals('SPOT', :Test_Ref);
end;
/

SQL> exec Show_Ref_Cursor(:Test_Ref);
Column 1: DEAL_ID   ; Type: NUMBER
Column 2: DEAL_TYPE ; Type: VARCHAR2
Column 3: CREATED   ; Type: TIMESTAMP
Column 4: TRADE_RISK; Type: NUMBER
Column 5: GBP_AMNT  ; Type: NUMBER
Column 6: TB_DT     ; Type: USER DEFINED: TB_DEAL_TYPE

Converting a result to a ref cursor using DBM_SQL

The following example illustrates how to convert a result
set, created from a dynamic DML statement, to a reference cursor.

In 11g Release 2, the converted reference cursor can then be passed, as a parameter, onto a Java class or any PL/SQL procedure for further processing. In 11g Release 1, the reference cursor could only be passed as a parameter to PL/SQL procedures.

Firstly, create a test table with some sample data;

create table Trade_Deal
(Deal_ID      number          not null
,Deal_Type    varchar2(10)
,Deal_Version integer
,Created      timestamp default current_timestamp
,Trade_Risk   integer check (Trade_Risk in (1,2,3,4,5,6,7,8,9))
,GBP_Amnt     number          not null
,USD_Amnt     number);

insert all
  into Trade_Deal values(1,'SPOT',   1,current_timestamp,1,10,null)
  into Trade_Deal values(2,'FORWARD',1,current_timestamp,1,12,null)
  into Trade_Deal values(3,'DEPOSIT',1,current_timestamp,1,11,null)
  into Trade_Deal values(4,'SPOT',   1,current_timestamp,1,22,null)
select 1
from dual
/

Next, create a procedure to test with;
create or replace
procedure Get_Deals(p_Deal_Type in varchar2
                   ,p_Ref      out sys_refcursor) as

   v_SQL     clob;
   v_Cursor  binary_integer := dbms_sql.open_cursor;
   v_Ref     sys_refcursor;
   v_Exec    binary_integer;

begin

  -- Note, there is no reason to execute the following SELECT as a
  -- dynamic statement, the reason for doing so is simply
  -- to illustrate that a reference cursor can be pointed to a
  -- result set using the DBMS_SQL API.

   v_SQL := 'select td.Deal_ID
                   ,td.Deal_Type
                   ,td.Created
                   ,td.Trade_Risk
                   ,td.GBP_Amnt
              from
                    Trade_Deal td
              where
                    td.Deal_Type = :DT';

   dbms_sql.parse(v_Cursor, v_SQL, dbms_sql.native);

   dbms_sql.bind_variable(v_Cursor, 'DT', p_Deal_Type);

   v_Exec := dbms_sql.execute(v_Cursor);

   -- Convert to a REF cursor after execution
   v_Ref := dbms_sql.to_refcursor(v_Cursor);

   p_Ref := v_Ref;

end;
/

Now for the test;

SQL> variable test_Ref refcursor;
SQL> set serveroutput on
SQL> begin
  2    Get_Deals('SPOT', :Test_Ref);
  3  end;
  4  /

PL/SQL procedure successfully completed.


Verfify the results;

SQL> print :Test_Ref;

DEAL_ID DEAL_TYPE CREATED                   TRADE_RISK GBP_AMNT
------- --------- ------------------------- ---------- --------
      1 SPOT      16-NOV-11 11.04.57.957000          1       10
      4 SPOT      16-NOV-11 11.04.57.957000          1       22

Script to Alter varchar2 byte cols to char

The script below modifies all columns from VARCHAR2 BYTE to CHAR for all tables listed in the IN list. The script uses the USER_TAB_COLUMNS view. A log table has been created to record failures.

create table log_tbl (
  table_name varchar2(30)
, column_name varchar2(30)
, msg varchar2(200)
, error_flag varchar2(1) default 'P') -- P for Pass and F for Fail.
/ 

SQL> select table_name, column_name, char_used
  2  from user_tab_columns
  3  where table_name in ('T1','T2')
  4  / 
 
TABLE_NAME                     COLUMN_NAME                    C
------------------------------ ------------------------------ -
T1                             A                              B
T2                             A                              B
 
SQL> declare
  2    l_Err varchar2(200);
  3  begin
  4    for r in (select  atc.table_name, atc.column_name, atc.data_length
  5              from    user_tab_columns atc -- You would probably use ALL_
  6              left outer join Log_Tbl lt on (atc.Table_name   = lt.Table_Name
  7                                         and atc.Column_name = lt.Column_Name
  8                                         and lt.Error_Flag   = 'P')
  9              where   atc.data_type   = 'VARCHAR2'
 10              and     atc.char_used   = 'B'
 11              and     atc.Table_Name in ('T1', 'T2', 'T3')) loop
 12  
 13      begin 
 14        execute immediate 'alter table ' || r.table_name 
 15                                        || ' modify ' 
 16                                        || r.column_name 
 17                                        || ' varchar2('
 18                                        || r.data_length
 19                                        || ' char)';
 20      
 21        insert into Log_tbl (Table_Name, Column_Name) 
 22        values  (r.Table_Name, r.Column_Name);
 23        
 24        exception
 25          when others then
 26            l_Err := sqlerrm;
 27            insert into Log_tbl (Table_Name, Column_Name, Msg, Error_Flag) 
 28            values  (r.Table_Name, r.Column_Name, l_Err, 'F');
 29      end;
 30      
 31      commit;
 32      
 33    end loop;
 34    
 35  end;
 36  / 
 
PL/SQL procedure successfully completed.
 
SQL> select table_name, column_name, char_used
  2  from user_tab_columns
  3  where table_name in ('T1','T2', 'T3')
  4  / 
 
TABLE_NAME                     COLUMN_NAME                    C
------------------------------ ------------------------------ -
T1                             A                              C
T2                             A                              C
 
SQL> select table_name,column_name,error_flag
  2  from log_tbl;
 
TABLE_NAME      COLUMN_NAME     E
--------------- --------------- -
T1              A               P
T2              A               P
 
SQL> create table t3 (a varchar2(20) )
  2  / 
 
Table created.
 
SQL> insert into t3 (a) values ('Hello')
  2  / 
 
1 row created.

SQL> select table_name, column_name, char_used
  2  from user_tab_columns
  3  where table_name in ('T1','T2', 'T3');

TABLE_NAME                     COLUMN_NAME                    C
------------------------------ ------------------------------ -
T1                             A                              C
T2                             A                              C
T3                             A                              B 

Note the difference in the column char usage between T3 and the other tables given that T3 was created after the script was executed.

SQL> declare
  2    l_Err varchar2(200);
  3  begin
  4    for r in (select  atc.table_name, atc.column_name, atc.data_length
  5              from    user_tab_columns atc -- You would probably use ALL_
  6              left outer join Log_Tbl lt on (atc.Table_name   = lt.Table_Name
  7                                         and atc.Column_name = lt.Column_Name
  8                                         and lt.Error_Flag   = 'P')
  9              where   atc.data_type   = 'VARCHAR2'
 10              and     atc.char_used   = 'B'
 11              and     atc.Table_Name in ('T1', 'T2', 'T3')) loop
 12  
 13      begin 
 14        execute immediate 'alter table ' || r.table_name 
 15                                        || ' modify ' 
 16                                        || r.column_name 
 17                                        || ' varchar2('
 18                                        || r.data_length
 19                                        || ' char)';
 20      
 21        insert into Log_tbl (Table_Name, Column_Name) 
 22        values  (r.Table_Name, r.Column_Name);
 23        
 24        exception
 25          when others then
 26            l_Err := sqlerrm;
 27            insert into Log_tbl (Table_Name, Column_Name, Msg, Error_Flag) 
 28            values  (r.Table_Name, r.Column_Name, l_Err, 'F');
 29      end;
 30      
 31      commit;
 32      
 33    end loop;
 34    
 35  end;
 36  / 
 
PL/SQL procedure successfully completed.
 
SQL> select table_name, column_name, char_used
  2  from user_tab_columns
  3  where table_name in ('T1','T2', 'T3')
  4  / 
 
TABLE_NAME      COLUMN_NAME     C
--------------- --------------- -
T1              A               C
T2              A               C
T3              A               C

The script uses the USER_TAB_COLUMNS view. If you modify the script to use the ALL_TAB_COLUMNS or the DBA_TAB_COLUMNS view, then the script would look as follows:

undefine schema_name
declare
  l_Err varchar2(200);
begin
  for r in (select  atc.table_name, atc.column_name, atc.data_length
            from    all_tab_columns atc -- You would probably use ALL_
            left outer join Log_Tbl lt on (atc.Table_name   = lt.Table_Name
                                       and atc.Column_name = lt.Column_Name
                                       and lt.Error_Flag   = 'P')
            where   atc.data_type   = 'VARCHAR2'
            and     atc.char_used   = 'B'
            and     atc.Table_Name  in ('T1', 'T2', 'T3')
            and     atc.owner       = upper('&&schema_name')) loop

    begin
      execute immediate 'alter table '|| upper('&&schema_name')
                                      || '.'
                                      || r.table_name
                                      || ' modify '
                                      || r.column_name
                                      || ' varchar2('
                                      || r.data_length
                                      || ' char)';

      insert into Log_tbl (Table_Name, Column_Name)
      values  (r.Table_Name, r.Column_Name);

      exception
        when others then
          l_Err := sqlerrm;
          insert into Log_tbl (Table_Name, Column_Name, Msg, Error_Flag)
          values  (r.Table_Name, r.Column_Name, l_Err, 'F');
    end;

    commit;

  end loop;

end;
/
 

The default bhaviour, when the usage is not specified explicitly, is to set each column to BYTE. The default behaviour can be altered by setting NLS_LENGTH_SEMANTICS, for example:

SQL> ALTER SESSION SET NLS_LENGTH_SEMANTICS=BYTE;

Session altered.

SQL> DROP TABLE T1;

Table dropped.

SQL> DROP TABLE T2;

Table dropped.

SQL> ALTER SESSION SET NLS_LENGTH_SEMANTICS=CHAR;

Session altered.

SQL> create table t1 (a varchar2(10));

Table created.

SQL> select table_name,column_name,char_used from user_tab_columns
  2  where table_name = 'T1';

TABLE_NAME COLUMN_NAME C
---------- ----------- -
T1         A           C

SQL> ALTER SESSION SET NLS_LENGTH_SEMANTICS=BYTE;

Session altered.

SQL> create table t2 (a varchar2(10));

Table created.

SQL> select table_name,column_name,char_used from user_tab_columns
  2  where table_name IN ('T2', 'T1');

TABLE_NAME COLUMN_NAME C
---------- ----------- -
T1         A           C
T2         A           B