Oracle apex выгрузка в excel
This topic describes how to export and import data between Oracle Database XE 11.2 and XE 18c for Oracle Application Express (APEX) users.
Application Express Installation Guide for more information about Upgrading Oracle Application Express (APEX) within Oracle Database Express Edition
To export the data from your 11.2 XE database, perform the following steps:
Create a directory on the local file system for the DUMP_DIR directory object.
Connect to the 11.2 XE database as user SYS using the SYSDBA privilege.
Create directory object DUMP_DIR and grant READ and WRITE privileges on the directory to the SYSTEM user.
Export data from the 11.2 XE database in the DUMP_DIR directory.
Deinstall Oracle Database XE 11.2 if installation of Oracle Database XE 18c is planned on the same system. See Deinstalling Oracle Database XE for more information
Install Oracle Database XE 18c.
To import data to the 18c XE database, perform the following steps:
Connect to 18c XE database as user SYS using the SYSDBA privilege.
Create directory object DUMP_DIR and grant READ and WRITE privileges on the directory to the SYSTEM user.
Import data to your 18c XE database from the dump folder.
Remapping the directory is necessary when you use different directory file naming conventions. The first argument of the REMAP_DIRECTORY parameter is the location of your 11.2 XE data files (the source) and the second argument is the location of the 18c XE data files (target).
See Oracle Database Utilities for more information about impdp REMAP_DIRECTORY parameter syntax
ORA-39083: Object type TABLESPACE:"SYSAUX" failed to create with error
ORA-31685: Object type USER:"SYS" failed due to insufficient privileges
ORA-39083: Object type PROCACT_SYSTEM failed to create with error
ORA-01917: user or role 'APEX_040000' does not exist
ORA-31684 "already exists" errors
Copy the file apxfix.sql into the top level directory of the APEX source you used to upgrade APEX in your 11.2 XE database. Change your working directory to that source.
Run apxfix.sql passing the schema name that owns the APEX software. For example, if you upgraded 11.2 XE to APEX 5.1.4 prior to exporting the data, provide the schema name APEX_050100 as the argument:
Configure the embedded PL/SQL gateway. Run the apex_epg_config.sql script passing the file system path to the Oracle Application Express (APEX) software. For example, if you unzipped the APEX software in /tmp:
"Кодекс жути ночной" - это инкунабула в цикле романов Александра Бушкова о приключениях лорда Сварога, в прошлом майора ВДВ, волею судеб, транспространственной магии и собственного незаурядного и неуемного характера овладевшего многими королевствами и многими проблемами, далеко выходящими за пределы майорских забот. В оном кодексе сведены воедино свидетельства очевидцев о всяческих жутких и необъяснимых явлениях, добавивших авторам наблюдений немало седых волос. Таков MS Excel с точки зрения базиста.
Проблема в том, что в базе данных - таблицы. И в MS Excel - тоже таблица. Плюс огромные возможности по визуализации данных, их представлению и прочим творческим действиям. Огромные! Руководитель и аналитик любят MS Excel, поскольку в нем все на виду и со всем этим парой нажатий кнопок можно сделать все, что угодно: обсчитать, график построить. И они требуют от базиста: Excel - в базу! или базу в Excel! Базист сползает под стол в предвкушении тяжелого садо-маза. Oracle напрямую преобразование в MS Excel не поддерживает. И обратно тоже.
База данных и Excel внутренне глубоко противоречивы друг другу, они похожи лишь внешне, но внутри оптимизированы в противоположных направлениях. Это своего рода антиматерии ИТ.
Существуют частные - и зачастую авторские - решения частных задач.
Выгрузка Oracle -> MS Excel
Предположим, у нас есть таблица 3x3 в Oracle. Имитируем ее запросом:
select 1 as id, 'Some text' as text, 'Английский' as lang from dual
union all
select 2, 'Еще текст', 'Русский' from dual
union all
select 3, 'Lisää teksti', 'Финский' from dual
;
1 Some text Английский
2 Еще текст Русский
3 Lisää teksti Финский
Ее надо выгрузить в MS Excel. Рассмотрим три варианта, используем три PL/SQL пакета - ни один из них в собственно Oracle не входит. Поэтому до их применения пакеты надо сначала найти, потом установить, потом разобраться в них. Потом осознать их ограничения.
Выгрузка в CSV - Comma Separated Values, список через запятую
CSV - это стандартный формат "взрослого" обмена данными. Именно данными.
Самый простой способ выгрузить таблицу в формат, понимаемый MS Excel'ем, это записать ее данные в разделенный сепараторами список, что-то типа (сепаратор - запятая)
1,Some text,Английский
2,Еще текст,Русский
3,Lisää teksti,Финский
MS Excel это поймет, но есть беда - запятая часто используется в русских системах в качестве десятичного разделителя, может запросто встретиться в текстовых строках и т.п. Поэтому в качестве разделителя чаще используют точку с запятой (;), а сами элементы списка помещают в двойные кавычки, причем только текстовые значения - а числовые оставляют "голыми"; вероятность перепутать разделитель резко снижается:
1;"Some text";"Английский"
2;"Еще текст";"Русский"
3;"Lisää teksti";"Финский"
Это можно получить простым запросом, в котором, правда, все придется прописать абсолютно явно:
with tab as
(
select 1 as id, 'Some text' as text, 'Английский' as lang from dual
union all
select 2, 'Еще текст', 'Русский' from dual
union all
select 3, 'Lisää teksti', 'Финский' from dual
)
select id || ';"' || text || '";"' || lang || '"' as line from tab
;
1;"Some text";"Английский"
2;"Еще текст";"Русский"
3;"Lisää teksti";"Финский"
А дальше возникнет проблема - как записать это в файловую систему?? Поэтому для генерации даже CSV лучше воспользоваться каким-нибудь авторским PL/SQL пакетом и написать что-то типа:
declare
p_dir varchar2(200) := 'YK_EXPORT_DIR';
p_file_name varchar2(200) := 'my_file.csv';
l_refcursor sys_refcursor;
begin
open l_refcursor for select id as c1, text as c2, lang as c3 from tab;
csv.set_quotes(p_add_quotes => TRUE, p_quote_char => '"');
csv.set_separator(p_sep => ';');
csv.generate_rc(p_dir, p_file_name, l_refcursor);
end;
При этом 'YK_EXPORT_DIR' - это объект базы типа directory , указывающий на конкретный каталог файловой системы сервера.
Это - самый простой способ.
Выгрузка в XLSX - "новый" Excel
XLSX - это формат для создания готовых отчетов, которые красиво выглядят и могут быть предъявлены начальству и/или поставлены на полку.
Иногда требуется вывести данные не во вполне понимаемый MS Excel'ем естественный для обмена данными CSV, а в "натуральный Excel". Скажем сразу, реальных способов вывести данные в "старый" формат XLS (95-2003) сейчас не существует, выводить будем в "новый" формат XLSX, представляющий по сути компрессированный XML документ. Т.е. файл бинарный, а структура внутри текстовая и упорядоченная.
В этом случае стоит сразу воспользоваться авторским пакетом, уже другим, и записать что-то типа:
declare
p_dir varchar2(200) := 'YK_EXPORT_DIR';
p_file_name varchar2(200) := 'my_file.xlsx';
v_row number := 0;
begin
as_xlsx.clear_workbook;
as_xlsx.new_sheet;
for c in (select id, text, lang from tab) loop
v_row := v_row + 1;
as_xlsx.cell(p_col => 1, p_row => v_row, p_value => c.id);
as_xlsx.cell(p_col => 2, p_row => v_row, p_value => c.text);
as_xlsx.cell(p_col => 3, p_row => v_row, p_value => c.lang);
end loop;
as_xlsx.save(p_dir, p_file_name);
end;
Выгрузка в XML формата MS Excel
XML - это формат для машинного обмена, но в ряде случаев необходимо выгрузить не в простой и сухой XML от Oracle типа строка-ячейка, а в XML, совместимый с MS Excel.
Вывод в MS Excel XML нужен в тех случаях, когда основным потребителем является машина, но возможен вариант "повышенного интереса" человека, желающего взглянуть на процесс глазами через привычную призму MS Excel. Встречается, в том числе, при пошаговой автоматизации частично ручных процессов.
В этом случае стоит тоже обратиться к специализированному пакету, уже третьему, и записать что-то такого типа:
declare
p_dir varchar2(200) := 'YK_EXPORT_DIR';
p_file_name varchar2(200) := 'my_file.xml';
p_worksheet_name varchar2(200) := 'Book 1';
v_row number := 0;
begin
gen_xl_xml.create_excel(p_dir, p_file_name);
gen_xl_xml.create_worksheet(p_worksheet_name);
for c in (select id, text, lang from tab) loop
v_row := v_row + 1;
gen_xl_xml.write_cell_char(p_row => v_row, p_column => 1,
p_worksheet_name => p_worksheet_name, p_value => c.id, p_style => null);
gen_xl_xml.write_cell_char(p_row => v_row, p_column => 2,
p_worksheet_name => p_worksheet_name, p_value => c.text, p_style => null);
gen_xl_xml.write_cell_char(p_row => v_row, p_column => 3,
p_worksheet_name => p_worksheet_name, p_value => c.lang, p_style => null);
end loop;
end;
Загрузка MS Excel -> Oracle
Можно потребовать преобразовать загружаемый файл в CSV и воспользоваться
- загрузчиком Oracle SQL Developer;
- загрузчиком Oracle APEX (написать его на основе шаблона);
- разобрать CSV самостоятельно написанной PL/SQL процедурой.
Но иногда пользователи могут отказаться это делать и потребовать готовое приложение, которое будет "глотать" родной формат MS Excel или несколько таковых.
Загрузка CSV, XLS, XLSX, XML с использованием плагина Oracle APEX
Плагин Oracle APEX - это дополнительный модуль, который может быть импортирован в приложение Oracle APEX.
Плагин импортируется в приложение Oracle APEX, на страницу помещается стандартный элемент типа file browse , создается процесс "имени плагина" (он будет в разделе "unsupported"), ему обязательно и статично указывается имя элемента file browse , на странице размещается кнопка с действием submit - и файл MS Excel в коллекции APEX с указанным процессу именем.
Загрузка файла MS Excel XLS в коллекцию Oracle APEX с последующим сохранением в виде файла MS Excel XML
Загрузка файла MS Excel XLS в коллекцию Oracle APEX с последующим сохранением в виде файла MS Excel XML
Итак, вроде бы мы нашли решения для всех возможных задач. Но!
Во-первых, для четырех задач нам пришлось применить 4 разных решения от трех разных авторов.
Во-вторых, все они "самодельные" в статусе as is .
В-третьих, это лишь обзор, оставивший за скобками значительное количество нюансов.
В-четвертых, там много реальных ограничений; например, конвертер XLS -> MS Excel XML мне пока удалось реализовать лишь в направлении клиент -> сервер, хотя естественнее было бы обратное.
Начальство любит отчеты. Дайте ему их! Дайте много!! Дайте быстро.
Как-то присутствовал на совещании: что делать сначала, формы или отчеты. Программисты справедливо говорят: нет форм - нет данных - не с чего отчеты делать. Представитель заказчика справедливо говорит: нет отчетов - весь ваш софт на. никому не нужен и платить начальство не станет, ему отчеты нужны. Ругались долго.
Обобщение опытного человека: делать надо формы, а сдавать отчеты. Соответственно, для создания отчетов нужна такая технология, чтоб аж заметно не было: раз - и отчет, два - и еще один. А формы, ясно-дело, бюджетировать по-полной.
Как делать отчеты БЫСТРО?
Очень просто. У нас есть база, в базе данные ("как клопы", цитируя известный сериал). Ну позовите базиста - пусть запрос напишет. Пусть вьюхой оформит. С параметром , если надо. С контекстным , если совсем круто. Он справится. А что дальше делать?
Берем Oracle APEX. Создаем в приложении "Отчеты для начальства" страницу. Или берем существующую:
Берем представление/запрос от базиста - он целых 15 минут его сочинял - и иерархический запрос применил, и аналитические функции. Oracle, работал много, однако:
select bd
, nvl(cou, 0) as cou, sum(cou) over (order by bd) as c_cou
, nvl(total_s, 0) as total_s, sum(total_s) over (order by bd) as c_total_s
, nvl(cash_s, 0) as cash_s, sum(cash_s) over (order by bd) as c_cash_s
, nvl(bank_s, 0) as bank_s, sum(bank_s) over (order by bd) as c_bank_s
from
(
select trunc(sale_date) as sd
, count(*) as cou
, sum(sale_summ) as total_s
, sum(decode(is_cash, 1,sale_summ, 0)) as cash_s
, sum(decode(is_cash, 1,0, sale_summ)) as bank_s
from sales_vi where shop_id = :P0_SHOP_ID group by trunc(sale_date)
),
(
select
(select min(trunc(sale_date)) from sales_vi where shop_id = :P0_SHOP_ID) + level - 1 as bd
from dual
connect by (select min(trunc(sale_date)) from sales_vi where shop_id = :P0_SHOP_ID) + level - 1
)
where sd (+)= bd
order by bd
I created this Blog to share my knowledge especially in Oracle Application Express (APEX) and my feelings .
Thursday, June 28, 2018
How to Export to Excel and Print to PDF in Oracle APEX? The answer.
- How can I export my data from APEX to Excel?
- How can I print to PDF? Or how can I get a document/report with my data?
The reason those questions are asked every time again is that although those features exist to a certain extent in APEX, what you actually want, is not shipped with Oracle Application Express (APEX), at least not yet in Oracle APEX 18.1 and before.
Although the solution to both questions is the same, I'll go into more detail on the specific questions separately.
How can I export my data from APEX to Excel?
People typically want to export data from a Classic Report, Interactive Report, Interactive Grid or a combination of those to Excel.
What APEX provides out-of-the-box is the export to CSV format, which can be opened in Excel.
The biggest issue with CSV is that it's not native Excel format. Depending on the settings of Excel (or better your OS globalization settings) the CSV will open incorrectly. Instead of different columns, you see one big line. You also get an annoying message that some functions will be lost as it's not a native Excel format.
You can customize the CSV separator, so the columns are recognized. But with a global application (users with different settings), it's still a pain. Maybe the biggest issue people have with CSV export is that it's just plain text, so the markup or customizations (sum, group by, . ) are lost.
You can enable the CSV export in the attributes section of the respective components:
When you have BI Publisher (BIP) setup and in APEX specified as Print Server, you have a few more options. In the Classic Report, you find it in the Printing section - there's an option for Excel. In the Interactive Report, there's an option for XLS, the Interactive Grid doesn't have an option.
BI Publisher is expensive and comes with a big infrastructure and maintenance overhead, so this is not an option for many APEX people. But even the companies who have it, are looking at other solutions because although you get a native Excel file, it's cumbersome to use and BIP doesn't export your Interactive Report exactly as you see it on the screen with the customizations you did.
So how to get around those issues then? There are some APEX plugins to export an Interactive Report and Grid as you see it on the screen. The plugin of Pavel is probably the most popular one.
If you need to export one IR/IG at a time to Excel in a pre-defined Excel file, this might be an option for you. If you want to use your own Excel template, the ability to export multiple IR/IG at the same time or want more flexibility all around, you want to read on.
The solution
APEX Office Print (AOP). The AOP plugin extends APEX so you can specify the Excel file you want to start from, your template, in combination with the different APEX reports (Classic Report, Interactive Report, Interactive Grid) and get the output in Excel (or other formats). AOP is really easy to use, yet flexible and full of features no other solution provides. I'll touch on three different aspects customers love.
Interactive Report/Grid to Excel with AOP - WYSIWYG (!)
This feature is what customers love about AOP and something you won't find anywhere else. You can print one or more Interactive Reports and Grids directly to Excel, exactly as you see it on the screen. So if the end-user made a break, added some highlights or did some computations, it's all known by AOP. Even the Group by and Pivot are no problem. The implementation is super simple; in Excel, you can define your template; a title, a logo etc. Where you want to see the Interactive Report or Grid you specify , and for the Interactive Grid you specify . In the AOP APEX plugin, you specify the template, and the static ids of the Interactive Report / Grid regions and that is it! AOP is doing the merge. if in the template the special tags are seen, AOP will generate the IR/IG. Not a screenshot - REAL table data! Here's an example with one Interactive Report:
In your Excel you can add multiple tags, on the same sheet and on different sheets. and this doesn't only work in Excel, but also in Word and PDF!
But there is even more. what if you look at the Interactive Report as a chart?
You got it. AOP even understands this. You can plot the table data with and by using it will generate the chart . and that is a native Office chart, you can still change it in Excel!
Here's an example of the output generated by AOP with three interactive reports, one as a chart:
All the above goodies you can do through the AOP PL/SQL API too. Some people use this to schedule their reports and email them out on a daily basis, so they don't even have to go into APEX.
For me, the Interactive Report and Grid feature are one of the killer features of AOP.
Advanced templates in Excel with AOP
AOP is really flexible in how you build your template. The templating engine supports hierarchical data, angular expressions, conditions, blocks of data so you can view data next to each other and it supports HTML expressions too.
Here's an example of a template which loops over the orders and shows the product of that order. It contains a condition to show an "X" when the quantity is higher than 2 and it also has an expression to calculate the price of the line (unit price * quantity).
The data source specified in the plugin is of type SQL. AOP supports the cursor technique in SQL to create hierarchical data:
And (a part of) the output looks like this:
I'm amazed by what people come up with in their templates to create really advanced Excel sheets. It's really up to your imagination. and a combination of the features of Excel.
Multiple sheets in one Excel file with AOP
We have one customer who basically dumps their entire database in Excel. Every table has its own sheet in Excel. You just need to put the right tags in the different sheets and you are done.
AOP also supports the dynamic generation of sheets in Excel, so you get for example one sheet per customer and on that sheet the orders of that customer. The template looks like this (the magic tag is ):
The output is this:
We built this feature a while back based on some customers feedback.
Dynamic column generation in Excel with AOP
This is a new feature we have been working for in AOP 4.0. By using the we can generate columns dynamically now too:
This might be useful if you want to pivot the data or want to see it in a different format. This feature is also available for Word tables. Another way of pivoting is doing it in Oracle or in an Interactive Report. This feature took us a long time to develop, but we think it's worth it.
I hope by the above I demonstrated why I believe APEX Office Print (AOP) is "THE" solution if you want to export your data from APEX (or the Oracle Database) into Excel.
Let's move on to the second question.
How can I print to PDF? Or how can I get a document/report with my data?
Oracle Application Express (APEX) has two integrated ways to print to PDF: either you use XSL-FO or you use BI Publisher. But the reason people still ask the question of how to print to PDF is that the one is too hard to implement (XSL-FO) and the other (BI Publisher) is too expensive, too hard to maintain and not user-friendly enough.
Again APEX Office Print (AOP) is the way to go. AOP is so easy to use, so well integrated with APEX, that most developers love to work with it. Based on a template you create in Word, Excel, Powerpoint, HTML or Text you can output to PDF. In combination with the AOP plugin or PL/SQL API, it's easy to define where your data and template is, and AOP does the merge for you.
Building the template
It begins the same as with any print engine. You don't want to learn a new tool to build your template in. You want to have a fast result. So the way you get there with AOP is, use the AOP plugin, define your data source and let AOP generate the template for you. AOP will look at your data and create a starter template for you (in Word, Excel, HTML or Text) with the tags you can use based on your data and some explanation how to use the tags.
Here's an example where AOP generates a Word template based on the SQL Query specified in the Data Source:
So now you have a template you can start from. Next, you customize the template to your needs. or you can even let the business user customize the template. The only thing to know is how to use the specific . As a developer, I always thought my time would be better spent than changing the logo on a template or changing some sentences over and over again. With AOP my dream comes true; as a developer, I can concentrate on my query (data), the business user can create the template themselves and send the new version or upload it straight into the app whenever changes are required.
When customers show me what they did with AOP; from creating templates for invoices, bills of materials, certificates to full-blown books, I'm really impressed by their creativity. If you imagine it, you can probably do it :)
Here's the AOP plugin, where we specify where the customized Word template can be found (in Static Application Files) and set the output to PDF:
Features in AOP that people love
When you download APEX Office Print, it comes with a Sample app, which shows the features of AOP in action. Here's a screenshot of some of the Examples you find in the AOP Sample App:
As this blog post is getting long, I won't highlight all the features of AOP and why they rock so much, but I do want to take two features you probably won't find anywhere else.
Native Office Charts and JET Charts in PDF
AOP supports the creation of native Office Charts, so you can even customize the charts further in Word. But sometimes people want to see exactly the chart they have on the screen, it is a JET chart, a Fusion chart, Highchart or any other library. With AOP you can get those charts straight into your PDF! The only thing you have to do is specifying the static id of the region and in your template, you put . AOP will screenshot what the user sees and replace the tag with a sharp image. So even when the customer removed a series from the legend, it's exactly like that in the PDF.
HTML content in PDF
At the APEX World conference, a customer showed their use case of APEX together with AOP. Before they had to manage different Word documents and PDFs, but it was so hard as they had to update different documents every time again, it got out of sync and it was just a pain overall to deal with. So they replaced all this by Oracle APEX and Rich Text Editors. They created a structured database, so the information was in there once, but by using APEX Office Print (AOP) they generate all the different documents (Word/PDF) they need.
AOP will interpret the HTML when it sees an underscore in the tag e.g. , then it will translate that HTML into native Word styling. If a PDF is requested, the Word is converted to PDF, so the PDF contains real bold text, or real colors etc.
Here's an example of how Rich Text is rendered to PDF.
AOP also understands when you use for example HTML expressions in your Classic or Interactive Report, or you do some inline styling. It took us a very long time to develop this feature, but the feedback we get from our customer base made it worthwhile :)
So far I showed Word as starting template for your PDF, but sometimes Powerpoint is a great start too, and not many people know about that. In Powerpoint you can make pixel perfect templates too and go to PDF is as easy as coming from Word.
In our upcoming release of AOP 4.0, we spend a lot of time improving our PDF feature set. We will introduce PDF split and merge and the ability to prepend and append files to any of your documents.
Some last words
If you are interested in what APEX Office Print (AOP) is all about, I recommend to sit down and watch this 45 minutes video I did at the APEX Connect conference. In that presentation, I go from downloading, installing to using and show many features of AOP live.
We at APEX R&D are committed to bringing the best possible print engine to APEX, which makes your life easier. We find it important to listen to you and support you however we can. We really want you to be successful. So if you have feedback for us in ways we can help you, even more, let us know, we care about you. We won't rest before we let everybody know about our mission and want to stay "the" printing solution for APEX.
Sometimes I get emails from developers who tell me they have to do a comparison between the print engines for Oracle APEX, but they love AOP. If you include some of the above features (IR/IG to PDF or Excel, JET Charts, and HTML to PDF) in your requirements, you are guaranteed to work with APEX Office Print, there's nothing else that comes even close to those features :)
AOP's philosophy has been to be as integrated as possible in APEX, as easy as building APEX applications, yet flexible enough to build really advanced reports. We make printing and exporting of data in APEX easy.
I created this Blog to share my knowledge especially in Oracle Application Express (APEX) and my feelings .
Wednesday, September 25, 2019
Free Oracle Cloud: 8. Setup APEX Office Print (AOP) to export to PDF, Excel, Word, Powerpoint, HTML and Text
This post is part of a series of blog posts on the Best and Cheapest Oracle APEX hosting: Free Oracle Cloud.
In the previous posts we setup our Always Free Oracle Cloud machine and an Autonomous Database with Oracle Application Express (APEX). In this post, I want to show you how to get started with the popular printing and reporting engine, APEX Office Print (AOP). The AOP software makes it super easy to export your data into a nice looking PDF, a custom Excel file, a fancy Powerpoint or other output formats of your choice, just the way you want it.
AOP is being used by many customers, even Oracle internally, to export their data in the format they want. The data can come from the database, a REST or GraphQL web service, or even components like the Interactive Report/Grid from Oracle APEX. Although AOP works with any technology, it is most known in the Oracle APEX community as it's the easiest and most integrated print engine for Oracle APEX. You create a template in DOCX, XLSX, PPTX, HTML or TEXT, specify the data source, and tell AOP in which format you want the output to be (PDF, Excel, Word, Powerpoint, HTML, Text) and AOP will do the rest! You can find more information in this presentation about AOP.
I use AOP in every project because exporting/printing is a requirement sooner or later and an essential part of my Oracle APEX apps. So I thought to write how to use this in the Oracle Cloud :)
We have two options: we let our Oracle Autonomous Database and APEX talk to the AOP Cloud or we install an on-premises version of AOP on our own Compute VM.
Enter your email and hit Signup:
You will receive an email. Push the Confirm your email address button:
The browser will open where you can set a password for your account:
After hitting the Set Password button, you are logged in automatically and will see a Getting Started wizard:
Follow the wizard and you are all set! It should take less than 15 minutes :)
In short this is what the wizard will tell you:
- Download the AOP software and unzip the file
- Go to APEX > SQL Workshop > SQL Scripts > Upload and Run the file aop_db_pkg.sql which you find in the db folder. This will install the AOP PL/SQL API.
- Go to APEX > Your APP > Shared Components > Plug-ins and Import the APEX Plug-ins you find in the apex folder.
- Go to APEX > Your APP > Shared Components > Component Settings > APEX Office Print (AOP) and enter your API Key which you find in the Dashboard on the AOP site:
The Component Settings in your APEX app:
The above is to configure APEX Office Print (AOP) in your own app using the AOP Cloud.
When you look closely at the previous screenshot of the Component Settings, look at the AOP URL.
The URL specifies where the AOP Server is running, which the AOP APEX Plug-in and AOP PL/SQL API communicate with. By default this is set to the AOP Cloud, so you don't have to set up an AOP Server in your own environment.
On-premises version of AOP
Although the AOP Cloud is really convenient as it's maintained and support by the APEX Office Print team, some customers prefer to run the AOP Server on their own machine, especially when data can't leave the datacenter.
So if you read on, I will walk you through Setting up the AOP Server on your own Compute VM in the Oracle Cloud. Just be sure you have already installed the AOP Sample Application, plug-ins, and Database Objects, if needed, as instructed in the Getting Started section, above.
From a Terminal connect to your Oracle Cloud VM:
ssh -i ssh_key opc@public_ip
The first thing we do is change to the root user, as we want to install some supporting objects for AOP it will be easier to do it with the root user. Alternatively, in front of every command, you can add sudo.
We logged in as the OPC user, to become the ROOT user we do:
To support PDF output, AOP relies on a 3rd party converter like MS Office or LibreOffice. Here are the steps to install LibreOffice:
yum install java-1.8.0-openjdk.x86_64
yum install cups.x86_64
wget http://ftp.rz.tu-bs.de/pub/mirror/tdf/tdf-pub/libreoffice/stable/6.2.7/rpm/x86_64/LibreOffice_6.2.7_Linux_x86-64_rpm.tar.gz
tar -xvf LibreOffice_6.2.7_Linux_x86-64_rpm.tar.gz
cd /tmp/LibreOffice_6.2.7.1_Linux_x86-64_rpm/RPMS/
yum localinstall *.rpm
ln -s /opt/libreoffice6.2/program/soffice /usr/sbin/soffice
Note: if one of the above commands fail because yum is locked, you can remove the lock with: yum versionlock clear
LibreOffice is installed. To see if everything is fine you can run "soffice --version" and you should see something like this:
You can now, for example, load a sample by clicking the "Load sample" button and select PDF.
Scroll down a bit lower and click the Process button and a PDF is being generated :)
The Web Editor is built in React.js and you can drag-drop your template and add some data to test the features of AOP. There's also a Logging tab (toggle between Editor and Logging), so you can see incoming requests, results and debug output in case of errors.
Now if we want to tell our Oracle APEX apps to use our own AOP Server, the only thing we have to do is change the AOP URL.
In your Oracle APEX app, go to Shared Components > Component Settings > APEX Office Print (AOP) and change the AOP URL to the URL of your own Compute VM:
I would recommend looking at the AOP Sample App which you installed in the last step if you followed the Getting Started wizard. It will show over 500 examples of how to use AOP and its features!
Now I hope you enough knowledge so that you can please your customers with nice looking PDF, Excels and other documents in the format they want.
In the next post, we will add an Object Storage to our Always Free Oracle Cloud Plan so we have a place to store files and backups.
Читайте также: