create or replace view wb_rt_adapter_store_view as
   select 
      da.deployment_adapter_id, 
      da.adapter_name, 
      st.store_type_name,
      st.store_type_version
   from 
      wb_rt_def_deployment_adapters da,
      wb_rt_def_store_types st
   where 
      st.store_type_id=da.store_type_id;

create or replace view wb_rt_temp_platfiles as
   select
      f.platform_file_id as platform_file_id,
      ot.object_type_name as object_type_name,
      wo.object_name,
      dbms_lob.getlength(f.text) as length
   from
      wb_rt_platform_files f,
      wb_rt_def_object_types ot,
      wb_rt_warehouse_objects wo
   where
      f.object_type_id=ot.object_type_id and
      f.object_uoid=wo.object_uoid;

create or replace view wb_rt_temp_wobjects as
   select
      wo.warehouse_object_id,
      wo.object_type_name as object_type_name,
      wo.object_name,
      dbms_lob.getlength(wo.creation_script) as length
   from
      wb_rt_warehouse_objects wo;

create or replace view wb_rt_temp_tasks as
   select
      task_id as object_id,
      task_name as object_name,
      dbms_lob.getlength(task_input) as length
   from
      wb_rt_tasks;
          
create or replace view wb_rt_temp_files as
   select
      audit_file_id as object_id,
      null as object_name,
      dbms_lob.getlength(file_text) as length
   from
      wb_rt_audit_files;
   
create or replace function wb_rt_streamlob(
       p_tableName IN VARCHAR2,
       p_startIndex IN NUMBER,
       p_rowId IN INTEGER,
       p_readSize INTEGER,
       p_charBlock OUT VARCHAR2)
 return INTEGER IS
    l_clob CLOB;
begin
   if (p_tableName = 'WB_RT_AUDIT_FILES') THEN
     select
       file_text into l_clob
     from
       wb_rt_audit_files
     where
       audit_file_id=p_rowId;
   elsif (p_tableName = 'WB_RT_WAREHOUSE_OBJECTS') THEN
     select
       creation_script into l_clob
     from
       WB_RT_WAREHOUSE_OBJECTS
     where
       warehouse_object_id=p_rowId;
   elsif (p_tableName = 'WB_RT_PLATFORM_FILES') THEN
     select
       text into l_clob
     from
       WB_RT_PLATFORM_FILES
     where
       platform_file_id=p_rowId;
   elsif (p_tableName = 'WB_RT_TASKS') THEN
     select
       task_input into l_clob
     from
       WB_RT_TASKS
     where
       task_id=p_rowId;
   end if;
   if (l_clob is null) then
     p_charBlock := '';
     return 0;
   end if;
   dbms_lob.open(l_clob, DBMS_LOB.LOB_READONLY);
   p_charBlock := dbms_lob.substr(l_clob, p_readSize, p_startIndex);
   dbms_lob.close(l_clob);
   if (p_startIndex+p_readSize<dbms_lob.getlength(l_clob)) then
     return p_readSize;
   else
     return dbms_lob.getlength(l_clob)-p_startIndex+1;
   end if;
end wb_rt_streamlob;

begin
    execute immediate 'drop table wb_rt_temp_sequences';
exception when others then
  null;
end;
/

begin
    execute immediate 'create table wb_rt_temp_sequences ( ' ||
           '    seq_name varchar2(64), ' ||
           '    seq_value number ' ||
           '  )';
end;
/

begin
  declare
    l_seq_value number;
  begin
    select wb_rt_audit_amounts_seq.nextval into l_seq_value from dual;
    insert into wb_rt_temp_sequences (seq_name, seq_value) values ('WB_RT_AUDIT_AMOUNTS_SEQ', l_seq_value);

    select wb_rt_audit_seq.nextval into l_seq_value from dual;
    insert into wb_rt_temp_sequences (seq_name, seq_value) values ('WB_RT_AUDIT_SEQ', l_seq_value);

    select wb_rt_errors_seq.nextval into l_seq_value from dual;
    insert into wb_rt_temp_sequences (seq_name, seq_value) values ('WB_RT_ERRORS_SEQ', l_seq_value);

    select wb_rt_error_rows_seq.nextval into l_seq_value from dual;
    insert into wb_rt_temp_sequences (seq_name, seq_value) values ('WB_RT_ERROR_ROWS_SEQ', l_seq_value);

    select wb_rt_error_sources_seq.nextval into l_seq_value from dual;
    insert into wb_rt_temp_sequences (seq_name, seq_value) values ('WB_RT_ERROR_SOURCES_SEQ', l_seq_value);

    select wb_rt_feedback_seq.nextval into l_seq_value from dual;
    insert into wb_rt_temp_sequences (seq_name, seq_value) values ('WB_RT_FEEDBACK_SEQ', l_seq_value);

    select wb_rt_map_audit_detail_seq.nextval into l_seq_value from dual;
    insert into wb_rt_temp_sequences (seq_name, seq_value) values ('WB_RT_MAP_AUDIT_DETAIL_SEQ', l_seq_value);

    select wb_rt_map_audit_seq.nextval into l_seq_value from dual;
    insert into wb_rt_temp_sequences (seq_name, seq_value) values ('WB_RT_MAP_AUDIT_SEQ', l_seq_value);

    select wb_rt_operator_seq.nextval into l_seq_value from dual;
    insert into wb_rt_temp_sequences (seq_name, seq_value) values ('WB_RT_OPERATOR_SEQ', l_seq_value);

    select wb_rt_platform_catalog_seq.nextval into l_seq_value from dual;
    insert into wb_rt_temp_sequences (seq_name, seq_value) values ('WB_RT_PLATFORM_CATALOG_SEQ', l_seq_value);

    select wb_rt_platform_file_seq.nextval into l_seq_value from dual;
    insert into wb_rt_temp_sequences (seq_name, seq_value) values ('WB_RT_PLATFORM_FILE_SEQ', l_seq_value);

    select wb_rt_platform_properties_seq.nextval into l_seq_value from dual;
    insert into wb_rt_temp_sequences (seq_name, seq_value) values ('WB_RT_PLATFORM_PROPERTIES_SEQ', l_seq_value);

    select wb_rt_platform_task_seq.nextval into l_seq_value from dual;
    insert into wb_rt_temp_sequences (seq_name, seq_value) values ('WB_RT_PLATFORM_TASK_SEQ', l_seq_value);

    select wb_rt_rowkey_seq.nextval into l_seq_value from dual;
    insert into wb_rt_temp_sequences (seq_name, seq_value) values ('WB_RT_ROWKEY_SEQ', l_seq_value);

    select wb_rt_system_metadata_seq.nextval into l_seq_value from dual;
    insert into wb_rt_temp_sequences (seq_name, seq_value) values ('WB_RT_SYSTEM_METADATA_SEQ', l_seq_value);

    begin
      execute immediate 'select wb_rt_map_audit_struct_seq.nextval from dual' into l_seq_value;
      insert into wb_rt_temp_sequences (seq_name, seq_value) values ('WB_RT_MAP_AUDIT_STRUCT_SEQ', l_seq_value);
    exception when others then
      insert into wb_rt_temp_sequences (seq_name, seq_value) values ('WB_RT_MAP_AUDIT_STRUCT_SEQ', 1);
    end;

  end;
end;
/

commit;