Как из oracle sql developer выгрузить в excel
В конце, я написал один 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)"
Парсинг и выполнение
Ради этого раздела я затеялся писать эту статью. Здесь я напишу об опыте, который приобрел, и который наверняка не нужен тому, кто не ходил на границах допустимого в Оракле. Например, все знают, что максимальная длина текстового поля в таблице — 4000, но многие ли знают, что предел для конкатенации строкового поля в селекте в оракле тоже 4000 байтов.
Получили селект после предкомпиляции с параметрами выполнения. Он у нас в переменной l_Stmt.
К сожалению, в PL/SQL нет легкой возможности организовать цикл по полям селекта, как это можно было бы сделать в Java. Будем пользоваться процедурой dbms_sql.parse, которая возвращает поля селекта как таблицу, по которой сделаем цикл в дальнейшем.
Что мы хотим сделать?
Выполнить парсинг и получить список полей с datatype.
Для этого применяем
Мы получили самое главное — список полей селекта в 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, но главное, это определения параметров и типовой экран ввода.
I'm using Oracle SQL Developer 3.0. Trying to figure out how to export a query result to a text file (preferably CSV). Right clicking on the query results window doesn't give me any export options.
21.5k 17 17 gold badges 72 72 silver badges 97 97 bronze badges6 Answers 6
Version I am using
Update 5th May 2012
Jeff Smith has blogged showing, what I believe is the superior method to get CSV output from SQL Developer. Jeff's method is shown as Method 1 below:
Method 1
Add the comment /*csv*/ to your SQL query and run the query as a script (using F5 or the 2nd execution button on the worksheet toolbar)
Method 2
Run a query
Right click and select unload.
Update. In Sql Developer Version 3.0.04 unload has been changed to export Thanks to Janis Peisenieks for pointing this out
Revised screen shot for SQL Developer Version 3.0.04
From the format drop down select CSV
And follow the rest of the on screen instructions.
Not exactly "exporting," but you can select the rows (or Ctrl - A to select all of them) in the grid you'd like to export, and then copy with Ctrl - C .
The default is tab-delimited. You can paste that into Excel or some other editor and manipulate the delimiters all you like.
Also, if you use Ctrl - Shift - C instead of Ctrl - C , you'll also copy the column headers.
FYI, you can substitute the /*csv*/ for other formats as well including /*xml*/ and /*html*/ . select /*xml*/ * from emp would return an xml document with the query results for example. I came across this article while looking for an easy way to return xml from a query.
FYI to anyone who runs into problems, there is a bug in CSV timestamp export that I just spent a few hours working around. Some fields I needed to export were of type timestamp. It appears the CSV export option even in the current version (3.0.04 as of this posting) fails to put the grouping symbols around timestamps. Very frustrating since spaces in the timestamps broke my import. The best workaround I found was to write my query with a TO_CHAR() on all my timestamps, which yields the correct output, albeit with a little more work. I hope this saves someone some time or gets Oracle on the ball with their next release.
Есть ли альтернативный способ скопировать данные из листа Excel и вставить его в таблицу с помощью Oracle SQL Developer!?
В настоящее время я использую (PL/SQL Developer), пишу (для обновления) в конце инструкции select, Ex:
Затем вставьте столбцы из Excel в таблицу.
спросил(а) 2011-11-23T15:34:00+04:00 9 лет, 12 месяцев назадОн не совсем копирует и вставляет, но вы можете импортировать данные из Excel с помощью Oracle SQL Developer.
Перейдите к таблице, в которую вы хотите импортировать данные, и нажмите вкладку Data .
После нажатия на вкладке данных вы увидите раскрывающееся меню, в котором указано Actions.
Нажмите Actions. и выберите нижнюю опцию Import Data.
Затем просто следуйте указаниям мастера, чтобы выбрать правильный лист и столбцы, которые вы хотите импортировать.
РЕДАКТИРОВАТЬ: Чтобы просмотреть вкладку данных:
-
Выберите SCHEMA , где создана ваша таблица. (Выберите вкладку "Подключения" на левой панели).
Щелкните правой кнопкой мыши на SCHEMA и выберите SCHEMA BROWSER .
Выберите таблицу из списка (указав свою схему).
Теперь вы увидите вкладку Data .
Нажмите Actions и Import Data.
Ни один из этих вариантов не отображается для меня. Способ вставить данные из Excel выглядит следующим образом:
Добавьте дополнительный столбец слева от данных вашей таблицы (если вы
не отображаются номера строк в PL/SQL Developer, у вас может не быть
чтобы добавить лишний пустой столбец влево).
Скопировать строки данных из вашей таблицы, включая пустые
столбец.
В PL/SQL Developer откройте свою таблицу в режиме редактирования. Ты можешь
щелкните правой кнопкой мыши имя таблицы в обозревателе объектов и выберите "Редактировать данные"
или напишите свой собственный оператор select, который включает в себя rowid и click
значок замка. Убедитесь, что ваши столбцы упорядочены так же, как и в вашем
таблицы.
Вот часть, которая заставила меня навсегда выяснить: нажмите на левую
стороне первой пустой строки, чтобы выделить ее. Это не сработает, если вы
не выделяется первая пустая строка.
Вставить, как обычно, с помощью Ctrl + V или щелкнуть правой кнопкой мыши Paste.
Я не мог найти эту информацию нигде, когда мне это было нужно, поэтому я хотел быть уверен, что опубликую ее.
Your search did not match any results.
We suggest you try the following to help find what you’re looking for:
- Check the spelling of your keyword search.
- Use synonyms for the keyword you typed, for example, try “application” instead of “software.”
- Try one of the popular searches shown below.
- Start a new search.
How-To Document
How to Export Data using SQL Developer
Updated: Nov 2009 for SQL Developer 2.1
After completing this How-To, you should be able to understand:
- How to export data to various file formats using SQL Developer
- How to export the object definitions in your schema
Table of Contents
Oracle SQL Developer provides utilities to export both the definitions of the objects in a schema and the data stored in those objects. In this How-To we start by exporting the data and then move onto exporting object definitions.
- Oracle SQL Developer
- Oracle Database 9.2.0.1 or later
- Access to the Sample User Account HR (See Step 2 of this Getting Started: Oracle SQL DeveloperGuide)
SQL Developer provides the ability to export user data to a variety of formats: CSV, XML, LOADER, TEXT, INSERT, HTML and XLS.
-
In order to export the data from a table you can either use the SQL Worksheet and write a SQL query to retrieve the required data or you can Click on the Data tab of a table definition. We'll use this latter approach.
Expand the Connections Navigator and the Tables node.
Click on DEPARTMENTS.
Select the Data tab.
Select insert from the Export Data context menu. This will produce a file of insert statements.
You can also fine tune what is exported by selecting the columns and adding a where clause to reduce the number of records returned. In this example, we'll take the default of all the records and columns.
Exporting Object Definitions
SQL Developer provides the ability to export some or all of your object definitions to script files. You can use these as a backup of the object definitions or run them in another schema. In this exercise, you export all the object definitions and the data for the Departments table.
-
Using the main menu, select Tools->Database Export.
Click Next.
Click Go. The list now displays tables that start with "D".
Читайте также: