Выгрузка в Excel

Добрый день!
Подскажите пожалуйста как максимально просто выгрузить данные из таблицы Оракла в эксель. Обязательное условие: делать это через процедуру или функцию. Например имеется таблица ТОВАР c полями код, название и цена. Требуется в итоге получить файл с такой структурой:

код | название | цена
102 Товар А 200

Выгрузка данных из Oracle (Developer Reports) в Excel
Привет всем! Может кто делал это подскажите плиз, хотя бы направление в котором думать. Знаю.

Xml выгрузка
Всем привет. Я в этом профан, так что не судите строго если говорю бред) Ситация такая, у меня.

Выгрузка из базы данных
доброе утро Выгрузка данных из базы данных. при выгрузуке из базы данных в отчеты в формате .xls.

Выгрузка из базы данных
Подскажите как можно сделать выгрузку s sqlplus чтобы она выгружалась в txt и к примеру ставила.

Можно поискать в интернете пакеты AS_XLSX и AS_READ_XLSX. Первый пишет, а второй читает файл *.xlsx . Сделать можно не все, но достаточно много. Есть некоторые баги (если очень большой набор данных) поэтому я переделывал (один из пакетов точно) с varchar2 на clob. Но для небольшого количества столбцов работают.

Добавлено через 1 минуту
Якобы в 12 версии есть готовый драйвер для подключения внешних excel-файлов. Но у меня нет 12 версии.

Excel — Подключение и получение данных с SQL сервера

Мало пользователей, да и начинающих программистов, которые знают о возможности Excel подключаться к внешним источникам, и в частности к SQL серверу, для загрузки данных из этих источников. Эта возможность достаточно полезна, поэтому сегодня мы займемся ее рассмотрением.

Функционал Excel получения данных из внешних источников значительно упростит выгрузку данных с SQL сервера, так как Вам не придется просить об этом программиста, к тому же данные попадают сразу в Excel. Для этого достаточно один раз настроить подключение и в случае необходимости получать данные в Excel из любых таблиц и представлений Views, из базы настроенной в источнике, естественно таких источников может быть много, например, если у Вас несколько баз данных.

Задача для получения данных в Excel

И для того чтобы более понятно рассмотреть данную возможность, мы это будем делать как обычно на примере. Другими словами допустим, что нам надо выгрузить данные, одной таблицы, из базы SQL сервера, средствами Excel, т.е. без помощи вспомогательных инструментов, таких как Management Studio SQL сервера.

Примечание! Все действия мы будем делать, используя Excel 2010. SQL сервер у нас будет MS Sql 2008.

И для начала разберем исходные данные, допустим, есть база test, а в ней таблица test_table, данные которой нам нужно получить, для примера будут следующими:

Скриншот 1

Эти данные располагаются в таблице test_table базы test, их я получил с помощью простого SQL запроса select, который я выполнил в окне запросов Management Studio. И если Вы программист SQL сервера, то Вы можете выгрузить эти данные в Excel путем простого копирования (данные не большие), или используя средство импорта и экспорта MS Sql 2008. Но сейчас речь идет о том, чтобы простые пользователи могли выгружать эти данные.

Заметка! Если Вас интересует SQL и T-SQL, рекомендую посмотреть мои видеокурсы по T-SQL, с помощью которых Вы «с нуля» научитесь работать с SQL и программировать с использованием языка T-SQL в Microsoft SQL Server.

Настройка Excel для получения данных с SQL сервера

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

И первое что нам нужно сделать, это конечно открыть Excel 2010. Затем перейти на вкладку «Данные» и нажать на кнопку «Из других источников» и выбрать «С сервера SQL Server»

Скриншот 2

Скриншот 3

Затем у Вас откроется окно «Мастер подключения данных» в котором Вам необходимо, указать на каком сервере располагается база данных и вариант проверки подлинности. Вот именно это Вам придется узнать у администратора баз данных, а если Вы и есть администратор, то заполняйте поля и жмите «Далее».

Скриншот 4

Имя сервера – это адрес Вашего сервера, здесь можно указывать как ip адрес так и DNS имя, в моем случае сервер расположен на этом же компьютере поэтому я и указал localhost;
Учетные данные – т.е. это логин и пароль подключения к серверу, здесь возможно два варианта, первый это когда в сети Вашей организации развернута Active directory (Служба каталогов или домен), то в этом случае можно указать, что использовать те данные, под которыми Вы загрузили компьютер, т.е. доступы доменной учетки, и в этом случае никаких паролей здесь вводить не надо, единственное замечание что и на MSSql сервере должна стоять такая настройка по проверки подлинности. У меня именно так и настроено, поэтому я и выбрал этот пункт. А второй вариант, это когда администратор сам заводит учетные данные на SQL сервере и выдает их Вам, и в этом случае он должен их Вам предоставить.

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

Скриншот 5

В следующем окне нам предложат задать имя файла подключения, название и описание, я например, написал вот так:

Скриншот 6

После того как Вы нажмете «Готово» у Вас откроется окно импорта этих данных, где можно указать в какие ячейки копировать данные, я например, по стандарту выгружу данные, начиная с первой ячейки, и жмем «ОК»:

Скриншот 7

В итоге у меня загрузятся из базы вот такие данные:

Скриншот 8

Т.е. в точности как в базе. Теперь когда, например, изменились данные в этой таблице, и Вы хотите выгрузить их повторно Вам не нужно повторять все заново, достаточно в excel перейти на вкладку «Данные» нажать кнопку «Существующие подключения» и выбрать соответствующее, т.е. то которое Вы только что создали.

Скриншот 9

Вот собственно и все, как мне кажется все достаточно просто.

Таким способом получать данные в Excel из базы SQL сервера очень удобно и главное быстро, надеюсь, Вам пригодятся эти знания полученные в сегодняшнем уроке. Удачи!

Oracle SQL Developer 19.2.1

Oracle SQL Developer – это полнофункциональная среда разработки на языке SQL и расширении PL/SQL, написанная на JAVA. Благодаря этому она запускается на всех ОС, где доступна JAVA: Windows, Linux, Mac OS. Продукт имеет проприетарную лицензию, что ограничивает ее использование в организациях.

В среде SQL Developer реализованы инструменты создания и изменения структуры базы данных в графическом режиме. Также она позволяет вносить или изменять непосредственно сами данные. Хотя платформу можно использовать для MySQL, MS SQL и DB2, изначально данная среда разработки «заточена» для работы с базами данных Oracle. Это объектно-реляционная СУБД широкого применения. Для личного использования доступна версия Express Edition (XE). В версии XE имеются ограничения на использование только 1 процессора и 1 Гб оперативной памяти.

Для SQL Developer написано много расширений, выпущенных как компанией Oracle, так и сторонними разработчиками. Среда не имеет русифицированного интерфейса. Однако если применяется СУБД от Oracle, рекомендуется использовать полный комплект от официального разработчика. Также доступна расширенная версия — SQL Developer Data Modeler (распространяется бесплатно).

Функции и особенности Oracle SQL Developer 19.2.1:

Тесная связь между средой разработки и СУБД;
Совместимость с различными ОС;
Сохранение настроек в XML файлах (их можно перенести на другой ПК);
Отсутствие русской локализации;
Бесплатное распространение версии для личного пользования.

Скриншоты

Скриншот №1 к программе Oracle SQL Developer Скриншот №2 к программе Oracle SQL Developer Скриншот №3 к программе Oracle SQL Developer

Создание Excel файла из селекта с параметрами при помощи чистого PL/SQL, как альтернатива Oracle*Reports

В конце, я написал один PL/SQL пакет, который находится в database и может быть вызван из любой аппликации. В ходе написания я столкнулся со многими ограничениями и хочу рассказать о том, как их поборол.

Для тех — кто сомневается, я этого, конечно не мог знать заранее, но за несколько лет, что пакет работает в большой компании, у меня не было проблем свести RDF любой сложности, с многими триггерами/формулами, в один селект, хвала Ораклу. Наоборот, так как селект — стринговый параметр и его можно построить динамически, это дает большую гибкость. В параметрах можно задать даже имя таблицы.

Прежде всего не судите строго за обилие англицизмов(так, по-моему, это называется), я просто давно вне русского программного сообщества и не знаю, чем заменяют эти слова.

Очень часто в аппликациях, написанных в Oracle Forms/Reports для создания файла Excel используют Oracle*Reports, потому что там есть возможность использовать параметры и видоизменять селект до его выполнения. Потом в триггер на уровне строки вывода пишут вывод в файл. Получается csv файл. Ну что же, можно и так, конечно.
Если вместе с Excel нужно создать pdf, то никуда не денешься, пользуйся Reports и не жалуйся как тебя достала эта программа. Но ведь часто нужен только Excel и городить для этого RDF как-то не хочется.

Примерно такой набор. Думаю, тут все понятно. Несколько слов:

LIMIT_ROWS, LIMIT_LEN позволяют делить результирующий файл в процессе создания по мере достижения предельных значений на несколько Excel корректных.

LITERAL_PARAMS говорит о том, как использовать параметры выполнения — вставлять значения или выполнять селект в dbms_sql с dbms_sql.bind_variable.

OUT_TYPE задает формат: Excel Workbook,CSV,HTML,XML

Как обеспечить динамичность селекта с параметрами, получаемыми в runtime

Параметры передаем так:

Язык предвыполнения

Получаем параметры выполнения.
Компилируем текст селекта.
Подаем его для выполнения следующему шагу.

Вот язык, который в конце покрывал все мои потребности

В тексте селекта это выглядит как комментарий(hint)

Шаг компиляции заключается в том, что я нахожу в тексте команду, если один из операндов требует выполнения — выполняю это как select (expression) from dual или как PL/SQL блок в execute immediate и заменяю всю команду на результат выполнения.

или
ну и так далее…

Я это описал для того, чтобы вы поверили, что эти приемы позволяют писать действительно эффективные селекты.
Никаких » and (:param1 is null or table_field=:param1)»

Парсинг и выполнение

Мы получили самое главное — список полей селекта в PL/SQL таблице l_LogColumnTblInit.
Это для нас выполнил великий пакет DBMS_SQL. Теперь мы можем организовать цикл по полям селекта.
Тот, кто пишет на Java(в том числе и я теперешний) посмеется над такой победой, там это всегда было — перебор полей в PreparedStatement.
Сейчас, зная Java, я бы написал бы, может, по другому, но принципиальные вещи не изменились бы.
Кстати, здесь я встретил ограничение на размер селекта 32К, не сразу, в ходе эксплуатации, когда начали писать серьезные селекты. И тут меня снова порадовал Оракл. Оказывается, длинный селект можно разбить на порции 256 байт, зарузить в PL/SQL таблицу l_LongSelectStmt dbms_sql.varchar2s и передать в overload версию dbms_sql.parse.

Теперь пришло время подумать о форматах вывода.
Допустим, наш селект выглядит так:

Для вывода в формате CSV нужно написать

Для вывода в формате HTML нужно написать

Для создания самого красивого, но и самого сложного формата Excel Workbook, мне пришлось поэкспериментировать с Excel. Excel Workbook — это не бинарный, а текстовый файл, его можно посмотреть и понять, как там все устроено.
Там есть CSS, определения Workbook,Worksheet, заголовков таблиц. Не буду углубляться, это не очень сложно понять, если вы встречали раньше HTML.
В Excel Workbook строка вывода будет выглядеть примерно так

Здесь, как вы видите, нам может пригодиться знание типов данных из виртуальной таблицы, полученной в dbms_sql.describe_columns.
Если сравнивать типы вывода, то можно сказать следующее:
CSV — маленький по размеру(это плюс), некрасивый, нет возможности нескольких таблиц(spreadshhets)
HTML — средний по размеру, достаточно сексуальный, нет возможности нескольких таблиц
Excel Workbook — большой файл, красивый, есть возможность создания нескольких таблиц

Алгоритм работы

Цикл по полям

Двигаясь по таблице выходных полей, заворачиваем очередное поле в соответствующие формату тэги или просто добавляем табуляцию(CSV). Теперь вы поняли, как я ударился об эти 4000 байтов. Мне пришлось проверять перед слиянием строк длину результата и, если она была больше 4000, то начинал новое поле вывода, примерно так:

Когда селект построен, выполняем его. Если селект большой, то его надо загрузить в dbms_sql.varchar2s таблицу и выполнить в dbms_sql. Если ваш DBA сказал, что он не потерпит литералы и требует, чтобы параметры были bind variable, то тоже нужно использовать dbms_sql с dbms_sql.bind_variable.
Иначе, если ваш селект поместился в 32К вашей varchar2 переменной l_Stmt можете открыть ref cursor:

Цикл по курсору

Делаем fetch и пишем в utl_file. Следим за количеством строк и за величиной выводимого файла, если нужно, завершаем его(красиво, Excel корректно) и начинаем следующий.
В конце, или, если это Excel Workbook в отдельном sheet, выводим параметры, с которыми выполнен отчет.

Ну вот, наверно и все по большому счету.
Наверное теперь можно показать результат:

Если кому интересно, я могу рассказать, как я завернул этот пакет в другой, который зиповал файл, если он был большой, посылал его по мейлу как ссылку или как attachment, но главное, это определения параметров и типовой экран ввода.

Как из oracle sql developer выгрузить в excel