prompt Package Body: jr_reg_io

create or replace package body jr_reg_io is

  --------------------------
  -- Private data/methods --
  --------------------------

  subtype file_type is utl_file.file_type;

  debug_enabled boolean := false;
  debug_level   number  := null;

  -- CW 12-Apr-1999
  -- NB. It seems that Oracle can only manage 10 file handles at the same time
  -- Instead of adding a file handle for jrapi.sql, reuse the stored objects file
  -- handle instead

  fh_log_file     file_type;

  fh_tables       file_type;
  fh_indexes      file_type;
  fh_constraints  file_type;
  fh_triggers     file_type;
  fh_functions    file_type;
  fh_views        file_type;
  fh_stored_objs  file_type;
  fh_synonyms     file_type;
  fh_sequences    file_type;

  END_OF_DML_STATEMENT  constant varchar2(11) := '##EOF_DML##';

  ------------------------------------------------
  -- Procedure : debug_put
  ------------------------------------------------
  procedure debug_put (msg varchar2) is
  begin
    if debug_enabled then
      dbms_output.put(msg);
    end if;
  end debug_put;

  ------------------------------------------------
  -- Procedure : debug_put_line
  ------------------------------------------------
  procedure debug_put_line (msg varchar2) is
  begin
    if debug_enabled then
      dbms_output.put_line(msg);
    end if;
  end debug_put_line;

  ------------------------------------------------
  -- Function : open_file
  ------------------------------------------------
  function open_file ( file_location  varchar2
                     , filename       varchar2
                     ) return file_type
  is
  begin
    if file_location is not null and filename is not null then
      debug_put_line('Opening file : ' || file_location || ', ' || filename);
      return utl_file.fopen(file_location, filename, 'w');
    else
      return null;
    end if;
  exception
    when utl_file.invalid_path then
      debug_put_line('Exception jr_reg_io.open_file - invalid_path : ' || file_location || ', ' || filename);
      raise;
    when utl_file.invalid_mode then
      debug_put_line('Exception jr_reg_io.open_file - invalid_mode : ' || file_location || ', ' || filename);
      raise;
    when utl_file.invalid_operation then
      debug_put_line('Exception jr_reg_io.open_file - invalid_operation : ' || file_location || ', ' || filename || ', w');
      raise;
    when others then
      debug_put_line('Error opening file : ' || file_location || ', ' || filename);
      raise;
  end open_file;

  ------------------------------------------------
  -- Function : is_open
  ------------------------------------------------
  function is_open (fh in out file_type) return boolean
  is
  begin
    return utl_file.is_open(fh);
  exception
    when utl_file.invalid_filehandle then
      debug_put_line('Exception jr_reg_io.is_open - invalid_filehandle' );
      raise;
    when utl_file.write_error then
      debug_put_line('Exception jr_reg_io.is_open - write_error' );
      raise;
    when others then
      debug_put_line('Exception jr_reg_io.is_open - other exception' );
      raise;
  end is_open;

  ------------------------------------------------
  -- Procedure : close_file
  ------------------------------------------------
  procedure close_file (fh in out file_type) is
  begin
    if is_open(fh) then
      utl_file.fclose(fh);
    end if;
  exception
    when utl_file.invalid_filehandle then
      debug_put_line('Exception jr_reg_io.close_file - invalid_filehandle' );
      raise;
    when utl_file.write_error then
      debug_put_line('Exception jr_reg_io.close_file - write_error' );
      raise;
    when others then
      debug_put_line('Exception jr_reg_io.close_file - other exception' );
      raise;
  end close_file;

  ------------------------------------------------
  -- Procedure : write_file
  ------------------------------------------------
  procedure write_file(fh in out file_type, msg varchar2) is
  begin
    if is_open(fh) then
      utl_file.put_line(fh, msg);
    end if;
  exception
    when no_data_found then
      put_line('Exception write_file : no_data_found');
      raise;
    when others then
      put_line('Exception write_file : other exception');
      raise;
  end write_file;

  --------------------------------------------------
  -- Public methods --
  --------------------------------------------------

  --------------------------------------------------
  -- Procedure : enable_debug
  --------------------------------------------------
  procedure enable_debug (dlevel number default null)
  is
  begin
    debug_enabled := true;
    debug_level   := dlevel;
    dbms_output.enable(1000000);
  end enable_debug;

  --------------------------------------------------
  -- Procedure : disable_debug
  --------------------------------------------------
  procedure disable_debug
  is
  begin
    debug_enabled := false;
    dbms_output.disable;
  end disable_debug;

  ------------------------------------------------
  -- Procedure : init_log_file
  ------------------------------------------------
  procedure init_log_file ( file_location varchar2
                          , filename      varchar2
                          )
  is
  begin
    if file_location is not null and is_open(fh_log_file) = false then
      fh_log_file := open_file(file_location, filename);
    end if;
  end init_log_file;

  ------------------------------------------------
  -- Procedure : close_log_file
  ------------------------------------------------
  procedure close_log_file (file_location varchar2) is
  begin
    if file_location is not null then
      close_file(fh_log_file);
    end if;
  end close_log_file;

  ------------------------------------------------
  -- Procedure : put
  ------------------------------------------------
  procedure put(msg varchar2) is
  begin
    if is_open(fh_log_file) then
      utl_file.put(fh_log_file, msg);
    else
      debug_put(msg);
    end if;
  end put;

  ------------------------------------------------
  -- Procedure : put_line
  ------------------------------------------------
  procedure put_line(msg varchar2) is
  begin
    if is_open(fh_log_file) then
      utl_file.put_line(fh_log_file, msg);
    else
      debug_put_line(msg);
    end if;
  end put_line;

  ------------------------------------------------
  -- Procedure : init_ddl_files
  ------------------------------------------------
  procedure init_ddl_files ( file_location     varchar2
                           , tables_file       varchar2  default 'jrtable.sql'
                           , indexes_file      varchar2  default 'jrindex.sql'
                           , constraints_file  varchar2  default 'jrcon.sql'
                           , triggers_file     varchar2  default 'jrtrig.sql'
                           , functions_file    varchar2  default 'jrfunc.sql'
                           , views_file        varchar2  default 'jrview.sql'
                           , stored_objs_file  varchar2  default 'jrstor.sql'
                           , synonyms_file     varchar2  default 'jrsyn.sql'
                           , sequences_file    varchar2  default 'jrseq.sql'
                           )
  is
  begin
    if file_location is not null then
      fh_tables      := open_file(file_location, tables_file);
      fh_indexes     := open_file(file_location, indexes_file);
      fh_constraints := open_file(file_location, constraints_file);
      fh_triggers    := open_file(file_location, triggers_file);
      fh_functions   := open_file(file_location, functions_file);
      fh_views       := open_file(file_location, views_file);
      fh_stored_objs := open_file(file_location, stored_objs_file);
      fh_synonyms    := open_file(file_location, synonyms_file);
      fh_sequences   := open_file(file_location, sequences_file);
    end if;
  end init_ddl_files;

  ------------------------------------------------
  -- Procedure : close_ddl_files
  ------------------------------------------------
  procedure close_ddl_files is
  begin
    close_file(fh_tables);
    close_file(fh_indexes);
    close_file(fh_constraints);
    close_file(fh_triggers);
    close_file(fh_functions);
    close_file(fh_views);
    close_file(fh_stored_objs);
    close_file(fh_synonyms);
    close_file(fh_sequences);
  end close_ddl_files;

  ------------------------------------------------
  -- Procedure : init_ddl_file
  ------------------------------------------------
  procedure init_ddl_file ( file_location varchar2
                          , file_name     varchar2  default 'jrddl.sql'
                          )
  is
  begin
    if file_location is not null and is_open(fh_stored_objs) = false then
      -- Reuse existing file handle as we are limited in number
      fh_stored_objs := open_file(file_location, file_name);
    end if;
  end init_ddl_file;

  ------------------------------------------------
  -- Procedure : close_ddl_file
  ------------------------------------------------
  procedure close_ddl_file (file_location varchar2) is
  begin
    if file_location is not null then
      close_file(fh_stored_objs);
    end if;
  end close_ddl_file;

  ----------------------------------------------------------------------
  -- Procedure : write_ddl_file
  --
  -- CW 14-Apr-00 Fix bug 965883, handle force create of views
  ----------------------------------------------------------------------
  procedure write_ddl_file ( statement        varchar2
                           , fh        in out file_type
                           )
  is
    UNIX_LF         constant char := chr(0);
    COL_DEFAULT_LF  constant char := chr(32);
  begin
    -- Write ddl to file. Change file depending on type of statement, else write to same file as previously determined
    if statement =    'create or replace '           or   -- user defined plsql source or views
       statement like 'create or replace package%'   or
       statement like 'create or replace procedure%'
    then
      fh := fh_stored_objs;
    elsif statement like 'create table%'                  then
      fh := fh_tables;
    elsif statement like 'create view%'                   or
          statement like 'create or replace view%'        or
          statement like 'create or replace force view%'  then
      fh := fh_views;
    elsif statement like 'create sequence%'               then
      fh := fh_sequences;
    elsif statement like 'create or replace trigger%'     then
      fh := fh_triggers;
    elsif statement like 'create index%'                  then
      fh := fh_indexes;
    elsif statement like 'create or replace function%'    then
      fh := fh_functions;
    elsif statement like 'create synonym%'                then
      fh := fh_synonyms;
    elsif statement like '--RAU%'                         then
      -- Add constraint name as comment, so appears in file for RAU
      fh := fh_constraints;
    elsif statement like 'alter table%'                   then
      fh := fh_constraints;

    -- CW 18-Jan-2000
    -- Do not default to a single file, the file output works by writing to the
    -- *same* file until a new create xxx or alter xxx is found !
--    else
      -- Default sql to the single file opened by utilities like migrate users data
--      fh := fh_stored_objs;
    end if;

    -- Now write to the file, replace end of statement by /

    if    statement = END_OF_STATEMENT    then
      write_file(fh, '/');
    elsif statement like 'create schema%' then
      -- Ignore create schema statement
      null;
    elsif statement like 'insert into i$sdd_version_registry%' then
      -- Ignore sql that populates version registry, not ddl
      null;
    elsif statement = END_OF_DML_STATEMENT then
      -- Ignore trailing slash after sql that populates version registry, not ddl
      null;
    else
      -- Oracle stores linefeed characters at the end of lines of trigger bodies, view text etc
      -- Strip these before writing to file as write_file will write a newline anyway
      -- Strangely, trigger bodies *also* have a '/0' stored at the end of the statement, strip
      -- this off as well
      -- CW 11-Feb-2000 Fix bug 1192048, in the create table statement, for columns with default
      -- values, a newline is written to the ddl file. The extra character is chr(32).
      write_file(fh, rtrim(statement, LF || UNIX_LF || COL_DEFAULT_LF));
    end if;

  exception
    when no_data_found then
      put_line('Exception write_ddl_file : no_data_found');
      raise;
    when others then
      put_line('Exception write_ddl_file : other exception');
      raise;
  end write_ddl_file;

  ----------------------------------------------------------------------
  -- Procedure : exec
  ----------------------------------------------------------------------
  procedure exec (statement varchar2, ignore_error boolean default false) is
    cur integer;
    ret integer;
    fh  file_type;
  begin
    -- Write ddl to file
    write_ddl_file(statement || ';', fh);

    cur := dbms_sql.open_cursor;
    dbms_sql.parse(cur, statement, dbms_sql.NATIVE);
    ret := dbms_sql.execute(cur);
    dbms_sql.close_cursor(cur);
  exception
    when others then
      if dbms_sql.is_open(cur) then dbms_sql.close_cursor(cur); end if;
      put_line('Error : Failed to execute :');
      put_line(statement);
      put_line(sqlerrm(sqlcode));
      if ignore_error then
        null;
      else
        raise_application_error(-20000, 'Failure executing ddl');
      end if;
  end exec;

  ----------------------------------------------------------------------
  -- Procedure : exec
  ----------------------------------------------------------------------
  procedure exec ( statement          dbms_sql.varchar2s
                 , check_termination  boolean             default false
                 , lfflg              boolean             default true
                 )
  is
    lb          pls_integer := 1;
    ub          pls_integer := 0;
    cur         integer;
    ret         integer;
    first_stmt  boolean := true;
    fh          file_type;

    err_pos     number;
    done        boolean;
  begin
    -- Make sure there's something to execute
    if statement.count = 0 then
      return;
    end if;

    if check_termination then
      while lb <= statement.count loop
        ub := ub + 1;

        -- Write ddl to file
        write_ddl_file(statement(ub), fh);

        if statement(ub) = END_OF_STATEMENT or statement(ub) = END_OF_DML_STATEMENT then
          begin
            cur := dbms_sql.open_cursor;
            dbms_sql.parse(cur, statement, lb, ub-1, lfflg, dbms_sql.NATIVE);
            ret := dbms_sql.execute(cur);
            dbms_sql.close_cursor(cur);
          exception
            when others then
              err_pos := dbms_sql.last_error_position;  -- position of error
              if dbms_sql.is_open(cur) then dbms_sql.close_cursor(cur); end if;
              -- dump out some information to the user
              put_line('Error : Failed to execute :');
              done := false;
              for i in lb..ub-1 loop
                put_line(statement(i));
                if err_pos < length(statement(i)) and not done then
                  put_line(rpad('-', err_pos, '-') || '^');
                  done := true;
                else
                  err_pos := err_pos - length(statement(i)) -1;
                end if;
              end loop;
              put_line(sqlerrm(sqlcode));
              raise;
          end;
          lb := ub + 1;
        end if;

      end loop;
    else
      -- used by schema statement
      begin
        for lnum in 1..statement.count loop
          -- Write ddl to file. Change file depending on type of statement, else write to same file as previously determined
          -- There are no end of statement markers in the schema statement so if we come across a
          -- create ... line (and it isn't the first one), put '/' to terminate the previous statement (only in the ddl file !)

          if statement(lnum) like 'create table%' then
            if not first_stmt then write_file(fh, '/'); end if;
            fh := fh_tables;
            first_stmt := false;
          elsif statement(lnum) like 'create view%' or statement(lnum) like 'create or replace view%' then
            -- Fix bug 806794, force create views so RAU can install them regardless of dependencies
            -- CW 1-Feb-99, create force view not a valid schema statement
            if not first_stmt then write_file(fh, '/'); end if;
            fh := fh_views;
            first_stmt := false;
          end if;

          --write_file(fh, statement(lnum));
          write_ddl_file(statement(lnum), fh);
        end loop;

        -- Write a final slash
        write_file(fh, '/');

        cur := dbms_sql.open_cursor;
        dbms_sql.parse(cur, statement, 1, statement.count, lfflg, dbms_sql.NATIVE);
        ret := dbms_sql.execute(cur);
        dbms_sql.close_cursor(cur);
      exception
        when others then
          err_pos := dbms_sql.last_error_position;  -- position of error
          if dbms_sql.is_open(cur) then dbms_sql.close_cursor(cur); end if;
          -- Dump out some information to the user
          put_line('Error (jr_reg_io.exec) : Failed to execute :');
          done := false;
          for i in statement.first..statement.last loop
            put_line(statement(i));
            if err_pos < length(statement(i)) and not done then
              put_line(rpad('-', err_pos, '-') || '^');
              done := true;
            else
              err_pos := err_pos - length(statement(i)) -1;
            end if;
          end loop;
          put_line(sqlerrm(sqlcode));
          raise;
      end;
    end if;
  end exec;
  
end jr_reg_io;
/