экспортировать данные из набора таблиц в Oracle

У меня есть вариант использования, когда набор хранимых процедур вызывается (с использованием планировщика), который состоит из следующего шаблона работы:

  1. Установите некоторые переменные, такие как текущая дата подачи заявки и т. д. [Готово]
  2. Выполнение запросов к таблицам БД для заполнения глобальных временных таблиц в соответствии с пользовательскими вариантами [Готово]
  3. Экспортируйте эти результаты в соответствии с отчетом (используя предложение where во временных таблицах) в формате файла CSV [ Need Help ]
  4. Каталог файлов CSV основан на типе отчета, а имя файла CSV является динамическим в зависимости от дня и времени. [Нужна помощь]

Предполагается, что эти хранимые процедуры планируются и выполняются ежедневно для своевременного создания отчетов. Теперь мы не можем выполнить, как мы можем экспортировать этот набор результатов, который присутствует во временных таблицах.

Я попробовал функциональность SPOOL, но похоже, что она основана на SQLPLUS, и не уверен, что мы можем использовать ее в хранимой процедуре. ВСЕГДА ли мы можем использовать ее в этом потоке в хранимой процедуре. Если нет - каковы возможные варианты вызова SPOOL в Oracle 19c? Если да, то данные временной таблицы будут доступны, поскольку мы поняли, что они доступны только на уровне сеанса. Пожалуйста, дайте нам знать, если у вас есть обходной путь для того же.

set term off
set feed off
set feedback off
set sqlformat csv
spool 'D:\test\outputExample.csv'
select Date_Time,a,b,c,Net_Amount from temp_tbl1 ;
spool off

Поскольку объем данных для каждого отчета csv выше, я избегаю использования функции utl_file. Существуют ли какие-либо функции, доступные в Oracle 19c для того же?

Спасибо


oracle stored-procedures plsql oracle19c spool
person Vicky    schedule 18.04.2020    source источник

Ответы (1)


arrow_upward
0
arrow_downward

SPOOL — это функция SQL Plus, вы не можете использовать ее внутри PL/SQL. Многие инструменты, такие как SQL Developer и т. д., поддерживают команды sqlplus. Сказав это, вам нужно вызвать все процедуры и вызвать sqlplus как уровень ОС. Если вы хотите записать вывод процедуры в файл ОС, вы можете использовать UTL_FILE. Если вы хотите избежать этого, то единственный способ — использовать DBMS_OUTPUT внутри ваших процедур, а затем буферизовать вывод в файл.

Начиная с Oracle 12.2, вы можете использовать команду SET MARKUP:

SET MARKUP CSV ON

Синтаксис SET MARKUP имеет два варианта:

  • CSV
  • HTML

Параметр CSV имеет следующий синтаксис:

CSV {ON|OFF} [DELIMI[TER] character] [QUOTE {ON|OFF}]

См. документация по SQLPlus для создания отчеты CSV.

Альтернативное решение UTL_FILE:

Вы можете использовать DBMS_SCHEDULER для создания задания для сценария SQL. В последних версиях планировщик базы данных был улучшен, чтобы иметь возможность запускать сценарии в стиле SQL Plus непосредственно из базы данных без необходимости предоставления ОС доступа к серверу базы данных или исполняемому файлу SQL*Plus.

Например:

declare
  sql_script   VARCHAR2(32767) :=
  'conn /@db
  set markup csv on
  set pages 0
  set lines 200
  set trimspool on
  spool c:\your_directory\file.csv
  select * from tab;
  spool off';
begin
 dbms_scheduler.create_job(
   job_name        => 'UNLOAD_DATA',
   job_type        => 'SQL_SCRIPT',
   job_action      => sql_script,
   credential_name => 'username',
   enabled         => true
   );
end;
/

Вы можете расширить и внедрить приведенный выше пример в свои сложные процедуры. здесь есть хорошее объяснение.

person Lalit Kumar B    schedule 18.04.2020
comment
Если вы подробно читаете вопрос - Query вокруг - вызывает функцию экспорта буфера в хранимой процедуре, которая заполняет временные таблицы поддержки. Я попробовал буфер из утилиты sqlplus, и он работает хорошо. К вашему сведению. Спасибо. - person Vicky; 19.04.2020
comment
spool — это команда sqlplus, а не команда PL/SQL. Для отчетов и т. д. sqlplus можно легко использовать в автоматическом режиме на уровне ОС. А теперь задумайтесь над этим, стоит ли вызывать процесс ОС внутри процедуры? Вам нужно сделать шаг назад и вернуться к чертежной доске и подумать о дизайне, чем прыгать на том, как добиться результата. Сосредоточьтесь, что делать. - person Lalit Kumar B; 19.04.2020
comment
Полностью согласен. Я думаю, что в этом случае спул может не работать, поскольку данные агрегируются и генерируются во временных таблицах в хранимых процессах, которые выполняются в ежедневных планировщиках. Любые другие альтернативы? - person Vicky; 19.04.2020
comment
@Vicky Действительно, есть способ использовать планировщик. Я обновил свой ответ, посмотрите. - person Lalit Kumar B; 19.04.2020
comment
Он просто решает одну часть этого, то есть вызов спула, постановка проблемы - вызов экспорта временной таблицы, которая является частью хранимой процедуры, я не вижу возможности сделать это. Данные временной таблицы также доступны только в данном сеансе, поэтому нам нужно выполнить извлечение данных в рамках того же сеанса. дайте мне знать, если у вас есть какие-либо предложения по этому поводу. - person Vicky; 20.04.2020
comment
Если вы используете глобальные временные таблицы и хотите экспортировать данные из этого сеанса в файл ОС, то это проектная катастрофа. Существует очень уродливый способ добиться того, что вы ищете, используйте client_info для вывода данных для сеанса. Результат сеанса будет доступен в v$session. - person Lalit Kumar B; 20.04.2020
comment
Можно ли будет вызвать задание, которое было создано (вы предложили выше из хранимой процедуры, используя run_job или что-то подобное)? если у вас есть образец, это было бы полезно. - person Vicky; 21.04.2020
comment
Вы можете увидеть примеры здесь oracle-base.com/articles/12c/scheduler- улучшения-12cr2 - person Lalit Kumar B; 21.04.2020