create or replace package pack_script is
/*
  09.06.2015 (ABS-8405/SO 20015011) fixed varchar2 -> number conversion issue in convertScriptNameToNumber (8.03.12.n)
  12.06.2015 (ABS-8523 ABS-8619) Update issue deleteITables.sql packscript 80312m (8.03.12.n)
  03.09.2015 (ABS-13997 ABS-14043) Enhanced 'DROP PRIMARY KEY' statement in order to drop also the associated index (9.00.00.i1, 8.03.12.s, 8.03.11.n)
  18.11.2015 (ABS-14766) Increase String buffer size to be able to process very large constraints (9.00.00.b, 8.03.12.t, 8.03.11.n)
  02.02.2016 (ABS-21962) Revert change on pack_script for drop object statements - change from 8299 revision (9.00.00.c, 8.03.12.t, 8.03.11.n)
  12.10.2016 (ABS-37641) Fixed bug in column's definition recognition (column type was truncated to the first space char) (9.00.05.a, 9.00.01.a, 9.00.00.e)
  16.08.2017 (ABS-43025) Included command 'Alter Table ... Read Only' (9.01.03.a)
  27.09.2017 (ABS-50291) Pack_Script throws error when trying to add an existing TYPE
  02.10.2017 (ABS-51124) If error is thrown by a DML, the script execution will be stopped and error thown (9.01.04.a)
  31.05.2018 (ABS-38273) Correction in old vs. new columns comparison for indexes and contraints.
                         Also corrected a possible Oracle 12 bug regarding function parameters usage (9.02.03.a)
  07.09.2018 (ABS-70238) Allow ALTER TABLE DROP CONSTRAINT CASCADE syntax (9.02.04.a)
  28.12.2018 (ABS-72986) Do not drop & recreate anymore an existing constraint (9.03.00.a)
  03.01.2019 (ABS-76483) Handle ORA-01430 due to column names specified with quotation marks (9.03.00.a)
  18.01.2019 (ABS-72986) Do not drop & recreate anymore an existing constraint - re-work to ignore irrelevant characters in definitions (9.03.00.a)
  15.03.2019 (ABS-80290) Correction in reading index definition + re-work for ABS-72986: dropping a constraint that exists for another table
                         shouldn't stop script execution. The situation will be logged as warning in the script log. (9.03.01.RC)
  11.04.2019 (ABS-80835) Manage ; char included into a string (9.03.02.RC1)
  11.04.2019 (ABS-62787) Improve statement parsing (Space inserted before comment which starts with bracket) (9.03.02.RC1)
  28.06.2019 (ABS-85377) Unique constraint drop statement should drop also the associated index (9.03.03.RC1)
  09.01.2020 (ABS-94221) SpecialScripts - ORA-02216: tablespace name expected  (9.04.00.RC1)
  10.01.2020 (ABS-94213) SpecialScript_AUS_09.03.01.01.sql - ORA-00904: "RPAD": invalid identifier (9.04.00.RC1)
  20.01.2020 (ABS-85504) Scripts in patch update should be automatically ran - ABS-94961: obsolete drop index/constraint should be skipped (9.04.00.RC1)
  21.01.2020 (ABS-69737) Pack_script handling correctly same index name, referencing different columns
                         Situation to be handled from script text. Pack_script will overwrite such index (no code changes)
  27.01.2020 (ABS-94821) Special script issues Untreated_statement: create or replace directory (9.04.00.RC1)
  28.01.2020 (ABS-90132) SpecialScript_CUS_09.04.00.01 - ORA-20002: Untreated_statement CREATE OR REPLACE PUBLIC SYNONYM
  05.06.2020 (ABS-101953) Untreated statement in PACK_SCRIPT: MERGE INTO (9.04.03.RC)
  24.08.2020 (ABS-103666) Extend pack_script functionality to handle exceptions in Create table better
  15.09.2020 (ABS-107984) ORA-20002: ORA-01451: column to be modified to NULL cannot be modified to NULL
  30.09.2020 (ABS-108787) PACK_SCRIPT.e48d5686364.sql is failing with Script_09.04.04.35.sql (column formatter wrong + constraintrec.constraint_name too small)
  25.02.2021 (ABS-115127) Create table/constraint/index statements to be skipped when the same script is run again (9.05.01.RC1)
  19.05.2021 (ABS-115127) Prevent create comment/index/constraint on a column deleted by a newer/same script
                          Skip constraint creation if constraint will be deleted in a newer script (9.05.02.RC1)
  05.07.2021 (ABS-119408) Fix bug in function to find the last statement for constraint
  05.01.2023 (ABS-129857) Improve logging mechanism Adapt pack_script for the timestamp data type
  27.04.2023 (ABS-150389) Changed regex for get_last_script_for_table
  19.01.2024 (ABS-155712) Increased column information variable in alter_table_column
  01.03.2024 (ABS-147482) Raise 'Error in statement' exception when there's a missing comma in create table statement
  08.03.2024 (ABS-152410) Standardize ddl statements to uppercases (except for the strings specified between single quotes.
  22.05.2024 (ABS-165178) Handle sequence as default column value
  07.06.2024 (ABS-162468) Pack_script is throwing error 'table exists but is different'
  08.08.2024 (ABS-167808) Change pack_script.pck to ignore difference between 'sysdate' and 'SYSDATE'
  23.12.2024 (ABSN-726) Misleading 'Table exists but is different' error
*/

  -----------------------------------------------------------------------------
  -------------------- SERVICE SPECIFIC PUBLIC METHODS ------------------------
  -----------------------------------------------------------------------------
  procedure execscriptfile(sfile clob, scriptName varchar2 default null);

  function convertScriptNameToNumber(scriptName in varchar2) return number;

end pack_script;
/
create or replace package body pack_script is
  -- Tab for columns
  type colrec is record(
    column_name   user_tab_columns.COLUMN_NAME%type,
    data_type     user_tab_columns.DATA_TYPE%type,
    nullable      user_tab_columns.NULLABLE%type,
    default_value user_tab_columns.DATA_DEFAULT%type,
    constr        long default null);

  type columntabtype is table of colrec index by binary_integer;

  type constraintrec is record(
    constraint_name user_constraints.CONSTRAINT_NAME%type,
    constraint_type varchar2(50),
    default_value   user_constraints.SEARCH_CONDITION%type,
    columns_names   columntabtype,
    stmt            long);

  type constrtabtype is table of constraintrec index by binary_integer;

  referenced_table exception;
  pragma exception_init(referenced_table, -2449);
  object_not_found exception;
  pragma exception_init(object_not_found, -4043);
  trigger_not_found exception;
  pragma exception_init(trigger_not_found, -4080);
  invalid_identifier exception;
  pragma exception_init(invalid_identifier, -904);
  untreated_statement exception;
  error_in_statement exception;
  gv_tablespace varchar2(40);
  gv_dataTblspace varchar2(40);
  gv_currentScriptNo number := 0;
  gv_latestScriptNo number := 0;
  gv_skipStatement varchar2(150);
  gv_scriptName varchar2(100);

  -- put_buffer is limited to 255 characters in Oracle 9i
  -- there for must be replaced with put_buffer
  procedure put_buffer(v_buffer varchar2) is
    cnt      number;
    buf      varchar2(300);
    v_length number;
  begin
    v_length := length(v_buffer);
    if v_length <= 250 then
      dbms_output.put_line(v_buffer);
    else
      cnt := 1;
      while cnt < v_length loop
        if (v_length - cnt + 1) > 250 then
          buf := substr(v_buffer, cnt, 250);
          put_buffer(buf);
          cnt := cnt + 250;
        else
          buf := substr(v_buffer, cnt, v_length - cnt + 1);
          put_buffer(buf);
          cnt := v_length;
        end if;
      end loop;
    end if;
  end put_buffer;

  procedure run_ddl_command(stmt in varchar2, proc_name in varchar2) is
  begin
    execute immediate stmt;
  exception
    when dup_val_on_index then
      raise;
    when referenced_table then
      raise;
    when others then
      put_buffer(proc_name || ' generates error when executing: ');
      put_buffer(substr(stmt, 1, 2550));
      raise;
  end run_ddl_command;

  function myTrim (str varchar2, var varchar2, pos varchar2 := '') return varchar2 is
  res varchar2(32767);
  begin
    res := str;
    if (pos = 'l') then
      loop
        if (instr(res, var) = 1) then
           res := substr(res, 1 + length(var));
        else exit;
        end if;
      end loop;
    end if;
    if (pos = 'r') then
      loop
        if (length(res) = instr(res, var, -1) - 1 + length(var)) then
           res := substr(res, 1, instr(res, var, -1) - 1);
        else exit;
        end if;
      end loop;
    end if;
    if (pos is null ) then
       res := myTrim(res, var, 'l');
       res := myTrim(res, var, 'r');
    end if;
    return res;
  end myTrim;

  function tokenizer(startpos in out number, separator in varchar2, strfortoken in clob)
    return varchar2 as
    lv_strfortoken clob;
    strresult      varchar2(32767);
    lv_startpos    number;
  begin
    if (length(separator) = 1) then
       lv_strfortoken := trim(both separator from dbms_lob.substr(strfortoken, 32767, startpos));
    else
       lv_strfortoken := myTrim(dbms_lob.substr(strfortoken, 32767, startpos), separator);
    end if;
    lv_startpos := dbms_lob.instr(lv_strfortoken, separator, 1, 1);
    if lv_startpos = 0 then
      strresult := lv_strfortoken;
      startpos  := 0;
    else
      strresult := dbms_lob.substr(lv_strfortoken, lv_startpos - 1, 1);
      startpos  := dbms_lob.instr(strfortoken, strresult, startpos, 1) + length(strresult) + 1;
    end if;
    strresult := rtrim(rtrim(strresult, chr(10)), chr(13));
    if (length(separator) = 1) then
       return trim(both separator from strresult);
    else
       return myTrim(strresult, separator);
    end if;
  end tokenizer;

  function checkConsistentToken(token in varchar2, forString boolean default false) return boolean as
    npos1    number;
    openBracketsNum  number := 0;
    closeBracketsNum number := 0;
    stringMark varchar2(1) := '''';
    stringMarkNum number := 0;
  begin
    if (forString) then
      npos1 := instr(token, stringMark);
      while npos1 != 0 loop
        stringMarkNum := stringMarkNum + 1;
        npos1   := instr(token, stringMark, npos1 + 1);
      end loop;
      if (MOD(stringMarkNum, 2) = 0) then
        return true;
      else
        return false;
      end if;
    else
      npos1 := instr(token, '(');
      while npos1 != 0 loop
        openBracketsNum := openBracketsNum + 1;
        npos1   := instr(token, '(', npos1 + 1);
      end loop;
      npos1 := instr(token, ')');
      while npos1 != 0 loop
        closeBracketsNum := closeBracketsNum + 1;
        npos1    := instr(token, ')', npos1 + 1);
      end loop;
      if (openBracketsNum = closeBracketsNum) then
        return true;
      else
        return false;
      end if;
    end if;
  end checkConsistentToken;

  procedure standardizeStatement(stmt in out varchar2) is
    lv_orig_stmt varchar2(32767);
    lv_to_keep_str varchar2(10000);
    lv_regex_format varchar2(10) := q'{'[^']+'}';
    occurences number;
  begin
    if (stmt is null) then
      return;
    end if;

    lv_orig_stmt := stmt;
    stmt := upper(lv_orig_stmt);
    occurences := regexp_count(lv_orig_stmt, lv_regex_format);

    if (occurences = 0) then
      return;
    end if;
    for i in 1..occurences loop
      lv_to_keep_str := regexp_substr(lv_orig_stmt, lv_regex_format, 1, i);
      stmt := replace(stmt, upper(lv_to_keep_str), lv_to_keep_str);
    end loop;
  end standardizeStatement;

  function formatStatement(stmt in out varchar2, innerText boolean default false) return varchar2 is
    type ExceptionsTable IS TABLE OF VARCHAR2(30);
    notFormatting ExceptionsTable := ExceptionsTable('VARCHAR2', 'NUMBER', 'CHAR', 'DECIMAL', 'INTEGER',
                                  'PRECISION', 'REAL', 'SMALLINT', 'FLOAT', 'TO_DATE', 'TRUNC', 'NVL',
                                  'RPAD', 'LPAD', 'SUBSTR','TIMESTAMP');
  begin
    if ((instr(upper(stmt), 'TABLESPACE ABS_DATA') > 0) and gv_dataTblspace is not null) then
       stmt := substr(stmt, 0, instr(upper(stmt), 'TABLESPACE ABS_DATA') + 10) || upper(gv_dataTblspace) || substr(stmt, instr(upper(stmt), 'TABLESPACE ABS_DATA') + 19);
    end if;

    stmt := replace(stmt, chr(9), ' ');
    stmt := replace(stmt, chr(10), ' ');
    -- insert space before '(' and after ')' if not exists already and it's not one of the next formats:
    -- )<punctuation>
    -- (<comment>)
    while (regexp_instr(stmt, '([^''[:space:]])(\()') > 0) loop
      stmt := regexp_replace(stmt, '([^''[:space:]])(\()', '\1 (', 1, 0);
    end loop;
    while (regexp_instr(stmt, '(\))([^''[:space:],;:.])') > 0) loop
      stmt := regexp_replace(stmt, '(\))([^''[:space:],;:.])', ') \2', 1, 0);
    end loop;

    -- revert previously inserted spaces for format: <method>(
    for x in notFormatting.first..notFormatting.last loop
      stmt := regexp_replace(stmt, notFormatting(x) ||' \(', notFormatting(x) ||'(', 1, 0, 'i');
    end loop;

    stmt := replace(stmt, ') ,', '),');
    while (instr(stmt, '  ') > 0) loop
      stmt := replace(stmt, '  ', ' ');
    end loop;
    if (innerText) then
      while (instr(stmt, '''''') > 0) loop
        stmt := replace(stmt, '''''', '''');
      end loop;
    end if;
    stmt := trim(rtrim(stmt, '; '));

    return stmt;
  end formatStatement;

  function print_by_newer_script_message(script_number number) return varchar2 is
    lv_scriptName sqlscript.scriptname%type;
  begin
    lv_scriptName := lpad(script_number, 9, '0');
    lv_scriptName := substr(lv_scriptName, 1, 2) || '.' || substr(lv_scriptName, 3, 2) || '.' || substr(lv_scriptName, 5, 2) ||
                     '.' || regexp_replace(substr(lv_scriptName, 7), '^0', '');
    return ('by a newer script (' || lv_scriptName || ').');
  end print_by_newer_script_message;

  function get_invalid_identifier_from_sqlerrm(errm varchar2) return varchar2 is
  begin
    return trim(regexp_replace(regexp_substr(errm, '(:\s)(\S*)'), ':|"', ''));
  end get_invalid_identifier_from_sqlerrm;

  function get_last_script_for_stmt(stmt varchar2) return number is
    ret number;
    scriptCategory varchar2(30);
  begin
    if(gv_scriptName like 'Script\_%' escape '\') then
      scriptCategory:='Script\_%';
    elsif (gv_scriptName like 'SpecialScript\_%' escape '\') then
      scriptCategory:='SpecialScript\_%';
    end if;
    select scriptno into ret from
    (select pack_script.convertScriptNameToNumber(s.scriptname) scriptno
      from sqlscript s
     where (s.scriptname like scriptCategory escape '\')
       and regexp_instr(upper(s.scriptcontent), stmt) > 0
       and pack_script.convertScriptNameToNumber(s.scriptname) > gv_currentScriptNo
     order by s.scriptname desc)
     where rownum = 1;
    return ret;
  exception
    when no_data_found then
      return 0;
  end get_last_script_for_stmt;

  function get_last_script_for_constraint(constraint_name varchar2, table_name varchar2) return number is
    lv_constraintName user_tab_columns.COLUMN_NAME%type := constraint_name;
    lv_tableName  user_tab_columns.TABLE_NAME%type := table_name;
  begin
    -- get last script that ADD/MODIFY a certain constraint of a table
    return get_last_script_for_stmt('TABLE ' || lv_tableName || '\s+(MODIFY\.*|ADD\.*)\s+CONSTRAINT ' || lv_constraintName || '(\s)');
  end get_last_script_for_constraint;

  function get_last_script_for_drop_constraint(constraint_name varchar2, table_name varchar2) return number is
    lv_constraintName user_tab_columns.COLUMN_NAME%type := constraint_name;
    lv_tableName  user_tab_columns.TABLE_NAME%type := table_name;
  begin
    -- get last script that DROP/RENAME a certain constraint of a table
    return get_last_script_for_stmt('ALTER TABLE ' || lv_tableName || '\s+(DROP|RENAME)\s+CONSTRAINT ' || lv_constraintName || '(\s|\;)');
  end get_last_script_for_drop_constraint;

  function get_last_script_for_table(table_name varchar2) return number is
    lv_tableName user_tables.TABLE_NAME%type := table_name;
  begin
    -- get last script that modified a table structure (ADD/MODIFY/DROP/RENAME a column)
    return get_last_script_for_stmt('ALTER TABLE ' || lv_tableName || '\s+(((MODIFY\.*|ADD\.*)\s?+[^CONSTRAINT])|((DROP|RENAME) COLUMN ))');
  end get_last_script_for_table;

  function get_last_script_for_index(index_name varchar2, table_name varchar2) return number is
    lv_indexName user_indexes.INDEX_NAME%type := index_name;
    lv_tableName user_indexes.TABLE_NAME%type := table_name;
  begin
    -- get last script that CREATE a certain index on a table
    return get_last_script_for_stmt('CREATE (UNIQUE )??INDEX ' || lv_indexName || ' ON ' || lv_tableName || '(\s|\()');
  end get_last_script_for_index;

  function get_last_script_for_column(column_name varchar2, table_name varchar2) return number is
    lv_columnName user_tab_columns.COLUMN_NAME%type := column_name;
    lv_tableName user_tab_columns.TABLE_NAME%type := table_name;
  begin
    -- get last script that DROP/RENAME a certain column of a table
    return get_last_script_for_stmt('ALTER TABLE ' || lv_tableName || '\s+(DROP|RENAME)\s+COLUMN ' || lv_columnName || '(\s|\;)');
  end get_last_script_for_column;


  procedure set_table_column(tab_columns in out columntabtype,
                             column      in colrec) is
    col_ix number;
  begin
    col_ix := tab_columns.count + 1;
    tab_columns(col_ix) := column;
  end set_table_column;

  procedure set_table_constraint(constraints in out constrtabtype,
                                 constr      in constraintrec) is
    col_ix number;
  begin
    col_ix := constraints.count + 1;
    constraints(col_ix) := constr;
  end set_table_constraint;

  function get_default_value_for_column(inputString in varchar2) return varchar2 is
    temp varchar2(30000);
    begin
      temp := upper(inputString);
      if (instr(temp, 'CONSTRAINT') > 0) then
        temp := trim(substr(temp, 1, instr(temp, 'CONSTRAINT') - 1));
      end if;
      if (substr(temp, instr(temp, ' ', -1) + 1) = 'NULL') then
        temp := trim(substr(temp, 1, instr(temp, ' ', -1) - 1));
      end if;
      if (substr(temp, instr(temp, ' ', -1) + 1) = 'NOT') then
        temp := trim(substr(temp, 1, instr(temp, ' ', -1) - 1));
      end if;
      if (temp is null or length(temp) = 0) then
        return 'NULL';
      else
        return substr(inputString, 1, length(temp));
      end if;
  end get_default_value_for_column;

  function format_to_colrec(columninfo in varchar2) return colrec is
    column_rec colrec;
    temppos    number := 1;
    tempstr    varchar2(300) := '';
    param      varchar2(300) := '';
  begin
    column_rec.column_name := tokenizer(temppos, ' ', columninfo);
    column_rec.data_type   := tokenizer(temppos, ' ', columninfo);
    if (column_rec.data_type in ('DEFAULT', 'NULL', 'NOT')) then
      tempstr             := column_rec.data_type;
      column_rec.data_type := '';
    else
      param := column_rec.data_type;
      while (not checkconsistenttoken(param)) loop
        param := param || ' ' || tokenizer(temppos, ' ', columninfo);
      end loop;
      column_rec.data_type := param;
      tempstr := tokenizer(temppos, ' ', columninfo);
      if (column_rec.data_type = 'TIMESTAMP') then
         column_rec.data_type := 'TIMESTAMP(6)';
      end if;
    end if;
    if (tempstr = 'DEFAULT') then
      param := trim(get_default_value_for_column(substr(columninfo, temppos)));
      column_rec.default_value := param;
      temppos := temppos + length(param);
      tempstr := tokenizer(temppos, ' ', columninfo);
    else
      column_rec.default_value := '';
    end if;
    if (tempstr = 'NULL') then
      column_rec.nullable := 'Y';
    elsif (tempstr = 'NOT') then
      column_rec.nullable := 'N';
      tempstr := tokenizer(temppos, ' ', columninfo);
    end if;
    -- check for an eventually constraint on column
    column_rec.constr := '';
    if (tempstr = 'NULL') then
      tempstr := tokenizer(temppos, ' ', columninfo);
    end if;
    if (temppos > 0 and tempstr = 'CONSTRAINT') then
      column_rec.constr := trim(substr(columninfo, temppos));
    elsif (temppos > 0) then
      raise error_in_statement;
    end if;
    return column_rec;
  end format_to_colrec;

  function format_to_constraintrec(stmnt in varchar2) return constraintrec is
    constr_rec     constraintrec;
    temppos        number := 1;
    tempstr        varchar2(32000);
    colnames       varchar2(500);
    tempcolrec     colrec;
    tempcoltab     columntabtype;
    constrinfo     varchar2(32000);
  begin
    constrinfo := tokenizer(temppos, 'TABLESPACE ', stmnt);
    if (temppos > 0 and gv_tablespace is not null) then
      tempstr := tokenizer(temppos, ' ', stmnt);
      tempstr := tokenizer(temppos, ' ', stmnt);
      if (temppos > 0) then
         tempstr := substr(stmnt, temppos);
      else
         tempstr := '';
      end if;
      constrinfo := trim(constrinfo || 'TABLESPACE ' || gv_tablespace || ' ' || tempstr);
    else
      constrinfo := stmnt;
    end if;
    temppos := 1;

    constr_rec.constraint_name := tokenizer(temppos, ' ', constrinfo);
    tempstr := tokenizer(temppos, ' ', constrinfo);
    if (tempstr in ('PRIMARY', 'FOREIGN')) then
      constr_rec.constraint_type := tempstr || ' KEY';
      tempstr := tokenizer(temppos, ' ', constrinfo);
    else
      constr_rec.constraint_type := tempstr;
    end if;
    temppos := 1;
    if (constr_rec.constraint_type not like 'CHECK%') then
      colnames := trim(substr(constrinfo, instr(constrinfo, '(') + 1,
                              instr(constrinfo, ')', instr(constrinfo, '(') + 1) -
                               instr(constrinfo, '(') - 1));
      while (temppos != 0) loop
        tempstr := trim(tokenizer(temppos, ',', colnames));
        tempcolrec.column_name := tempstr;
        set_table_column(tempcoltab, tempcolrec);
      end loop;
      constr_rec.stmt := trim(substr(constrinfo, instr(constrinfo, ')', 1) + 1));
    else
      temppos := instr(constrinfo, ')', - 1);
      tempstr := trim(substr(constrinfo, instr(constrinfo, '(') + 1, temppos - instr(constrinfo, '(') - 1));
      constr_rec.stmt := tempstr;
      if (length(trim(substr(constrinfo, temppos + 1))) > 0) then
        raise error_in_statement;
      end if;
    end if;
    constr_rec.columns_names := tempcoltab;
    return constr_rec;
  end format_to_constraintrec;

  function table_exist(fp_table_name in varchar2) return boolean is
    lv_counter number;
  begin
    select 1
    into   lv_counter
    from   user_tables t
    where  t.table_name = trim(fp_table_name);
    return true;
  exception
    when no_data_found then
      return false;
  end table_exist;

  function format_column(column in colrec, type in varchar2 default 'TABLE', compare boolean default false)
    return varchar2 is
    stmt varchar2(4000);
  begin
    stmt := column.column_name;
    if type = 'TABLE' then
      if (column.data_type is not null) then
        stmt := stmt || ' ' || column.data_type;
        --check if data_type is like CHAR/VARCHAR2(string length)
        if (regexp_like(column.data_type, '^VARCHAR2\s*?\(\s*?\d+?\s*?\)', 'i') or regexp_like(column.data_type, '^CHAR\s*?\(\s*?\d+?\s*?\)', 'i')) then
          stmt := substr(stmt, 0, length(stmt) - 1) || ' CHAR)';
        end if;
      end if;
      if (column.default_value is not null) then
        stmt := stmt || ' DEFAULT ' || column.default_value;
      end if;
      if (column.nullable is not null) then
        if (column.nullable = 'N') then
          stmt := stmt || ' NOT NULL';
        elsif (column.nullable = 'Y') then
          stmt := stmt || ' NULL';
        end if;
      elsif (compare) then
        stmt := stmt || ' NULL';
      end if;
    end if;
    if type = 'INDEX' then
      stmt := stmt || ' ' || column.data_type;
    end if;
    if type = 'RENAME' then
      stmt := stmt || ' to ' || column.default_value;
    end if;
    return(stmt);
  end format_column;

  function format_columns(tab_columns in columntabtype, column_type in varchar2 default 'TABLE')
    return varchar2 is
    stmt varchar2(32000);
  begin
    if (tab_columns is null) or (tab_columns.count = 0) then
       return '';
    end if;
    for i in tab_columns.first .. tab_columns.last loop
      if i = 1 then
        stmt := stmt || format_column(tab_columns(i), column_type);
      else
        stmt := stmt || ', ' || format_column(tab_columns(i), column_type);
      end if;
    end loop;
    return(stmt);
  end format_columns;

  function columns_compare(fp_old_tab_columns in columntabtype,
                           fp_tab_columns in columntabtype) return boolean is
 newColumnDefinitionString varchar2(100);
 existingColumnDefinitionString varchar2(100);
 oldColumn colrec;
  begin
    if (fp_old_tab_columns.count != fp_tab_columns.count) then
      return false;
    else
      for i in fp_tab_columns.first..fp_tab_columns.last loop
        for j in fp_old_tab_columns.first..fp_old_tab_columns.last loop
          oldColumn := fp_old_tab_columns(j);
          standardizeStatement(oldColumn.default_value);
          newColumnDefinitionString := replace(format_column(fp_tab_columns(i), 'TABLE', true), ' ', '');
          newColumnDefinitionString := replace(newColumnDefinitionString, '"', '');
          existingColumnDefinitionString := replace(format_column(oldColumn, 'TABLE', true), ' ', '');
          existingColumnDefinitionString := replace(existingColumnDefinitionString, '"', '');
          if (newColumnDefinitionString = existingColumnDefinitionString) then
            exit;
          end if;
          if(j = fp_old_tab_columns.last) then
            return false;
          end if;
        end loop;
      end loop;
    end if;
    return true;
  end columns_compare;

  function column_exist(fp_table_name in varchar2, fp_column_name in varchar2) return boolean is
    lv_tableName  user_tab_columns.TABLE_NAME%type := fp_table_name;
    lv_columnName user_tab_columns.COLUMN_NAME%type := trim(BOTH '"' from fp_column_name);
    ret number := 0;
  begin
    select count(1) into ret from user_tab_columns
           where table_name = lv_tableName and column_name = lv_columnName;
    return (ret > 0);
  end column_exist;

  function constraint_exist(fp_constraint_name in varchar2, fp_table_name in varchar2) return boolean is
    lv_constraintName user_tab_columns.COLUMN_NAME%type := fp_constraint_name;
    lv_tableName  user_tab_columns.TABLE_NAME%type := fp_table_name;
    ret number := 0;
  begin
    if (fp_table_name is not null) then
      select count(1) into ret from user_constraints
             where constraint_name = lv_constraintName and table_name = lv_tableName;
    else
      select count(1) into ret from user_constraints
             where constraint_name = lv_constraintName;
    end if;
    return (ret > 0);
  end constraint_exist;

  function index_exist(fp_index_name in varchar2, fp_table_name in varchar2) return boolean is
    lv_indexName user_indexes.INDEX_NAME%type := fp_index_name;
    lv_tableName user_indexes.TABLE_NAME%type := fp_table_name;
    ret number := 0;
  begin
    if (fp_table_name is not null) then
      select count(1) into ret from user_indexes
             where index_name = lv_indexName and table_name = lv_tableName;
    else
      select count(1) into ret from user_indexes
             where index_name = lv_indexName;
    end if;
    return (ret > 0);
  end index_exist;

  function object_exist(fp_object_name in varchar2, fp_object_type in varchar2) return boolean is
    lv_objectName user_objects.OBJECT_NAME%type := upper(trim(fp_object_name));
    lv_objectType user_objects.OBJECT_TYPE%type := upper(trim(fp_object_type));
    ret number := 0;
  begin
    if (lv_objectType = 'ANY') then
      select count(1) into ret from user_objects where object_name = lv_objectName;
      return (ret > 0);
    elsif (lv_objectType = 'CONSTRAINT') then
      return constraint_exist(fp_object_name, null);
    elsif (lv_objectType = 'TRIGGER') then
      select count(1) into ret from user_triggers where upper(trigger_name) like lv_objectName;
      return (ret > 0);
    else
      select count(1) into ret
      from   user_objects
      where  upper(object_name) = lv_objectName
      and    (object_type = nvl(lv_objectType, 'PROCEDURE') or
            object_type = nvl(lv_objectType, 'FUNCTION'));
      return (ret > 0);
    end if;
  end object_exist;

  procedure remove_object(object_name in varchar2, object_type in varchar2) is
    stmnt varchar2(200);
  begin
    if (object_exist(object_name, object_type)) then
      begin
        stmnt := 'DROP ' || object_type || ' ' || object_name;
        run_ddl_command(stmnt, 'Remove_' || object_type);
      exception
        when object_not_found then
         stmnt := 'DROP ' || object_type || ' ' || object_name;
         run_ddl_command(stmnt, 'Remove_' || object_type);
        when trigger_not_found then
         stmnt := 'DROP ' || object_type || ' ' || object_name;
         run_ddl_command(stmnt, 'Remove_' || object_type);
      end;
      put_buffer('Remove_' || object_type || ': ' || object_type || ' ' || object_name ||
                 ' dropped.');
    else
      put_buffer('Remove_' || object_type || ': ' || object_type || ' ' || object_name ||
                 ' not exist.');
    end if;
  end remove_object;

  procedure remove_constraint(procedure_name  in varchar2,
                               table_name      in varchar2,
                               constr          in constraintrec) is
    lv_tableName user_constraints.TABLE_NAME%type := table_name;
    lv_constrName user_constraints.CONSTRAINT_NAME%type := constr.constraint_name;
    stmnt varchar2(200);
    cursor get_cons is
      select constraint_name, constraint_type
      from   user_constraints
      where  constraint_name = lv_constrName
      and    constraint_type in ('P', 'R', 'U', 'C') -- Primary key, referential integrity, unique and check constraints
      and    table_name = lv_tableName;
  begin
    for cons in get_cons loop
      gv_latestScriptNo := get_last_script_for_constraint(lv_constrName, lv_tableName);
      if (gv_currentScriptNo > 0 and gv_currentScriptNo < gv_latestScriptNo) then
        put_buffer(procedure_name || ': Drop Constraint ' || cons.constraint_name || ' skipped. Constraint is created ' || print_by_newer_script_message(gv_latestScriptNo));
      else
        stmnt := 'ALTER TABLE ' || lv_tableName || ' DROP CONSTRAINT ' || cons.constraint_name;
        if constr.stmt is not null then
          stmnt := stmnt || ' ' || constr.stmt;
        elsif (cons.constraint_type = 'P' or cons.constraint_type = 'U') then
          stmnt := stmnt || ' DROP INDEX';
        end if;
        run_ddl_command(stmnt, procedure_name);
        put_buffer(procedure_name || ': Constraint ' || cons.constraint_name || ' dropped.');
      end if;
    end loop;
  end remove_constraint;

  procedure remove_indexes_hlp(procedure_name in varchar2,
                               fp_table_name     in varchar2 default '%',
                               fp_index_name     in varchar2 default '%') is
    lv_tableName user_indexes.TABLE_NAME%type := fp_table_name;
    lv_indexName user_indexes.INDEX_NAME%type := fp_index_name;
    stmnt varchar2(200);
    cursor get_ind is
      select index_name,
             (select count(*)
              from   user_constraints u
              where  i.index_name = u.constraint_name
              and    u.table_name = i.table_name) constrNo
      from   user_indexes i
      where  i.index_name like lv_indexName
      and    i.table_name like lv_tableName;
  begin
    for ind in get_ind loop
      if (ind.constrNo = 0) then
        stmnt := 'DROP INDEX ' || ind.index_name;
        run_ddl_command(stmnt, procedure_name);
        put_buffer(procedure_name || ': Index ' || fp_index_name || ' dropped.');
      else
        put_buffer(procedure_name || ': Index ' || fp_index_name || ' can not be dropped. Associated constraint exists.');
        raise_application_error(-20001, 'Index ' || fp_index_name || ' not removed.');
      end if;
    end loop;
  end remove_indexes_hlp;

  procedure remove_indexes(table_name in varchar2 default '%',
                           index_name in varchar2 default '%') is
    ok number := 1;
  begin
    if (table_name != '%') then
      if (not table_exist(table_name)) then
        ok := 0;
        put_buffer('Remove_Indexes: Table ' || table_name || ' not exist.');
        raise_application_error(-20001, 'Index ' || index_name || ' not removed.');
      end if;
    end if;
    if (index_name != '%') then
      if (not index_exist(index_name, null)) then
        ok := 0;
        put_buffer('Remove_Indexes: Index ' || index_name || ' not exist.');
      end if;
    end if;
    if (ok = 1) then
      gv_latestScriptNo := get_last_script_for_index(index_name, table_name);
      if (gv_currentScriptNo > 0 and gv_currentScriptNo < gv_latestScriptNo) then
        put_buffer('Remove_Indexes: Drop Index ' || index_name || ' skipped. Index is created ' || print_by_newer_script_message(gv_latestScriptNo));
      else
        remove_indexes_hlp('Remove_Indexes', table_name, index_name);
      end if;
    end if;
  end remove_indexes;

  procedure remove_table(table_name in varchar2, drop_info in varchar2) is
    stmnt varchar2(200);
    no_table exception;
    no_table_942 exception;
    pragma exception_init(no_table, -06550);
    pragma exception_init(no_table_942, -00942);
  begin
    if (table_exist(table_name)) then
      stmnt := 'DROP TABLE ' || table_name || ' ' || drop_info; -- || ' CASCADE CONSTRAINTS';
      run_ddl_command(stmnt, 'Remove_Table');
      put_buffer('Remove_Table: Table ' || table_name || ' dropped.'); -- with cascade constraints.');
    else
      put_buffer('Remove_Table: Table ' || table_name || ' not exist.');
    end if;
  exception
    when no_table or no_table_942 then
      --no problem
      null;
    when referenced_table then
      put_buffer('Remove_Table: Table ' || table_name || ' not dropped due to dependency.');
      raise;
  end remove_table;

  function get_column_nullable(table_name in varchar2, column_name in varchar2) return varchar2 is
    lv_tableName  user_tab_columns.TABLE_NAME%type := table_name;
    lv_columnName user_tab_columns.COLUMN_NAME%type := column_name;
    lv_nullable   varchar2(1);
  begin
    select nullable into lv_nullable
      from   user_tab_columns
      where  table_name = lv_tableName
      and    column_name = lv_columnName;
    return lv_nullable;
  end get_column_nullable;

  function get_column_datatype(table_name in varchar2, column_name in varchar2) return varchar2 is
    lv_tableName  user_tab_columns.TABLE_NAME%type := table_name;
    lv_columnName user_tab_columns.COLUMN_NAME%type := column_name;
    data_type varchar2(50);
    semanticLength number;
  begin
    select (case value
           when 'CHAR' then 4
           when 'BYTE' then 1
           end) into semanticLength from v$nls_parameters where parameter = 'NLS_LENGTH_SEMANTICS';

    select u.data_type || case u.data_type
                when 'NUMBER' then
                 nvl2(to_char(u.data_precision),('(' || u.data_precision || (case u.data_scale
                when 0 then
                 ''
                else
                 ',' || to_char(u.data_scale)
              end) || ')'), '')
              when 'CHAR' then '(' || to_char(u.data_length/semanticLength) || ')'
              when 'VARCHAR2' then '(' || to_char(u.data_length/semanticLength) || ')' else '' end into data_type
      from   user_tab_columns u
      where  table_name = lv_tableName
      and    column_name = lv_columnName;
    return data_type;
  end get_column_datatype;

  function get_index_columns(table_name in varchar2, index_name in varchar2) return varchar2 is
    ind_columns varchar2(2000);
    lv_tableName user_ind_columns.TABLE_NAME%type := table_name;
    lv_indexName user_ind_columns.INDEX_NAME%type := index_name;
    cursor get_ind_columns is
      select column_name || ' ' || descend as column_name
      from   user_ind_columns
      where  table_name = lv_tableName
      and    index_name = lv_indexName
      order  by column_position;
  begin
    for rec in get_ind_columns loop
      if (ind_columns is null) then
        ind_columns := rec.column_name;
      else
        ind_columns := ind_columns || ', ' || rec.column_name;
      end if;
    end loop;
    return ind_columns;
  end get_index_columns;

  function get_constraint_condition(constr_name in varchar2) return varchar2 is
    constr_condition varchar2(1000);
    lv_constrName user_cons_columns.CONSTRAINT_NAME%type := constr_name;
  begin
    select search_condition into constr_condition from user_constraints u where u.constraint_name = lv_constrName;
    return constr_condition;
  exception
    when others then
      return '';
  end get_constraint_condition;

  function get_constraint_columns(table_name in varchar2, constr_name in varchar2) return varchar2 is
    constr_columns varchar2(2000);
    lv_tableName user_cons_columns.TABLE_NAME%type := table_name;
    lv_constrName user_cons_columns.CONSTRAINT_NAME%type := constr_name;
    cursor get_constr_columns is
      select column_name
      from   user_cons_columns
      where  table_name = lv_tableName
      and    constraint_name = lv_constrName
      order  by position;
  begin
    for rec in get_constr_columns loop
      if (constr_columns is null) then
        constr_columns := rec.column_name;
      else
        constr_columns := constr_columns || ', ' || rec.column_name;
      end if;
    end loop;
    return constr_columns;
  end get_constraint_columns;

	function get_table_columns(table_name in varchar2) return columntabtype is
    table_columns columntabtype;
    col_rec colrec;
    lv_tableName user_tab_columns.TABLE_NAME%type := table_name;
    n number := 1;
    dataDefault varchar2(4000);
    lv_schemaName varchar2(50);
  begin
    select user into lv_schemaName from dual;
      for i in (select column_name, data_type, data_default, nullable, char_length, data_precision, data_scale,
                       decode(ut.char_used, 'C', 'CHAR', 'B', 'BYTE', null) char_used
                from user_tab_columns ut
                where ut.table_name = lv_tableName) loop
                  col_rec.column_name := i.column_name;
                  if (i.char_length != 0 and i.data_type like '%CHAR%') then
                     if (i.data_type like 'CHAR%' or i.data_type like 'VARCHAR%') then
                  	   col_rec.data_type := i.data_type || '(' || i.char_length || ' ' || i.char_used || ')';
                     else
                       col_rec.data_type := i.data_type || '(' || i.char_length || ')';
                     end if;
                  else
                  	if(i.data_type = 'NUMBER' and i.data_precision is not null and i.data_scale is not null and i.data_scale<>0) then
                  		col_rec.data_type := i.data_type || '(' || i.data_precision || ',' || i.data_scale || ')';
                  	else
                  		if(i.data_type = 'NUMBER' and i.data_precision is not null and (i.data_scale is null or i.data_scale = 0)) then
                  			col_rec.data_type := i.data_type || '(' || i.data_precision || ')';
                  		else
                  			col_rec.data_type := i.data_type;
                  		end if;
                  	end if;
                  end if;
                  dataDefault := translate(i.data_default, 'x"' || chr(10) || chr(13), 'x');
                  if (instr(dataDefault, (lv_schemaName || '.')) = 1) then
                    dataDefault := substr(dataDefault, length(lv_schemaName) + 2);
                  end if;
                  col_rec.default_value := dataDefault;
                  col_rec.nullable := i.nullable;
                  table_columns(n) := col_rec;
                  n := n + 1;
      end loop;
    return table_columns;
  end get_table_columns;

  function get_index_uniqueness(table_name in varchar2, index_name in varchar2) return varchar2 is
    indexUnique  varchar2(10);
    lv_indexName user_indexes.INDEX_NAME%type := index_name;
    lv_tableName user_indexes.TABLE_NAME%type := table_name;
  begin
    select uniqueness into indexUnique
      from   user_indexes
      where  table_name = lv_tableName
      and    index_name = lv_indexName;
    return indexUnique;
  end get_index_uniqueness;

  procedure create_constraint(procedure_name in varchar2,
                              table_name     in varchar2,
                              constrrec      in constraintrec,
                              tablespace     in varchar2 default null,
                              storage        in varchar2 default null) is
    constraint_name varchar2(50) := constrrec.constraint_name;
    tab_columns     columntabtype := constrrec.columns_names;
    constraint_type varchar2(12) := constrrec.constraint_type;
    stmnt_orig      varchar2(3000) := null;
    ok              number := 1;
    stmnt           varchar2(32000);
    chk_type        varchar2(20);
    old_columns     varchar2(4000);
    new_columns     varchar2(4000) := format_columns(tab_columns, 'CONSTRAINT');
    old_condition   varchar2(4000);
    temppos         number := 1;
    tempstr         varchar2(32000);
    identifier      user_tab_cols.COLUMN_NAME%type;
  begin
    stmnt_orig := constrrec.stmt;
    stmnt := tokenizer(temppos, 'TABLESPACE ', stmnt_orig);
    if (temppos > 0 and gv_tablespace is not null) then
      tempstr := tokenizer(temppos, ' ', stmnt_orig);
      tempstr := tokenizer(temppos, ' ', stmnt_orig);
      if (temppos > 0) then
         tempstr := substr(stmnt_orig, temppos);
      else
         tempstr := '';
      end if;
      stmnt_orig := trim(stmnt || 'TABLESPACE ' || gv_tablespace || ' ' || tempstr);
    end if;

    chk_type    := constraint_type;
    old_condition := null;
    if (constraint_type like 'U%') then
      chk_type := 'UNIQUE';
    elsif (constraint_type like 'P%') then
      chk_type := 'PRIMARY KEY';
    elsif (constraint_type like 'F%') then
      chk_type := 'FOREIGN KEY';
    elsif (constraint_type like 'C%') then
      chk_type := 'CHECK';
    end if;

    gv_latestScriptNo := get_last_script_for_drop_constraint(constraint_name, table_name);
    if(gv_currentScriptNo > 0 and gv_currentScriptNo < gv_latestScriptNo) then
      put_buffer('Create_Constraint: Constraint ' || constraint_name || ' skipped. Constraint is dropped ' || print_by_newer_script_message(gv_latestScriptNo));
      return;
    end if;

    if not (table_exist(table_name)) then
      put_buffer(procedure_name || ': Table ' || table_name || ' not exist.');
      ok := 0;
      raise_application_error(-20001, 'Constraint ' || constraint_name || ' not created.');
    elsif (constraint_exist(constraint_name, table_name)) then
      gv_latestScriptNo := get_last_script_for_constraint(constraint_name, table_name);
      if (gv_currentScriptNo > 0 and gv_currentScriptNo < gv_latestScriptNo) then
        put_buffer('Create_Constraint: Constraint ' || constraint_name ||
                   ' skipped. Constraint is already created ' || print_by_newer_script_message(gv_latestScriptNo));
        ok := 0;
      elsif (new_columns is null) then
        old_condition := get_constraint_condition(constraint_name);
        standardizeStatement(old_condition);
        tempstr := replace(formatstatement(old_condition, false), ' ');
        if (replace(stmnt_orig, ' ') = tempstr) then
          put_buffer('Create_Constraint: Constraint ' || constraint_name ||
                     ' already exist with same condition.');
          ok := 0;
        else
          put_buffer('Create_Constraint: Constraint ' || constraint_name || ' having condition: ' || stmnt_orig ||
                     ' already exist with a different condition: ' || old_condition);
          ok := 0;
          raise_application_error(-20001, 'Constraint ' || constraint_name || ' not created.');
        end if;
      else
        old_columns := upper(get_constraint_columns(table_name, constraint_name));
        if (replace(new_columns, '"') = old_columns) then
          put_buffer('Create_Constraint: Constraint ' || constraint_name ||
                     ' already exist with same columns.');
          ok := 0;
        else
          put_buffer('Create_Constraint: Constraint ' || constraint_name ||
                     ' already exist with different columns:' || chr(9) || old_columns);
          ok := 0;
          raise_application_error(-20001, 'Constraint ' || constraint_name || ' not created.');
        end if;
      end if;
    elsif (constraint_exist(constraint_name, null)) then
      put_buffer('Create_Constraint: Constraint ' || constraint_name ||
                 ' exists for other table with same name.');
      ok := 0;
      raise_application_error(-20001, 'Constraint ' || constraint_name || ' not created.');
    end if;
    if (ok = 1) then
      stmnt := 'ALTER TABLE ' || table_name || ' ADD CONSTRAINT ' || constraint_name || ' ' ||
                chk_type || ' (' || new_columns;
      if (stmnt_orig is not null) then
        if (chk_type = 'CHECK') then
          stmnt := stmnt || ' ' || stmnt_orig || ') ';
        else
          stmnt := stmnt || ') ' || stmnt_orig;
        end if;
      else
        stmnt := stmnt || ') ';
        stmnt := stmnt || ' USING INDEX  ';
        if (tablespace is not null) then
           if (gv_tablespace is not null) then
              stmnt := stmnt || ' TABLESPACE ' || gv_tablespace;
           else
              stmnt := stmnt || ' TABLESPACE ' || tablespace;
           end if;

        end if;
        if (storage is not null) then
          stmnt := stmnt || ' STORAGE (' || storage || ')';
        end if;
      end if;
      run_ddl_command(stmnt, procedure_name);
      put_buffer('Create_Constraint: Constraint ' || constraint_name || ' created.');
    end if;
  exception
    when invalid_identifier then
      identifier := get_invalid_identifier_from_sqlerrm(sqlerrm);
      gv_latestScriptNo := get_last_script_for_column(identifier, table_name);
      if(gv_currentScriptNo > 0 and gv_currentScriptNo < gv_latestScriptNo) then
        put_buffer('Create_Constraint: Constraint ' || constraint_name || ' not created. Column ' || identifier
                   || ' was modified ' || print_by_newer_script_message(gv_latestScriptNo));
      else
        raise;
      end if;
  end create_constraint;

  procedure create_index(table_name  in varchar2,
                         index_name  in varchar2,
                         tab_columns in columntabtype,
                         index_type  in varchar2 default 'N',
                         tablespace  in varchar2 default null,
                         storage     in varchar2 default null) is
    ok             number := 1;
    stmnt          varchar2(32000);
    chk_type       varchar2(20);
    old_columns    varchar2(2000);
    new_columns    varchar2(2000) := format_columns(tab_columns, 'INDEX');
    old_uniqueness varchar2(10);
    new_uniqueness varchar2(10);
    identifier     user_tab_cols.COLUMN_NAME%type;
    column_already_indexed exception;
    pragma exception_init(column_already_indexed, -1408);

  begin
    chk_type       := index_type;
    old_columns    := null;
    old_uniqueness := null;
    if (index_type like 'U%') then
      chk_type       := 'UNIQUE';
      new_uniqueness := 'UNIQUE';
    elsif (index_type like 'N%') then
      chk_type       := null;
      new_uniqueness := 'NONUNIQUE';
    end if;
    if not (table_exist(table_name)) then
      put_buffer('Create_Index: Table ' || table_name || ' not exist.');
      ok := 0;
      raise_application_error(-20001, 'Index ' || index_name || ' not created.');
    elsif (index_exist(index_name, table_name)) then
      gv_latestScriptNo := get_last_script_for_index(index_name, table_name);
      if (gv_currentScriptNo > 0 and gv_currentScriptNo < gv_latestScriptNo) then
        put_buffer('Create_Index: Index ' || index_name ||
                   ' skipped. Index is already created ' || print_by_newer_script_message(gv_latestScriptNo));
        ok := 0;
      else
        old_columns    := get_index_columns(table_name, index_name);
        old_uniqueness := get_index_uniqueness(table_name, index_name);
        if (old_columns = replace(new_columns, '"') and old_uniqueness = new_uniqueness) then
          put_buffer('Create_Index: Index ' || index_name ||
                     ' already exist with same uniqueness/columns.');
          ok := 0;
        else
          remove_indexes_hlp('Create_Index', table_name, index_name);
        end if;
      end if;
    elsif (index_exist(index_name, null)) then
      put_buffer('Create_Index: Index ' || index_name ||
                 ' exists for other table with same name.');
      ok := 0;
      raise_application_error(-20001, 'Index ' || index_name || ' not created.');
    end if;
    if (ok = 1) then
      stmnt := 'CREATE ' || chk_type || ' INDEX ' || index_name || ' ';
      stmnt := stmnt || ' ON ' || table_name || '(' || new_columns || ') ';
      if (tablespace is not null) then
        stmnt := stmnt || ' TABLESPACE ' || tablespace;
      end if;
      if (storage is not null) then
        stmnt := stmnt || ' STORAGE (' || storage || ')';
      end if;
      run_ddl_command(stmnt, 'Create_Index');
      put_buffer('Create_Index: Index ' || index_name || ' (' || new_columns || ') (' ||
                 new_uniqueness || ') created.');
    end if;
  exception
    when column_already_indexed then
      put_buffer('Create_Index: Columns (' || new_columns || ') for table ' || table_name ||
                 ' already indexed.');
    when invalid_identifier then
      identifier := get_invalid_identifier_from_sqlerrm(sqlerrm);
      gv_latestScriptNo := get_last_script_for_column(identifier, table_name);
      if(gv_currentScriptNo > 0 and gv_currentScriptNo < gv_latestScriptNo) then
        put_buffer('Create_Index: Index ' || index_name || ' not created. Column ' || identifier
                   || ' was modified ' || print_by_newer_script_message(gv_latestScriptNo));
      else
        raise;
      end if;
    when others then
      raise;
  end create_index;

  procedure create_sequence(sequence in varchar2, parameters in varchar2) is
    stmnt varchar2(2000);
    ok    number := 1;
  begin
    if (object_exist(sequence, 'SEQUENCE')) then
      put_buffer('Create_Sequence: Sequence ' || sequence || ' already exist.');
      ok := 0;
    end if;
    if (ok = 1) then
      stmnt := 'CREATE SEQUENCE ' || sequence || ' ' || parameters;
      run_ddl_command(stmnt, 'Create_Sequence');
      put_buffer('Create_Sequence: Sequence ' || sequence || ' created.');
    end if;
  end create_sequence;

  procedure create_type(type in varchar2, parameters in varchar2) is
    stmnt varchar2(2000);
  begin
    stmnt := 'CREATE OR REPLACE TYPE ' || type || ' ' || parameters;
    run_ddl_command(stmnt, 'Create_Type');
    put_buffer('Create_Type: Type ' || type || ' created.');
  end create_type;

  procedure create_table(objname in varchar2, stmt in varchar2) is
  begin
    if (table_exist(objname)) then
      put_buffer('Create_table: Table ' || objname || ' already exist.');
    else
      run_ddl_command(stmt, 'Create_table');
      put_buffer('Create_table: Table ' || objname || ' created.');
    end if;
  end create_table;

  procedure create_table(table_name  in varchar2,
                         tab_columns in columntabtype,
                         tablespace  in varchar2 default null,
                         storage     in varchar2 default null,
                         other       in varchar2 default null) is
    stmnt varchar2(32000);
		old_tab_columns columntabtype := get_table_columns(table_name);
  begin
    if not (table_exist(table_name)) then
      stmnt := 'CREATE TABLE ' || table_name || ' ( ';
      stmnt := stmnt || format_columns(tab_columns, 'TABLE');
      stmnt := stmnt || ') ';
      if (tablespace is not null) then
        stmnt := stmnt || ' TABLESPACE ' || tablespace;
      end if;
      if (storage is not null) then
        if storage like '%INITIAL%' then
          stmnt := stmnt || ' STORAGE (' || storage || ')';
        else
          stmnt := stmnt || ' STORAGE (INITIAL ' || storage || ')';
        end if;
      end if;
      if (other is not null) then
        stmnt := stmnt || ' ' || other;
      end if;
      run_ddl_command(stmnt, 'Create_Table');
      put_buffer('Create_Table: Table ' || table_name || ' created.');
    else
			if (table_exist(table_name)) then
        gv_latestScriptNo := get_last_script_for_table(table_name);
				if(gv_currentScriptNo > 0 and gv_currentScriptNo < gv_latestScriptNo) then
					put_buffer('Create_Table: Table ' || table_name || ' skipped. Table was modified ' || print_by_newer_script_message(gv_latestScriptNo));
				else
					if not (columns_compare(old_tab_columns, tab_columns)) then
						put_buffer('Create_Table: Table ' || table_name || ' exists but is different');
						raise_application_error(-20001, 'Table '|| table_name || ' not created');
					else
						put_buffer('Create_Table: Table ' || table_name || ' already exists.');
					end if;
				end if;
			end if;
		end if;
  exception
    when others then
      raise;
  end create_table;

  procedure create_comment(table_name in varchar2, column_name in varchar2, comment_txt in varchar2) is
    stmnt varchar2(32000);
  begin
    if (table_exist(table_name)) then
      if (column_exist(table_name, column_name)) then
        stmnt := 'COMMENT ON COLUMN ' || table_name || '.' || column_name || ' IS ''' ||
                  comment_txt || '''';
        run_ddl_command(stmnt, 'Create_Comment');
        put_buffer('Comment_on: Table ' || table_name || ' column ' || column_name ||
                   ' created.');
      else
        gv_latestScriptNo := get_last_script_for_column(column_name, table_name);
        if(gv_currentScriptNo > 0 and gv_currentScriptNo < gv_latestScriptNo) then
          put_buffer('Comment_on: Table ' || table_name || ' column ' || column_name || ' skipped. Column was modified ' || print_by_newer_script_message(gv_latestScriptNo));
        else
          put_buffer('Comment_on: Table ' || table_name || ' column ' || column_name ||
                     ' not exists.');
          raise_application_error(-20001,
                                  'Comment on table ' || table_name || ' column ' || column_name ||
                                   ' not created.');
        end if;
      end if;
    else
      put_buffer('Comment_on: Table ' || table_name || ' not exists.');
      raise_application_error(-20001,
                              'Comment on table ' || table_name || ' column ' || column_name ||
                               ' not created.');
    end if;
  end create_comment;

	procedure create_directory(dir_name in varchar2, dir_path in varchar2) is
   stmnt varchar2(32000);
     begin
      stmnt := 'CREATE OR REPLACE DIRECTORY'|| ' ' || dir_name || ' ';
      stmnt := stmnt || dir_path ;
      run_ddl_command(stmnt, 'Create_directory');
      put_buffer('Create_directory: Directory ' || dir_name || ' created.');
     exception
       when others then
         raise;
  end create_directory;

  procedure create_synonym(synonym_name in varchar2,synonym_details in varchar2,synonym_type in varchar2) is
   stmnt varchar2(32000);
     begin
      stmnt := 'CREATE OR REPLACE ' || synonym_type || ' ' || 'SYNONYM'|| ' ' || synonym_name || ' ';
      stmnt := stmnt || synonym_details ;
      run_ddl_command(stmnt, 'Create_synonym');
      put_buffer('Create_synonym: Synonym created for ' || synonym_name || '.');
     exception
       when others then
         raise;
  end create_synonym;

  procedure alter_table_column(table_name in varchar2,
                               alter_type in varchar2,
                               column     in colrec) is
    chk_alter_type varchar2(20);
    old_nullable   varchar2(1);
    old_data_type  varchar2(50);
    ok             number := 1;
    stmnt          varchar2(10000);
    col_string     user_tab_columns.DATA_DEFAULT%type;
    wrong_alter_type exception;
    modify_exception exception;
    pragma exception_init(modify_exception, -1463);
    pragma exception_init(modify_exception, -1451);
  begin
    if not (table_exist(table_name)) then
      put_buffer('Alter_Table_Column: Table ' || table_name || ' not exist.');
      ok := 0;
      raise_application_error(-20001, 'Alter_Table_Column ' || table_name || ' not executed.');
    else
      chk_alter_type := alter_type;
      if (alter_type like 'A%') then
        chk_alter_type := 'ADD';
      elsif (alter_type like 'R%') then
        chk_alter_type := 'RENAME COLUMN';
      elsif (alter_type like 'M%') then
        chk_alter_type := 'MODIFY';
      elsif (alter_type like 'D%') then
        chk_alter_type := 'DROP COLUMN';
      else
        raise wrong_alter_type;
      end if;
      if (chk_alter_type = 'ADD') then
        if (column_exist(table_name, column.column_name)) then
          put_buffer('Alter_Table_Column: Table/Column ' || table_name || '/' ||
                     column.column_name || ' already exist.');
          ok := 0;
        end if;
      elsif (chk_alter_type = 'RENAME COLUMN') then
        -- the new name for the column is passed in default_value field
        if (column_exist(table_name, column.default_value)) then
          put_buffer('Alter_Table_Column: Table/Column ' || table_name || '/' ||
                     column.default_value || ' already exist.');
          ok := 0;
        elsif not (column_exist(table_name, column.column_name)) then
          put_buffer('Alter_Table_Column: Table/Column ' || table_name || '/' ||
                     column.column_name || ' not exist.');
          ok := 0;
          raise_application_error(-20001, 'Alter_Table_Column ' || table_name || ' not executed.');
        end if;
      elsif (chk_alter_type = 'MODIFY') then
        if not (column_exist(table_name, column.column_name)) then
          put_buffer('Alter_Table_Column: Table/Column ' || table_name || '/' ||
                     column.column_name || ' not exist.');
          ok := 0;
          raise_application_error(-20001, 'Alter_Table_Column ' || table_name || ' not executed.');
        else
          if (column.data_type is not null) then
            old_data_type := get_column_datatype(table_name, column.column_name);
            if (old_data_type = replace(column.data_type, ' ', '')) then
              put_buffer('Alter_Table_Column: Table/Column ' || table_name || '/' ||
                         column.column_name || ' has the same datatype.');
              ok := 0;
            end if;
          end if;
          if (column.nullable is not null) then
            old_nullable := get_column_nullable(table_name, column.column_name);
            if (column.nullable = 'Y' and old_nullable = 'Y') then
              put_buffer('Alter_Table_Column: Table/Column ' || table_name || '/' ||
                         column.column_name || ' is already nullable.');
              ok := 0;
            elsif (column.nullable = 'N' and old_nullable = 'N') then
              put_buffer('Alter_Table_Column: Table/Column ' || table_name || '/' ||
                         column.column_name || ' is already not nullable.');
              ok := 0;
            else
              ok := 1;
            end if;
          end if;
        end if;
      elsif (chk_alter_type = 'DROP COLUMN') then
        if not (column_exist(table_name, column.column_name)) then
          put_buffer('Alter_Table_Column: Table/Column ' || table_name || '/' ||
                     column.column_name || ' not exist.');
          ok := 0;
        end if;
      end if;
    end if;
    if (ok = 1) then
      if (chk_alter_type = 'DROP COLUMN') then
        col_string := format_column(column, 'INDEX');
      elsif (chk_alter_type = 'RENAME COLUMN') then
        col_string := format_column(column, 'RENAME');
      else
        col_string := '(' || format_column(column, 'TABLE') || ')';
      end if;
      stmnt := 'ALTER TABLE ' || table_name || ' ' || chk_alter_type || ' ' || col_string;
      run_ddl_command(stmnt, 'Alter_Table_Column');
      if (chk_alter_type = 'ADD') then
        put_buffer('Alter_Table_Column: Column ' || column.column_name || ' in table ' ||
                   table_name || ' is added.');
      elsif (chk_alter_type = 'RENAME COLUMN') then
        put_buffer('Alter_Table_Column: Column ' || column.column_name || ' in table ' ||
                   table_name || ' is renamed.');
      elsif (chk_alter_type = 'MODIFY') then
        put_buffer('Alter_Table_Column: Column ' || column.column_name || ' in table ' ||
                   table_name || ' is modified.');
      elsif (chk_alter_type = 'DROP COLUMN') then
        put_buffer('Alter_Table_Column: Column ' || column.column_name || ' in table ' ||
                   table_name || ' dropped.');
      end if;
    end if;
  exception
    when modify_exception then
      put_buffer('Alter_Table_Column: Table ' || table_name || '. Statement: ' || stmnt);
      put_buffer(sqlcode || ' ' || sqlerrm);
      raise;
    when wrong_alter_type then
      put_buffer('Alter_Table_Column: Wrong alter type, alter type must be ''A[LTER]'',''M[ODIFY]'' or ''D[ROP COLUMN]''.');
      raise;
    when others then
      raise;
  end alter_table_column;

  procedure alter_table(table_name  in varchar2,
                        alter_type  in varchar2,
                        tab_columns in columntabtype,
                        constraints in constrtabtype) is
    column colrec;
    constr constraintrec;
  begin
    if (tab_columns.count > 0) then
      for i in tab_columns.first .. tab_columns.last loop
        column := tab_columns(i);
        alter_table_column(table_name, alter_type, column);
      end loop;
    end if;
    if (constraints.count > 0) then
      for i in constraints.first .. constraints.last loop
        constr := constraints(i);
        if (alter_type = 'ADD' or alter_type = 'MODIFY') then
          create_constraint('Alter_Table ' || table_name, table_name, constr, null, null);

        elsif (alter_type = 'DROP') then
          if (constraint_exist(constr.constraint_name, table_name)) then
            remove_constraint('Alter_Table ' || table_name, table_name, constr);
          elsif (constraint_exist(constr.constraint_name, null)) then
            put_buffer('Alter_Table generates warning when executing: ');
            put_buffer('Alter_Table ' || table_name || ': Constraint ' || constr.constraint_name ||
                       ' exists for another table.');
          else
            put_buffer('Alter_Table ' || table_name || ': Constraint ' || constr.constraint_name ||
                       ' not exist.');
          end if;
        end if;
      end loop;
    end if;
  end alter_table;

  function process_createobject(procscript varchar2, strinit varchar2, objname varchar2)
    return boolean is
    allok boolean := true;
  begin
    if (object_exist(objname, strinit)) then
      put_buffer('Create_or_replace ' || strinit || ': ' || strinit || ' ' || objname ||
                 ' already exist and will be replaced.');
    end if;
    begin
      run_ddl_command(procscript, 'Create_or_replace: ' || strinit || ' ' || objname);
      put_buffer('Create_or_replace: ' || strinit || ' ' || objname || ' created.');
    exception
      when others then
        put_buffer('Error: ' || sqlerrm);
        allok := false;
    end;
    return allok;
  end process_createobject;

  function process_commandexecute(tokenpos in out number, scriptstmt varchar2) return boolean is
    tempstr varchar2(400);
    objname varchar2(50);
    allok   boolean := true;
  begin
    tempstr := tokenizer(tokenpos, ' ', scriptstmt);
    if (instr(tempstr, '(') > 0) then
      objname := trim(substr(tempstr, 1, instr(tempstr, '(') - 1));
      tempstr := '';
    elsif (instr(tempstr, '.') > 0) then
      objname := trim(substr(tempstr, 1, instr(tempstr, '.') - 1));
      tempstr := 'Package';
    else
      objname := tempstr;
      tempstr := '';
    end if;
    if (not object_exist(objname, tempstr)) then
      put_buffer('Execute ' || objname || ': ' || objname || ' not exist.');
      allok := false;
    else
      tempstr := substr(scriptstmt, 9);
      run_ddl_command('begin ' || tempstr || '; end;', 'Execute ' || objname);
      put_buffer('Execute ' || objname || ': ' || objname || ' executed successfully.');
    end if;
    return allok;
  end process_commandexecute;

  function process_alterpackage(tokenpos in out number, scriptstmt varchar2) return boolean is
    objname varchar2(50);
    allok   boolean := true;
  begin
    objname := tokenizer(tokenpos, ' ', scriptstmt);
    if (not object_exist(objname, 'PACKAGE')) then
      put_buffer('Alter package ' || objname || ': ' || objname || ' not exist.');
      allok := false;
    else
      run_ddl_command(scriptstmt, 'Alter package ' || objname);
      put_buffer('Alter package ' || objname || ': ' || objname || ' compiled.');
    end if;
    return allok;
  end process_alterpackage;

  function process_rename(tokenpos in out number, scriptstmt varchar2) return boolean is
    objname varchar2(50);
    objtype varchar2(50);
    tempstr varchar2(50);
    allok   boolean := true;
    temppos number;
  begin
    objname := tokenizer(tokenpos, ' ', scriptstmt);
    temppos := instr(scriptstmt, ' RENAME ') + 8;
    objtype := tokenizer(temppos, ' ', scriptstmt);
    if (objtype <> 'CONSTRAINT') then
       objtype := 'ANY';
    end if;

    if (object_exist(objname, objtype)) then
      temppos := instr(scriptstmt, ' TO ') + 4;
      tempstr := tokenizer(temppos, ' ', scriptstmt);
      if (object_exist(tempstr, objtype)) then
        put_buffer('Rename: Object ' || tempstr || ' already exist.');
      else
        begin
          run_ddl_command(scriptstmt, 'Rename: Object ' || objname);
          put_buffer('Rename: Object ' || objname || ' - object renamed.');
        exception
          when others then
            put_buffer('Rename: Object ' || objname || ' error: ' || sqlerrm);
        end;
      end if;
    else
      temppos := instr(scriptstmt, ' TO ') + 4;
      tempstr := tokenizer(temppos, ' ', scriptstmt);
      if (object_exist(tempstr, objtype)) then
        put_buffer('Rename: Object ' || tempstr || ' already exist.');
      else
        put_buffer('Rename: Object ' || objname || ' not exist.');
      end if;
    end if;
    return allok;
  end process_rename;

  function process_createtable(tokenpos in out number, scriptstmt varchar2) return boolean is
    tempstr    varchar2(10000);
    objname    varchar2(50);
    param      varchar2(8000);
    temppos    number;
    tblspace   varchar2(100);
    storage    varchar2(100);
    columnstab columntabtype;
    columnrec  colrec;
    constrtab  constrtabtype;
    constrrec  constraintrec;
    columninfo varchar2(400);
    allok      boolean := true;
  begin
    objname := tokenizer(tokenpos, ' ', scriptstmt);
    tempstr := trim(substr(scriptstmt, tokenpos));
    if (instr(tempstr, 'AS ') = 1) then
      tempstr := trim(substr(tempstr, 3));
    end if;
    tokenpos := 1;
    while (tokenpos != 0) loop
      param := tokenizer(tokenpos, ' ', tempstr);
      while (not checkconsistenttoken(param)) loop
        param := param || ' ' || tokenizer(tokenpos, ' ', tempstr);
      end loop;
      exit;
    end loop;
    param := substr(param, instr(param, '(') + 1, instr(param, ')', -1) - instr(param, '(') - 1);
    if (instr(param, 'SELECT ') > 0) then
      tempstr := 'CREATE TABLE ' || objname || ' AS (' || param || ')';
      create_table(objname, tempstr);
    else
      tokenpos := 1;
      while (tokenpos != 0) loop
        temppos    := 1;
        columninfo := '';
        -- columnInfo has info for a ColRec object
        while temppos != 0 loop
          columninfo := columninfo || ' ' || tokenizer(tokenpos, ',', param);
          while (not checkconsistenttoken(columninfo)) loop
            columninfo := columninfo || ',' || tokenizer(tokenpos, ',', param);
          end loop;
          tempstr := substr(param, tokenpos + 1, 10);
          if (tempstr != 'CONSTRAINT') then
            temppos := 0;
          end if;
        end loop;
        columnrec := format_to_colrec(trim(columninfo));
        if (columnrec.constr is not null) then
          constrrec := format_to_constraintrec(trim(columnrec.constr));
          set_table_constraint(constrtab, constrrec);
        end if;
        set_table_column(columnstab, columnrec);
      end loop;
      tempstr := '';
      temppos := instr(scriptstmt, ' TABLESPACE ');
      if (temppos = 0) then
        temppos := instr(scriptstmt, ' STORAGE ');
        if (temppos = 0) then
          temppos := instr(scriptstmt, ' NESTED ');
        end if;
      end if;
      if (temppos > 0) then
        tempstr := substr(scriptstmt, temppos);
      end if;
      create_table(objname, columnstab, tblspace, storage, tempstr);
      for i in 1 .. constrtab.count loop
        constrrec := constrtab(i);
        create_constraint('Create_Table ' || objname, objname, constrrec, null, null);
      end loop;
    end if;
    return allok;
  end process_createtable;

  function process_altertable(tokenpos in out number, scriptstmt varchar2) return boolean is
    tempstr    varchar2(10000);
    objname    varchar2(50);
    param      varchar2(8000);
    columnstab columntabtype;
    columnrec  colrec;
    constrtab  constrtabtype;
    constrrec  constraintrec;
    columninfo varchar2(400);
    allok      boolean := true;
  begin
    objname := tokenizer(tokenpos, ' ', scriptstmt);
    tempstr := tokenizer(tokenpos, ' ', scriptstmt);
    -- ADDING
    if (tempstr = 'ADD') then
      tokenpos := 1;
      param    := trim(substr(scriptstmt, instr(scriptstmt, ' ADD ') + 4));
      if (instr(param, '(') = 1) then
        param := trim(substr(param, instr(param, '(') + 1,
                             instr(param, ')', -1) - instr(param, '(') - 1));
      end if;
      tempstr := tokenizer(tokenpos, ' ', param);
      if (tempstr = 'CONSTRAINT') then
        --- ADD CONSTRAINT
        constrrec := format_to_constraintrec(trim(substr(param, instr(param, ' '))));
        set_table_constraint(constrtab, constrrec);
      else
        --- ADD COLUMN(S)
        tokenpos := 1;
        while (tokenpos != 0) loop
          -- columnInfo has info for a ColRec object
          columninfo := tokenizer(tokenpos, ',', param);
          while (not checkconsistenttoken(columninfo)) loop
            columninfo := columninfo || ',' || tokenizer(tokenpos, ',', param);
          end loop;
          columnrec := format_to_colrec(columninfo);
          if (columnrec.constr is not null) then
            constrrec := format_to_constraintrec(columnrec.constr);
            set_table_constraint(constrtab, constrrec);
          end if;
          set_table_column(columnstab, columnrec);
        end loop;
      end if;
      alter_table(objname, 'ADD', columnstab, constrtab);
      -- MODIFYING
    elsif (tempstr = 'MODIFY') then
      param := trim(substr(scriptstmt, instr(scriptstmt, ' MODIFY ') + 7));
      if (instr(param, '(') = 1) then
        param := trim(substr(param, instr(param, '(') + 1,
                             instr(param, ')', -1) - instr(param, '(') - 1));
      end if;
      tokenpos := 1;
      while (tokenpos != 0) loop
        -- columnInfo has info for a ColRec object
        columninfo := tokenizer(tokenpos, ',', param);
        while (not checkconsistenttoken(columninfo)) loop
          columninfo := columninfo || ',' || tokenizer(tokenpos, ',', param);
        end loop;

        columnrec := format_to_colrec(columninfo);
        if (columnrec.constr is not null) then
          constrrec := format_to_constraintrec(columnrec.constr);
          set_table_constraint(constrtab, constrrec);
        end if;
        set_table_column(columnstab, columnrec);
      end loop;
      alter_table(objname, 'MODIFY', columnstab, constrtab);
      -- DROPPING
    elsif (tempstr = 'DROP') then
      tokenpos := 1;
      param    := trim(substr(scriptstmt, instr(scriptstmt, ' DROP ') + 6));
      tempstr  := tokenizer(tokenpos, ' ', param);
      if (tempstr = 'CONSTRAINT') then
        --- DROP CONSTRAINT
        constrrec.constraint_name := tokenizer(tokenpos, ' ', param);
        constrrec.stmt := tokenizer(tokenpos, ' ', param);
        set_table_constraint(constrtab, constrrec);
      elsif (tempstr = 'COLUMN') then
        --- DROP COLUMN
        columnrec.column_name := tokenizer(tokenpos, ' ', param);
        if (tokenpos > 0) then
           columnrec.data_type := trim(substr(param, tokenpos));
        end if;
        set_table_column(columnstab, columnrec);
      elsif (tempstr = 'PRIMARY') then
        --- DROP PRIMARY KEY
        begin
          select a.constraint_name into tempstr from user_constraints a where a.table_name like objname and a.constraint_type = 'P';
          constrrec.constraint_name := tempstr;
          set_table_constraint(constrtab, constrrec);
        exception
          when no_data_found then
            put_buffer('Alter_Table: Primary key for table ' || objname || ' not exist.');
            return true;
        end;
      elsif (tempstr = 'UNUSED') then
        tokenpos := 1;
        param    := trim(substr(scriptstmt, instr(scriptstmt, ' DROP UNUSED ') + 13));
        tempstr  := tokenizer(tokenpos, ' ', param);
        if (tempstr = 'COLUMNS') then
          --- DROP UNUSED COLUMN
          run_ddl_command(scriptstmt, 'Alter_Table: Drop unused columns ');
          put_buffer('Alter_Table: Table ' || objname || ' - Unused columns dropped.');
        end if;
      else
        raise untreated_statement;
      end if;
      alter_table(objname, 'DROP', columnstab, constrtab);
      -- SET (UNUSED)
    elsif (tempstr = 'SET') then
      tokenpos := 1;
      param    := trim(substr(scriptstmt, instr(scriptstmt, ' SET ') + 5));
      tempstr  := tokenizer(tokenpos, ' ', param);
      if (tempstr = 'UNUSED') then
        tokenpos := 1;
        param    := trim(substr(scriptstmt, instr(scriptstmt, ' SET UNUSED ') + 12));
        tempstr  := tokenizer(tokenpos, ' ', param);
        if (tempstr = 'COLUMN') then
          --- SET UNUSED COLUMN
          columnrec.column_name := tokenizer(tokenpos, ' ', param);
          if not (column_exist(objname, columnrec.column_name)) then
            put_buffer('Alter_Table_Column: Table/Column ' || objname || '/' ||
                       columnrec.column_name || ' not exist.');
          else
            run_ddl_command(scriptstmt, 'Alter_Table_Column: Table/Column ' || objname || '/' || columnrec.column_name);
            put_buffer('Alter_Table_Column: Table/Column ' || objname || '/' || columnrec.column_name || ' - column set to unused.');
          end if;
        else
          raise untreated_statement;
        end if;
        allok := true;
      end if;
      -- RENAMING
    elsif (tempstr = 'RENAME') then
      --tokenpos := 1;
      param    := trim(substr(scriptstmt, instr(scriptstmt, ' RENAME ') + 8));
      tempstr  := tokenizer(tokenpos, ' ', scriptstmt);
      if (tempstr = 'COLUMN') then
        -- RENAMING COLUMN
        tokenpos := 8;
        columnrec.column_name   := tokenizer(tokenpos, ' ', param);
        tempstr                 := tokenizer(tokenpos, ' ', param);
        columnrec.default_value := tokenizer(tokenpos, ' ', param);
        set_table_column(columnstab, columnrec);
      elsif (tempstr = 'CONSTRAINT') then
        -- RENAMING CONSTRAINT
        allok := process_rename(tokenpos, scriptstmt);
      else
        -- RENAMING TABLE
        tokenpos := 12;
        allok := process_rename(tokenpos, scriptstmt);
      end if;
      alter_table(objname, 'RENAME', columnstab, constrtab);
    -- READ ONLY
    elsif (tempstr = 'READ') then
      if (table_exist(objname)) then
        tempstr  := tokenizer(tokenpos, ' ', scriptstmt);
        if (tempstr = 'ONLY') then
          select read_only into tempstr from user_tables a where a.TABLE_NAME = objname;
          if (tempstr = 'NO') then
            run_ddl_command(scriptstmt, 'Alter_Table: Make table ' || objname || ' read only.');
            put_buffer('Alter_Table: Table ' || objname || ' was made read only.');
          else
            put_buffer('Alter_Table: Table ' || objname || ' is already read only.');
          end if;
        elsif (tempstr = 'WRITE') then
          select read_only into tempstr from user_tables a where a.TABLE_NAME = objname;
          if (tempstr = 'YES') then
            run_ddl_command(scriptstmt, 'Alter_Table: Make table ' || objname || ' read/write.');
            put_buffer('Alter_Table: Table ' || objname || ' was made read/write.');
          else
            put_buffer('Alter_Table: Table ' || objname || ' is already read/write.');
          end if;
        else
          raise untreated_statement;
        end if;
      else
        put_buffer('Alter_Table: Table ' || objname || ' not exist.');
      end if;
    else
      raise untreated_statement;
    end if;
    return allok;
  end process_altertable;

  function process_alterindex(tokenpos in out number, scriptstmt varchar2) return boolean is
    tempstr varchar2(10000);
    temppos number;
    objname varchar2(50);
    allok   boolean := true;
  begin
    objname := tokenizer(tokenpos, ' ', scriptstmt);
    tempstr := tokenizer(tokenpos, ' ', scriptstmt);
    if (not object_exist(objname, 'INDEX')) then
      put_buffer('Alter index: Index ' || objname || ' not exist.');
    else
      begin
        -- RENAMING
        if (tempstr = 'RENAME') then
          temppos := instr(scriptstmt, ' TO ') + 4;
          tempstr := tokenizer(temppos, ' ', scriptstmt);
          if (object_exist(tempstr, 'ANY')) then
            put_buffer('Alter index: Object ' || tempstr || ' already exist.');
          else
            run_ddl_command(scriptstmt, 'Alter index: Index ' || objname);
            put_buffer('Alter index: Index ' || objname || ' - index renamed.');
          end if;
        elsif (tempstr = 'REBUILD') then
            run_ddl_command(scriptstmt, 'Alter index: Index ' || objname);
            put_buffer('Alter index: Index ' || objname || ' - index rebuilt.');
        end if;
      exception
        when others then
          put_buffer('Alter index: Index ' || objname || ' error: ' || sqlerrm);
      end;
    end if;
    return allok;
  end process_alterindex;

  function process_createindex(tokenpos in out number, scriptstmt varchar2, strinit varchar2)
    return boolean is
    tempstr    varchar2(4000);
    temppos    number;
    objname    varchar2(50);
    param      varchar2(8000);
    typestr    varchar2(10);
    namestr    varchar2(50);
    tblspace   varchar2(100);
    storage    varchar2(100);
    columnstab columntabtype;
    columnrec  colrec;
    columninfo varchar2(400);
    allok      boolean := true;
  begin
    if (strinit = 'CREATE UNIQUE') then
      tempstr := tokenizer(tokenpos, ' ', scriptstmt);
      if (tempstr != 'INDEX') then
        raise untreated_statement;
      end if;
      typestr := 'UNIQUE';
    else
      typestr := 'NON UNIQUE';
    end if;
    namestr  := tokenizer(tokenpos, ' ', scriptstmt);
    tempstr  := tokenizer(tokenpos, ' ', scriptstmt);
    objname  := tokenizer(tokenpos, ' ', scriptstmt);
    tempstr  := substr(scriptstmt, tokenpos);
    tokenpos := 1;
    while (tokenpos != 0) loop
      param := tokenizer(tokenpos, ' ', tempstr);
      while (not checkconsistenttoken(param)) loop
        param := param || ' ' || tokenizer(tokenpos, ' ', tempstr);
      end loop;
      exit;
    end loop;
    param    := trim(substr(param, instr(param, '(') + 1,
                            instr(param, ')', -1) - instr(param, '(') - 1));
    tokenpos := 1;
    while (tokenpos != 0) loop
      -- columnInfo has info for a ColRec object
      columninfo := trim(tokenizer(tokenpos, ',', param));
      while (not checkconsistenttoken(columninfo)) loop
        columninfo := columninfo || ',' || tokenizer(tokenpos, ',', param);
      end loop;
      if (instr(columninfo, ' ') > 0) then
        columnrec.column_name := substr(columninfo, 1, instr(columninfo, ' ') - 1);
        columnrec.data_type   := substr(columninfo, instr(columninfo, ' ') + 1);
      else
        columnrec.column_name := columninfo;
        columnrec.data_type   := 'ASC';
      end if;
      set_table_column(columnstab, columnrec);
    end loop;
    temppos := instr(scriptstmt, ' TABLESPACE ');
    if (temppos > 0 and gv_tablespace is null) then
      temppos  := temppos + 11;
      tblspace := tokenizer(temppos, ' ', scriptstmt);
    else
      tblspace := gv_tablespace;
    end if;
    temppos := instr(scriptstmt, ' STORAGE ');
    if (temppos > 0) then
      temppos := instr(scriptstmt, '(', temppos);
      storage := substr(scriptstmt, temppos + 1, instr(scriptstmt, ')', temppos) - temppos - 1);
    end if;
    create_index(objname, namestr, columnstab, typestr, tblspace, storage);
    return allok;
  end process_createindex;

  function process_dropindex(tokenpos in out number, scriptstmt varchar2) return boolean is
    namestr varchar2(50);
    allok   boolean := true;
  begin
    namestr := tokenizer(tokenpos, ' ', scriptstmt);
    remove_indexes('%', namestr);
    return allok;
  end process_dropindex;

  function process_droptable(tokenpos in out number, scriptstmt varchar2) return boolean is
    tempstr varchar2(400);
    objname varchar2(50);
    allok   boolean := true;
  begin
    objname := tokenizer(tokenpos, ' ', scriptstmt);
    if (tokenpos > 0) then
      tempstr := trim(substr(scriptstmt, tokenpos));
    else
      tempstr := '';
    end if;
    remove_table(objname, tempstr);
    return allok;
  end process_droptable;

  function process_dropobject(object_type varchar2, tokenpos in out number, scriptstmt varchar2)
    return boolean is
    objname varchar2(50);
    allok   boolean := true;
  begin
    objname := tokenizer(tokenpos, ' ', scriptstmt);
    remove_object(objname, object_type);
    return allok;
  end process_dropobject;

  function process_createsequence(tokenpos in out number, scriptstmt varchar2) return boolean is
    objname varchar2(50);
    param   varchar2(8000);
    allok   boolean := true;
  begin
    objname := tokenizer(tokenpos, ' ', scriptstmt);
    param   := trim(substr(scriptstmt, tokenpos));
    create_sequence(objname, param);
    return allok;
  end process_createsequence;

  function process_createcomment(tokenpos in out number, scriptstmt varchar2) return boolean is
    tempstr varchar2(32767);
    objname varchar2(50);
    param   varchar2(8000);
    namestr varchar2(50);
    allok   boolean := true;
  begin
    tempstr := tokenizer(tokenpos, ' ', scriptstmt);
    if (tempstr = 'COLUMN') then
      objname := trim(tokenizer(tokenpos, '.', scriptstmt));
      namestr := tokenizer(tokenpos, ' ', scriptstmt);
      param   := trim(substr(scriptstmt, instr(scriptstmt, '''') + 1,
                             instr(scriptstmt, '''', -1) - instr(scriptstmt, '''') - 1));
      create_comment(objname, namestr, param);
    end if;
    return allok;
  end process_createcomment;

	function process_createdirectory(tokenpos in out number, scriptstmt varchar2) return boolean is
    objname varchar2(50);
    param   varchar2(8000);
    allok   boolean := true;
  begin
    objname := tokenizer(tokenpos, ' ', scriptstmt);
    param   := trim(substr(scriptstmt, tokenpos));
    create_directory(objname, param);
    return allok;
  end process_createdirectory;

  function process_createsynonym(tokenpos in out number, scriptstmt varchar2, typesyn varchar2) return boolean is
    objname varchar2(50);
    param   varchar2(8000);
    allok   boolean := true;
  begin
    objname := tokenizer(tokenpos, ' ', scriptstmt);
    param   := trim(substr(scriptstmt, tokenpos));
    if (typesyn = 'PUBLIC') then
       create_synonym(objname,param,'PUBLIC');
    else
      create_synonym(objname,param,'');
    end if;
    return allok;
  end process_createsynonym;

  function process_createtype(tokenpos in out number, scriptstmt varchar2) return boolean is
    objname varchar2(50);
    param   varchar2(8000);
    allok   boolean := true;
  begin
    objname := tokenizer(tokenpos, ' ', scriptstmt);
    param   := trim(substr(scriptstmt, tokenpos));
    create_type(objname, param);
    return allok;
  end process_createtype;

  function process_updatetable(tokenpos in out number, scriptstmt varchar2) return boolean is
    objname varchar2(50);
    allok   boolean := true;
  begin
    objname := tokenizer(tokenpos, ' ', scriptstmt);
    if (table_exist(objname)) then
      begin
        run_ddl_command(scriptstmt, 'Update_table: Table ' || objname);
        put_buffer('Update_table: Table ' || objname || ' - records updated.');
      exception
        when dup_val_on_index then
          put_buffer('Update_table: Table ' || objname || ': record already exist');
        when others then
          put_buffer('Update_table: Table ' || objname || ' error: ' || sqlerrm);
          raise;
      end;
    else
      put_buffer('Update_table: Table ' || objname || ' not exist.');
    end if;
    return allok;
  end process_updatetable;

  function process_mergeintotable(tokenpos in out number, scriptstmt varchar2) return boolean is
    objname varchar2(50);
    allok   boolean := true;
  begin
    objname := tokenizer(tokenpos, ' ', scriptstmt);
    if (table_exist(objname)) then
      begin
        run_ddl_command(scriptstmt, 'Merge_into_table: Table ' || objname);
        put_buffer('Merge_into_table: Table ' || objname || ' - records updated.');
      exception
        when others then
          put_buffer('Merge_into_table: Table ' || objname || ' error: ' || sqlerrm);
          raise;
      end;
    else
      put_buffer('Merge_into_table: Table ' || objname || ' not exist.');
    end if;
    return allok;
  end process_mergeintotable;

  function process_insertintotable(tokenpos in out number, scriptstmt varchar2) return boolean is
    tempstr varchar2(32767);
    objname varchar2(50);
    allok   boolean := true;
  begin
    tempstr := tokenizer(tokenpos, ' ', scriptstmt);
    if (instr(tempstr, '(') > 0) then
      objname := trim(substr(tempstr, 1, instr(tempstr, '(') - 1));
    else
      objname := trim(tempstr);
    end if;
    if (table_exist(objname)) then
      begin
        run_ddl_command(scriptstmt, 'Insert_into: Table ' || objname);
        put_buffer('Insert_into: Table ' || objname || ' - records inserted.');
      exception
        when dup_val_on_index then
          put_buffer('Insert_into: Table ' || objname || ': record already exist');
        when others then
          put_buffer('Insert_into: Table ' || objname || ' error: ' || sqlerrm);
          raise;
      end;
    else
      put_buffer('Insert_into: Table ' || objname || ' not exist.');
    end if;
    return allok;
  end process_insertintotable;

  function process_deletefromtable(tokenpos in out number, scriptstmt varchar2) return boolean is
    objname varchar2(50);
    allok   boolean := true;
  begin
    objname := tokenizer(tokenpos, ' ', scriptstmt);
    if (objname = 'FROM') then
      objname := tokenizer(tokenpos, ' ', scriptstmt);
    end if;
    if (table_exist(objname)) then
      begin
        run_ddl_command(scriptstmt, 'Delete_from_table: Table ' || objname);
        put_buffer('Delete_from_table: Table ' || objname || ' - records deleted.');
      exception
        when others then
          put_buffer('Delete_from_table: Table ' || objname || ' error: ' || sqlerrm);
          raise;
      end;
    else
      put_buffer('Delete_from_table: Table ' || objname || ' not exist.');
    end if;
    return allok;
  end process_deletefromtable;

  function process_truncatetable(tokenpos in out number, scriptstmt varchar2) return boolean is
    objname varchar2(50);
    allok   boolean := true;
  begin
    objname := tokenizer(tokenpos, ' ', scriptstmt);
    if (objname= 'TABLE') then
      objname := tokenizer(tokenpos, ' ', scriptstmt);
    end if;
    if (table_exist(objname)) then
      begin
        run_ddl_command(scriptstmt, 'Truncate_table: Table ' || objname);
        put_buffer('Truncate_table: Table ' || objname || ' - records deleted.');
      exception
        when others then
          put_buffer('Truncate_table: Table ' || objname || ' error: ' || sqlerrm);
      end;
    else
      put_buffer('Truncate_table: Table ' || objname || ' not exist.');
    end if;
    return allok;
  end process_truncatetable;

  function process_altertrigger(tokenpos in out number, scriptstmt varchar2)
    return boolean is
    objname varchar2(50);
    allok boolean := true;
  begin
    objname := tokenizer(tokenpos, ' ', scriptstmt);

    if (not(object_exist(objname, 'TRIGGER'))) then
      put_buffer('Alter_trigger: Trigger ' || objname || ' not exist.');
    else
      begin
        run_ddl_command(scriptstmt, 'Alter_trigger: ' || objname);
        put_buffer('Alter_trigger: ' || objname || ' modified.');
      exception
        when others then
          put_buffer('Error: ' || sqlerrm);
          allok := false;
      end;
    end if;
    return allok;
  end process_altertrigger;

  function identify_statement(scriptstmt_orig varchar2, parampos in out number, sfile clob) return boolean is
    tokenpos   number;
    ltext      varchar2(32767);
    strinit    varchar2(100);
    procscript varchar2(32767);
    objname    varchar2(50);
    allok      boolean := true;
    scriptstmt varchar2(32767) := scriptstmt_orig;
  begin
        if (upper(scriptstmt) like 'CREATE OR REPLACE %') then
          tokenpos := 18;
          strinit  := upper(tokenizer(tokenpos, ' ', scriptstmt));
        else
          tokenpos := 1;
          strinit  := tokenizer(tokenpos, ' ', scriptstmt);
          strinit  := upper(strinit || ' ' || tokenizer(tokenpos, ' ', scriptstmt));
        end if;
        begin
          -- standardize scriptStmt (in order to perform standard checks)
          if not(strinit like 'PROCEDURE%' or strinit like 'FUNCTION%' or strinit like 'TRIGGER%' or strinit = 'VIEW'
            or strinit like 'EXECUTE %' or strinit like 'EXEC %' or strinit = 'ALTER PACKAGE') then
          standardizeStatement(scriptstmt);
          end if;

          case
            when (strinit like 'PROCEDURE%' or strinit like 'FUNCTION%' or strinit like 'TRIGGER%') then
              begin
                  -- CREATE OR REPLACE PROCEDURE/FUNCTION/TRIGGER
                  procscript := scriptstmt || ';';
                  procscript := regexp_replace(procscript, ' is ', ' is ' || chr(10) || ' ', 1, 1, 'i');
                  procscript := regexp_replace(procscript, ' as ', ' as ' || chr(10) || ' ', 1, 1, 'i');
                  procscript := regexp_replace(procscript, ' begin ', chr(10) || 'begin' || chr(10), 1, 0, 'i');
                  objname    := trim ('"' from tokenizer(tokenpos, ' ', procscript));
                  if (instr(objname, '(') > 0) then
                    objname := trim(substr(objname, 1, instr(objname, '(') - 1));
                  else
                    objname := trim(objname);
                  end if;
                -- USER DEFINED AGGREGATE FUNCTION are excluded from this processing
                if (upper(scriptstmt) not like '% AGGREGATE USING %') then
                  ltext := tokenizer(parampos, chr(10), sfile);
                  loop
                    if (instr(upper(ltext), 'END ' || upper(objname)) > 0) then
                      procscript := procscript || chr(10) || ltrim(ltext);
                      exit;
                    else
                      procscript := procscript || chr(10) || ltrim(ltext);
                      ltext      := tokenizer(parampos, chr(10), sfile);
                    end if;
                  end loop;
                end if;
                allok := process_createobject(procscript, strinit, objname);
              end;
            when (strinit = 'VIEW') then
              begin
                -- CREATE OR REPLACE VIEW
                objname := tokenizer(tokenpos, ' ', scriptstmt);
                allok   := process_createobject(scriptstmt, objname, 'VIEW');
              end;
            when (strinit like 'EXECUTE %' or strinit like 'EXEC %') then
              begin
                -- EXECUTE COMMAND
                tokenpos := 8;
                allok    := process_commandexecute(tokenpos, scriptstmt);
              end;
            when (strinit = 'ALTER PACKAGE') then
              --ALTER PACKAGE
              allok := process_alterpackage(tokenpos, scriptstmt);
            when (strinit = 'CREATE TABLE') then
              -- CREATE TABLE
              allok := process_createtable(tokenpos, scriptstmt);
            when (strinit = 'ALTER TABLE') then
              -- ALTER TABLE
              allok := process_altertable(tokenpos, scriptstmt);
            when (strinit = 'ALTER INDEX') then
              -- ALTER INDEX
              allok := process_alterindex(tokenpos, scriptstmt);
            when (strinit = 'ALTER TRIGGER') then
              -- ALTER TRIGGER
              allok := process_altertrigger(tokenpos, scriptstmt);
            when (strinit = 'CREATE UNIQUE' or strinit = 'CREATE INDEX') then
              -- CREATE INDEX
              allok := process_createindex(tokenpos, scriptstmt, strinit);
            when (strinit = 'DROP INDEX') then
              -- DROP INDEX
              allok := process_dropindex(tokenpos, scriptstmt);
            when (strinit = 'DROP TABLE') then
              -- DROP TABLE
              allok := process_droptable(tokenpos, scriptstmt);
            when (strinit = 'DROP PACKAGE' or strinit = 'DROP FUNCTION' or
                 strinit = 'DROP PROCEDURE' or strinit = 'DROP VIEW' or strinit = 'DROP TYPE' or
                 strinit = 'DROP TRIGGER' or strinit = 'DROP SEQUENCE') then
              -- DROP PACKAGE/FUNCTION/VIEW/TYPE/PROCEDURE/TRIGGER/SEQUENCE
              allok := process_dropobject(substr(strinit, 6), tokenpos, scriptstmt);
            when (strinit = 'CREATE SEQUENCE') then
              -- CREATE SEQUENCE
              allok := process_createsequence(tokenpos, scriptstmt);
	          when (strinit = 'DIRECTORY' or strinit = 'CREATE DIRECTORY') then
              -- CREATE DIRECTORY
              allok := process_createdirectory(tokenpos, scriptstmt);
            when (strinit = 'PUBLIC') then
	            -- CREATE PUBLIC SYNONYM
              strinit := tokenizer(tokenpos, ' ', scriptstmt);
              if (strinit = 'SYNONYM') then
                allok := process_createsynonym(tokenpos, scriptstmt, 'PUBLIC');
              end if;
            when (strinit = 'SYNONYM') then
	            -- CREATE PUBLIC SYNONYM
              allok := process_createsynonym(tokenpos, scriptstmt,'');
            when (strinit = 'CREATE TYPE' or strinit = 'TYPE') then
              -- CREATE TYPE
              allok := process_createtype(tokenpos, scriptstmt);
            when (strinit = 'COMMENT ON') then
              -- CREATE COMMENT
              allok := process_createcomment(tokenpos, scriptstmt);
            when (strinit like 'UPDATE %') then
              -- UPDATE TABLE
              tokenpos := 8;
              allok    := process_updatetable(tokenpos, scriptstmt);
            when (strinit = 'INSERT INTO') then
              -- INSERT INTO TABLE
              allok := process_insertintotable(tokenpos, scriptstmt);
            when (strinit = 'MERGE INTO') then
              -- MERGE INTO TABLE
              allok := process_mergeintotable(tokenpos, scriptstmt);
            when (strinit like 'DELETE %') then
              -- DELETE FROM TABLE
              tokenpos := 8;
              allok    := process_deletefromtable(tokenpos, scriptstmt);
            when (strinit like 'TRUNCATE %') then
              -- DELETE FROM TABLE
              tokenpos := 10;
              allok    := process_truncatetable(tokenpos, scriptstmt);
            when (strinit like 'RENAME %') then
              -- RENAME
              tokenpos := 8;
              allok    := process_rename(tokenpos, scriptstmt);
            when (strinit like 'COMMIT %' or strinit like 'ROLLBACK %') then
              -- OTHERS
              run_ddl_command(scriptstmt, scriptstmt);
            else
              -- if the statement isn't recognized
              raise untreated_statement;
          end case;
          return allok;
        exception
          when untreated_statement then
            raise;
          when others then
            raise;
        end;
  end identify_statement;

  -- function necessary to convert Script name to number (for scripts over 100)
  function convertScriptNameToNumber(scriptName in varchar2) return number is
      scriptStr varchar2(16);
    begin
      select replace(REGEXP_substr(scriptName, '[[:digit:]]+\.+[[:digit:]]+\.+[[:digit:]]+\.') ||
         case REGEXP_substr(scriptName, '[^.]+', 1, 4)
           when '99' then
            '999'
           else
            lpad(REGEXP_substr(scriptName, '[^.]+', 1, 4),
                 3,
                 '0')
         end, '.', '') into scriptStr
         from dual;

       return to_number(scriptStr);

  exception
    when others then
      return -1;
  end convertScriptNameToNumber;

  -- public procedure called in java code for running scripts
  procedure execScriptFile(sfile clob, scriptName varchar2 default null) is
    ltext      varchar2(32767);
    scriptstmt varchar2(32767);
    parampos   number;
    tokenpos   number;
    allok      boolean := true;
    localpos   number := 0;
  begin
    parampos := 1;
    gv_currentScriptNo := 0;
    gv_scriptName := scriptName;
    if ((scriptName is not null) and (scriptName like 'Script\_%' escape '\' or scriptName like 'SpecialScript\_%' escape '\')) then
      gv_currentScriptNo := convertScriptNameToNumber(scriptName);
    end if;
    gv_skipStatement := '';
    while (parampos != 0 and parampos < dbms_lob.getlength(sfile)) loop
      ltext      := tokenizer(parampos, chr(10), sfile);
      scriptstmt := '';
      --strinit    := '';
      tokenpos   := 0;
      -- skip comments ('--' and '/* */')
      if (instr(ltext, '--') > 0) then
        ltext := substr(ltext, 1, instr(ltext, '--') - 1);
      end if;
      if (instr(ltext, '/*') > 0) then
        if (instr(ltext, '*/') > 0) then
          null;
        else
          loop
            ltext := tokenizer(parampos, chr(10), sfile);
            if (instr(ltext, '*/') > 0) then
              exit;
            end if;
          end loop;
        end if;
      elsif (trim(both '/' from trim(both ' ' from ltext)) is not null) then
        loop
          if (instr(ltext, '--') > 0) then
             ltext := substr(ltext, 1, instr(ltext, '--') - 1);
          end if;

          if (instr(upper(ltext), 'EXECUTE IMMEDIATE') > 0) then
            loop
              if (instr(ltext, '--') > 0) then
                 ltext := substr(ltext, 1, instr(ltext, '--') - 1);
              end if;
              scriptstmt := scriptstmt || ' ' || ltext;
              ltext      := tokenizer(parampos, chr(10), sfile);
              if (trim(upper(ltext)) = 'END;') then
                exit;
              end if;
            end loop;
            exit;
          elsif ((instr(upper(ltext), 'REPLACE TYPE BODY') > 0) or (instr(upper(ltext), 'REPLACE PACKAGE') > 0)) then
            loop
              if (instr(ltext, '--') > 0) then
                 ltext := substr(ltext, 1, instr(ltext, '--') - 1);
              end if;
              scriptstmt := scriptstmt || ' ' || ltext;
              ltext      := tokenizer(parampos, chr(10), sfile);
              if (trim(upper(ltext)) = 'END;') then
                scriptstmt := scriptstmt || ' ' || ltext;
                exit;
              end if;
            end loop;
            exit;
          elsif (instr(ltext, ';') > 0) then
            scriptstmt := scriptstmt || ' ' || ltext;
            if (checkconsistenttoken(scriptstmt, true)) then
              exit;
            else
              ltext      := tokenizer(parampos, chr(10), sfile);
            end if;
          elsif (parampos = 0 or parampos >= dbms_lob.getlength(sfile)) then
            exit;
          else
            scriptstmt := scriptstmt || ' ' || ltext;
            ltext      := tokenizer(parampos, chr(10), sfile);
          end if;
        end loop;
      end if;
      -- extracting ddl from begin/end block if exists
      localpos := instr(upper(scriptstmt), 'EXECUTE IMMEDIATE');
      if (localpos > 0) then
         loop
           ltext := tokenizer(localpos, 'EXECUTE IMMEDIATE', upper(scriptstmt));
           ltext := substr(ltext, instr(ltext, '''') + 1,
                             instr(ltext, '''', -1) - instr(ltext, '''') - 1);
           ltext := formatstatement(ltext, true);
           -- EXECUTE COMMAND
           if (ltext is not null) then
              declare
                localposition number := 1;
                localtext varchar2(10000);
              begin
                   loop
                     localtext := tokenizer(localposition, ';', ltext);
                     allok := identify_statement(localtext, parampos, sfile);
                     exit when localposition = 0;
                   end loop;
              end;
           end if;
           exit when localpos = 0;
         end loop;
         scriptstmt := '';
      end if;

      -- extracting ddl for "create type body" if exists
      localpos := instr(upper(scriptstmt), 'REPLACE TYPE BODY');
      if (localpos > 0) then
        localpos := localpos + 18;
        ltext := tokenizer(localpos, ' ', upper(scriptstmt));
        run_ddl_command(scriptstmt, 'Create_or_Replace_Type_Body');
        put_buffer('Create_or_Replace_Type_Body ' || ltext || ' successful.');
        scriptstmt := '';
      end if;

      -- extracting ddl for "create or replace package/ replace package body" if exists
      localpos := instr(upper(scriptstmt), 'REPLACE PACKAGE BODY');
      if (localpos > 0) then
        localpos := localpos + 21;
        ltext := tokenizer(localpos, ' ', upper(scriptstmt));
        run_ddl_command(scriptstmt, 'Create_or_Replace_Package_Body ' || ltext);
        put_buffer('Create_or_Replace_Package_Body ' || ltext || ' successful.');
        scriptstmt := '';
      else
        localpos := instr(upper(scriptstmt), 'REPLACE PACKAGE');
        if (localpos > 0) then
          localpos := localpos + 16;
          ltext := tokenizer(localpos, ' ', upper(scriptstmt));
          run_ddl_command(scriptstmt, 'Create_or_Replace_Package ' || ltext);
          put_buffer('Create_or_Replace_Package ' || ltext || ' successful.');
          scriptstmt := '';
        end if;
      end if;

      if (scriptstmt is not null) then
        -- format scriptStmt (in order to be easily parsed)
        scriptstmt := formatstatement(scriptstmt);
        -- identify sql command in strInit
         allok := identify_statement(scriptstmt, parampos, sfile);
      end if;
    end loop;
  exception
    when untreated_statement then
      begin
        put_buffer('Untreated_statement: ' || scriptstmt);
        raise_application_error(-20002, 'Untreated_statement: ' || scriptstmt);
      end;
    when error_in_statement then
      begin
        put_buffer('Error_in_statement: ' || scriptstmt);
        raise_application_error(-20002, 'Error_in_statement: ' || scriptstmt);
      end;
    when others then
      begin
        put_buffer(sqlerrm);
        raise_application_error(-20002, sqlerrm);
      end;
  end execscriptfile;

begin
  select count(1) into gv_tablespace from user_tab_cols where table_name = 'DATABASESETTINGS' and column_name = 'TABLESPACENAME';

  if (gv_tablespace > 0) then
     execute immediate 'select tablespacename from databasesettings'
                       into gv_tablespace;
  else
     gv_tablespace := '';
  end if;

  select count(1) into gv_dataTblspace from user_tab_cols where table_name = 'DATABASESETTINGS' and column_name = 'DATATBLSPACENAME';

  if (gv_dataTblspace > 0) then
     execute immediate 'select datatblspacename from databasesettings'
                       into gv_dataTblspace;
  else
     gv_dataTblspace := '';
  end if;
end pack_script;
/
