X

The Oracle APEX blog is your source for APEX news, technical tips and strategic direction

Recent Posts

Oracle Database + APEX + JavaScript/Python = Awesome!

The Oracle Database Multilingual Engine (MLE) is an exciting new feature, targeted for a future release in Oracle Database.  This feature will enable the inclusion of other programming languages for execution inside the database.  While the native procedural language of Oracle Database will remain the ever-powerful and elegant PL/SQL, extending the database with support for other languages opens up the creation of Oracle Database powered applications to a plethora of developers with other skill sets. The Oracle Database Multilingual Engine is based on the work done for GraalVM, a high-performance virtual machine developed by the researchers at Oracle Labs.  GraalVM is an open source project which is already used with success at many high-profile companies around the world (Twitter, for example). The Oracle Labs, Oracle Database & Oracle APEX teams have collaborated to provide an online, free preview of what this integration will look like.  Beginning today and running through December 2018, anyone can sign up for a free hosted preview ("early adopter") at https://apexea.oracle.com.  The purpose of this site is to offer our many Oracle Database and Oracle APEX customers a glimpse of what the future holds, as well as obtain your feedback and suggestions.   How do you get started? To get started, you first need to sign up for a workspace at https://apexea.oracle.com.  Click the Request a Workspace button and complete the 3 short steps.  Within a minute, you should receive an email with a link to verify your account and create your workspace.  Upon first login, you'll also be prompted to setup a password for your associated APEX account credentials.  These credentials will only be necessary if you create or install an APEX application.   You don't know anything about APEX.  How can you test MLE? Simple.  Once you have successfully logged into your workspace, click the green SQL Workshop icon and then click SQL Commands.  This is a SQL command processor, just like you would find with sqlcl or SQL*Plus.  Using this command interface, you can execute SQL & PL/SQL.  You can create and drop tables.  You can issue any SELECT statement.  You can now also execute JavaScript and Python from the command processor.  As a quick example, select Language = JavaScript and enter the following code: console.log("hello world!"); and click the Run button.  The output should be displayed in the Results region at the bottom. To try out Python (Python support is experimental, at this stage), select Language = Python and execute the following code: from datetime import date today = str(date.today()) print(today)   Let's try another simple example.  Enter the following code in the SQL Command processor with Language = JavaScript: var currentDate = new Date(), day = currentDate.getDate(), month = currentDate.getMonth() + 1, year = currentDate.getFullYear(); console.log(day + "/" + month + "/" + year) click the Run button, and the current date will be printed in the results. For the next example, you will need to create some sample database objects in your workspace.  Click the SQL Workshop tab, then go to Utilities -> Sample Datasets.  Install the EMP / DEPT sample dataset, which will create both tables and insert sample data into them.  After creating the sample tables, navigate back to SQL Commands. To show the integration between the SQL engine and JavaScript, ensure that the Language select list is set to JavaScript and enter the following code: for (var row of mle.sql.execute("SELECT EMPNO, ENAME FROM EMP ORDER BY EMPNO").rows) { console.log("[" + row[0] + ", " + row[1] + "]") }; This will use the MLE engine to execute the SQL query, and then in JavaScript, iterate through the results and print them.  But what if you wanted to include a bind variable in your query?  It's actually quite easy with the SQL driver for JavaScript.  You simply need to provide an array of the bind values following the SQL statement, with the position in the array corresponding to the position of the bind variable in the SQL statement.  As a complete example: for (var row of mle.sql.execute("SELECT EMPNO, ENAME FROM EMP WHERE SAL > :sal AND DEPTNO = :deptno ORDER BY EMPNO", [ 1000, 30 ]).rows) { console.log("[" + row[0] + ", " + row[1] + "]") }; In this example, the value 1000 will be bound to bind variable :sal and 30 will be bound to bind variable :deptno. For more information on the SQL driver for JavaScript in MLE, please refer to the Callouts section of the MLE documentation. It is also possible to dynamically execute JavaScript or Python from PL/SQL, using the PL/SQL package DBMS_MLE.  Data can be exchanged both ways, between JavaScript and PL/SQL.  In SQL Commands, set the Language to "SQL and PL/SQL" and execute the following code: declare l_script_source clob; l_script_handle dbms_mle.script_handle_t; l_script_result number; begin l_script_source := q'~ mle.binds.totalsal = 0; for (var row of mle.sql.execute("SELECT SAL FROM EMP WHERE SAL > " + mle.binds.salary).rows) { mle.binds.totalsal += row[0]; } ~'; l_script_handle := dbms_mle.create_script( l_script_source, 'JS' ); dbms_mle.bind_variable( l_script_handle, 'salary', 1000 ); dbms_mle.execute_script( l_script_handle ); dbms_mle.variable_value( l_script_handle, 'totalsal', l_script_result ); dbms_mle.drop_script( l_script_handle ); dbms_output.put_line( 'Total Salary: ' || l_script_result ); end;   In this block of PL/SQL, you are creating a script handle for the JavaScript code, binding a value for salary (represented as mle.binds.salary in the JavaScript code), executing the script, which will execute the query and then iterate through the results, computing a sum of the salaries, and then retrieving the output value which was assigned in the script (i.e., mle.binds.totalsal).   Where can you use JavaScript or Python in an APEX application? If you are knowledgable with APEX, you'll be able to appreciate the combination of APEX + MLE, as this is where the integration of these two technologies really shines.  JavaScript is available in the APEX application definition, in page processes, validations, computations.  You can author JavaScript functions returning SQL queries as the dynamic source for classic reports, calendars & charts.  And you can use Python for page processes and validations. When used within an APEX context, you are able to reference the value of page items (to both get and set the item values) using mle.binds.PAGE_ITEM.  For example, the following code could be used in a page validation of type "JavaScript Function Body (returning Boolean)": var l = mle.binds.P3_NAME.length; return l >= 3 && l <= 20; The length of the value of page item P3_NAME is referenced via mle.binds.P3_NAME.length.  The function body then returns the boolean result of the expression, if the length is greater than or equal to 3 and less than or equal to 20. Binding of page item values to bind variables in a SQL statement is similar to the PL/SQL example above, but you have to remember that the page item values will be available via mle.binds.PAGE_ITEM.  Assuming you had page 2 in your APEX application with page items for salary and department number, an example would be: for (var row of mle.sql.execute("SELECT EMPNO, ENAME FROM EMP WHERE SAL > :sal AND DEPTNO = :deptno ORDER BY EMPNO", [ mle.binds.P2_SAL, mle.binds.P2_DEPTNO ]).rows) { console.log("[" + row[0] + ", " + row[1] + "]") };   For APEX developers, the easiest way to get started is to install the APEX + MLE Sample Application.  To do this: Login to your workspace Click App Gallery Click MLE + APEX Sample Application Click Install App, Next and Install App. Click the Run button. It's that simple.  You'll need to login with the credentials of your Application Express account, which you would have setup when you first logged into your workspace.  The sample application is unlocked so you can examine how the JavaScript & Python code is used within the app.   Where can you access additional documentation? The Github MLE repository contains documentation for the SQL driver which is built into MLE. It is automatically exposed in the mle.sql JavaScript namespace within APEX. The Help tab of the APEX Page Designer contains additional code examples, when you're editing the JavaScript or Python code attribute in the Property Editor.   How do you provide feedback?  Where can you get support? This hosted early adopter program is not supported by Oracle Support.  It is not intended for production applications.  You will not be able to import the APEX applications developed on this site into any other APEX installation.  It is only intended for evaluation and to get your feedback and suggestions.  We welcome any and all feedback, in the MLE discussion forum in the Oracle Database Developer community.   If you have any questions, please let us know in the MLE discussion forum.  We look forward to your feedback.  We are excited about the future with Oracle Database, APEX and MLE!

The Oracle Database Multilingual Engine (MLE) is an exciting new feature, targeted for a future release in Oracle Database.  This feature will enable the inclusion of other programming languages for...

Announcing Oracle APEX Static Resources on Content Delivery Network

We are pleased to announce the availability of APEX static resources on a Content Delivery Network (CDN).   As defined in Wikipedia, "a content delivery network or content distribution network (CDN) is a geographically distributed network of proxy servers and their data centers. The goal is to distribute service spatially relative to end-users to provide high availability and high performance."  If you have an application which is used by an audience geographically distributed around the globe, you can improve the overall performance of your application by having some of the content delivered from servers physically closer to the end-user.  For example, if I have an application running on the free site https://apex.oracle.com (which is running in a data center in Austin, Texas USA), and a user in Vinnytsia, Ukraine runs my application, they will need to wait for the static files associated with my APEX application to be physically transmitted from Texas to Ukraine.  There is greater network transmission time and higher risk of latency the further away you are from the source.  This results in degraded performance, and is especially perceptible on mobile devices.  But with a CDN, the files may be delivered from a server which is physically closer to the client, and it's all done transparently. The Oracle APEX 18.1 and APEX 18.2 static resources are available via the following paths: https://static.oracle.com/cdn/apex/18.1.0.00.45/ https://static.oracle.com/cdn/apex/18.2.0.00.12/   It is really easy to make use of this. 1) If you want to convert the entire APEX instance to use the static resources from the CDN, you will want to reset the APEX image prefix for your instance.  You should not do this on a live system, as this process will invalidate many objects in the APEX schema and they will need to be recompiled.  Again - don't do this on a live system.  To accomplish this: Locate your APEX distribution Navigate to the apex/utilities subdirectory Connect to your database as user SYS Run:  @reset_image_prefix.sql and when prompted for the image prefix, enter the correct path, like: https://static.oracle.com/cdn/apex/18.2.0.00.12/ If you're using Oracle REST Data Services (ORDS) with APEX, you will need to restart ORDS after resetting the image prefix.   2) If you simply want to use the CDN-hosted resources in a single APEX application: In Application Builder, edit your application and click Shared Components Click User Interface Attributes In the Image Prefix attribute, enter the correct path:  https://static.oracle.com/cdn/apex/18.2.0.00.12/ Click Apply Changes.  That's all there is to it!   Anyone on the planet is welcome to use the APEX resources available via the Content Delivery Network.  If you have an application that is used around the world, you should consider using the CDN.  If you have an application which is only used within a specific geography or only within your company, it may remain faster to have the APEX static resources served by your infrastructure.  Testing can prove this. We will be updating the free site https://apex.oracle.com to make use of the CDN.

We are pleased to announce the availability of APEX static resources on a Content Delivery Network (CDN).   As defined in Wikipedia, "a content delivery network or content distribution network (CDN) is...

Web Source Modules and the Interactive Grid: Part 1

by Andreea Dobre, Oracle Romania in Bucharest With Application Express 18.1, APEX introduced support for using REST Services directly in APEX components: Using Web Source Modules, reports, charts or calendar regions can work directly on top of a REST service - with no manual coding required. However, only „read only“ components support REST Services, thus we can create an Interactive Report or a Chart page on top of a REST service, but not a form page or an Interactive Grid.  The good news is: With a Web Source Module and some custom PL/SQL code, using the APEX_EXEC package, you can REST-enable your Interactive Grid based on a REST Service today. The INSERT, UPDATE or DELETE operations of the interactive Grid will map to the POST, PUT and DELETE operations of a REST service. This blog posting, which is the first part of a series on REST-Enabling the Interactive Grid, will show how this works. The well-known EMP table and a REST service on top of it will serve as an example. This first part will show ... how to create a Web Source Module on top of a REST service how to create an interactive grid (on a local table first) how to have the interactive grid invoke the REST service for SQL INSERT operations Following blog postings will show how to complete that scenario, i.e. support other DML operations and have Interactive Grid actually show data from the REST service. Prerequisites In order to have a proper environment where to start playing with REST Services, go to apex.oracle.com and sign up for a free workspace. Navigate to SQL Workshop > RESTful Services and make sure to check ORDS Based RESTful Services. The environment is now ready to go. First we need to REST-Enable the database schema, which is assigned to the APEX workspace. This can be done in SQL Workshop > RESTful Services > Register Schema with ORDS. As an alternative, you can do that with the following SQL statements in SQL Workshop > SQL Commands. begin ords.enable_schema; end; Next, we need to create a REST Endpoint for a table. The easiest way to achieve that is to simply „REST-Enable“ the table as follows. REST-Enabling a table will create GET, POST, PUT and DELETE endpoints. begin ords.enable_object( p_object => 'EMP' ); end; Then navigate to SQL Workshop > Restful Services and check if the EMP table was enabled within the schema. Use http://{server}/ords/{schema-alias}/emp/ (adjust the URL to your environment and workspace) to check the response. You should have some data displayed. Create Application and Web Source Module Create a new, empty application using the Create Application Wizard or navigate to an existing one. Within your application, navigate to Shared Components and look up Web Source Modules in the Data Sources category. Click on Create Button in order to create a new Web Source Module from Scratch. On the next step, choose Oracle REST Data Services as the Web Source Type, provide a name and the URL from above as the URL Endpoint. Click Next. Keep the default settings for Remote Server on the next step and click Next.  For our exercise we set the Authentication Required to No and click on Discover.  You will see the data preview for REST Service. Click on Create Web Source. Once the Web Source Module is created, you will be redirected to the overview page on existing Web Source Modules. We now need to make a few changes in the Web Source Module we just have created. Click on the module name. Click the Advanced tab and set the Static ID to WebSourceModule_on_EMP, as we will need it when calling the Web Source Module with PL/SQL later on. Open the Operations tab and click the pencil to edit the POST operation. Use the following JSON template as the Request Body Template: {"empno":#EMPNO#,"ename":"#ENAME#","job":"#JOB#","hiredate":"#HIREDATE#","sal":#SAL#,"comm":#COMM#,"deptno":#DEPTNO#,"mgr":#MGR#} Click Apply Changes, then click the pencil corresponding to the POST operation again and on Add Parameter. Create the following parameters and use Request / Response Body as the parameter Type and keep the Direction as In. EMPNO ENAME JOB HIREDATE SAL COMM MGR DEPTNO Then create an additional parameter named Content-Type, use application/json as its Value and HTTP Header as Type. Set the Static switch to No for this parameter. Once finished, you should have 9 parameters as follows. Finally click the Apply Changes button. Create the Interactive Grid After creating the Web Source Module, you'll now use Page Designer to create a new Interactive Grid region. Navigate to Page Designer for an existing page or create a new empty page. Then drag a region of the Interactive Grid type to the page, name it WSM Interactive Grid and use select * from emp as the region source query. Then, head over to the region attributes, enable Editing and, for now, only allow to Add Row. Replace Standard DML processes with custom PL/SQL code Since you have created the interactive grid region on a query selecting the EMP table, any changes would be written back to the EMP table as well. But we want to write our changes "to the web source module". So we need to replace the standard interactive grid DML functionality with our own PL/SQL code. This code will use the APEX_EXEC PL/SQL package in order to invoke the Web Source Module. Note that APEX_EXEC package will encapsulate all data processing and querying capabilities. We do not need to use JSON parsing or make request calls anymore. Navigate to the Page Processes of your page and look up the Interactive Grid - Automatic Row Processing (DML) process.  Navigate to the process attributes and change the Target Type attribute to PL/SQL and provide the following PL/SQL code. declare l_parameters apex_exec.t_parameters; l_dateval date; begin -- how to handle nulls on HIREDATE if :HIREDATE is null then l_dateval := sysdate; else l_dateval := to_date( :HIREDATE ); end if; -- in this case we take into account only the INSERT operation therefore the condition will be based on :APEX$ROW_STATUS = 'C' -- APEX_EXEC.ADD_PARAMETER procedure adds an SQL parameter to the parameter collection if :APEX$ROW_STATUS = 'C' then apex_exec.add_parameter( p_parameters => l_parameters, p_name => 'ENAME', p_value => :ENAME ); apex_exec.add_parameter( p_parameters => l_parameters, p_name => 'EMPNO', p_value => :EMPNO ); apex_exec.add_parameter( p_parameters => l_parameters, p_name => 'JOB', p_value => :JOB ); apex_exec.add_parameter( p_parameters => l_parameters, p_name => 'HIREDATE', p_value => to_char( l_dateval, 'YYYY-MM-DD' ) || 'T00:00:00Z' ); apex_exec.add_parameter( p_parameters => l_parameters, p_name => 'SAL', p_value => coalesce( :SAL, 'null' ) ); apex_exec.add_parameter( p_parameters => l_parameters, p_name => 'COMM', p_value => coalesce( :COMM, 'null' ) ); apex_exec.add_parameter( p_parameters => l_parameters, p_name => 'MGR', p_value => coalesce( :MGR, 'null' ) ); apex_exec.add_parameter( p_parameters => l_parameters, p_name => 'DEPTNO', p_value => coalesce( :DEPTNO, 'null' ) ); end if; -- invoke Web Source Module for and select data apex_exec.execute_web_source( p_module_static_id => 'WebSourceModule_on_EMP', p_operation => case :APEX$ROW_STATUS when 'C' then 'POST' end, p_parameters => l_parameters ); end; Test the functionality Now it's time to do a first test on the Interactive Grid. Save your changes and run the page. First, the Interactive Grid looks as follows: Do a few changes and save the page ... When saving the changes to the interactive grid, you should notice that the actual DML operation takes a bit longer than usual. This is because interactive grid does now do a REST service invocation instead of simply executing SQL DML on a local table.  Adding validations to avoid error messages from the REST service However, depending on the actual data being entered, you won't see successful changes, but error messages instead: This can happen when the entered data violates constraints being in place. Within the Oracle Database, we would see a clear ORA error message telling us what went wrong. However, a REST infrastructure does not know about databases and ORA error messages - REST is all about HTTP. A REST service responds with a HTTP status code, which is 200 for a successful operation and between 400 and 599 for error situations. The meanings of HTTP status codes are very generic - they have been made for HTTP and the web, not for interacting with a table in a database. So, when the REST POST operation results in an error (because of table constraints), the REST service will just respond with either HTTP-500 (Internal Server Error) or HTTP-400 (Bad Request). To avoid end users running into these error messages, we should add Validations to the Interactive Grid columns - in order to make sure that only valid data is being posted to the REST Service. In Page Designer, navigate to the Interactive Grid, open the Columns node in the navigation tree on the left and add Validations to the columns as follows: The manager EMPNO, being inserted, is already present in the table Create a validation on the MGR column, pick Rows Returned as the Validation type and use select 1 from emp where empno = :MGR as the SQL Query. The Salary is a valid number and between 0 and 100000. Create a validation on the SAL column, pick Rows Returned as the Validation type and use select 1 from dual where trunc(nvl(:SAL,0),2) < 100000 as the SQL Query. Make sure that the DEPTNO value is existent in the DEPT table Create a validation on the DEPTNO column, pick Rows Returned as the Validation type and use select 1 from dept where deptno = :DEPTNO as the SQL Query. The Commission value is a valid number and between 0 and 100000. Create a validation on the COMM column, pick Rows Returned as the Validation type and use select 1 from dual where trunc(nvl(:COMM,0),2) < 100000 as the SQL Query. After these changes being applied, the interactive grid will not allow to post invalid values to the REST service any more. This concludes the first blog posting on REST-Enabling the Interactive Grid component. Subsequent postings will explain how to implement DELETE and UPDATE operations and how to have the interactive grid displaying data from the REST service as well. p.p1 {margin: 0.0px 0.0px 8.0px 0.0px; font: 12.0px 'Times New Roman'; color: #000000; -webkit-text-stroke: #000000; min-height: 15.0px} p.p2 {margin: 0.0px 0.0px 8.0px 0.0px; font: 12.0px 'Times New Roman'; color: #000000; -webkit-text-stroke: #000000} span.s1 {font-kerning: none}

by Andreea Dobre, Oracle Romania in Bucharest With Application Express 18.1, APEX introduced support for using REST Services directly in APEX components: Using Web Source Modules, reports, charts or...

Announcing Oracle APEX 18.2

  Oracle Application Express (APEX) 18.2 is now generally available! APEX enables you to develop, design and deploy beautiful, responsive, data-driven desktop and mobile applications using only a browser. APEX 18.1 was a dramatic step forward in the ease of integration with remote data sources, and the easy inclusion of robust, high-quality application features.  Building upon this success, APEX 18.2 introduces a number of features as well as improves the overall quality of APEX. Some of the improvements to Oracle APEX 18.2 include: Create Page Wizards In Oracle APEX 18.2, the Create Page wizard has been extended to enable the creation of application pages for Side-by-Side Master Detail and a Dashboard.  The Side-by-Side master detail form creates a single page master detail utilizing a side by side layout and report regions with modal edit windows.  The dashboard enables the creation of a first-cut dashboard based on sample data which you can later customize using Page Designer.  Font APEX Font APEX is a collection of over 1,000 high-quality icons, many specifically created for use in business applications.  Font APEX in APEX 18.1 included a new set of high-resolution 32 x 32 icons which included much greater detail and the correctly-sized font would be automatically selected for you, based upon where it is used in your APEX application.  Oracle APEX 18.2 also includes the ability to update Font APEX stylesheets and font files, independent of Oracle APEX releases.  Font APEX is now available on GitHub. Reduced Downtime In an era of always-available cloud services and enterprise applications, particular focus has been given in APEX 18.2 to significantly reduce the outage time of an APEX upgrade. A multi-phase reduced-downtime upgrade approach was first introduced in APEX 18.1, which resulted in a significant decrease in the overall outage incurred during the upgrade process.  In APEX 18.2, this multi-phase upgrade process has been further refined, resulting in an even greater reduction in the already brief outage period for an APEX upgrade.   Simplified Service Request & Creation The workflow to request an APEX workspace has been dramatically simplified, improving the experience for users trying Oracle APEX for the first time.  Additional settings have been added to the APEX configuration so that, out-of-the-box, reasonable defaults are used during the workspace request process, and very little information is required to get up and running with APEX! Sample Data Set Enhancements To assist in the learning process, Oracle APEX includes a number of data sets which can be easily installed and used for application creation.  In APEX 18.2, the Create Application wizard will be automatically populated with components built on top of the datasets.  Additionally, some of the datasets have been expanded to include data in other languages, including Chinese, Czech, French, German, Japanese, Korean, Polish, Russian, and Spanish.     As in every APEX release, a number of enhancements and and bugs fixes have been made to the productivity and sample applications.  Additionally, more than 150 bugs have been fixed in APEX 18.2, resulting in even higher quality and stability for APEX environments.   Additional Information Application Express (APEX) is the low code rapid app dev platform which can run in any Oracle Database and is included with every Oracle Database Cloud Service.  APEX, combined with the Oracle Database, provides a fully integrated environment to build, deploy, maintain and monitor data-driven business applications that look great on mobile and desktop devices.  To learn more about Oracle Application Express, visit apex.oracle.com.  To learn more about Oracle Database Cloud, visit cloud.oracle.com/database. 

  Oracle Application Express (APEX) 18.2 is now generally available! APEX enables you to develop, design and deploy beautiful, responsive, data-driven desktop and mobile applications using only a...

Application Express

Easy XLSX Parser: Just with SQL and PL/SQL

Uploading XLSX files is a very common requirement these days. Application Express at this time only supports uploading CSV files out of the box. The community provides quite a few blog postings and also plug-ins; most of these store the data into a collection, which makes it hard to parse an XLSX outside of an APEX context, e.g. in the background using a scheduler job. This blog posting shows a method to parse XLSX files with only SQL and PL/SQL. The actual work will be done by a table function, thus no collections and no APEX session is required. Since a table function returns rows and columns, one can easily insert the results into a table, apply some transformation logic, push it into the background with a scheduler job or just display. The XLSX format is based on XML: an XSLX file is actually a ZIP file (you can easily verify that by renaming to .zip and opening it) containing several XML files. To parse the data, we first have to extract the contents, look up the right XML files and parse these. For the latter task, the XMLTABLE SQL function comes in very handy. The following example code creates an XLSX_PARSER package. It uses the APEX_ZIP package to unpack the XLSX file and the XMLTABLE SQL function in order to parse the XML. The PARSE function is implemented as a table function which returns the first 50 columns ("A" to "AX"). If you need more columns, you can easily extend the code accordingly; the upper limit is 1000 columns. create or replace package xlsx_parser is c_date_format constant varchar2(255) := 'YYYY-MM-DD'; -- we currently support 50 columns - but this can easily be increased. Just increase the columns in the -- record definition and add corresponing lines into the package body type xlsx_row_t is record( line# number, col01 varchar2(4000), col02 varchar2(4000), col03 varchar2(4000), col04 varchar2(4000), col05 varchar2(4000), col06 varchar2(4000), col07 varchar2(4000), col08 varchar2(4000), col09 varchar2(4000), col10 varchar2(4000), col11 varchar2(4000), col12 varchar2(4000), col13 varchar2(4000), col14 varchar2(4000), col15 varchar2(4000), col16 varchar2(4000), col17 varchar2(4000), col18 varchar2(4000), col19 varchar2(4000), col20 varchar2(4000), col21 varchar2(4000), col22 varchar2(4000), col23 varchar2(4000), col24 varchar2(4000), col25 varchar2(4000), col26 varchar2(4000), col27 varchar2(4000), col28 varchar2(4000), col29 varchar2(4000), col30 varchar2(4000), col31 varchar2(4000), col32 varchar2(4000), col33 varchar2(4000), col34 varchar2(4000), col35 varchar2(4000), col36 varchar2(4000), col37 varchar2(4000), col38 varchar2(4000), col39 varchar2(4000), col40 varchar2(4000), col41 varchar2(4000), col42 varchar2(4000), col43 varchar2(4000), col44 varchar2(4000), col45 varchar2(4000), col46 varchar2(4000), col47 varchar2(4000), col48 varchar2(4000), col49 varchar2(4000), col50 varchar2(4000)); type xlsx_tab_t is table of xlsx_row_t; --================================================================================================================== -- table function parses the XLSX file and returns the first 15 columns. -- pass either the XLSX blob directly or reference a name in the APEX_APPLICATION_TEMP_FILES table. -- -- p_xlsx_name - NAME column of the APEX_APPLICATION_TEMP_FILES table -- p_xlsx_content - XLSX as a BLOB -- p_worksheet_name - Worksheet to extract -- -- usage: -- -- select * from table( -- xlsx_parser.parse( -- p_xlsx_name => :P1_XLSX_FILE, -- p_worksheet_name => :P1_WORKSHEET_NAME ) ); -- function parse( p_xlsx_name in varchar2 default null, p_xlsx_content in blob default null, p_worksheet_name in varchar2 default 'sheet1', p_max_rows in number default 1000000 ) return xlsx_tab_t pipelined; --================================================================================================================== -- table function to list the available worksheets in an XLSX file -- -- p_xlsx_name - NAME column of the APEX_APPLICATION_TEMP_FILES table -- p_xlsx_content - XLSX as a BLOB -- -- usage: -- -- select * from table( -- xlsx_parser.get_worksheets( -- p_xlsx_name => :P1_XLSX_FILE ) ); -- function get_worksheets( p_xlsx_content in blob default null, p_xlsx_name in varchar2 default null ) return apex_t_varchar2 pipelined; --================================================================================================================== -- date and datetimes are stored as a number in XLSX; this function converts that number to an ORACLE DATE -- -- p_xlsx_date_number numeric XLSX date value -- -- usage: -- select xlsx_parser.get_date( 46172 ) from dual; -- function get_date( p_xlsx_date_number in number ) return date; end xlsx_parser; / sho err create or replace package body xlsx_parser is g_worksheets_path_prefix constant varchar2(14) := 'xl/worksheets/'; --================================================================================================================== function get_date( p_xlsx_date_number in number ) return date is begin return case when p_xlsx_date_number > 61 then DATE'1900-01-01' - 2 + p_xlsx_date_number else DATE'1900-01-01' - 1 + p_xlsx_date_number end; end get_date; --================================================================================================================== procedure get_blob_content( p_xlsx_name in varchar2, p_xlsx_content in out nocopy blob ) is begin if p_xlsx_name is not null then select blob_content into p_xlsx_content from apex_application_temp_files where name = p_xlsx_name; end if; exception when no_data_found then null; end get_blob_content; --================================================================================================================== function extract_worksheet( p_xlsx in blob, p_worksheet_name in varchar2 ) return blob is l_worksheet blob; begin if p_xlsx is null or p_worksheet_name is null then return null; end if; l_worksheet := apex_zip.get_file_content( p_zipped_blob => p_xlsx, p_file_name => g_worksheets_path_prefix || p_worksheet_name || '.xml' ); if l_worksheet is null then raise_application_error(-20000, 'WORKSHEET "' || p_worksheet_name || '" DOES NOT EXIST'); end if; return l_worksheet; end extract_worksheet; --================================================================================================================== procedure extract_shared_strings( p_xlsx in blob, p_strings in out nocopy wwv_flow_global.vc_arr2 ) is l_shared_strings blob; begin l_shared_strings := apex_zip.get_file_content( p_zipped_blob => p_xlsx, p_file_name => 'xl/sharedStrings.xml' ); if l_shared_strings is null then return; end if; select shared_string bulk collect into p_strings from xmltable( xmlnamespaces( default 'http://schemas.openxmlformats.org/spreadsheetml/2006/main' ), '//si' passing xmltype.createxml( l_shared_strings, nls_charset_id('AL32UTF8'), null ) columns shared_string varchar2(4000) path 't/text()' ); end extract_shared_strings; --================================================================================================================== procedure extract_date_styles( p_xlsx in blob, p_format_codes in out nocopy wwv_flow_global.vc_arr2 ) is l_stylesheet blob; begin l_stylesheet := apex_zip.get_file_content( p_zipped_blob => p_xlsx, p_file_name => 'xl/styles.xml' ); if l_stylesheet is null then return; end if; select lower( n.formatCode ) bulk collect into p_format_codes from xmltable( xmlnamespaces( default 'http://schemas.openxmlformats.org/spreadsheetml/2006/main' ), '//cellXfs/xf' passing xmltype.createxml( l_stylesheet, nls_charset_id('AL32UTF8'), null ) columns numFmtId number path '@numFmtId' ) s, xmltable( xmlnamespaces( default 'http://schemas.openxmlformats.org/spreadsheetml/2006/main' ), '//numFmts/numFmt' passing xmltype.createxml( l_stylesheet, nls_charset_id('AL32UTF8'), null ) columns formatCode varchar2(255) path '@formatCode', numFmtId number path '@numFmtId' ) n where s.numFmtId = n.numFmtId ( + ); end extract_date_styles; --================================================================================================================== function convert_ref_to_col#( p_col_ref in varchar2 ) return pls_integer is l_colpart varchar2(10); l_linepart varchar2(10); begin l_colpart := replace(translate(p_col_ref,'1234567890','__________'), '_'); if length( l_colpart ) = 1 then return ascii( l_colpart ) - 64; else return ( ascii( substr( l_colpart, 1, 1 ) ) - 64 ) * 26 + ( ascii( substr( l_colpart, 2, 1 ) ) - 64 ); end if; end convert_ref_to_col#; --================================================================================================================== procedure reset_row( p_parsed_row in out nocopy xlsx_row_t ) is begin -- reset row p_parsed_row.col01 := null; p_parsed_row.col02 := null; p_parsed_row.col03 := null; p_parsed_row.col04 := null; p_parsed_row.col05 := null; p_parsed_row.col06 := null; p_parsed_row.col07 := null; p_parsed_row.col08 := null; p_parsed_row.col09 := null; p_parsed_row.col10 := null; p_parsed_row.col11 := null; p_parsed_row.col12 := null; p_parsed_row.col13 := null; p_parsed_row.col14 := null; p_parsed_row.col15 := null; p_parsed_row.col16 := null; p_parsed_row.col17 := null; p_parsed_row.col18 := null; p_parsed_row.col19 := null; p_parsed_row.col20 := null; p_parsed_row.col21 := null; p_parsed_row.col22 := null; p_parsed_row.col23 := null; p_parsed_row.col24 := null; p_parsed_row.col25 := null; p_parsed_row.col26 := null; p_parsed_row.col27 := null; p_parsed_row.col28 := null; p_parsed_row.col29 := null; p_parsed_row.col30 := null; p_parsed_row.col31 := null; p_parsed_row.col32 := null; p_parsed_row.col33 := null; p_parsed_row.col34 := null; p_parsed_row.col35 := null; p_parsed_row.col36 := null; p_parsed_row.col37 := null; p_parsed_row.col38 := null; p_parsed_row.col39 := null; p_parsed_row.col40 := null; p_parsed_row.col41 := null; p_parsed_row.col42 := null; p_parsed_row.col43 := null; p_parsed_row.col44 := null; p_parsed_row.col45 := null; p_parsed_row.col46 := null; p_parsed_row.col47 := null; p_parsed_row.col48 := null; p_parsed_row.col49 := null; p_parsed_row.col50 := null; end reset_row; --================================================================================================================== function parse( p_xlsx_name in varchar2 default null, p_xlsx_content in blob default null, p_worksheet_name in varchar2 default 'sheet1', p_max_rows in number default 1000000 ) return xlsx_tab_t pipelined is l_worksheet blob; l_xlsx_content blob; l_shared_strings wwv_flow_global.vc_arr2; l_format_codes wwv_flow_global.vc_arr2; l_parsed_row xlsx_row_t; l_first_row boolean := true; l_value varchar2(32767); l_line# pls_integer := 1; l_real_col# pls_integer; l_row_has_content boolean := false; begin if p_xlsx_content is null then get_blob_content( p_xlsx_name, l_xlsx_content ); else l_xlsx_content := p_xlsx_content; end if; if l_xlsx_content is null then return; end if; l_worksheet := extract_worksheet( p_xlsx => l_xlsx_content, p_worksheet_name => p_worksheet_name ); extract_shared_strings( p_xlsx => l_xlsx_content, p_strings => l_shared_strings ); extract_date_styles( p_xlsx => l_xlsx_content, p_format_codes => l_format_codes ); -- the actual XML parsing starts here for i in ( select r.xlsx_row, c.xlsx_col#, c.xlsx_col, c.xlsx_col_type, c.xlsx_col_style, c.xlsx_val from xmltable( xmlnamespaces( default 'http://schemas.openxmlformats.org/spreadsheetml/2006/main' ), '//row' passing xmltype.createxml( l_worksheet, nls_charset_id('AL32UTF8'), null ) columns xlsx_row number path '@r', xlsx_cols xmltype path '.' ) r, xmltable ( xmlnamespaces( default 'http://schemas.openxmlformats.org/spreadsheetml/2006/main' ), '//c' passing r.xlsx_cols columns xlsx_col# for ordinality, xlsx_col varchar2(15) path '@r', xlsx_col_type varchar2(15) path '@t', xlsx_col_style varchar2(15) path '@s', xlsx_val varchar2(4000) path 'v/text()' ) c where p_max_rows is null or r.xlsx_row <= p_max_rows ) loop if i.xlsx_col# = 1 then l_parsed_row.line# := l_line#; if not l_first_row then pipe row( l_parsed_row ); l_line# := l_line# + 1; reset_row( l_parsed_row ); l_row_has_content := false; else l_first_row := false; end if; end if; if i.xlsx_col_type = 's' then if l_shared_strings.exists( i.xlsx_val + 1) then l_value := l_shared_strings( i.xlsx_val + 1); else l_value := '[Data Error: N/A]' ; end if; else if l_format_codes.exists( i.xlsx_col_style + 1 ) and ( instr( l_format_codes( i.xlsx_col_style + 1 ), 'd' ) > 0 and instr( l_format_codes( i.xlsx_col_style + 1 ), 'm' ) > 0 ) then l_value := to_char( get_date( i.xlsx_val ), c_date_format ); else l_value := i.xlsx_val; end if; end if; pragma inline( convert_ref_to_col#, 'YES' ); l_real_col# := convert_ref_to_col#( i.xlsx_col ); if l_real_col# between 1 and 50 then l_row_has_content := true; end if; -- we currently support 50 columns - but this can easily be increased. Just add additional lines -- as follows: -- when l_real_col# = {nn} then l_parsed_row.col{nn} := l_value; case when l_real_col# = 1 then l_parsed_row.col01 := l_value; when l_real_col# = 2 then l_parsed_row.col02 := l_value; when l_real_col# = 3 then l_parsed_row.col03 := l_value; when l_real_col# = 4 then l_parsed_row.col04 := l_value; when l_real_col# = 5 then l_parsed_row.col05 := l_value; when l_real_col# = 6 then l_parsed_row.col06 := l_value; when l_real_col# = 7 then l_parsed_row.col07 := l_value; when l_real_col# = 8 then l_parsed_row.col08 := l_value; when l_real_col# = 9 then l_parsed_row.col09 := l_value; when l_real_col# = 10 then l_parsed_row.col10 := l_value; when l_real_col# = 11 then l_parsed_row.col11 := l_value; when l_real_col# = 12 then l_parsed_row.col12 := l_value; when l_real_col# = 13 then l_parsed_row.col13 := l_value; when l_real_col# = 14 then l_parsed_row.col14 := l_value; when l_real_col# = 15 then l_parsed_row.col15 := l_value; when l_real_col# = 16 then l_parsed_row.col16 := l_value; when l_real_col# = 17 then l_parsed_row.col17 := l_value; when l_real_col# = 18 then l_parsed_row.col18 := l_value; when l_real_col# = 19 then l_parsed_row.col19 := l_value; when l_real_col# = 20 then l_parsed_row.col20 := l_value; when l_real_col# = 21 then l_parsed_row.col21 := l_value; when l_real_col# = 22 then l_parsed_row.col22 := l_value; when l_real_col# = 23 then l_parsed_row.col23 := l_value; when l_real_col# = 24 then l_parsed_row.col24 := l_value; when l_real_col# = 25 then l_parsed_row.col25 := l_value; when l_real_col# = 26 then l_parsed_row.col26 := l_value; when l_real_col# = 27 then l_parsed_row.col27 := l_value; when l_real_col# = 28 then l_parsed_row.col28 := l_value; when l_real_col# = 29 then l_parsed_row.col29 := l_value; when l_real_col# = 30 then l_parsed_row.col30 := l_value; when l_real_col# = 31 then l_parsed_row.col31 := l_value; when l_real_col# = 32 then l_parsed_row.col32 := l_value; when l_real_col# = 33 then l_parsed_row.col33 := l_value; when l_real_col# = 34 then l_parsed_row.col34 := l_value; when l_real_col# = 35 then l_parsed_row.col35 := l_value; when l_real_col# = 36 then l_parsed_row.col36 := l_value; when l_real_col# = 37 then l_parsed_row.col37 := l_value; when l_real_col# = 38 then l_parsed_row.col38 := l_value; when l_real_col# = 39 then l_parsed_row.col39 := l_value; when l_real_col# = 40 then l_parsed_row.col40 := l_value; when l_real_col# = 41 then l_parsed_row.col41 := l_value; when l_real_col# = 42 then l_parsed_row.col42 := l_value; when l_real_col# = 43 then l_parsed_row.col43 := l_value; when l_real_col# = 44 then l_parsed_row.col44 := l_value; when l_real_col# = 45 then l_parsed_row.col45 := l_value; when l_real_col# = 46 then l_parsed_row.col46 := l_value; when l_real_col# = 47 then l_parsed_row.col47 := l_value; when l_real_col# = 48 then l_parsed_row.col48 := l_value; when l_real_col# = 49 then l_parsed_row.col49 := l_value; when l_real_col# = 50 then l_parsed_row.col50 := l_value; else null; end case; end loop; if l_row_has_content then l_parsed_row.line# := l_line#; pipe row( l_parsed_row ); end if; return; end parse; --================================================================================================================== function get_worksheets( p_xlsx_content in blob default null, p_xlsx_name in varchar2 default null ) return apex_t_varchar2 pipelined is l_zip_files apex_zip.t_files; l_xlsx_content blob; begin if p_xlsx_content is null then get_blob_content( p_xlsx_name, l_xlsx_content ); else l_xlsx_content := p_xlsx_content; end if; l_zip_files := apex_zip.get_files( p_zipped_blob => l_xlsx_content ); for i in 1 .. l_zip_files.count loop if substr( l_zip_files( i ), 1, length( g_worksheets_path_prefix ) ) = g_worksheets_path_prefix then pipe row( rtrim( substr( l_zip_files ( i ), length( g_worksheets_path_prefix ) + 1 ), '.xml' ) ); end if; end loop; return; end get_worksheets; end xlsx_parser; / sho err The package provides the following functions: FUNCTION GET_WORKSHEETS RETURNS APEX_T_VARCHAR2 Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- P_XLSX_CONTENT BLOB IN DEFAULT P_XLSX_NAME VARCHAR2 IN DEFAULT FUNCTION PARSE RETURNS XLSX_TAB_T Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- P_XLSX_NAME VARCHAR2 IN DEFAULT P_XLSX_CONTENT BLOB IN DEFAULT P_WORKSHEET_NAME VARCHAR2 IN DEFAULT P_MAX_ROWS NUMBER IN DEFAULT You can pass either the XLSX content as a BLOB or the name of an uploaded file. In the latter case, the package will look up the file name in the APEX_APPLICATION_TEMP_FILES table. A typical call sequence is as follows: Look up the worksheets contained in the XSLX file with XLSX_PARSER.GET_WORKSHEETS Retrieve the Worksheet data with XLSX_PARSER.PARSE. Let's try this out. Create an APEX application with an empty page, then add the following elements to that page. Add a Region of Static HTML type. Add a File Browse item named PX_XLSX_FILE. Choose Table APEX_APPLICATION_TEMP_FILES as the Storage Type and keep the files until the End Of Session. Add a Button to Upload the file. Choose Submit Page as the button action. Your page should now look as follows: You can already upload a file, but nothing will happen. Next, we'll want to show a select list to pick one of the worksheets contained in the XLSX file.  Add a Select List item named PX_WORKSHEET.  Use SQL Query as the List Of Values Type and provide the following SQL query: select column_value d, column_value r from table( xlsx_parser.get_worksheets( p_xlsx_name => :PX_XLSX_FILE ) ) Set Display Extra Values to No, Display Null Value to Yes and use - Choose - as the Null Display Value. Finally add a Server-Side condition to only display the select list when the PX_XLSX_FILE item IS NOT NULL (when a file has actually been uploaded) Now run your page again. After you have uploaded an XSLX file, your page should look as follows: So we can now pick a worksheet. So far, so good. Time to actually do the job and extract data from the XLSX file. For now, we just want to display the data as a classic report. So create a Classic Report region and use the following SQL Query. select * from table( xlsx_parser.parse( p_xlsx_name => :PX_XLSX_FILE, p_worksheet_name => :PX_WORKSHEET ) ); Add the PX_WORKSHEET item to the Classic Reports Page Items to Submit attribute. Then add a Dynamic Action in order to refresh the report when a worksheet has been chosen in the Select List item. The dynamic action should fire on the Change event of the PX_WORKSHEET item As the TRUE action, choose Refresh of the Classic Report region you just have created. You then should be able to do the following steps: Run the page and upload an XLSX file Pick a worksheet from the select list The Classic Report refreshes and shows the first 50 columns of worksheet data  And that's it ... the nice thing is that there are no limits to process the worksheet data ... You can simply create a table ... create table worksheet_data as select col02 as first_name, col03 as last_name, col05 as country from table( xlsx_parser.parse( p_xlsx_name => :PX_XLSX_FILE, p_worksheet_name => :PX_WORKSHEET ) ); You can apply a few transformations ... insert into worksheet_data( select cast( col02 as varchar2(200) ) as first_name, cast( col03 as varchar2(200) ) as last_name, case col04 when 'Female' then 'F' when 'Male' then 'M' end as gender, cast( col05 as varchar2(200) ) as country, to_number( to_char( sysdate, 'YYYY') ) - to_number( col06 ) as birth_year from table( xlsx_parser.parse( p_xlsx_name => :PX_XLSX_FILE, p_worksheet_name => :PX_WORKSHEET ) ) where line# != 1) You can also create a DBMS_SCHEDULER job to push the task into the background. Note that this job does not have access to APEX session state - so you need to store the BLOB into your own table, then. Try the sample code out - once the table function returns data, there are no limits for further processing any more. You can do with the data whatever you want: from just displaying over loading into a table to executing complex processing in the background: Everything is possible.

Uploading XLSX files is a very common requirement these days. Application Express at this time only supports uploading CSV files out of the box. The community provides quite a few blog postings and...

Creating a CRUD form on a REST Service with APEX 18.1

In this blog posting we will highlight the new REST capabilities of Application Express 18.1 once more. This time we will show how to create a form to insert, update or delete rows - but not from a table, the form will work on a REST service instead. APEX 18.1 provides declarative support to build components on top of a REST service. However, only Read Only components like like reports or charts can use Web Source Modules directly; there is no wizard to create a form on a Web Source Module. So we have to implement some custom PL/SQL code in order to build a DML form on a REST service. As this blog posting shows, Web Source Modules can still do a lot of the work for us: We will still be able to use the APEX_EXEC package - no manual JSON parsing will be required and we won't have to do any manual HTTP request. Note that full declarative support for Read/Write components like Forms or the Interactive Grid is planned for one of the next releases of Application Express - when that is available, you will be able to create a Form on a REST service directly, without any manual work at all - as you can do today with a report. Provide a REST Service: Using ORDS Auto-REST First we need the REST Service to work on. For this blog posting we will use the well-known EMP table and the "Auto-REST" feature of Oracle REST Data Services. The most easy approach is to navigate to SQL Workshop > SQL Commands and to execute the following simple PL/SQL Block: begin ords.enable_schema; ords.enable_object( p_object => 'EMP' ); end; / Then, to check whether the REST Service works, try to execute the REST Service using a browser or the "curl" command line utility. Use the URL: http://{ords-server}:{port}/ords/{schema}/emp/.  With a browser, the response should look as follows: Create the APEX Web Source Module Then, head over to Application Express, log into your workspace and create an application. Navigate to Shared Components and lookup Web Source Modules. In a new application, this section is empty. Click the Create button in order to create a new Web Source Module from Scratch. Choose Oracle REST Data Services as the Web Source Type, provide the URL endpoint and click Next. Just click the Next button in the following wizard step for the Remote Server. In the Authentication step, simply hit the Discover button. You should see the following screen, which indicates that APEX can talk to the REST service. Click Create Web Source Module in order to save REST service meta data. You will be navigated back to the list of web source modules. Create a report on top of the Web Source Module Then create a classic report on that Web Source module, as described in the Application Express Early Adopter: REST Services! blog posting. You can also use page designer: Drag a classic report region onto your page and change the Source attributes in the property pane on the right to use the new Web Source Module. Run the page. You should see the data from the EMP table - which actually comes from a REST service. This was the read only part. Now we'll get to the read / write part of the story.  Insert, Update or Delete on a REST Service When we REST-Enabled the EMP table (with ORDS.ENABLE_OBJECT), not only a GET REST Service had been created. ORDS also provides REST Services to perform DML (insert, update or delete) rows. INSERT: POST Request to http://{server}:{port}/ords/{schema}/emp/. The request body contains the data for the new row in JSON format. UPDATE: PUT Request to http://{server}:{port}/ords/{schema}/emp/{empno}. The request body contains the data for the new row in JSON format. DELETE: DELETE Request to http://{server}:{port}/ords/{schema}/emp/{empno}. The request body must be empty. Let's first try that with the "curl" command line utility (which is always a good tool to test our understanding of a REST service). The following curl invocation creates a new row by executing a POST request. curl -H"Content-Type:application/json" \ -X POST \ -d'{"empno":4711,"ename":"APEX","job":"DEV","hiredate":"2001-01-01T20:00:00Z","sal":10,"comm":null,"deptno":10,"mgr":7839}' \ http://localhost:28080/ords/testit/emp/ You should see the following resonse, indicating that the operation was successful. {"empno":4711,"ename":"APEX","job":"DEV","mgr":7839,"hiredate":"2001-01-01T20:00:00Z","sal":10,"comm":null,"deptno":10,"links":[{"rel":"self","href":"http://localhost:28080/ords/testit/emp/4711"},{"rel":"edit","href":"http://localhost:28080/ords/testit/emp/4711"},{"rel":"describedby","href":"http://localhost:28080/ords/testit/metadata-catalog/emp/item"},{"rel":"collection","href":"http://localhost:28080/ords/testit/emp/"}]} We can also check by querying the table directly: SQL> select * from emp; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ------- -------- ------------- ---- ---------------------- ----- ------ -------- 7369 SMITH CLERK 7902 17.12.1980 00:00:00 800 20 7499 ALLEN SALESMAN 7698 20.02.1981 00:00:00 1600 300 30 7521 WARD SALESMAN 7698 22.02.1981 00:00:00 1250 500 30 7566 JONES MANAGER 7839 02.04.1981 00:00:00 2975 20 7654 MARTIN SALESMAN 7698 28.09.1981 00:00:00 1250 1400 30 7698 BLAKE MANAGER 7839 01.05.1981 00:00:00 2850 30 7782 CLARK MANAGER 7839 09.06.1981 00:00:00 2450 10 7788 SCOTT ANALYST 7566 09.12.1982 00:00:00 3000 20 7839 KING PRESIDENT 17.11.1981 00:00:00 5000 10 7844 TURNER SALESMAN 7698 08.09.1981 00:00:00 1500 0 30 7876 ADAMS CLERK 7788 12.01.1983 00:00:00 1100 20 7900 JAMES CLERK 7698 03.12.1981 00:00:00 950 30 7902 FORD ANALYST 7566 03.12.1981 00:00:00 3000 20 7934 MILLER CLERK 7782 23.01.1982 00:00:00 1300 10 4711 APEX DEV 7839 01.01.2001 12:00:00 10 10 Since we now know, how to call the POST, PUT and DELETE REST Services, let's prepare the already existing Web Source Module and then build the CRUD form. Prepare the Web Source Module for CRUD operations Since APEX 18.1 does not provide declarative support for Forms on a Web Source Module, we must provide some information ourselves, but we can still leverage Web Source Modules and let APEX do a lot of low-level work for us. Look up your new web source module in Shared Components. First, navigate to the Advanced tab and note down the exact Static ID value of the Web Source Module (here: Auto_REST_Service_EMP). We'll need that later on.​     Then select the Operations tab. We can see that APEX already did some work for us. Since this is an ORDS REST Service, APEX detected, that URL endpoints for POST, PUT and DELETE exist and it added these to the REST Service meta data. Remove the unneeded DELETE operation ORDS provides indeed two DELETE endpoints: One is to delete a single row, the other one to delete the whole collection (i.e. all rows from the table). We won't need the latter one in our APEX application. So click the yellow pencil of the DELETE operation which has no value in the Database Operation column and remove that operation by clicking the Delete button. Changes to the POST operation Then, click the pencil to edit the POST operation. Provide the following JSON template as the Request Body Template. {"empno":#EMPNO#,"ename":"#ENAME#","job":"#JOB#","hiredate":"#HIREDATE#","sal":#SAL#,"comm":#COMM#,"deptno":#DEPTNO#,"mgr":#MGR#} Click Apply Changes and then the pencil again to come back to this form. Then click the Add Parameter button to open the Operation Parameter dialog. For each column name, which you have created a #PLACEHOLDER# for, add a Parameter of the Request / Response Body type. When finished, you should have eight Operation Parameters, one for each column of the EMP table. Then add another parameter of Type HTTP Header with Content-Type as its name and application/json as the value. The Operation Parameters section for the POST operation should finally look as follows. Changes to the PUT operation. For the PUT operation, provide the following JSON Request Body template (it's similar to the template for the POST operation, but without the EMPNO column). {"ename":"#ENAME#","job":"#JOB#","hiredate":"#HIREDATE#","sal":#SAL#,"comm":#COMM#,"deptno":#DEPTNO#,"mgr":#MGR#} Then add Web Source Parameters as well, similar as done for the POST operations: One Parameter of type Request / Response body for each #PLACEHOLDER# in the JSON Request Template One static Parameter of type HTTP Header with Content-Type as the Name and application/json as the value One Parameter of type URL Pattern variable with name id. The Operation parameters section for the PUT operation should look as follows: Changes to the DELETE operation The remaining DELETE operation does not need any JSON Request template and only one parameter definition: Create a parameter of type URL pattern variable with name id. Now, the operations are all set and the Web Source Module has been prepared. We can continue building the form page. Create the form page Next, create a new page for the form on the REST service. Create a blank page and add a region containing eight items (one for each form element) to that page. When finished, your form might looks as follows (it has no functionality so far). Next, we'll add functionality to populate the form with values on page load. We'll need do this using custom PL/SQL code and the APEX_EXEC package. The blog posting  APEX 18.1 Early Adopter 2: REST Services and PL/SQL contains a good template for our form loading process.  In Page Designer, navigate to the Before Headers section in the page tree on the left and create a new Process. Name the process Load REST Data and provide the following PL/SQL Code: declare l_columns apex_exec.t_columns; l_context apex_exec.t_context; l_filters apex_exec.t_filters; type t_column_position is table of pls_integer index by varchar2(32767); l_column_position t_column_position; begin -- specify columns to select from the web source module apex_exec.add_column( p_columns => l_columns, p_column_name => 'EMPNO'); apex_exec.add_column( p_columns => l_columns, p_column_name => 'ENAME'); apex_exec.add_column( p_columns => l_columns, p_column_name => 'JOB'); apex_exec.add_column( p_columns => l_columns, p_column_name => 'HIREDATE'); apex_exec.add_column( p_columns => l_columns, p_column_name => 'MGR'); apex_exec.add_column( p_columns => l_columns, p_column_name => 'SAL'); apex_exec.add_column( p_columns => l_columns, p_column_name => 'COMM'); apex_exec.add_column( p_columns => l_columns, p_column_name => 'DEPTNO'); -- add a filter to query only the selected row apex_exec.add_filter( p_filters => l_filters, p_column_name => 'EMPNO', p_filter_type => apex_exec.c_filter_eq, p_value => to_number( :P2_EMPNO ) ); -- invoke Web Source Module and select data l_context := apex_exec.open_web_source_query( p_module_static_id => 'Auto_REST_Service_EMP', p_filters => l_filters, p_columns => l_columns ); -- now get result set positions for the selected columns for c in 1 .. l_columns.count loop l_column_position( l_columns( c ).name ) := apex_exec.get_column_position( l_context, l_columns( c ).name ); end loop; -- if we have a result set, set the form items if apex_exec.next_row( l_context ) then :P2_ENAME := apex_exec.get_varchar2( l_context, l_column_position( 'ENAME' ) ); :P2_JOB := apex_exec.get_varchar2( l_context, l_column_position( 'JOB' ) ); :P2_HIREDATE := apex_exec.get_varchar2( l_context, l_column_position( 'HIREDATE' ) ); :P2_SAL := apex_exec.get_number ( l_context, l_column_position( 'SAL' ) ); :P2_COMM := apex_exec.get_number ( l_context, l_column_position( 'COMM' ) ); :P2_MGR := apex_exec.get_number ( l_context, l_column_position( 'MGR' ) ); :P2_DEPTNO := apex_exec.get_number ( l_context, l_column_position( 'DEPTNO' ) ); -- otherwise raise NO_DATA_FOUND else raise no_data_found; end if; -- finally: release all resources apex_exec.close( l_context ); exception when others then -- IMPORTANT: also release all resources, when an exception occcurs! apex_exec.close( l_context ); raise; end; Add a server-side condition to have the process only executed when the P2_EMPNO item IS NOT NULL, then save your changes. Let's now test whether this PL/SQL code already works. Navigate back to the report page and change the EMPNO column of the report to be rendered as a link to the new form page.  Run the report page - you should now see links in the EMPNO column. Then add a button to the report page which allows to create a new row. Button Name: CREATE_NEW Button Label: Create New Button Action: Redirect to Page Target: Page 2 (the form page); set Clear Cache for page 2 (the form page) When clicking the link in the EMPNO column, the form page should be loaded and populated with the values for the selected row; when clicking the Create New button, an empty form should be loaded. This concludes the first part of the form page. We loaded data from the REST service into the page items without using any APEX_WEB_SERVICE call and without doing any manual JSON parsing. Add DML processes to the form Before adding the PL/SQL code to process POST, PUT and DELETE requests add the required buttons to the page: CREATE with an IS NULL condition for the P2_ENAME item APPLY_CHANGES with the IS NOT NULL condition for the P2_ENAME item DELETE with the IS NOT NULL condition for the P2_ENAME item The page should then look as follows: Now let's add the PL/SQL Code to process the INSERT, UPDATE or DELETE operations. We have provided all required meta data in the Web Source Modules and we can thus work with the APEX_EXEC package alone. Dependent on the button clicked, the PL/SQL code will prepare parameters for the Web Source Module operation and finally call APEX_EXEC.PROCESS_WEB_SOURCE.  In Page Designer, create a new PL/SQL process in the Processing section. Name it Process REST DML and provide the following PL/SQL code: declare l_parameters apex_exec.t_parameters; begin -- add the primary key value as the "id" parameter; this will be appended to the URL for DELETE and APPLY_CHANGES if :REQUEST in ( 'DELETE', 'APPLY_CHANGES' ) then apex_exec.add_parameter( p_parameters => l_parameters, p_name => 'id', p_value => :P2_EMPNO ); end if; -- add form item values as parameters for CREATE and APPLY_CHANGES if :REQUEST in ( 'CREATE', 'APPLY_CHANGES' ) then apex_exec.add_parameter( p_parameters => l_parameters, p_name => 'ENAME', p_value => :P2_ENAME ); apex_exec.add_parameter( p_parameters => l_parameters, p_name => 'EMPNO', p_value => :P2_EMPNO ); apex_exec.add_parameter( p_parameters => l_parameters, p_name => 'JOB', p_value => :P2_JOB ); apex_exec.add_parameter( p_parameters => l_parameters, p_name => 'HIREDATE', p_value => to_char( to_date( :P2_HIREDATE ), 'YYYY-MM-DD' ) || 'T00:00:00Z' ); apex_exec.add_parameter( p_parameters => l_parameters, p_name => 'SAL', p_value => :P2_SAL ); apex_exec.add_parameter( p_parameters => l_parameters, p_name => 'COMM', p_value => coalesce( :P2_COMM, 'null' ) ); apex_exec.add_parameter( p_parameters => l_parameters, p_name => 'MGR', p_value => :P2_MGR ); apex_exec.add_parameter( p_parameters => l_parameters, p_name => 'DEPTNO', p_value => :P2_DEPTNO ); end if; -- invoke Web Source Module for and select data apex_exec.execute_web_source( p_module_static_id => 'Auto_REST_Service_EMP', p_operation => case :REQUEST when 'APPLY_CHANGES' then 'PUT' when 'CREATE' then 'POST' when 'DELETE' then 'DELETE' end, p_parameters => l_parameters ); end; As Server-Side condition, choose Request is contained in value and provide CREATE:APPLY_CHANGES:DELETE as the Value. That makes sure that the code is only called when one of the three buttons has been clicked. Finally provide a nice success message and add a branch back to the report page (here: page 1). Now we can test the CRUD form: Click the Create New button below the report, enter some data and save a new row Update the Name value of that new row Of course, this form can be further extended - think about validations, additional processes and dynamic Actions. The interesting bit is, that we're able to use Web Source Modules for a DML form, although APEX 18.1 does not provide declarative support so far. Using a bit of additional meta data and some custom PL/SQL code using the APEX_EXEC package we're able to build the form processes without using any manual HTTP or JSON handling code.

In this blog posting we will highlight the new REST capabilities of Application Express 18.1 once more. This time we will show how to create a form to insert, update or delete rows - but not from...

Introducing Oracle APEX Office Hours!

Earlier in 2018, Oracle introduced the Oracle AskTOM Office Hours, which is doing a great job of increasing the community outreach from the various Oracle Database product teams.  On a regular schedule and via a webinar, AskTOM Office Hours provides direct access to internal experts from Oracle across a variety of technologies - SQL, PL/SQL, Database Cloud, .NET, Multitenant, Upgrades & Migrations, and many more.  While "office hours" are classically question and answer sessions, some of the AskTOM office hours also include demonstrations, announcements, and of course, general Q&A.  What's even better, if you cannot attend a specific Office Hours session, the recording will be available for viewing later at your convenience.  The Oracle APEX team has been conspicuously absent from the AskTOM Office Hours, but those days are over. We are introducing the Oracle APEX Office Hours, beginning June 28, 2018 and continuing approximately every two weeks through early 2019.  Every other session will be targeted at beginners, so if you're completely new to APEX or want to learn more, there will be topics for you too.  We will also schedule a number of the sessions to be done live during times conducive to our many customers in Asia, Middle East, Australia, etc. The first session, scheduled for June 28, 2018 is an "ask me anything", and will include the vast majority of the Oracle APEX product development team members.  This will be followed by an office hours session on July 12, 2018 - led by Senior Software Development Manager Marc Sewtz, on the new features of Oracle APEX 18.1. You can sign up for the Oracle APEX Office Hours with the link: https://apex.oracle.com/officehours We encourage you to also review the AskTOM Office Hours site and review all of the other Office Hours sessions from the many product experts at Oracle.   P.S.  Please bring your questions, especially those questions which are appropriate for a large, general audience.  Unfortunately, office hours is not intended to be a general product support medium or live debugging forum.    

Earlier in 2018, Oracle introduced the Oracle AskTOM Office Hours, which is doing a great job of increasing the community outreach from the various Oracle Database product teams.  On a regular...

Application Express

Announcing Oracle APEX 18.1

  Oracle Application Express (APEX) 18.1 is now generally available! APEX enables you to develop, design and deploy beautiful, responsive, data-driven desktop and mobile applications using only a browser. This release of APEX is a dramatic leap forward in both the ease of integration with remote data sources, and the easy inclusion of robust, high-quality application features. Keeping up with the rapidly changing industry, APEX now makes it easier than ever to build attractive and scalable applications which integrate data from anywhere - within your Oracle database, from a remote Oracle database, or from any REST Service, all with no coding.  And the new APEX 18.1 enables you to quickly add higher-level features which are common to many applications - delivering a rich and powerful end-user experience without writing a line of code. "Over a half million developers are building Oracle Database applications today using  Oracle Application Express (APEX).  Oracle APEX is a low code, high productivity app dev tool which combines rich declarative UI components with SQL data access.  With the new 18.1 release, Oracle APEX can now integrate data from REST services with data from SQL queries.  This new functionality is eagerly awaited by the APEX developer community", said Andy Mendelsohn, Executive Vice President of Database Server Technologies at Oracle Corporation.   Some of the major improvements to Oracle Application Express 18.1 include: Application Features It has always been easy to add components to an APEX application - a chart, a form, a report.  But in APEX 18.1, you now have the ability to add higher-level application features to your app, including access control, feedback, activity reporting, email reporting, dynamic user interface selection, and more.  In addition to the existing reporting and data visualization components, you can now create an application with a "cards" report interface, a dashboard, and a timeline report.  The result?  An easily-created powerful and rich application, all without writing a single line of code.     REST Enabled SQL Support Oracle REST Data Services (ORDS) REST-Enabled SQL Services enables the execution of SQL in remote Oracle Databases, over HTTP and REST.  You can POST SQL statements to the service, and the service then runs the SQL statements against Oracle database and returns the result to the client in a JSON format.   In APEX 18.1, you can build charts, reports, calendars, trees and even invoke processes against Oracle REST Data Services (ORDS)-provided REST Enabled SQL Services.  No longer is a database link necessary to include data from remote database objects in your APEX application - it can all be done seamlessly via REST Enabled SQL. Web Source Modules APEX now offers the ability to declaratively access data services from a variety of REST endpoints, including ordinary REST data feeds, REST Services from Oracle REST Data Services, and Oracle Cloud Applications REST Services.  In addition to supporting smart caching rules for remote REST data, APEX also offers the unique ability to directly manipulate the results of REST data sources using industry standard SQL. REST Workshop APEX includes a completely rearchitected REST Workshop, to assist in the creation of REST Services against your Oracle database objects.  The REST definitions are managed in a single repository, and the same definitions can be edited via the APEX REST Workshop, SQL Developer or via documented API's.  Users can exploit the data management skills they possess, such as writing SQL and PL/SQL to define RESTful API services for their database.   The new REST Workshop also includes the ability to generate Swagger documentation against your REST definitions, all with the click of a button.   Application Builder Improvements In Oracle Application Express 18.1, wizards have been streamlined with smarter defaults and fewer steps, enabling developers to create components quicker than ever before.  There have also been a number of usability enhancements to Page Designer, including greater use of color and graphics on page elements, and "Sticky Filter" which is used to maintain a specific filter in the property editor.  These features are designed to enhance the overall developer experience and improve development productivity.  APEX Spotlight Search provides quick navigation and a unified search experience across the entire APEX interface.   Social Authentication APEX 18.1 introduces a new native authentication scheme, Social Sign-In.  Developers can now easily create APEX applications which can use Oracle Identity Cloud Service, Google, Facebook, generic OpenID Connect and generic OAuth2 as the authentication method, all with no coding. Charts The data visualization engine of Oracle Application Express powered by Oracle JET (JavaScript Extension Toolkit), a modular open source toolkit based on modern JavaScript, CSS3 and HTML5 design and development principles.  The charts in APEX are fully HTML5 capable and work on any modern browser, regardless of platform, or screen size.  These charts provide numerous ways to visualize a data set, including bar, line, area, range, combination, scatter, bubble, polar, radar, pie, funnel, and stock charts.  APEX 18.1 features an upgraded Oracle JET 4.2 engine with updated charts and API's.  There are also new chart types including Gantt, Box-Plot and Pyramid, and better support for multi-series, sparse data sets. Mobile UI APEX 18.1 introduce many new UI components to assist in the creation of mobile applications.  Three new component types, ListView, Column Toggle and Reflow Report, are now components which can be used natively with the Universal Theme and are commonly used in mobile applications.  Additional enhancements have been made to the APEX Universal Theme which are mobile-focused, namely, mobile page headers and footers which will remain consistently displayed on mobile devices, and floating item label templates, which optimize the information presented on a mobile screen.  Lastly, APEX 18.1 also includes declarative support for touch-based dynamic actions, tap and double tap, press, swipe, and pan, supporting the creation of rich and functional mobile applications. Font APEX Font APEX is a collection of over 1,000 high-quality icons, many specifically created for use in business applications.  Font APEX in APEX 18.1 includes a new set of high-resolution 32 x 32 icons which include much greater detail and the correctly-sized font will automatically be selected for you, based upon where it is used in your APEX application. Accessibility APEX 18.1 includes a collection of tests in the APEX Advisor which can be used to identify common accessibility issues in an APEX application, including missing headers and titles, and more. This release also deprecates the accessibility modes, as a separate mode is no longer necessary to be accessible. Upgrading If you're an existing Oracle APEX customer, upgrading to APEX 18.1 is as simple as installing the latest version.  The APEX engine will automatically be upgraded and your existing applications will look and run exactly as they did in the earlier versions of APEX.     "We believe that APEX-based PaaS solutions provide a complete platform for extending Oracle’s ERP Cloud. APEX 18.1 introduces two new features that make it a landmark release for our customers. REST Service Consumption gives us the ability to build APEX reports from REST services as if the data were in the local database. This makes embedding data from a REST service directly into an ERP Cloud page much simpler. REST enabled SQL allows us to incorporate data from any Cloud or on-premise Oracle database into our Applications. We can’t wait to introduce APEX 18.1 to our customers!", said Jon Dixon, co-founder of JMJ Cloud.   Additional Information Application Express (APEX) is the low code rapid app dev platform which can run in any Oracle Database and is included with every Oracle Database Cloud Service.  APEX, combined with the Oracle Database, provides a fully integrated environment to build, deploy, maintain and monitor data-driven business applications that look great on mobile and desktop devices.  To learn more about Oracle Application Express, visit apex.oracle.com.  To learn more about Oracle Database Cloud, visit cloud.oracle.com/database.   

  Oracle Application Express (APEX) 18.1 is now generally available! APEX enables you to develop, design and deploy beautiful, responsive, data-driven desktop and mobile applications using only a...

Quick and Easy Twitter API with APEX 18.1

This is the next part of the blog post series on the new REST features in Application Express 18.1. In this blog posting, we will show how to access a protected REST service, which requires authentication. Since APEX has a great Twitter community, we'll be accessing the Twitter API using the new REST capabilities of Application Express.  So, you need to have a Twitter account in order to follow this example. Once you have the Twitter Account, log into apps.twitter.com using your Twitter account and click on the Create new App button in order to register a Twitter Application. Then provide a name and a description for your new Twitter application. You can use http://apex.oracle.com as the Web site. Make sure to check the Yes, I have read and agree to the Twitter Developer Agreement (after you read it) and click the Create your Twitter Application button. Once your application has been created, you will be directed to an overview page. Note the Token URL for the App-only authentication (https://api.twitter.com/oauth2/token), you will need this later on. Then click on the manage keys and access tokens link. Note down the Consumer Key and Consumer Secret; these are the authentication credentials we will be using in Application Express in order to access the Twitter API. It also might be a good idea to change the applications' permissions to Read Only. Next, head over to Application Express. Create an empty application or use an existing one. If your database is behind a Firewall, set the Proxy Server first (Shared Components > Application Definition Attributes). Then navigate to Shared Components > Web Sources. Create a new Web Source Module from Scratch.  Use Simple HTTP as the Web Source Type and https://api.twitter.com/1.1/search/tweets.json as the URL Endpoint. Then click Next. In the Remote Server dialog, make a change to the proposal of APEX. Use https://api.twitter.com/1.1/ as Base URL and search/tweets.json as Service URL Path. Then click Next to advance to the Authentication dialog. The Twitter API does require authentication, so set the switch to Yes. Choose OAuth2 Client Credentials flow as the Authentication Type and use the Twitter Token URL noted down above above. Put in the Consumer Key and Secret (which you also noted down earlier) and the Client ID and Client Secret attributes. Then finished, do not click on Discover, click on Advanced instead, in order to configure required parameters. The q parameter is used to pass the actual query to the Twitter Search API endpoint. Declare it as a Query String Variable and use #orclapex as the default value (we need a default value in order to discover the API response in the next step). Add a count parameter with a value of 100. When the Is Static attribute is set to Yes, individual APEX components cannot change that parameter value. When done, click on the Discover button. Application Express will invoke the REST endpoint and then investigate the response.   You will see a sample of the data being returned from the REST Endpoint. Click Create Web Source to save the Web Source Module in Application Express. Before building components based on this API, we'll do two things: To minimize HTTP requests performed, the Caching feature will be used Within the data profile, a "real" TIMESTAMP column will be created, based on the CREATED_AT attribute from the Twitter API. The Twitter API returns many attributes. We'll hide all unnecessary ones. So click the name of the Web Source Module (here: Twitter Search API) and open the Operations tab. Click the pencil to edit this operation and choose the Caching tab. Enable Caching for All Users and choose 15 as the Invalidate When attribute. That means, that an API response is being cached for all APEX sessions and users and that the cached content will remain valid for 15 minutes. Of course, the cache is parameter-aware. So a different query parameter will lead to a new request being made. Click the Apply Changes to save your changes. Then click the Data Profile tab. As you can see, the Twitter API returns a lot of attributes - and we will only need a subset of these. So click the Edit Data Profile button to see the details. In the Columns section first scroll down until the end, to have all the columns being loaded into the page. Then scroll up to the heading again, click the Select All check box and open the context menu. Click Hide selected Columns. All columns are now "invisible" to APEX components. Next, we'll unhide the columns of interest. Look up the following attributes and set the Visible column to Yes.  CREATED_AT ID TEXT NAME SCREEN_NAME FOLLOWERS_COUNT FRIENDS_COUNT FAVOURITES_COUNT RETWEET_COUNT_2 FAVORITE_COUNT_2 Then click the Add Column button to add a new column. We will create the column CREATED_AT_TSTZ to derive a TIMESTAMP WITH TIME ZONE value from the CREATED_AT attribute (which is of VARCHAR2). Use the following column attributes: Name: CREATED_AT_TSTZ Column Type: Derived SQL Expression: TO_TIMESTAMP_TZ(SUBSTR(CREATED_AT,4), 'MON DD HH24:MI:SS TZHTZM YYYY') Data Type (should be populated automatically): Timestamp with time zone Click Create to save the new Derived Column. Click Apply Changes to save the data profile. Now all preparations are done and application components can be built. Navigate to the pages overview of your application and click Create Page. Create a report page and pick Classic Report.  Provide a name for your page and follow the wizard until the last Report Source step. In the Report Source wizard step, choose Web Source Module as the Data Source for your report. Then choose the Twitter Search API module. Accept the list of columns and click Create in order to create your page. Since the Web Source Module has a parameter defined, you can now change its value for this report (you can change it to use a page item later on, in Page Designer). Click Create when done. When the page has been created, you will be redirected to Page Designer. Change some titles and layout options if you wish, then run your page. It should look as follows.   Next, we'll improve the layout. There are many different layouts available out-of-the-box for Classic Report - the That's a Classic Report? Really? blog posting introduces these and shows how to use them.  In Page Designer, navigate to the report attributes and, in the property pane on the right, change the report to use the Search Results template. However, the Search Results template expects specific result column names like SEARCH_TITLE, SEARCH_DESC and so on. The Web Source Module does not return these columns. We could go back to the data profile and change column names there, but since we will need such customized columns only in this Classic Report, we'll use the Post Processing feature. In Page Designer, navigate to the region attributes, look up the Local Post Processing area (below Source) and choose SQL Query as the Post Processing Type. The SQL Query will be pre-populated with the data profile columns. Now change the SQL query as follows: select NAME || ' tweeted at ' || to_char(CREATED_AT_TSTZ, 'Mon DD, HH24:MI TZR') as search_title, TEXT as search_desc, 'https://twitter.com/' || SCREEN_NAME || '/status/' || id as search_link, 'Retweets' as label_01, RETWEET_COUNT_2 as value_01, 'Likes' as label_02, FAVORITE_COUNT_2 as value_02, SCREEN_NAME || ' followers' as label_03, FOLLOWERS_COUNT as value_03 from #APEX$SOURCE_DATA# In Page Designer, the column list will also change. Now run the page again. Finally, we'll amend this page with a page item, allowing to enter a search term. The report is then supposed to display tweets matching the search term. Luckily, all integration is already done in the Web Source Module. Here, we just need to add the required components to the page. Add a region above the Classic Report and add a Text Field Item named PX_SEARCH to that region  Use #orclapex as the default value for the PX_SEARCH item Add a dynamic action which refreshes the Classic Report when the item is changed Also add a button right to the text field. Add another dynamic action, which also refreshes the classic report on button click. The page should then look as follows (we're not done yet). Now go back to Page Designer and navigate to the Classic Report region in the tree on the left. Open the parameters tree node and look up the q parameter. Then, head over to the property pane on the right. In the Value section, change from Static Value to Item and pick the item PX_SEARCH, you just have created.  Finally, add PX_SEARCH to the Page Items to Submit attribute below the region source to make sure that changed values are being sent with every AJAX request. Save your changes and run the page. Change the query and click the button - you'll see that the result list changes as well. You have built your own Twitter Search interface - with Application Express!

This is the next part of the blog post series on the new REST features in Application Express 18.1. In this blog posting, we will show how to access a protected REST service, which...

APEX 18.1 Early Adopter 2: Caching for REST and Remote SQL

When using a REST Enabled SQL service or an external REST service to drive APEX components like reports, charts or calendars, each page view will lead to an HTTP request being made from the APEX database to the external service. This provides the developer great flexibility to incorporate external data sources into their applications - however, the HTTP requests will, of course, slow down page response times. How much that is, depends on network latency, bandwidth and availability of the remote service. It's often not required to actually fetch latest data from the remote service for each page view; in practice it would be fine to cache the data for a certain amount of time. For that reason, Application Express provides a Caching facility for Web Source Modules and REST Enabled SQL Services. For Web Source Modules, Caching is being specified in the module definition within Shared Components. The following sections will illustrate how to enable and to use caching for the Github Repositories example described in the Application Express Early Adopter: REST Services! blog posting. Navigate to the Operations tab within the Web Source Module definition. The Github Repositories module will only contain one GET operation, for which we'll now enable caching. Caching can actually only be enabled for GET operations, because only these are defined as idempotent. All other HTTP methods (PUT, POST, DELETE, PATCH) may change data on the remote server, thus such requests cannot be cached. Click on the GET operation in order to see the details for this operation; then look up the Caching tab. By default, Caching is disabled. It can be enabled on the User or Session level and globally (for all users). The Invalidate When attribute determines when cached content has to be treated as stale and fresh data is to be fetched. A numeric value for the Invalidate When attribute will be interpreted as Minutes - so the cache will be invalidated X minutes after it has been fetched. In addition to that, a DBMS_SCHEDULER Calendaring Expression can be used for the Invalidate When attribute. That syntax is described in the Documentation for the DBMS_SCHEDULER package and allows more flexible definitions for the time when the cached content is to be considered as stale. Examples: FREQ=DAILY;BYHOUR=0;BYMINUTE=0;BYSECOND=0 Cached content will be invalidated at midnight. FREQ=HOURLY;BYMINUTE=0;BYSECOND=0 Cached content will be invalidated at the top of the hour. A caching configuration might look as follows: Now create a page with a classic report using this Web Source module. In the region attributes, add #TIMING# to the Region Footer, which will print the elapsed time for this very region into the region footer. Note that #TIMING# does not work for components which use asynchronous requests to render (JET charts, Interactive Grid).  When the page is rendered the first time, the result might look as follows: In this case, it took almost 2 seconds to render the time. Reviewing the debug log will reveal that almost all time was spent for the HTTP request. Then refresh the page - and you'll see the same output, but it should be much faster. In the second case, no HTTP request has been performed at all. Application Express checked for a valid cache record and found one. APEX caches the response data received from the REST service, not parsed content. So the cache will contain JSON documents returned by the REST service. To manually invalidate the cache, use the APEX_EXEC package. begin apex_exec.purge_web_source_cache( p_module_static_id => 'Github_Repositories', p_current_session_only => false ); end; The developer can use APEX_EXEC.PURGE_WEB_SOURCE_CACHE, for instance, to add a button to the page. Clicking the button will execute the above code as a PL/SQL page process. When the page is rendered again afterwards, no cached data is available, so APEX will fetch fresh data from the REST service. The ability to enable caching for Web Source modules allows to reduce the dependency of the APEX application to the external REST service. Once the content is cached, no HTTP requests will be performed, rendering application pages significantly faster. But this is not the only use case: some REST services impose rate limits; invoking them for every page view might consume existing capacity way to quick. Once caching is enabled, requests will only happen when it's really required. Back to blogs.oracle.com/apex    

When using a REST Enabled SQL service or an external REST service to drive APEX components like reports, charts or calendars, each page view will lead to an HTTP request being made from the APEX...

APEX 18.1 Early Adopter 2: REST Services and Plug-Ins

In previous blog postings, we introduced and explained the new REST and REST Enabled SQL capabilities in Application Express 18.1. APEX can access data not only from the local database, but also from remote Oracle databases (using REST Enabled SQL) and from arbitrary REST services. APEX components are just built on top of these remote data sources. This blog posting will be about Plug-Ins. The new Application Express version enables remote databases and REST services not only for standard APEX components (like reports or charts); Plug-In developers can leverage this feature as well. The most important bits for Plug-In developers are the APEX_EXEC PL/SQL package (Documentation) and the Region supports different Data Sources Plug-In attribute. A Plug-In is able to deal with local SQL, remote SQL and REST services, when this attribute is enabled and when the Plug-In collects all its data only with the APEX_EXEC package.   So let's start building a Plug-In.  Navigate to Shared Components, then to Plug-Ins and click the Create button. Create a new Plug-In from scratch. In the following screen, provide a display and an internal name and specify that this will be a Region Plug-In. Click the Create Plug-In button to create an (empty) Plug-In. Then, navigate to the Standard Attributes section and enable the following items: Region Source supports different Data Sources Has "Page Items to Submit" Attribute Has "No Data Found Message" Attribute Has "Escape Special Characters" Region Attribute Also click the Return to page checkbox in the upper right corner of the screen. That will lead the browser to stay on this page when saving changes with the Apply Changes button (which you should do now). Your changes will be saved, but you will not leave the Plug-In edit page. Use the below code as the Plug-Ins PL/SQL Code (here is the complete code). function handleZero(p_number in number) return number is begin return case when p_number = 0 then 1 else p_number end; end handleZero; function calculate_color( p_min_col varchar2, p_max_col varchar2, p_min_val number, p_max_val number, p_val number ) return varchar2 is l_min_red pls_integer := to_number(substr(p_min_col, 2, 2), 'XX'); l_max_red pls_integer := to_number(substr(p_max_col, 2, 2), 'XX'); l_min_blu pls_integer := to_number(substr(p_min_col, 6, 2), 'XX'); l_max_blu pls_integer := to_number(substr(p_max_col, 6, 2), 'XX'); l_min_gre pls_integer := to_number(substr(p_min_col, 4, 2), 'XX'); l_max_gre pls_integer := to_number(substr(p_max_col, 4, 2), 'XX'); l_red pls_integer; l_gre pls_integer; l_blu pls_integer; begin l_red := l_min_red + ((l_max_red - l_min_red) * ((p_val - p_min_val) / handleZero(p_max_val - p_min_val))); l_gre := l_min_gre + ((l_max_gre - l_min_gre) * ((p_val - p_min_val) / handleZero(p_max_val - p_min_val))); l_blu := l_min_blu + ((l_max_blu - l_min_blu) * ((p_val - p_min_val) / handleZero(p_max_val - p_min_val))); return '#'||ltrim(to_char(l_red, '0X'))||ltrim(to_char(l_gre, '0X'))||ltrim(to_char(l_blu, '0X')); end calculate_color; procedure generate_cloud( p_region in apex_plugin.t_region ) is type t_label_cloud_row is record( label varchar2(32767), value number ); type t_label_cloud_tab is table of t_label_cloud_row index by pls_integer; l_label_col apex_application_page_regions.attribute_01%type := p_region.attribute_01; l_value_col apex_application_page_regions.attribute_02%type := p_region.attribute_02; l_maximum_rows apex_application_page_regions.attribute_03%type := p_region.attribute_03; l_mincolor apex_application_page_regions.attribute_04%type := p_region.attribute_04; l_maxcolor apex_application_page_regions.attribute_05%type := p_region.attribute_05; l_minsize apex_application_page_regions.attribute_06%type := p_region.attribute_06; l_maxsize apex_application_page_regions.attribute_07%type := p_region.attribute_07; l_values t_label_cloud_tab; l_columns apex_exec.t_columns; l_context apex_exec.t_context; l_idx pls_integer := 0; l_max_value number := 0; l_min_value number := 0; l_label_col_no pls_integer; l_value_col_no pls_integer; l_label varchar2(32767); begin l_context := apex_exec.open_query_context( p_columns => l_columns, p_max_rows => l_maximum_rows ); l_label_col_no := apex_exec.get_column_position( l_context, l_label_col ); l_value_col_no := apex_exec.get_column_position( l_context, l_value_col ); while apex_exec.next_row( p_context => l_context ) loop l_idx := l_idx + 1; l_label := apex_exec.get_varchar2( l_context, l_label_col_no ); l_values( l_idx ).value := apex_exec.get_number( l_context, l_value_col_no ); l_values( l_idx ).label := case when p_region.escape_output then apex_escape.html( l_label ) else l_label end; if l_values( l_idx ).value < l_min_value then l_min_value := l_values( l_idx ).value; end if; if l_values( l_idx ).value > l_max_value then l_max_value := l_values( l_idx ).value; end if; end loop; apex_exec.close( l_context ); if l_values.count > 0 then l_idx := l_values.first; while l_idx is not null loop sys.htp.prn( '<span class="labelcloud-plugin-label" style="white-space: nowrap; color: '|| calculate_color( l_mincolor, l_maxcolor, l_min_value, l_max_value, l_values( l_idx ).value )|| '; font-size: '|| round( l_minsize + ( ((l_values( l_idx ).value - l_min_value) / handleZero(l_max_value - l_min_value)) * (l_maxsize - l_minsize) ) )|| 'px;">'|| l_values( l_idx ).label || '</span>' ); l_idx := l_values.next( l_idx ); end loop; else sys.htp.p( p_region.no_data_found_message); end if; exception when others then apex_exec.close( l_context ); raise; end generate_cloud; function apexplugin_render_ajax ( p_region in apex_plugin.t_region, p_plugin in apex_plugin.t_plugin ) return apex_plugin.t_region_ajax_result is begin generate_cloud( p_region => p_region ); return null; end apexplugin_render_ajax; function apexplugin_render ( p_region in apex_plugin.t_region, p_plugin in apex_plugin.t_plugin, p_is_printer_friendly in boolean ) return apex_plugin.t_region_render_result is l_ajax_result apex_plugin.t_region_ajax_result; begin sys.htp.p( '<div id="labelcloud_plugin_' || apex_plugin_util.escape(p_region.static_id, true) || '" class="labelcloud-plugin">' ); l_ajax_result := apexplugin_render_ajax(p_region, p_plugin); sys.htp.p('</div>'); apex_javascript.add_onload_code( 'apex.jQuery("#'||apex_javascript.escape(p_region.static_id)||'")' || '.bind("apexrefresh", function() {labelCloudPluginRefresh('|| apex_javascript.add_value(p_region.static_id, true)|| apex_javascript.add_value(apex_plugin.get_ajax_identifier, true)|| apex_javascript.add_value(p_region.ajax_items_to_submit, false)||');});'); return null; end apexplugin_render; This code uses the APEX_EXEC package to get data from either the local or remote database, or a REST service. Some interesting snippets within this code are ... Invoke the actual SQL query or fetch data from the REST service: l_context := apex_exec.open_query_context( p_columns => l_columns, p_max_rows => l_maximum_rows ); The APEX_EXEC.OPEN_QUERY_CONTEXT (Documentation) uses the current region meta data in order to determine where to get the data from. When the region uses a SQL query to be executed on the local database, APEX_EXEC just does this. However, when the region has been configured to use a Web Source ( REST Service ), then APEX_EXEC.OPEN_QUERY_CONTEXT will invoke the REST service, parse the returned data and provide rows and columns to the Plug-In. OPEN_QUERY_CONTEXT takes even more parameters. Plug-In developers can, for instance, also supply a "wrapping SQL query" to post-process the data. The procedure signature is as follows: function open_query_context ( p_columns in t_columns default c_empty_columns, -- p_filters in t_filters default c_empty_filters, p_order_bys in t_order_bys default c_empty_order_bys, -- p_first_row in pls_integer default null, p_max_rows in pls_integer default null, p_total_row_count in boolean default false, p_total_row_count_limit in pls_integer default null, -- p_select_all_query_cols in boolean default false, -- p_sql_parameters in t_parameters default c_empty_parameters ) return t_context;   Loop over the result set: while apex_exec.next_row( p_context => l_context ) loop l_idx := l_idx + 1; : l_label := apex_exec.get_varchar2( l_context, l_label_col_no ); : end loop; Using the APEX_EXEC.NEXT_ROW function, the Plug-In will iterate over the rows, returned by the SQL query or REST service. Individual column values are being fetched with the GET_VARCHAR2, GET_NUMBER and other functions within the APEX_EXEC package. NEXT_ROW will return true as long as the data source returns rows.  When REST services powered by ORDS (Oracle REST Data Services) are being used, NEXT_ROW will even trigger fetching the next page from the server - transparently to the Plug-In.   Close the Query Context object and release resources apex_exec.close( l_context ); When all rows have been processed, release all resources with APEX_EXEC.CLOSE (Documentation). This will also close cursors being opened during query execution.   When something goes wrong: Don't forget the exception handler exception when others then apex_exec.close( l_context ); raise; Since APEX_EXEC.CLOSE also releases open cursors, it's important to have it called always, also when exceptions occur within the Plug-In PL/SQL code. So, make sure to have an exception handler with at least APEX_EXEC.CLOSE in it. You may simply raise the exception afterwards. The rest of the PL/SQL code is specific to the Plug-In domain (which is rendering data as a "label cloud"). Feel free to browse through the code in order to understand what will happen. When done, copy & paste the code into the PL/SQL Code code editor and click Apply Changes. Since you have enabled the Return to page checkbox in the upper right corner, you will stay on the Plug-In page. In the Callbacks section, enter the procedure names from the PL/SQL code as the AJAX and Render function names, as follows: apexplugin_render is the Render Procedure/Function Name apexplugin_render_ajax is the AJAX Procedure/Function Name   Next, it's time to configure the Plug-Ins Custom Attributes. In the Custom Attributes Section, click the Add Attribute button to create a new attribute. Configure the following attributes - make sure to set Required to Yes for all of them. Label Column of the Region SQL Statement Column type. For a label column, you can allow Varchar2, Number, Clob and all Date and Timestamp data types. Value Column of the Region SQL Statement Column type. Only the Number data type should be allowed here. Maximum Rows of the Number type. Use a default value of 1000 and allow 5 digits to enter. "Minimum" Color (Hex) of the Text type. Allow 10 characters to enter. Default 808080. "Maximum" Color (Hex) of the Text type. Allow 10 characters to enter. Default FF0000. Minimum Size of the Number type. Allow 5 digits to enter; default 10. Maximum Size of the Number type. Allow 5 digits to enter; default 20. When done, the Custom Attributes section should look as follows. Finally, we need to add a bit of Javascript and CSS code to the Plug-In. First, store the following code in a file named labelcloudajax.js and upload. function labelCloudPluginRefresh( pRegionId, pPluginName, pItemList ) { var lItemList; if ( pItemList && pItemList != "" ) { lItemList = pItemList.split(",").reduce( function( r, i ) { return r + "#" + i.replace( /^#?/, "" ) + ","; }, "" ) .replace( /,$/, "" ); } apex.event.trigger( $x("labelcloud_plugin_"+pRegionId), "apexbeforerefresh" ); apex.server.plugin( pPluginName, { pageItems: lItemList }, { dataType: "text", success: function( pData ) { $x("labelcloud_plugin_"+pRegionId).innerHTML = pData; apex.event.trigger( $x("labelcloud_plugin_"+pRegionId), "apexafterrefresh" ); } } ); } Then, store this CSS code as labelcloudcss.css and upload it. .labelcloud-plugin { text-align: center } .labelcloud-plugin-label { margin-left: 10px; margin-right: 10px; display: inline-block;} In the File URLs to Load section, reference the uploaded files as follows: #PLUGIN_FILES#labelcloudcss.css in the Cascading Style Sheet text area. #PLUGIN_FILES#labelcloudajax.js in the JavaScript text area. And that's it. After finally saving all changes we can start using the Plug-In on a new Application Express Page. Create a new, blank page and open Page Designer. From the Region Gallery below, drag a region of the new A brand new Label Cloud Plug-In to the Content Body section of the page. Let's first use the well-known EMP table as the data source for the Plug-In. Configuring Region Attributes (Section Source) is very straightforward: Location: Local database Type: Table / View Table Owner: Parsing Schema Table Name: EMP In the navigation tree on the left side, click the Attributes node of the new region (which should be marked red). Configure ENAME as the Label Column and SAL as the Value Column. Leave the other attributes at their defaults. When done, run your page. The result should look as follows: Now let's use this Plug-In for a REST service. Navigate to Shared Components and configure the "Github Repositories" Web Source Module, as described in the  Application Express Early Adopter: REST Services! blog posting. Then navigate back to Page Designer and drag another region of the Plug-In type to your page. In the Source section of the region attributes, now choose Web Source as Location and Github Repositories as the Web Source Module. In the Plug-In attributes, configure NAME as the Label Column and WATCHERS_COUNT as the Value Column (of course, you can choose other columns as well). Save your changes and run the page. Your page now contains two Label Clouds - both are rendered with the same APEX Plug-In. As APEX standard components, your Plug-Ins can work on remote and local data. To leverage REST Enabled SQL, configure a REST Enabled SQL service, as described in the  Application Express Early Adopter: REST Enabled SQL! blog posting and configure the Plug-In accordingly. One Plug-In implementation can work on different data sources. Back to blogs.oracle.com/apex

In previous blog postings, we introduced and explained the new REST and REST Enabled SQL capabilities in Application Express 18.1. APEX can access data not only from the local database, but also from...

APEX 18.1 Early Adopter 2: REST Services and PL/SQL

When Application Express 5.2 Early Adopter 1 release came out, the new REST service support was introduced with two blog postings: Application Express Early Adopter: REST Services! Application Express Early Adopter: More than one REST Service! Based on the first example (which will work similar in Early Adopter 2), we'll today introduce, how to use Web Source Modules within PL/SQL. So, in your Early Adopter Workspace, recreate the Github Repositories Web Source Module, as outlined in the Application Express Early Adopter: REST Services! posting. Then, in the Web Source Modules section within Shared Components, click the newly created module to see its details. Click the Advanced tab and look up the Static ID attribute .. The Github_Repositories identifier will be important, when using this Web Source Module in custom PL/SQL code. Of course, you can always change the Static ID; however, once you have PL/SQL procedures depending on it, this is not advisable any more. The new APEX_EXEC (Documentation) package is your entry point to use not only Web Source Modules, but also REST Enabled SQL sources with PL/SQL. Use-cases can be PL/SQL processes, page regions of the "Dynamic PL/SQL Content" type or Plug-Ins. Let's start with a simple example, a dynamic PL/SQL region. Add a page to your application and then add a Dynamic PL/SQL Content region with the following PL/SQL code. declare l_columns apex_exec.t_columns; l_context apex_exec.t_context; type t_column_position is table of pls_integer index by varchar2(32767); l_column_position t_column_position; begin -- specify columns to select from the web source module apex_exec.add_column( p_columns => l_columns, p_column_name => 'ID' ); apex_exec.add_column( p_columns => l_columns, p_column_name => 'NAME' ); apex_exec.add_column( p_columns => l_columns, p_column_name => 'LANGUAGE' ); apex_exec.add_column( p_columns => l_columns, p_column_name => 'WATCHERS_COUNT' ); apex_exec.add_column( p_columns => l_columns, p_column_name => 'CREATED_AT' ); -- invoke Web Source Module and select data l_context := apex_exec.open_web_source_query( p_module_static_id => 'Github_Repositories', p_columns => l_columns ); -- now get result set positions for the selected columns l_column_position( 'ID' ) := apex_exec.get_column_position( l_context, 'ID' ); l_column_position( 'NAME' ) := apex_exec.get_column_position( l_context, 'NAME' ); l_column_position( 'LANGUAGE' ) := apex_exec.get_column_position( l_context, 'LANGUAGE' ); l_column_position( 'WATCHERS_COUNT' ) := apex_exec.get_column_position( l_context, 'WATCHERS_COUNT' ); l_column_position( 'CREATED_AT' ) := apex_exec.get_column_position( l_context, 'CREATED_AT' ); -- loop through result set and print out contents sys.htp.p( '<pre>' ); while apex_exec.next_row( l_context ) loop htp.prn( lpad( apex_exec.get_varchar2( l_context, l_column_position( 'ID' ) ), 20, ' ' ) || ' '); htp.prn( rpad( apex_exec.get_varchar2( l_context, l_column_position( 'NAME' ) ), 40, ' ' ) ); htp.prn( rpad( nvl( apex_exec.get_varchar2( l_context, l_column_position( 'LANGUAGE' ) ), ' ' ), 20, ' ' ) ); htp.prn( lpad( apex_exec.get_varchar2( l_context, l_column_position( 'WATCHERS_COUNT' ) ), 6, ' ' ) ); htp.p( lpad( apex_exec.get_varchar2( l_context, l_column_position( 'CREATED_AT' ) ), 20, ' ' ) ); end loop; sys.htp.p( '</pre>' ); -- finally: release all resources apex_exec.close( l_context ); exception when others then -- IMPORTANT: also release all resources, when an exception occcurs! apex_exec.close( l_context ); raise; end; The resulting page should look as follows ... That is nothing too special ... a report with a simple template would achieve the same output. But it's not about the output as such; it's about how we achieved that output. It was a custom PL/SQL loop, and every bit was completely under control of the developer. If one uses that code in a PL/SQL process, replacing the SYS.HTP.P calls with a SQL INSERT statement, it becomes pretty simple to copy Web Source data into an own table. And the cool bit about this is, that we now can access a REST Services with PL/SQL; but we do not have to bother with JSON parsing or correct parameters for APEX_WEB_SERVICE calls. The PL/SQL code deals with the REST service on a high level, while all low-level work is done by Application Express.  Let's take that example a bit further. We want to have a PL/SQL table function which can be accessed in Scheduler Jobs and outside of APEX as well. First, let's implement the table function; the code above covers 85%; we just have to add some boilerplate code for a table function. Run the following code in SQL Workshop; either as a SQL Script or one by one in SQL Commands. create or replace type github_repo_row_t as object( id number, name varchar2(50), language varchar2(50), watchers_count number, created_at timestamp with time zone ) / create or replace type github_repo_table_t as table of github_repo_row_t / create or replace function github_repos return github_repo_table_t pipelined is l_columns apex_exec.t_columns; l_context apex_exec.t_context; type t_column_position is table of pls_integer index by varchar2(32767); l_column_position t_column_position; begin -- specify columns to select from the web source module apex_exec.add_column( p_columns => l_columns, p_column_name => 'ID' ); apex_exec.add_column( p_columns => l_columns, p_column_name => 'NAME' ); apex_exec.add_column( p_columns => l_columns, p_column_name => 'LANGUAGE' ); apex_exec.add_column( p_columns => l_columns, p_column_name => 'WATCHERS_COUNT' ); apex_exec.add_column( p_columns => l_columns, p_column_name => 'CREATED_AT' ); -- invoke Web Source Module and select data l_context := apex_exec.open_web_source_query( p_module_static_id => 'Github_Repositories', p_columns => l_columns ); -- now get result set positions for the selected columns l_column_position( 'ID' ) := apex_exec.get_column_position( l_context, 'ID' ); l_column_position( 'NAME' ) := apex_exec.get_column_position( l_context, 'NAME' ); l_column_position( 'LANGUAGE' ) := apex_exec.get_column_position( l_context, 'LANGUAGE' ); l_column_position( 'WATCHERS_COUNT' ) := apex_exec.get_column_position( l_context, 'WATCHERS_COUNT' ); l_column_position( 'CREATED_AT' ) := apex_exec.get_column_position( l_context, 'CREATED_AT' ); -- loop through result set and print out contents while apex_exec.next_row( l_context ) loop pipe row( github_repo_row_t( apex_exec.get_number( l_context, l_column_position( 'ID' ) ), apex_exec.get_varchar2( l_context, l_column_position( 'NAME' ) ), apex_exec.get_varchar2( l_context, l_column_position( 'LANGUAGE' ) ), apex_exec.get_number( l_context, l_column_position( 'WATCHERS_COUNT' ) ), apex_exec.get_timestamp_tz( l_context, l_column_position( 'CREATED_AT' ) ) ) ); end loop; -- finally: release all resources apex_exec.close( l_context ); exception when others then -- IMPORTANT: also release all resources, when an exception occcurs! apex_exec.close( l_context ); raise; end; The table function code looks very similar to the region code above. The main difference is that we are now using the PIPE ROW command to return rows. And we don't return everything as VARCHAR2, we respect the individual column data types instead. One way to test the table function could be a Classic Report component on an APEX page ... To use this table function outside of an APEX application, another new feature in Application Express 18.1 comes in handy: APEX_SESSION.CREATE_SESSION (Documentation). This procedure allows to construct an APEX session context within PL/SQL.  Thus, to use the table function, we need to set up a session context as follows: begin apex_session.create_session( p_app_id => 507, p_page_id => 1, p_username => 'XXXXXXXX' ); end; However, this is something which the Early Adopter Instance does not really allow you to test, since you won't get SQL*Plus access to it. When Application Express has been released, usage in SQL*Plus can look as follows ... Let's now make that table function a bit flexible. First, we add a Parameter to the Web Source Module definition to be used in the endpoint URL. Make changes as follows: Change the URL Path Prefix to users/:github_user/repos   Add the github_user parameter as an URL pattern variable. We have a Web Source Module invoking a dynamic endpoint URL; fetching repository data from any Github user we want. Let's now adjust the table function to make use of that parameter ... create or replace function github_repos( p_github_user in varchar2 default 'oracle' ) return github_repo_table_t pipelined is l_columns apex_exec.t_columns; l_context apex_exec.t_context; l_parameters apex_exec.t_parameters; type t_column_position is table of pls_integer index by varchar2(32767); l_column_position t_column_position; begin -- specify columns to select from the web source module apex_exec.add_column( p_columns => l_columns, p_column_name => 'ID' ); apex_exec.add_column( p_columns => l_columns, p_column_name => 'NAME' ); apex_exec.add_column( p_columns => l_columns, p_column_name => 'LANGUAGE' ); apex_exec.add_column( p_columns => l_columns, p_column_name => 'WATCHERS_COUNT' ); apex_exec.add_column( p_columns => l_columns, p_column_name => 'CREATED_AT' ); -- add the parameter apex_exec.add_parameter( p_parameters => l_parameters, p_name => 'github_user', p_value => p_github_user); -- invoke Web Source Module and select data l_context := apex_exec.open_web_source_query( p_module_static_id => 'Github_Repositories', p_parameters => l_parameters, p_columns => l_columns ); -- now get result set positions for the selected columns l_column_position( 'ID' ) := apex_exec.get_column_position( l_context, 'ID' ); l_column_position( 'NAME' ) := apex_exec.get_column_position( l_context, 'NAME' ); l_column_position( 'LANGUAGE' ) := apex_exec.get_column_position( l_context, 'LANGUAGE' ); l_column_position( 'WATCHERS_COUNT' ) := apex_exec.get_column_position( l_context, 'WATCHERS_COUNT' ); l_column_position( 'CREATED_AT' ) := apex_exec.get_column_position( l_context, 'CREATED_AT' ); -- loop through result set and print out contents while apex_exec.next_row( l_context ) loop pipe row( github_repo_row_t( apex_exec.get_number( l_context, l_column_position( 'ID' ) ), apex_exec.get_varchar2( l_context, l_column_position( 'NAME' ) ), apex_exec.get_varchar2( l_context, l_column_position( 'LANGUAGE' ) ), apex_exec.get_number( l_context, l_column_position( 'WATCHERS_COUNT' ) ), apex_exec.get_timestamp_tz( l_context, l_column_position( 'CREATED_AT' ) ) ) ); end loop; -- finally: release all resources apex_exec.close( l_context ); exception when others then -- IMPORTANT: also release all resources, when an exception occcurs! apex_exec.close( l_context ); raise; end; / From now on, we can call the table function with a Github username as the parameter - the table function will then return repository information for that very user.  The APEX_EXEC package opens the new Web Sources and REST Enabled SQL technology up for the PL/SQL developer. These external data sources can not only  consumed by out-of-the-box APEX components, but also by custom PL/SQL code. Plug-In Developers can use the APEX_EXEC package as well - but that topic will be covered in another blog posting. https://blogs.oracle.com/apex https://apexea.oracle.com

When Application Express 5.2 Early Adopter 1 release came out, the new REST service support was introduced with two blog postings: Application Express Early Adopter: REST Services! Application Express...

APEX 18.1 Early Adopter 2: Post-Process REST Service Queries

Application Express 18.1 Early Adopter 2 contains an important feature of the new REST Service support: The ability to specify Post Processing SQL.  The previous blog postings Application Express Early Adopter: REST Services! and Application Express Early Adopter: More than one REST Service! describe how to create a Web Source Module referencing an external REST service and how to use it in APEX components like reports, charts or the calendar. This posting will describe how to leverage the new Post Processing section for a Web Source Module in Page Designer. First, create the Github Repositories Web Source Module, as described in the Application Express Early Adopter: REST Services!  posting. Now create a new page within your application - choose a Blank Page for now. Open Page Designer and drag a new Classic Report region onto your page. Now comes the interesting bit: On the property pane (right side), look up the Post Processing section, then pick SQL Query.   Application Express will pre-populate the SQL Query text area with a query selecting all web source module columns. Note the special placeholder #APEX$SOURCE_DATA#, which represents the data coming from the web source module. You can now change that SQL query as you wish ... first, we'll use it just to reduce columns ... Run your page - and you'll only see the subset of columns being displayed in your report. Using column aliases, we'll be able to use one of the predefined Report Templates provided by Application Express. select apex_string.get_initials(name) as card_initials, name as card_title, to_char(created_at, 'YYYY') as card_subtitle, language as card_text, description as card_subtext from #APEX$SOURCE_DATA# After that, choose the Cards report template and configure Template Options. The resulting page will look similar to the following. Have a look into the  That's a Classic Report? Really? blog posting, to learn more about the report templates Application Express provides out of the box, and how to configure them. The Post Processing SQL query allows you to use all these templates for REST services as well. We can use SQL functions as well. The following example determines the lifetime of a repository in hours, restricts to repositories based on the Java language and uses a CASE expression to determine an icon, based on the last update on the repository.  select apex_string.get_initials(name) as card_initials, name as card_title, extract(HOUR from (systimestamp - created_at)) + extract(DAY from (systimestamp - created_at)) * 24 || ' hours ago' as card_subtitle, language as card_text, case when updated_at > sysdate - 30 then 'fa-pencil-square-o' else 'fa-square-o' end as card_icon, description as card_subtext from #APEX$SOURCE_DATA# where upper(language) = 'JAVA' Then navigate to the Template Options of the Cards report template and change the Icons option from Display Initials to Display Icons. The result will look as follows: And you can do even more. In the post processing SQL query you might want to join to another table, which is possible, of course. The Sample REST Services packaged application contains an example for this. Keep in mind, that for REST services returning their results in pages (like ORDS services do), APEX needs to fetch all pages when a post processing query is used. This is because the post processing query might contain ORDER BY clauses, aggregate functions or analytic (windowing) functions. In order to process these, Application Express must be able to access all rows from the REST service. So, use post processing only for REST services returning a reasonable amount of rows - for a service returning thousands of rows, performance will suffer. Consider buffering those services to local tables using the new APEX_EXEC PL/SQL package. However, this will also become unfeasible beyond larger result set size. The Post Processing feature for an APEX component based on a REST service lets you convert REST service data into any format you'll need for display. That provides great flexibility when it's about using external REST services within an APEX application.  Back to blogs.oracle.com/apex p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Helvetica; -webkit-text-stroke: #000000} span.s1 {font-kerning: none}

Application Express 18.1 Early Adopter 2 contains an important feature of the new REST Service support: The ability to specify Post Processing SQL.  The previous blog postings Application Express Early...

Tips for parsing JSON in APEX

Working with JSON is daily business for Application Express developers. RESTful services typically return JSON, JSON is frequently used to store configuration or other kinds of flexible data, and JSON also becomes more and more a data exchange format. So developers frequently encounter the requirement to parse JSON documents and process data. Based on the database version, there are multiple alternatives to work with JSON documents in a PL/SQL or SQL context. On 11.2 or 12.1.0.1 databases, no native JSON functionality is present in SQL or PL/SQL. However, APEX provides the APEX_JSON package since version 5.0. In 12.1.0.2, the JSON_TABLE, JSON_QUERY or JSON_VALUE SQL functions were introduced. These allow to parse JSON within a SQL query or a SQL DML statement. Starting with 12.2, additional SQL functions for JSON generation as well as a PL/SQL based JSON parser is available. When using 12.2 database (or even 18.1), there are a few alternatives to choose from. Many APEX developers just continue using APEX_JSON - because it's known - and probably also because of the APEX_ prefix. But having a closer look into the native JSON functionality is absolutely well-invested time: it can lead to massively improved performance thus lowering general load on the database. This article will show a few comparisons between APEX_JSON and native functionality. Don't stop reading when you're still on 11.2 or have to support 11.2 databases as well. It might be a good idea to use PL/SQL conditional compilation and add APEX_JSON as well as native PL/SQL JSON parsing to your code. On a more recent database, your application will perform better out-of-the-box. And as soon as you discontinue your 11.2 support, you can simply remove the old code. And the nice side-effect is, that you already trained yourself on the new functionality. Prerequisites Let's now start with an example to compare the various approaches of JSON parsing in the database. We first need a JSON document to play with - and since the internet is full of them, we simply grab it from there. As for earlier articles and how tos, we'll use Earthquake JSON feeds provided by the US Geological Survey (USGS). With the APEX_WEB_SERVICE package we'll grab them and because we don't want to execute HTTP requests all the time, we'll store them as a CLOB into a table: create table earthquake_json( id number generated always as identity, fetched_at timestamp default systimestamp, document clob, constraint document_isjson check (document is json) ); insert into earthquake_json( document ) values ( apex_web_service.make_rest_request( p_url => 'https://earthquake.usgs.gov/earthquakes/feed/v1.0/summary/all_month.geojson', p_http_method => 'GET' ) ); select id, fetched_at, dbms_lob.getlength(document) from earthquake_json; ID FETCHED_AT DBMS_LOB.GETLENGTH(DOCUMENT) --- ---------------------------- ---------------------------- 1 26.02.18 02:29:59,988083000 6743973 Depending on the URL being invoked, the JSON contains earthquakes for a day, a week or a month. The JSON document structure is outlined in the screen shot below. The features attribute is an array - each element contains data for one earthquake. When working on a 12c or 11g database, you might encounter an ORA-28860 message ("Fatal SSL error") when invoking APEX_WEB_SERVICE.MAKE_REST_REQUEST. This is due to a bug in the UTL_HTTP package (bugs 25734963 and 26040483). Navigate to MyOracleSupport and request or download a patch for your database version. For instance, download patch number 27551077 for Oracle Database 12.2. For the below examples, we invoked the URL returning earthquakes for a month. That leads to somewhat larger JSON documents; which is intentional. On a larger scale, we'll clearly see how the different JSON parsers will perform. Parse JSON within PL/SQL First, we want to know, how many array elements are present in the features attribute. First attempt is with APEX_JSON: declare l_clob clob; l_feature_count pls_integer; l_time timestamp; begin select document into l_clob from earthquake_json where id = 1; l_time := systimestamp; apex_json.parse( p_source => l_clob ); dbms_output.put_line( 'Parsing Time: ' || extract( second from ( systimestamp - l_time ) ) ); l_time := systimestamp; l_feature_count := apex_json.get_count( 'features' ); dbms_output.put_line( 'Array Count: ' || l_feature_count ); dbms_output.put_line( 'Get Array Count Time: ' || extract( second from ( systimestamp - l_time ) ) ); end; Parsing Time: 12,293681 Array Count: 9456 Get Array Count Time: 0,000039 As the output indicates, the call to APEX_JSON.PARSE needed about 12 seconds; that is the time APEX_JSON needed to actually parse the JSON document. Counting the elements of the features array is rather quick; it needed only a very small fraction of a second. During the PARSE call, APEX_JSON built an internal representation of the JSON document; that required all the time. Retrieving information from that internal memory structure is actually pretty cheap. So, when working with APEX_JSON, try to call PARSE only once and to reuse the parsed JSON as often as possible. Let's now have a look into the PL/SQL Object Types for JSON parsing, introduced in Oracle Database 12.2. These types are JSON_OBJECT_T, JSON_ARRAY_T, JSON_ELEMENT_T, JSON_SCALAR_T and JSON_KEY_LIST. JSON_ELEMENT_T is a generalization of JSON_OBJECT_T, JSON_ARRAY_T or JSON_SCALAR_T, representing either a JSON object, an array or a scalar attribute value. JSON_ELEMENT_T, JSON_OBJECT_T or JSON_ARRAY_T contain a static PARSE method in order to parse a JSON document. When we change the above PL/SQL block in order to use the new PL/SQL object types, the result doesn't look so different ... declare l_clob clob; l_feature_count pls_integer; l_time timestamp; l_json json_object_t; l_features json_array_t; begin select document into l_clob from earthquake_json where id = 1; l_time := systimestamp; l_json := json_object_t.parse( l_clob ); dbms_output.put_line( 'Parsing Time: ' || extract( second from ( systimestamp - l_time ) ) ); l_time := systimestamp; l_features := l_json.get_array( 'features' ); dbms_output.put_line( 'Array Count: ' || l_features.get_size ); dbms_output.put_line( 'Get Array Count Time: ' || extract( second from ( systimestamp - l_time ) ) ); end; Parsing Time: 0,124148 Array Count: 9456 Get Array Count Time: 0,000083 ... but there is a huge difference regarding the consumed time. 0.12 seconds vs 12 seconds - that is about 100 times. For smaller documents, the performance difference is smaller, but it's always there. So JSON parsing with APEX_JSON is much more expensive than with the native functions - which is obvious, since APEX_JSON is a PL/SQL implementation whereas the PL/SQL object types have a C implementation as part of the database itself. And such a thing like JSON parsing (which is mostly string operations) will gain huge benefits from a native implementation. Parse JSON with a SQL query Now we want to look into the earthquake data in more detail; we want to get some information about the weakest and the strongest earthquake contained in the JSON document. We could do this the same way as we did above: implementing a procedure using APEX_JSON (or better: JSON_OBJECT_T), parse the JSON, then retrieve the data of interest using PL/SQL loops and calling the various APEX_JSON getter functions or JSON_OBJECT_T methods. But we're in a database, aren't we? The most powerful way to deal with data is to use SQL - and to use SQL functionality with JSON data, we need to treat a JSON document as it was a table. The JSON_TABLE SQL function will come in very handy now ... with eqdata as ( select e.id, e.title, e.mag from earthquake_json j, json_table( document, '$.features[*]' columns( id varchar2(20) path '$.id', mag number path '$.properties.mag', title varchar2(200) path '$.properties.title' ) ) e ), minmax as ( select min(e.mag) minmag, max(e.mag) maxmag from eqdata e ) select e.id, e.title, e.mag from eqdata e, minmax m where e.mag in ( m.minmag, m.maxmag ) / ID TITLE MAG ----------- ---------------------------------------------- ----- us2000d7q6 M 7.5 - 89km SSW of Porgera, Papua New Guinea 7.5 uw61366531 M -0.8 - 36km NNE of Amboy, Washington -0.8 2 rows selected. Elapsed: 00:00:01.545 JSON_TABLE allows to project attributes of an array within a JSON document as a table - with rows and columns. That table can then be used like a normal table - so within a SQL query we can build subqueries, apply aggregations or all other SQL functionality to the data. Isn't that an elegant way to parse JSON? The usage of subquery factoring and SQL aggregate functions leads to a clear, structured and very maintainable SQL query. Even more: if, at some day, the data will be present as a normal relational table, it will be super-easy to adopt to this. Most of the query wouldn't even have to be changed. JSON_TABLE is available in 12.1.0.2 or later. When you're still on 11.2, there's no JSON_TABLE. But does that mean, you cannot use this elegant SQL-centric JSON parsing ...? No, APEX_JSON will help. It provides the TO_XMLTYPE function which converts a JSON to an XML document. And for XML documents, the SQL function XMLTABLE is available since Oracle 9.2. So, combining APEX_JSON and XMLTABLE will give you the same power as JSON_TABLE does. Here's the SQL query doing the same as above - but that one will run on 11.2 as well. with eqdata as ( select e.id, e.title, e.mag from earthquake_json j, xmltable( '/json/features/row' passing apex_json.to_xmltype( j.document ) columns id varchar2(20) path 'id/text()', mag number path 'properties/mag/text()', title varchar2(200) path 'properties/title/text()' ) e ), minmax as ( select min(e.mag) minmag, max(e.mag) maxmag from eqdata e ) select e.id, e.title, e.mag from eqdata e, minmax m where e.mag in ( m.minmag, m.maxmag ) / ID TITLE MAG ----------- ---------------------------------------------- ----- us2000d7q6 M 7.5 - 89km SSW of Porgera, Papua New Guinea 7.5 uw61366531 M -0.8 - 36km NNE of Amboy, Washington -0.8 2 rows selected. Elapsed: 00:00:27.152 The results are the same. As we can see in the timing - there is no free lunch: We got the power of SQL to work on the JSON document in 11.2. But we first had to convert to XML with APEX_JSON; and that comes at a cost. 27 seconds is way more than the 1.4 seconds JSON_TABLE consumed by JSON_TABLE. A significant part of this was spent for the JSON to XML conversion ... select apex_json.to_xmltype(document) from earthquake_json where id = 1; APEX_JSON.TO_XMLTYPE(DOCUMENT) ------------------------------ <json>... Elapsed: 00:00:12.711 ... which pretty nicely matches the time consumption of APEX_JSON.PARSE in the first example. From the results of these simple tests, we can derive some rules for working with JSON in APEX (and also outside of APEX) ... APEX_JSON introduces JSON functionality to 11.2 and 12.1.0.1 databases, which don't have any native JSON support. So before having nothing, use APEX_JSON. But it comes at a price. Since APEX_JSON is implemented in PL/SQL, parsing is much more expensive compared to the native JSON functionality introduced in the upcoming database releases. Thus, on the other side: use(!) native JSON functionality when available in your database.   The JSON_TABLE SQL function (12.1.0.2) or  APEX_JSON.TO_XMLTYPE / XMLTYPE (on 11.2) allow to work on JSON documents with SQL. That allows not only to apply aggregate or other SQL functions, it also allows to drive APEX components like reports or charts with JSON data. Some tasks are much more efficient to implement in SQL - so when you have such a task: Use JSON_TABLE instead of procedural PL/SQL.   As your database instance and APEX applications move forward, consider replacing APEX_JSON calls with JSON_TABLE or JSON_OBJECT_T.PARSE, depending on the concrete task.   More Information Documentation on APEX_JSON (APEX 5.0) https://docs.oracle.com/cd/E59726_01/doc.50/e39149/apex_json.htm#AEAPI29635   Documentation on JSON_TABLE (Database 12.1.0.2) https://docs.oracle.com/database/121/SQLRF/functions092.htm#SQLRF56973   Documentation on JSON_OBJECT_T (Database 12.2.0.1) https://docs.oracle.com/en/database/oracle/oracle-database/12.2/adjsn/using-PLSQL-object-types-for-JSON.html#GUID-F0561593-D0B9-44EA-9C8C-ACB6AA9474EE   back to blogs.oracle.com/apex  

Working with JSON is daily business for Application Express developers. RESTful services typically return JSON, JSON is frequently used to store configuration or other kinds of flexible data, and JSON...

That's a Classic Report? Really?

As an APEX developer, thinking about reports, the following picture comes to mind ... the typical, tabular layout of data. But Classic Reports can do more - these are, for sure, one of the most versatile components in Application Express. The reason is that classic reports are template-driven. Developers can create own report templates and visualize data however wanted. But Application Express also provides a few classic report templates - out of the box. These allow data visualizations, which make it hard to believe that there is an APEX classic report behind this. In this posting, we'll give an overview of the alternative report templates provided with Application Express. A lot of information comes from the Universal Theme Sample Application (apex.oracle.com/ut) - there you'll find even more information on the Look & Feel variants of APEX components.  Now let's get started with the first alternative classic report template. In Page Designer, navigate to your report based on the EMP table, open the Report Attributes property pane and look up the Appearance section. Start with the Media List template; save your change and re-run the page. Well ... that's not what we wanted. But it's obvious what happened: the report template expects specific result columns from the SQL query. We can see that even better when looking into the template definition. You'll get there by clicking the > button right to the Report Template select list. Within the HTML markup, placeholders like  #ICON_CLASS# or #LIST_TITLE# are easy to find. The report SQL query can (but not has to) provide a value for each placeholder. So, we'll adjust the SQL query as follows. Alias names will give us the column names the template expects. select ename as list_title, job as list_text, 'fa fa-user' as icon_class, null as edit_link, sal as list_badge from emp Save and run the page again ...  Looks better, doesn't it? But there is more - the Media List template provides a few Template Options. Access these in Page Designer by clicking the Template Options button below the Report Template select list. A dialog, similar to the following, will open. Activate Show Badges and Apply Theme Colors, save the changes and run the page again ... Just a tiny change to the SQL query, a few mouse clicks - and we have a completely different data visualization. You can use the same process or all other report templates provided by Application Express. Let's move on to the Comments template. First, adjust the SQL query (the EMP table data is not well suited for that template, but is sufficient to act as example) ... select ename as user_name, 'My comment is: ' || job as comment_text, hiredate as comment_date, null as user_icon, 'Comment' as actions, ' ' as attribute_1, sal as attribute_2, ' ' as attribute_3, ' ' as attribute_4 from emp And have a look at the result page: For the Alert template, not only the placeholder names are important - the value for the #ALERT_TYPE# placeholder is particularly interesting - here's the SQL query. select ename as alert_title, job as alert_desc, sal as alert_action, case when sal < 1000 then 'info' when sal between 2000 and 3000 then 'success' when sal between 3000 and 4000 then 'warning' else 'danger' end as alert_type from emp And here's the result. A report row just contains the three values  title, desc and action. However, the value of the alert_type column controls the row color and icon - It's good to know the keywords info, success, warning and danger. These follow the semantics of the Application Express Alert region type. The Badge List template is well suited when your SQL query returns only one row. To display multiple rows, it's not recommended. In this case, the SQL query result column names will directly display in the report output. Here's an example: First (again) the SQL query: select empno, ename, extract(year from hiredate) as hired, sal, comm, deptno from emp where rownum = 1 The query does not use any special placeholders - column names can be arbitrary.  As the result page shows, the template directly displays the column names. This is a good candidate to visualize key-value pairs - but the SQL query must return one row. The Timeline template specializes (as the name indicates) on displaying subsequent events. select apex_string.get_initials(ename) as user_avatar, ename as user_name, hiredate as event_date, 'fa fa-user' as event_icon, case deptno when 10 then 'is-new' when 20 then 'is-removed' when 30 then 'is-updated' when 40 then 'is-updated' end as event_status, 'Hired' as event_type, job as event_title, sal || case when comm is not null then ' - ' end || comm as event_desc from emp Here is the result page - we can see that this template supports many placeholders and therefore many display options. Search Results provides the typical search engine look & feel. So it's very useful in combination with search functionality. The following SQL query is a (simple) example for that. It filters rows based on user inputs on the P10_SEARCH item. select ename search_title, job search_desc, null search_link, 'Sal' label_01, sal value_01, 'Hiredate' label_02, hiredate value_02, 'Comm' label_03, comm value_03, 'Deptno' label_04, deptno value_04 from emp where instr(ename||job, :P10_SEARCH)>0 or :P10_SEARCH is null Of course, that query is not a blueprint for searching on huge amounts of data. The database provides other means of search and filtering in these cases - one example is Oracle Text - but that is a topic for a different blog posting. A report using the Search Results template will typically look as follows: Last, but not least, we'll have a look at the Cards template. On the internet, you'll frequently find that kind of data visualization. And with the cards template, it becomes more than easy to apply that look & feel to your table data. First, as always, the SQL query ... select ename card_title, apex_string.get_initials(ename) card_initials, job card_text, hiredate card_subtext, 'f?p=...' card_link from emp ... and then the result: The Cards template offers a few Template Options an. With these, we can have the cards display either initials or icons - we can have colored cards, control card sizes and how much cards to display in a row. After changing some of the Template Options, the report looks like this: As this brief overview on the alternative Classic Report templates illustrates - it's so easy to visualize data in a different look & feel.  HTML or CSS knowledge is not required; the only thing a developer has to do, is to adjust the SQL query and the Template Options. Try it out - today! Back to blogs.oracle.com/apex      

As an APEX developer, thinking about reports, the following picture comes to mind ... the typical, tabular layout of data. But Classic Reports can do more - these are, for sure, one of the most...

Application Express

Application Express Early Adopter: More than one REST Service!

In the first blog posting about REST Services in Application Express 5.2 Early Adopter you learned, how an external REST Service is being registered as a Web Source Module in Shared Components and then being used as the data source for a Classic Report. In this posting, another REST Service is being registered, we'll add another report to the application page and finally link both reports.   The second REST Service will also be an Github API endpoint: https://api.github.com/repos/oracle/docker-images/commits lists the commits which have been done to the specified repository.  Thus, navigate to the Shared Components of your application which has been created in the first blog posting. In Web Sources Modules, click on the Create button and choose Create from Scratch" - just as done in the first posting. Keep Simple HTTP as the type and GET as the HTTP method and provide a name ("Github Commits").  For the endpoint URL, we'll use Web Source Parameters right from the beginning this time. Use https://api.github.com/repos/:username/:repo/commits - Application Express will detect the URL Parameters and prompt for default values to be used during Web Servicer discovery. Choose oracle for the :username and docker-images for the :repo parameter. When finished, click Next.   Application Express checks whether an existing Remote Server object matches the given endpoint URL. Since as already have a remote server object for Github URL endpoints, we'll reuse that here. Click Next to get to the Authentication dialog. As the repository list, the commits list also does not require Authentication. So you might click the Discover button to start web source discovery. Application Express will invoke the URL endpoint, grab the JSON response and examine it to determine columns and data types. Once you see the list of columns and the sampled data, click the Create Web Source button. Once the Web Source has been created, click the icon in order to review its details. On the details page, open the Data Profile tab. The JSON response contains about 50 attributes - most of them will never be needed in the APEX application. So we might want to adjust the attributes, column names and their visibility. Click on the Edit Data Profile button to see all details on about how Application Express parses the JSON response and extracts rows and columns. The Columns tab within the Data Profile section lists all columns extracted from the JSON response. The report shows the column name, its data type and the JSON path expression (tells APEX where the value for that column is being found in the JSON response). This column list can be changed: Columns might be removed or even added in cases where Application Express did not detect them properly. First, we will not need all the 53 columns in the APEX applications - we also do not want to remove them (we might need them later on). So Application Express allows to hide the columns: Hidden columns will not be offered in the Create Page Wizards and not automatically added by Page Designer. However, when a component uses a hidden column, it still works. Thus we want to hide most of the columns, except NAME_2, EMAIL_2, LOGIN_2, AVATAR_URL_2, DATE_2 (we'll change these names as well), MESSAGE, COMMENT_COUNT and VERIFIED. Check the columns to be hidden on the very left side of the grid, open the context menu in the heading section (as illustrated below) and click Hide Selected Columns. The Visible column of the selected rows will change to No. Then click Apply Changes to make this persistent. Revisit the Columns tab and choose to only display the visible columns - as follows: Then change the column names (can be done in the grid directly) as follows: NAME_2 to COMMITTER_NAME EMAIL_2 to COMMITTER_EMAIL DATE_2 to COMMIT_DATE LOGIN_2 to COMMITTER_LOGIN AVATAR_URL_2 to COMMITTER_AVATAR_URL The list of visible columns should then look as follows: Finally save everything and navigate to the application page containing the Classic Report on Github Repositories from the first blog posting. Drag another classic report next to the existing one. That report will show the list of commits to a chosen repository. Name the region Commits and in the Data Source section, have it using the Github Commits Web Source Module. You will see the non-hidden columns automatically being added by Page Designer. Hidden Columns are not added.  In the above screen shot, Page Designer also shows the two Web Source Parameters: username and repo. For username, we already have a page item as a text field. For repo (repository name), add another (hidden) page item to the above Static Content region. Name it P2_REPO.  Then navigate to the Parameter node of the Classic Report and assign the P2_USERNAME text field item to the username parameter and the P2_REPO hidden item to the repo parameter.         Item values must be submitted in AJAX requests (report pagination and sorting). So add the items to the region's Items to Submit attribute. Let's then link the two reports together. Navigate to the NAME column of the first report (showing the repository list). Change it to become a link column. In the link attributes, link to the same page (here: page 2) and assign the value of the NAME column (#NAME#) to the P2_REPO item. Then let's format the second report as well: The committer's "Avatar URL" (again) is a link to a hosted image. It's best to just show it with an <img> tag. Configure that within the HTML Expression column attribute. Also make the second report conditional; when P2_REPO is null, it should not be shown. Finally run your page. First it will show the repository list of the given user. Clicking on a repository name shows the list of commits. You might sort it by commit date descending. You now have built a master-detail relationship with two Classic Reports on top of external REST services. It very similar to building such a page based on relational tables. Of course, this can be further optimized; When somebody clicks the repository name to see the commits, we could refresh the second report using dynamic actions instead of reloading the page. That will work similar to a page working on tables and views - there is no REST speciality any more. Just play with this and try some variations out.                              

In the first blog posting about REST Services in Application Express 5.2 Early Adopter you learned, how an external REST Service is being registered as a Web Source Module in Shared Components...

Application Express

Application Express Early Adopter: REST Services!

Since December 2017, Application Express 5.2 Early Adopter 1 is available. One of the new features in APEX 5.2 will be declarative support for REST Services in Application Express components - that means, developers will be able to create a component, like an interactive report, directly on an external REST service. This tutorial walks you through that new feature, by creating some APEX pages reporting on the GitHub REST API. You can try these out on the Application Express Early Adopter instance. Github provides a very comprehensive REST API in order to interact with the platform. Several API endpoints provide information about Github users, repositories, contributions and many more. The following screenshot is an example for this API: https://api.github.com/users/oracle/repos returns the list of repositories owned by the oracle organization. As with most REST APIs, the data comes as a JSON feed. The goal is to have an Application Express page presenting that data - as follows: Create REST service meta data The following steps explain how to achieve this. To begin, create your workspace on apexea.oracle.com (if not already done) and log in. Then create a simple application with just one home page (accept the defaults in the new Create Application wizard). One the application has been created, navigate to Shared Components and look for Web Source Modules into the lower left corner. The Web Source Modules section is new in Application Express 5.2; all meta data for external REST services is being configured there. Within Web Source Modules click on the Create button in order to begin working with the Github REST API. Since we are at the very beginning, create your first Web Source Module from Scratch. In the next dialog a name and the URL endpoint of the REST Service is needed. Use the following data, then click the Next button. Web Source Type: Simple HTTP (keep default) Name: Github Repositories URL Endpoint: https://api.github.com/users/oracle/repos HTTP Method: GET (keep default) The next step will split up the endpoint URL into a server-specific and a service-specific part. With the server-specific part, Application Express will create a new Remote Server object. That additional entity allows to group REST endpoints pointing to the same server. When, for such a collection of REST services, the server changes, it will be sufficient just to adjust the Remote Server object. APEX does a proposal how to split the URL - however, for Github, we want to adjust this:  Base URL: http://api.github.com/ Service URL Path: users/oracle/repos The next wizard step is about Authentication. The Github API does not require authentication, so simply click the Discover button in order to have Application Express invoke the REST endpoint.   Application Express will now invoke the REST endpoint, grab the response data and examine the data structures. The following wizard step shows the results of that discovery. Application Express displays the column it has found (and their data types) and a preview on the parsed response data.  The More Detail button opens additional tabs, showing HTTP response headers and the raw JSON response. When satisfied, click the Create Web Source button, in order to store the discovered meta data into Application Express. You will see the Web Source Modules section containing one new entry for the Github Repositories REST endpoint. Create a Classic Report Next, we are about to use the new Web Source Module within an APEX component. Navigate to the application pages and click the Create Page button in order to add a new page. Choose a Report page and use the Classic Report implementation (Application Express 5.2 will support Classic and Interactive Reports based on Web Source Modules). In the following steps, provide a name for your new page and choose whether to add an entry to the navigation bar on the left hand side of your application. The interesting part will be the last step of the wizard, where the data source for the report will be configured. Starting with 5.2, Application Express exposes the new Data Source attribute. Local Database creates a report on top of data in the local database (as for all the releases before). REST Enabled SQL is discussed in a separate blog posting. Web Source Modules allows to reference the REST service meta data, we just have created within Shared Components. Choose Web Source and pick Github Repositories in the second Web Source Module select list. Then the Columns shuttle will populate with about 90 columns (the Github REST API returns a lot of attributes). Choose an interesting selection; make sure to add at least, but not only the ID, NAME, CREATED_AT, UPDATED_AT and AVATAR_URL columns to your report. When finished, click Create. In Page Designer, you'll see your new report as part of the page. Run the page to see your first result. Time to adjust the layout of this report a bit. The AVATAR_URL is pointing to an avatar image of the repository owner. So, in Page Designer, navigate to that column and simple apply the <img> tag using the HTML Expression attribute. Provide some nice format masks for the CREATED_AT and UPDATED_AT columns, adjust the column order to your needs and hide columns you don't want to see. After some of these adjustments, your page might look as follows: Make it Dynamic! So far we have, very easily, created an APEX page reporting on an external REST service. But the oracle user is actually hard-coded in our Web Source Module. Now, we want to change that: The report should be able to report on any Github user we want. For that, we first need to add a Web Source Parameter to the meta data in Shared Components. Thus navigate to Web Source Modules within Shared Components and then to the details of the Github Repositories module. Navigate to the Module Parameters tab ... and click the Add Parameter button. The Github API requires the user name to be a part of the URL. So, the user/oracle/repos path lists the repositories of the "oracle" user, users/dani3lsun/repos would list the repositories of one of the very active APEX community members. So add the new parameter as follows: Type: URL Pattern variable Name: username  Value: oracle (this will act as the default value) Direction: In (keep the default)   Click on Add Parameter, when finished. Next, we have to change the URL endpoint of the Web Source Module in order to pick up the parameter. Click on the Web Source Module tab (this contains general information). Then change the URL Path Prefix of the Web Source Module from users/oracle/repos to users/:username/repos. Finally, click the Apply Changes button. Then, head back to your page containing the report. We want to have a text field on the page, where the end user enters a Github user name. Then the report is supposed to refresh with the data for that very user. Thus, in Page Designer, add a new region above the report, add a page item (Text Field) and configure its layout. Then review the Classic Report node in the tree on the left again. It now has an additional Parameters child node; there you'll find the username parameter you have configured. It still uses the default value configured in Shared Components. Change it to use the text field Item.   Add the item to the report's Items To Submit region attribute as well. Finally save everything and run the page. With the oracle user name, everything looks still as before ...  When changing it to dani3lsun, the report will show another list of repositories. That's it for now. Play a bit around with these features and settings. In the next posting, we'll create another component on another REST service and link the two together - as we did all the years before with normal tables.  And don't forget to provide your valuable feedback.              

Since December 2017, Application Express 5.2 Early Adopter 1 is available. One of the new features in APEX 5.2 will be declarative support for REST Services in Application Express components - that...

Application Express

Application Express Early Adopter: REST Enabled SQL!

One of the new features of Application Express 5.2 is the ability to execute SQL not only in the "local" Oracle database (where APEX runs in), but also on a remote Oracle instance. This is based on the new REST Enabled SQL feature introduced with ORDS 17.4. When the REST Enabled SQL feature is active on an ORDS instance, SQL statements can be passed in JSON format using HTTP POST requests. Execution results are being passed back as a self-describing JSON response. Clients thus can communicate with the Oracle database using open protocols and without SQL*Net.  A prerequisite for using REST Enabled SQL is to have at least ORDS 17.4 installed. Older ORDS versions (3.0.x) do not support REST Enabled SQL. The Application Express 5.2 Early Adopter Instance meets all requirements for REST enabled SQL. You can try out this new feature and its integration into Application Express using just your schema (or the schema of a second workspace). Set up First, we need to set up the schema and the workspace in order to use REST Enabled SQL. We'll authenticate with the REST Enabled SQL service using the database username and password (not the APEX workspace password). Navigate to SQL Workshop - SQL Commands. You can see your schema name in the upper right corner. If you do not know your schema password, execute an ALTER USER {username} IDENTIFIED BY {password} in order to set the schema password to a known value. Then we need to enable the REST Enabled SQL service for the given Schema. Run the following PL/SQL block. begin ords.enable_schema; end; After that, the schema is all set. Navigate back to Application Builder in order to create an application using the REST Enabled SQL feature. Create a REST Enabled SQL Reference In Application Builder, first create a new application without much content - in the create application wizard just accept all defaults. When the application has been created, navigate to Shared Components. In Shared Components, look up REST Enabled SQL in the lower left corner. Right now, no references exist. Click the Create button to add a new reference. Create the new REST Enabled SQL reference from scratch. Provide a name for the new reference and use the following URL scheme: http://apexea.oracle.com/pls/apex/{your-schema} On the next page, provide authentication credentials. Use the database schema name and the password you have set in SQL Workshop. Choose Basic Authentication and ignore the warning message. For the Early Adopter instance, Basic Authentication is OK to use. Click the Create ORDS Remote Server to finish. Application Express will test the REST Enabled SQL service. If everything has been done right (correct URL, schema name and password) you should see a message as follows. Click the Close button to complete the process. You should see your new REST Enabled SQL reference. Instead of using your own schema, you can also use a schema the development team has prepared for you. Use the following details when creating the REST Enabled SQL reference. Endpoint UTL: https://apexea.oracle.com/pls/apex/scott_connect Authentication Type: Basic Username: SCOTT_CONNECT (upper case) Password: tiger The SCOTT_CONNECT schema does not own any tables (so you cannot accidentally drop them), but it has SELECT privileges on tables in the SCOTT_DATA schema. When, later on, creating components on objects in that schema, make sure to pick up the SCOTT_DATA schema in order to see tables. Use REST Enabled SQL Next we'll start using the REST Enabled SQL reference for an APEX component. Navigate to the application pages overview and click the Create Page button.  Provide a Name for the new page  ... Provide input to the next wizards steps as needed. On the last wizard step, things have changed in Application Express 5.2: Whereas earlier versions just allowed to choose between a table or a SQL query, we now can choose REST Enabled SQL as the components' Data Source ... The report can be based on a SQL Query as well ... Finally click Create. The report page will be created and you will be redirected to Page Designer, where you can review the settings. Note that the Source section within the property editor on the right also allows to set the data source and to choose a REST Enabled SQL service. Finally run your page. The report results should look rather normal - just an interactive report based on the EMP table ... ... which we can apply filters on ... Enable debug mode in order to get some insight about what is happening during execution. Application Express generates a SQL query, as usual - but instead of simply executing it, the query is being posted to the REST Enabled SQL service as an HTTP POST request. In Application Express Early Adopter 1, REST Enabled SQL can be used for Interactive Reports, Classic Reports, the CSS Calendar, Oracle JET Charts and for "Execute PL/SQL" page processes. Try it out in your Early Adopter workspace now.              

One of the new features of Application Express 5.2 is the ability to execute SQL not only in the "local" Oracle database (where APEX runs in), but also on a remote Oracle instance. This is based on...

How to fully leverage the Rich Text Editor

The Rich Text Editor item in Application Express allows to edit formatted text - unlike the simple textarea item which just allows to work with plain ASCII text. The rich text editor generates the formatted text as HTML code and sends that to Application Express on page submit. Most applications then simply store that HTML text into a database table. The rich text editor is based on the Javascript CKEditor library. Page Designer does not offer too much configuration options, but the CKEditor library offers a huge variety of configuration options. And Application Express 5.1 makes such advanced configurations very easy.   In Page Designer, navigate to the settings of your Rich Text editor item and look up the Advanced section. The attribute Initialization Javascript Code is your entry to leverage the configuration options of CKEditor. A typical example for such a Javascript initialization function is as follows. function ( configObject ) { configObject.{attribute-name-1} = {attribute-value-1}; configObject.{attribute-name-2} = {attribute-value-2}; : return configObject; }   Application Express will call your function just before the editor is being initialized, passing in a Javascript object with the default configuration. In your code, that object is being changed and finally returned to Application Express. The Rich Text editor will then actually be initialized with the changed configuration object. To get an impression what this configuration object can do, have a look into CKEditor's API documentation. Let's start with a few simple tests. The following Javascript function will set the editor's base color to green and we'll disable the resizing capability, so end users won't be able to resize the editor any more. After saving the function, reload the page to see the effects. function ( configObject ) { configObject.uiColor = "#AADC6E"; configObject.resize_enabled = false; return configObject; } CKEditor offers many tweaks to control, how the actual HTML code will be generated from user inputs. For instance, hitting the Return key by default produces a new paragraph with the <p> HTML Tag. We can change that - now we want to have the editor produce simple line feeds using the <br> HTML Tag. function ( configObject ) { configObject.enterMode = 2; configObject.uiColor = "#AADC6E"; configObject.resize_enabled = false; return configObject; } If you don't like the toolbar options which are provided by Application Express, simply configure your own set of options. function ( configObject ) { configObject.enterMode = 2; configObject.uiColor = "#AADC6E"; configObject.resize_enabled = false; configObject.toolbar = [ ['Link','Unlink','Anchor'], ['Image','Table','HorizontalRule','Smiley','SpecialChar'], '/', ['Bold','Italic','Underline','Strike','-','Subscript','Superscript','-', 'RemoveFormat'], ['NumberedList','BulletedList','-','Outdent','Indent','Blockquote'] ]; return configObject; } In CKEditor's API documentation you'll find much more attributes and explanations how these work. It's advisable to invest some time into this - at the end of the day you'll be able to get a Rich Text editor as your application requires it. All functionality can be leveraged. But there is much more than this: when combining this elegantly with other APEX pages, dynamic actions or REST services, we can extend the editor with things like an image upload capability, we can make is responsive and much more. To learn about this, read the full community article here.      

The Rich Text Editor item in Application Express allows to edit formatted text - unlike the simple textarea item which just allows to work with plain ASCII text. The rich text editor generates the...

DATE, TIMESTAMP and Format Masks ...

Developers are working with DATE or TIMESTAMP data types more or less all the time; they're being used in allmost all APEX applications. This posting contains some information about the differences between DATE and TIMESTAMP and about format masks and how they work in Application Express. DATE or TIMESTAMP? Actually, there is not one, not two but four data types for datetime values in the Oracle database: DATE is the oldest and most widely used data type. Though the name is "DATE", it stores also information about the time. Internally, DATE stores year, month, day, hour, minute and second as explicit values. To get the current timestamp as an instance of DATE, use the SYSDATE SQL function. SQL> alter session set nls_date_format='YYYY-MM-DD HH24:MI:SS'; Session altered. SQL> select sysdate, dump(sysdate) as date_bytes from dual; SYSDATE DATE_BYTES -------------------- ----------------------------------- 2017-11-23 23:41:08 Typ=13 Len=8: 225,7,11,23,23,41,8,0 TIMESTAMP extends DATE by fractional seconds. Internally, time zone information is also contained, but in order to work with time zones, one of the other two data types, TIMESTAMP WITH TIME ZONE or TIMESTAMP WITH LOCAL TIME ZONE must be used. To get the current timestamp as a TIMESTAMP instance, use the LOCALTIMESTAMP SQL function. As the DUMP result below shows, TIMESTAMP stores year, month, day, hour, minute and second similar to DATE. The other bytes are there for fractional seconds and time zone information. TIMESTAMP instances consume more space on disk than DATE instances, which is natural - they contain more information. SQL> alter session set nls_timestamp_format='YYYY-MM-DD HH24:MI:SS.FF6'; Session altered. SQL> select localtimestamp, dump(localtimestamp) ts_bytes from dual; LOCALTIMESTAMP TS_BYTES --------------------------- --------------------------------------------------------------------- 2017-11-24 08:45:41.434175 Typ=187 Len=20: 225,7,11,24,8,45,41,0,24,252,224,25,1,0,3,0,127,1,0,0 TIMESTAMP WITH TIME ZONE allows to explicitly work with time zone information. A time zone can be used to create a TIMESTAMP WITH TIME ZONE instance and it is explicitly contained in the output (use the right format mask). When the output of a TIMESTAMP WITH TIME ZONE value does not contain a time zone, you have incomplete data. Oracle does not implicitly convert instances of this data type between time zones; this can be done explicitly with the AT TIME ZONE clause. To get the current time as a TIMESTAMP WITH TIME ZONE instance, we can use two functions: SYSTIMESTAMP returns the current time in the database time zone and CURRENT_TIMESTAMP returns it in the current session time zone. The following example uses SYSTIMESTAMP. SQL> alter session set nls_timestamp_tz_format='YYYY-MM-DD HH24:MI:SS.FF6 TZR'; Session altered. SQL> select systimestamp as ts_bytes from dual; SYSTIMESTAMP ---------------------------------- 2017-11-23 23:57:04.609608 -08:00 TIMESTAMP WITH LOCAL TIME ZONE does not expose the time zone information (to the application, it looks like a TIMESTAMP), but it uses tome zones implicitly: An instance is created without time zone information, the session time zone is assumed. Stored instances are normalized to the database time zone. When the data is retrieved, users see it (again) in the session time zone.  The following example illustrates how TIMESTAMP WITH LOCAL TIME ZONE works: First, a table is created. Then the current time is retrieved and stored into the table ( SYSTIMESTAMP returns TIMESTAMP WITH TIME ZONE, but this is automatically converted to TIMESTAMP WITH LOCAL TIME ZONE ). When the table is then being selected, the returned data changes with the session time zone. SQL> create table mytimestamps( ts timestamp with local time zone ); Table created. SQL> insert into mytimestamps values ( systimestamp ); 1 row created. SQL> alter session set time_zone='Europe/Berlin'; Session altered. SQL> select * from mytimestamps; TS --------------------------------------------------------------------------- 2017-11-24 12:55:39.761283 SQL> alter session set time_zone='EST'; Session altered. SQL> select * from mytimestamps; TS --------------------------------------------------------------------------- 2017-11-24 06:55:39.761283 Format Masks Whether we work within an application or a tool like SQL Plus or SQL Developer; whenever we output a DATE or TIMESTAMP instance, we're converting it to VARCHAR2. We can do this explicitly using TO_CHAR or Oracle will do it implcitly. And now NLS format masks kick in - the format mask decides how a DATE is converted to a VARCHAR2 and back. The are defaults for NLS format masks at the database level, we can set them at the session level or we can pass a format mask to the individual TO_CHAR call. Continue reading ... 

Developers are working with DATE or TIMESTAMP data types more or less all the time; they're being used in allmost all APEX applications. This posting contains some information about the differences...

I Need Help with Oracle APEX - Where Can I Get It?

The more you work with Oracle Application Express (APEX), the more likely it is that you'll have a question how to do something with it.  We field questions from internal and external customers, as well as Oracle Support, every day.  A lot of them.  These typically aren't basic questions, but instead, they focus on some specific customization or specialized area of functionality.  Sometimes, they're bugs in APEX. Sometimes, they're functional questions which are covered in the APEX documentation, but not always so lucidly.  And other times, they are simple how-to questions (e.g., how do I add workflow to my application?  how do I integrate with Oracle eBusiness Suite?  how scalable is APEX?, etc.). While we always enjoy talking with customers and doing our best to make them successful, it's impossible for us to be able to help all customers globally on a 24x7 basis.  Like you, we're very busy on a daily basis.  But this is where the global Oracle APEX community really shines.  There are many users of Oracle APEX around the world who are always willing to unselfishly and graciously help others.  We see this every day.  But how do you engage with the vast Oracle APEX Community?  I've listed a number of resources below. apex.world - If you're into Oracle APEX and not on apex.world, then something is wrong.  apex.world is the community-authored and community-hosted site for everything APEX.  It combines news, a Twitter feed, jobs, plug-ins, and a number of discussion groups through Slack.  About the only downside is that there isn't a searchable archive of all past questions and answers on Slack, but it's a very easy way to engage with Oracle APEX developers from around the world.   Oracle Technology Network - Oracle provides a number of discussion forums on the Oracle Technology Network, and there is a dedicated one for Oracle APEX.  The Oracle APEX discussion forum has been active for more than 15 years.  If you have a question about Oracle APEX, chances are very high that your question has been asked and answered on this discussion forum.  You should consider searching the forum before posting a new question.  However, if your question is new, you'll find many luminaries from the Oracle APEX community who are willing to share their time and expertise.   Stack Overflow - Stack Overflow has long been a discussion site for the software development community, and in the past couple of years, there has been a surge in the number of Oracle APEX-related questions asked and answered there.  You can either search for "oracle apex" or "oracle application express", or simply do a tag search for oracle-apex.   Twitter - Many APEX developers from around the world sign on to Twitter on a daily basis.  While this isn't the best forum to ask detailed questions (as you're limited to 140 characters), it's a great way to see what's going on in the Oracle APEX community.  And I personally have had numerous video calls with potential customers from around the globe, all simply initiated from a simple question posed on Twitter.  If you use the hash tag #orclapex, it will surely be noticed by the Oracle APEX community.  And you can easily search for the #orclapex hashtag on Twitter too.  If you decide to sign up for Twitter, be sure to follow account @oracleapex.   LinkedIn - Although not as active in terms of technical question and answers, LinkedIn is a great place to get connected with other members of the Oracle APEX community and see who is doing what.  I highly recommend Oracle Application Express - The Official Group on LinkedIn.   Facebook - Also not as active in terms of technical questions and answers, Facebook is still an excellent place to see all of the Oracle APEX enthusiasts from around the world.  There are over 10,000 followers of Oracle APEX on Facebook.   Google+ - Oracle APEX fan Tony Fatouros has been slowly building an APEX community on Google+, it now has over 1,000 members and discussions can also be initiated there too.   Oracle Support - And let's not forget - if you are a customer paying for support from Oracle, you can also initiate requests via My Oracle Support.  The Support analysts typically may not be able to help you with "how do I write this in APEX?" type of questions, but they have developed a large knowledge base of issues, work arounds, and other commonly encountered support issues.   I'm sure there are other forums for help which aren't listed here, so please feel free to include them in the comments.  For anyone new to Oracle APEX, you should understand that there is a vast and helpful community of developers and enthusiasts who want to see you become equally successful.  And now you know where to start.

The more you work with Oracle Application Express (APEX), the more likely it is that you'll have a question how to do something with it.  We field questions from internal and external customers, as...

Application Express

Page items and Javascript: More than $s and $v ...

Today, most APEX applications do contain more or less Javascript code. Javascript is particularly useful to make forms more interactive and therefore more user-friendly. Most developers know and already have used the $s, $v and $x functions to work with form items in Javascript. $s sets an item value, $v retrieves it and $x determines whether an item with the given name exists on the current page. These functions are available in Application Express for a long time. And the names are so nicely short and quickly typed. But actually this is just shortcut syntax for the functions within the "apex.item" Javascript namespace. And within apex.item, there are more functions than you might think. These functions are perfectly suited to bring functionality to your Application Express forms: Do all the things which Dynamic Actions provide, within your Javascript code. Activate and deactivate, show and hide items. Access or change item values programmatically, regardless whether this is a plain text field, a date picker or a select list. Even determine whether a form item value has been changed and decide whether or not to perform an AJAX request to the server.  Our latest article takes a closer look on the functions with the apex.item namespace. Have a read!

Today, most APEX applications do contain more or less Javascript code. Javascript is particularly useful to make forms more interactive and therefore more user-friendly. Most developers know and...

Oracle Announces Oracle Application Express Curriculum

The curriculum for Oracle Application Express (APEX) 5.1 is now generally available.  Additionally, the Oracle Application Express curriculum is now available with a permissive-use license, namely Creative Commons Attribution 4.0 International Public License and Universal Permissive License (UPL) Version 1.0. The Application Express courseware, which has been in development for the past year, includes 16 distinct lessons, complete with PowerPoint presentations, hands-on-labs in PDF format and all necessary SQL scripts and application export files.  Additionally, the source files of the hands-on-labs are provided in Microsoft Word format, suitable for translation or excerpting. The Oracle APEX community has come together in the past to provide crowd-sourced translations for the runtime user interface of Oracle Application Express (see translate-apex.com).  Oracle intends to work directly with the owners of translate-apex.com (Pretius sp. z o.o. sk) to organize and provide the crowd-sourced translations of the Oracle Application Express curriculum, making native-language Oracle APEX educational materials freely available to all, globally. If you are an educator at an accredited institution, you are also able to access this same curriculum for free from Oracle Academy, as part of the Oracle Application Express – Application Development Foundations.  With Oracle Academy, you gain access to a hosted work environment for your students (no software to install), you can track your students progress, and there are specialized learning paths and access to other curriculum, including Database Foundations, Database Design and Programming with SQL and Programming with PL/SQL.  For more information, visit academy.oracle.com. The Application Express curriculum will be kept up to date with future major releases of Oracle Application Express, with the goal of releasing the updated curriculum on the same day as the product release.  Additionally, it is Oracle's future goal to share these resources with the Application Express community and maintain them in a github repository, with the same permissive use license. If you wish to download the Oracle Application Express curriculum, you can access it directly at apex.oracle.com/education. Additional Information Oracle Application Express is a high productivity platform for creating declarative, low code Web applications on the Oracle Database and in the Oracle Cloud.  To learn more about Oracle Application Express, visit apex.oracle.com.  To learn more about Oracle Database Cloud, visit cloud.oracle.com/database.  To access the Oracle Application Express curriculum, visit apex.oracle.com/education.

The curriculum for Oracle Application Express (APEX) 5.1 is now generally available.  Additionally, the Oracle Application Express curriculum is now available with a permissive-use license,...

Application Express

REST and Application Express: Filtering

More and more Application Express developers are faced with the requirement to integrate REST services or HTTP/JSON data feeds into their applications. Application Express provides great support for SOAP web services, but for REST services using JSON to exchange data, the built-in functionality is limited. For instance, all the JSON parsing has to be done manually by employing PL/SQL code or SQL functions. During the last weeks, we released a few articles describing how to access a REST service from within Application Express, how to leverage the REST Client Assistant packaged application and how to deal with REST services providing large result sets ... Generate SQL and PL/SQL code to access REST services using REST Client Assistant Work with REST services returning large result sets page-wise Configure a database wallet to access HTTPS services In the previous articles, we always fetched all response data from the REST service - either all at once or page-wise, depending on REST service capabilities. But in practice, it's often required to get only a subset of the response data - we want to filter the response based on some end user criteria. "So that's easy" might be your first thought - since all JSON parsing is done with SQL functions. In the SQL query, both XMLTABLE and JSON_TABLE act as a row source, so its result can be treated like rows from a table or view. Thus we can simply amend the WHERE clause of the SQL query, which has been generated be the REST Client Assistant packaged application.  But imagine a REST service returning a huge amount of data (thousands or even millions of rows). As learned in the Work with REST services returning large result sets page-wise article, these services return their data page-wise. So, in order to apply a SQL filter, we would first have to fetch all data, walking though all the response pages.  For REST services provided by Oracle REST Data Services (ORDS), a filter syntax is provided: Filter criteria can be passed to ORDS with the REST request and will be executed server-side returning only the final (and limited) amount of rows. This article shows how the response of a  REST service invocation can be filtered either using SQL or a filtering syntax provided by the REST service. Have a read, how this can be used in your own Application Express applications.  

More and more Application Express developers are faced with the requirement to integrate REST services or HTTP/JSON data feeds into their applications. Application Express provides great support...

Application Express

APEX, HTTPS, certificates and the Oracle Wallet

Today we'll talk about performing HTTPS requests with the APEX_WEB_SERVICE or UTL_HTTP packages in Application Express. At the first glance, this is pretty simple - instead of an URL beginning with http://, we use one beginning with https://. As an example, we'll try out the HTTPS URL of the USGS (U.S. Geological Survey), which is also being used in the article about using REST services in Application Express. The following code example tries to fetch a JSON feed from earthquake.usgs.gov using HTTPS. select apex_web_service.make_rest_request( p_url => 'https://earthquake.usgs.gov/earthquakes/feed/v1.0/summary/2.5_day.geojson', p_http_method => 'GET' ) from dual; In most cases, the first attempt fails: ORA-29273: HTTP request failed ORA-29024: Certificate validation failure ORA-06512: at "SYS.UTL_HTTP", line 380 Blogs and discussion forums are full of questions and answers how to deal with this situation. This article provides an explanation and explains how to solve the problem for PL/SQL as well as for Application Express: The error message basically states that the Oracle database was not able to validate the SSL certificate, which the external web server sent in order to prove its identity. To get certificate validation working, we need to create an Oracle Wallet and configure it in Application Express. But before doing that, here is a simple explanation about HTTPS and SSL certificates - "in a nutshell" (these explanations are far away from being complete, but they should be sufficient to understand what the Oracle Database is doing and what the Oracle Wallet is needed for):  When an HTTPS request to a web server is done, the first step is the SSL handshake, in which client and server negotiate about the details of the SSL encryption. As part of this handshake, the server sends its SSL certificate, which is similar to an ID card for humans: The certificate is there to prove, that the responding server is really the one, which the client expects. The obvious question is now, whether the "ID card" is authentic - and this authenticity is certified by a Certificate Authority (CA). The web servers' SSL certificate is "signed" by the certificate authority using - again - a certificate. Now we can ask, whether the CA's certificate is authentic ... so it might be signed by another CA - and we have another certificate in the game. It's obvious that, at some point, this chain must come to an end. The client has to trust one CA certificate without looking into who has signed it - such a trusted certificate is then the end of the chain. In a browser like Firefox, Chrome or others, all common CA certificates are pre-installed and this list of certificates is also being updated with each browser update. The Oracle Database maintains such certificates in an Oracle Wallet - but this wallet has to be created; and right after creation it is empty. We have to add the CA certificates we need. This article explains how to create the wallet, how to load the required CA certificates into it and finally how to enable it for all Application Express workspaces and applications (read the full article).

Today we'll talk about performing HTTPS requests with the APEX_WEB_SERVICE or UTL_HTTP packages in Application Express. At the first glance, this is pretty simple - instead of an URL beginning with htt...

Application Express

A Simple Guide to Oracle Application Express (APEX)

If you want to learn more about Oracle Application Express (APEX), you've come to the right place! What is Oracle Application Express? Oracle Application Express is the native low code framework of the Oracle Database.  Using Application Express, you can easily create modern, responsive, scalable Web applications with minimal effort.  With no additional coding, Web apps created with APEX look great on the desktop, tablet or smartphone. Application Express is a supported feature of the Oracle Database and is included, at no additional cost, with every Oracle Database, both on-premises and in the cloud.  There are no additional licensing costs based on the number of developers, applications or end-users.  Application Express is also included with every Oracle Database Cloud Service, from the low-priced Oracle Database Exadata Express Cloud Service all the way up to the Oracle Database Exadata Cloud Service.   Why Low Code? The benefits of low code development are greater productivity in less time at less cost and with greater consistency.  Low code frameworks, such as Application Express, leverage powerful components that provide reporting, data visualization, and form controls. This enables an application developer to focus on solving the business problem and delivering superior solutions, with less time and effort spent on mundane and repetitive lower-level coding typically associated with application development. Who is using APEX? Application Express is used by large and small customers alike, across a broad number of industries, and is used to solve a vast array of business problems - from simple spreadsheet replacement to multi-thousand user banking and insurance applications.  Customers have also found APEX to be an easy way to extend the Oracle eBusiness Suite in a certified and supported fashion. Many customers have provided quotes about their use of APEX, and there are numerous customers success stories about APEX, covering both on-premises Oracle Database customers as well as customers on the Oracle Database Cloud. At Oracle, APEX also powers the Oracle Store (available in 17 languages) and the Oracle Learning Library. Anything relevant in APEX for business users? Bundled with Application Express is a suite of 35 productivity and sample applications, installed in minutes and ready to use out-of-the box.  These apps are fully supported by Oracle.  As easy as installing an app on your smartphone, you can install a productivity application to help solve many common business problems, from Group Calendar to P-Track (for project management) to Customer Tracker to Survey Builder. What's new in the latest release of APEX? At the time of this writing, the latest release of Oracle Application Express is 5.1.1.  This release introduced a number of compelling features, including: Interactive Grid - An all-new component which enables your end users to easily manage and report on grids of data.  Interactive grid includes numerous features for powerful reporting, including fixed headers, frozen columns, scroll pagination, multiple filters, sorting, aggregates, computations, and more.  Interactive grids also support the easy creation of master-detail relationships. Oracle JET Charts - The primary data visualizations in APEX are now provided by Oracle JET, the recently released Javascript Extension Toolkit from Oracle.  With only a little bit of SQL knowledge, you can create fantastic-looking charts and graphs.  See for your yourself! Universal Theme - The enhanced Universal Theme provides effortless UI design and customization without writing a single line of JavaScript or CSS.  From the 1100+ icons included with the Font APEX library to Live Template Options to easy, declarative bi-directional support, APEX 5.1 provides all the necessary tools and components to create fabulous-looking, fully responsive UI with little to no coding. Where to get started with APEX? You can find extensive content about Application Express from the APEX Learn More site.  There are also step-by-step articles published in Oracle Magazine, including Quick Iterative Development, See Better Results, and Easy Master/Detail. Wherever there is an Oracle Database, you can try Oracle Application Express.  If you have access to a trial of Oracle Database Cloud, or free credit to the Oracle Cloud, you can begin kicking the tires of APEX today.  There is also a free evaluation instance of Oracle Application Express at https://apex.oracle.com.  Sign up today, and within a couple minutes, you can start to learn what has led thousands of other users to flourish with the Oracle Database and Oracle Application Express!  

If you want to learn more about Oracle Application Express (APEX), you've come to the right place! What is Oracle Application Express? Oracle Application Express is the native low code framework of the...

REST and APEX 5.1: Pagination

More and more Application Express developers are faced with the requirement to integrate REST services or HTTP/JSON data feeds into their applications. Application Express provides great support for SOAP web services, but for REST services using JSON to exchange data, the built-in functionality is limited. For instance, all the JSON parsing has to be done manually by employing PL/SQL code or SQL functions. Oracle Application Express contains the REST Client Assistant, packaged application which is there to help the developer in integrating REST services into their applications.   In the first part of the series (REST Services and Application Express 5.1 - Part 1) REST Client Assistant was introduced and it was shown how to use it to generate SQL und PL/SQL code and to integrate an external REST service into an APEX application and to display its data in an APEX report. Today we're taking the next step: Typical REST services return their data not all at once (which would be problematic for larger data sets), but in pages.  A REST service returning its data in pages can be created within only minutes: All you need is the Oracle Database (as an APEX developer this should not really be an issue) and Oracle REST Data Services (ORDS). With only two PL/SQL calls you create a REST interface for any table in your database schema. You can test this REST service using a browser, one of the many publicly available REST Client applications or you can explore it using the REST Client Assistant packaged application. Our latest article shows not only how to access that REST service from an APEX application, but also how to implement pagination for the REST service - within your APEX application. Click a button on your APEX page, and have the application request the next page of REST data. Use this to walk through the complete result data of your REST service - back and forth. Finally you'll learn how REST Client Assistant can generate a table function for you which grabs the complete result set from the REST service - the function walks through all the pages automatically returning data from the REST service like rows from a local table. All code is generated for you - you can use it as-is or dive into it and extend it as you wish. Read the full article

More and more Application Express developers are faced with the requirement to integrate REST services or HTTP/JSON data feeds into their applications. Application Express provides great support...

Oracle Announces Oracle Application Express 5.1

Oracle Application Express 5.1 is now generally available! This release is a great leap forward in end-user productivity and introduces powerful new declarative features, enabling you to develop, design and deploy beautiful, responsive, database-driven desktop and mobile applications using only a browser.  "At Insum, we are very excited with the upcoming APEX 5.1 release. The new declarative Master-Detail-Detail capabilities, possible using the new Interactive Grid, will greatly assist many of our clients convert legacy applications to Oracle Application Express. I am not even mentioning the hundreds of new improvements that the APEX team delivers with each new release" said Francis Mignault, co-founder, CTO, Insum Solutions.    The marquee features of Oracle Application Express 5.1 include: Interactive Grids Interactive Grid is a new fully featured grid component that provides powerful reporting and editing capabilities.  It includes all of the features you would expect for displaying data, including frozen columns, scroll pagination, filtering, sorting, aggregations, charting, and more.  With an editable Interactive Grid, users can add, modify, and delete data directly within the component. Additionally, Interactive Grid is designed to support all item types and item type plug-ins, enabling easy editing of columns with a variety of input controls. Finally, with Interactive Grids, developers can now easily render master-detail-detail relationships that can be n-levels deep or across. Oracle JET Charts The data visualization engine of Oracle Application Express 5.1 is now powered by Oracle JET (JavaScript Extension Toolkit), a modular open source toolkit based on modern JavaScript, CSS3 and HTML5 design and development principles.  The charts in Oracle Application Express 5.1 are fully HTML5 capable and work on any modern browser, regardless of platform, or screen size.  These charts provide numerous ways to visualize a data set, including bar, line, area, range, combination, scatter, bubble, polar, radar, pie, funnel, and stock charts. Universal Theme  Oracle Application Express 5.1 builds on the success of the Universal Theme and introduces new templates, theme styles and Live Template Options. Just like Theme Roller, Live Template Options enables you to customize your application in real time, allowing you to try out various template options to get the perfect UI for your application. This release includes Font APEX, a new icon library with over 1,100 icons, which has been specifically designed to complement the development of business applications with Oracle Application Express and Universal Theme.  Universal Theme now provides declarative support for right-to-left languages, modal dialogs that will automatically grow or shrink in height to fit their contents, and over 100 other enhancements. You can learn more in the Universal Theme Sample Application which has been updated to provide better examples, more documentation, an icon and button builder, and a whole lot more! Application Builder Improvements In Oracle Application Express 5.1, wizards have been streamlined with smarter defaults and fewer steps, enabling developers to create components quicker than ever before.  There have also been a number of enhancements to Page Designer, including the integration of Component View, layout customizations, property filtering, and more. Page Designer can now be customized to be displayed in two or three columns, and individual tabs can be reordered using drag and drop. Property Editor now features a live search filter enabling developers to quickly find properties. These features, along with improved page navigation and refined user interface, are designed to enhance the overall developer experience. Productivity Apps Oracle Application Express 5.1 includes enhancements to all existing productivity and sample apps, and also introduces three new productivity apps - Competitive Analysis, Quick SQL and REST Client Assistant: Quick SQL provides a quick and intuitive way to generate a relational SQL data model based on text in a markdown-like format. Additionally, the app provides many options to generate SQL including generating triggers, APIs and history tables.   REST Client Assistant enables developers to access RESTful services defined in both Application Express workspaces and public services. The app provides metadata-driven mapping from service response data to SQL result set columns. The generated SQL and PL/SQL code can then be used directly in Oracle Application Express applications. Competitive Analysis can be used to create side-by-side comparisons which can be edited by many users simultaneously. These comparisons can be scored and displayed in aggregated chart form, or in a more detailed text form. Improvements have been made to all of the Sample and Productivity apps, capitalizing on the new functionality of Oracle Application Express 5.1. The Sample Charts app has been completely revamped to showcase the all new Oracle JET Charts and is an outstanding demonstration of the data visualization capabilities in Oracle Application Express 5.1. The Sample Master-Detail app now highlights the different ways related tables can be displayed using a marquee page or different combinations of Interactive Grids. This release also includes three new Sample apps: Sample Interactive Grids, Sample Projects, and Sample REST Services. The Sample Interactive Grids app demonstrates the rich functionality of Interactive Grids including read-only, editable, and advanced capabilities.  "Oracle Application Express will allow you to build any data-driven web application with less effort as compared to traditional programming languages. The added Interactive Grid component in APEX 5.1 will extend the capabilities of Application Express, thereby covering even the most complex requirements for dialogs out-of-the-box. I therefore foresee a great uptake of customers that will migrate their legacy apps to Application Express" said Niels de Bruijn, Business Unit Manager and Oracle Ace Director, MT AG, Germany.   Additional Information Oracle Application Express is a high productivity platform for creating declarative, low code Web applications on the Oracle Database and in the Oracle Cloud.  To learn more about Oracle Application Express, visit apex.oracle.com.  To learn more about Oracle Database Cloud, visit cloud.oracle.com/database. 

Oracle Application Express 5.1 is now generally available! This release is a great leap forward in end-user productivity and introduces powerful new declarative features, enabling you to develop,...

Oracle Application Express 5.1 Early Adopter available

Prior to every major release of Oracle Application Express, we conduct a hosted "Early Adopter" of APEX.  This is similar to a hosted beta program, and everyone is welcome to participate and provide your feedback.  This is an opportunity for the community to participate prior to release.  The URL to access the Early Adopter version of APEX is at: https://apexea.oracle.com On the Login page, you'll see a link to "Known Issues" and also to provide feedback.  There are some exciting new features in this release, including: Interactive Grid Integration with Oracle JET Charts Much better RTL support in Universal Theme Ability to have multiple tabs open to the same APEX application and isolate session state  and much more...  A few things to keep in mind: This is hosted only.  We do not make early releases of Application Express available for on-premises installation. Everything you do on the Early Adopter instances of APEX should be considered throw-away.  There is no guarantee that APEX applications you build in the Early Adopter instance will even be installable in the production release of Oracle Application Express.  We usually conduct 2 or 3 Early Adopter releases prior to production.  As of this writing, we are in Early Adopter 1.  As the feature-set and quality of the APEX release stabilizes, we will tear down the Early Adopter 1 instance and replace it with Early Adopter 2.  There will be no in-place upgrade.  You will need to sign up for a workspace all over again.  If you're reading this blog post after Application Express 5.1 is released, you will find that the Early Adopter at https://apexea.oracle.com is no longer available. We look forward to your feedback.  And thanks for being such strong supporters of Oracle Application Express.  Our community is awesome! 

Prior to every major release of Oracle Application Express, we conduct a hosted "Early Adopter" of APEX.  This is similar to a hosted beta program, and everyone is welcome to participate and provide...

Oracle Application Express in 2016 - You Ain't Seen Nothin' Yet!

2015 was simply a magnificent year for the Oracle Application Express community, as foretold on this blog exactly one year ago.  In addition to the release of Oracle Application Express 5.0 in April 2015, which enabled you to easily build modern, responsive and beautiful Web applications anywhere the Oracle Database runs (including the Oracle Cloud!),  the community also delivered in numerous, bold ways, including: apex.world - The APEX Community site, developed by the community for the community.  It includes something for everyone in the APEX Community - plug-ins, job market, member discussion, meetups, news, Twitter feed, and more! The Oracle APEX Talkshow - An innovative podcast with various members of the Oracle APEX community, all the brainchild of APEX super-luminary Jürgen Schuster. The APEX Fan Shop - Show your APEX pride with stickers, mugs, umbrellas and clothing! translate-apex.com - Over 30 community-provided translations for APEX applications, a fantastic initiative from our friends at Pretius. Customer Quotes on apex.oracle.com/community - Numerous customers provided quotes about their use of Oracle APEX, both big and small.  We are grateful for the time and energy spent by these customers to deliver these quotes for the benefit of the broader APEX community. But the fun is just starting, and 2016 holds even more promise for everyone in the ever-expanding Oracle APEX community.  The release of Oracle Application Express 5.1 in 2016 will deliver the highly anticipated "Interactive Grid" and an entirely new charting engine based upon the data visualization tools of Oracle JET.  These two features alone, building on top of the rich feature set of Oracle Application Express 5, will perfectly round out the APEX framework for Oracle Database and Oracle Database Cloud customers alike.  Fasten your seat belt! The list of conferences offering an expanding menu of world-class Oracle Application Express content continues to grow.  You should circle one or more of these conferences on your calendar now: OUG Ireland 2016 (March 3, 2016 - March 4, 2016), Dublin, Ireland - 12th annual conference with numerous streams of content, this year including a handful of sessions on Oracle Application Express! APEX World 2016 (March 7, 2016 - March 8, 2016), Rotterdam, The Netherlands - 7th annual conference, expanding to two days this year, and again on the beautiful SS Rotterdam. APEX Connect 2016 (April 26, 2016 - April 28, 2016), Berlin, Germany - 2nd annual conference and now being held in Berlin.  Expanded to three full days, including PL/SQL sessions, and more sessions and attendees than ever! 2016 Great Lakes Oracle Conference (May 18, 2016 - May 19, 2016), Cleveland, Ohio, USA - A collection of Oracle Application Express sessions in addition to a pre-conference Oracle Application Express workshop, being held at the historic Cleveland Public Auditorium. ODTUG Kscope16 (June 26, 2016 - June 30, 2016), Chicago, Illinois, USA -  46 sessions over 5 days (wow!), and as a bonus, the Oracle Application Express product development team exclusively presents on the must-attend Sunday Symposium.  If you're into APEX, Kscope16 cannot be missed.  And while in Chicago, you'll be able to sample the best pizza in the world! Oracle Open World 2016 (September 18, 2016 - September 22, 2016), San Francisco, California, USA -Numerous Oracle Application Express and Oracle Database Cloud sessions and hands-on labs from the Oracle Application Express product development team and world-class speakers from the global Oracle APEX community. Slovenian Oracle User Group 2016 (October 2016), Portorož, Slovenia - In 2015, there was a great collection of world class speakers from the APEX Community and 2016 will continue the tradition, catering to the expanding APEX community in Slovenia.  Don't forget the APEX Meetups that happen in Slovenia throughout the year. Croatian Oracle User Group 2016 (October 2016), Rovinj, Croatia - At the 20th annual conference in 2015, the Croatian Oracle User Group conference featured a first-ever dedicated track for Oracle Application Express with world-class speakers.  The tradition continues in 2016, and this has quickly become a must-attend conference for anyone doing Oracle Application Express development in Croatia and the Balkans. Oracle APEX Tour 2016 Latinoamérica (November 7, 2016), Brasília, Brazil - Oracle APEX Tour 2016 Latinoamérica continues to be the largest Oracle Application Express event in Latin America, with more than 250 attendees and world-class speakers from Latin America, USA and Europe. DOAG Konferenz + Ausstellung (November 15, 2016 - November 18, 2016), Nürnberg, Germany - The passionate APEX community in Germany comes together at this very technical and professionally delivered conference, with content delivered by world-class speakers from around the globe. UKOUG Technology Conference and Exhibition (December 4, 2016 - December 7, 2016), Birmingham, England - There were more APEX sessions than ever in 2015, and enthusiasm continues to grow.  The passionate APEX community in the United Kingdom converges at this conference (to wit, Exhibit A). Don't forget the Oracle APEX Meetup groups, which continue to expand across the globe.  As of January 1, 2016, there are 32 groups across 5 continents, and growing.  Come join one of the most passionate and enthusiastic user communities on the planet! 

2015 was simply a magnificent year for the Oracle Application Express community, as foretold on this blog exactly one year ago.  In addition to the release of Oracle Application Express 5.0 in April...

Oracle Application Express 5 Universal Theme & Accessibility Guidelines

A customer recently asked a question about Oracle Application Express 5 Universal Theme and its compliance with the Web Content Accessibility Guidelines (WCAG) Level A standard.  Our team member Anthony Rayner is responsible for the accessibility and assessment of Oracle Application Express releases (among many other things).  He provided such a masterfully written and lucid response that it deserved to be shared on the APEX blog for everyone's benefit.  My only changes were to "correct" the spelling of some of his English words to American English.  Here it is: Oracle Application Express 5.0, including the Universal Theme (UT) follow Oracle's corporate web accessibility guidelines, which themselves are based on WCAG 2.0 to 'A' and 'AA' Level and the applicable standards of Section 508. How well we meet these standards is described in the the VPAT for APEX 5.0, which is the framework's statement of conformance to these guidelines. In this document you should pay particular attention to any issues cited that are categorized as 'Affecting Database Applications' as these are what could affect applications that you build with UT.  I would also like to highlight the following 2 exclusions we have cited in the VPAT, which affect UT. Being cited here means that we make no claims to them meeting the aforementioned guidelines, either because they have not been fully tested, or because they are known to have many issues: Theme Roller - Module available to developers allowing declarative definition of look and feel. All sample applications, available from the Packaged Applications page in the Application Express development environment. (Specifically these applications, including the UT sample app contain many plug-ins which have not been fully tested by us, and are known to have accessibility issues). In addition to the VPAT, the APEX Release Notes always cites other accessibility bugs which are not severe enough to warrant VPAT inclusion, but we do want to highlight.  Specifically please see section 7.15.2 for issues affecting database applications. I would also like to highlight that a number of Windows High Contrast mode bugs were fixed in APEX 5.0.1, so if you are using APEX 5.0, then I would strongly suggest to upgrade to at least 5.0.1, but preferably of course the latest patch set 5.0.2 which includes these fixes also, and many others not accessibility related. So to summarize, the base UT theme was tested to these guidelines, with issues against these guidelines cited either in the VPAT or release notes.  However, if you use any additional plug-ins in these applications, then these will need to be assessed from an accessibility standpoint to ensure they meet the required guidelines. In the future, we want to continue to make it as easy as possible for developers to build accessible applications in APEX, this includes releasing a white paper and also a reduction in the number of exclusions cited in the VPAT.

A customer recently asked a question about Oracle Application Express 5 Universal Theme and its compliance with the Web Content Accessibility Guidelines (WCAG) Level A standard.  Our team member Anthon...

Share Your Quote and be a Part of #LetsWreckThisTogether, in 3 Easy Steps!

At the recent ODTUG Kscope15 conference, the new Oracle Application Express Community application was rolled out.  This is a vastly improved site to proudly advertise the successes of the growing global Oracle Application Express community.  Also at the same ODTUG Kscope15 conference, the Twitter hashtag #letswreckthistogether was fully explained.  Succinctly stated, countless brilliant customers have validated the application development thesis which is Oracle Application Express, and without any fear or hesitation, we are collectively going to tell the world about it. One of the areas added to the community site are Quotes, and this is your opportunity to help the Oracle Application Express community flex their muscle.  Are you doing more with less?  Are you writing applications which support thousands of users on modest hardware?  Are you solving problems today which previously went unsolved?  No need to embellish facts.  Let the empirical evidence speak for itself. Submitting a quote couldn't be easier, and the approval process can all be done via email.  It's easy as 1, 2, 3: 1)  You submit a quote by visiting the Oracle Application Express Community site, click on Quotes and then Submit Quote. 2)  We put your quote, title, organization name on a PDF and send it back to you via email, and with the following text in the body of the email: Thank you for working with us to put together the attached quote/slide. Oracle would like to distribute these materials. This includes posting them on and making them available for download through the Oracle Web site and social media sites. In addition, we would like to use portions of these materials, including your corporate logo and quotes attributed to your employees, in Oracle marketing materials. Please confirm that [Your Organization Name] approves by responding that you approve. In terms of the next steps: a)  Please review the attached slide and quote for accuracy  b)  Please attach a company logo that at a minimum is a JPEG, bitmapped logo that is clearly read when sized at a maximum 160 pixels wide, 80 pixels high.  Other options are Illustrator .ai  and .eps files, which can be resized without degradation. c)  Please respond to this email granting approval. 3) You send a reply email with your organization's logo, and that you approve. That's all there is to it! Not only will you be helping the Oracle Application Express community, you ultimately will be helping yourself too.  If you are a consultancy and you convince your customers to provide a quote, the quote can also reference your consultancy (just like the very first quote from CROATIA osiguranje and consulting partner, BiLog).  If you're a customer and love APEX, and you want to increase the available pool of resources for Application Express, here is your chance to show university students, PL/SQL developers, and all others that there is growing demand for these skills.   Getting approval from your employer for a quote is never easy, and it takes time and effort.  We understand.  But it's worth asking.  We're convinced that we can reach at least 100 customer quotes by the end of 2015, and we would like you to be a part of this.  Don't worry if you don't work at a Global Fortune 1000 company - all customer uses of Oracle Application Express, both big and small, are valuable.  We want to hear from you! We at Oracle know there are hundreds of thousands of Oracle Application Express developers worldwide, who are solving problems both big and small with Oracle Application Express, Oracle REST Data Services and the Oracle Database.  Now is your opportunity to be unafraid and boast!  #LetsWreckThisTogether

At the recent ODTUG Kscope15 conference, the new Oracle Application Express Community application was rolled out.  This is a vastly improved site to proudly advertise the successes of the growing...

Oracle Announces Oracle Application Express 5

Oracle Application Express 5 is now generally available! The result of a two and a half year engineering effort, it represents the greatest advancement of Oracle Application Express (APEX) in its 10 year history.  Oracle Application Express 5 enables customers to develop, design and deploy elegant, responsive, database-driven desktop and mobile applications using only a browser. "Oracle APEX 5 is the largest and most important release in the history of Oracle Application Express" said Michael J. Hichwa, Vice President of Oracle Database Tools.  "Universal Theme in Oracle APEX 5 dramatically improves how quickly and easily SQL-savvy developers can build Web apps that are both responsive and accessible. Universal Theme enables developers, like myself, to build a robust customized UI without the help of a CSS/HTML guru." The marquee features of Oracle Application Express 5 include: Universal Theme: A Better UI for Your Apps Universal Theme is an all-new user interface (UI) for your applications. It is engineered from the ground up for Application Express 5. It is simpler yet more capable than previous themes, and can enable effortless customization. Universal Theme can empower developers to build modern, responsive, sophisticated applications without requiring expert knowledge of HTML, CSS, or JavaScript. With Universal Theme, there are also several enhancements to the handling of Themes and Templates. This release features: Theme Subscriptions, Template Options, Theme Styles, List-based hierarchical Navigation Menus and much finer control over templates and the HTML emitted by the Application Express engine. It is now easier than ever to control the UI of your application.  Page Designer:  The New Intuitive Browser-Based IDE Page Designer is a new IDE designed to greatly enhance developer productivity for prototyping, design, development and maintenance of Application Express applications. Page Designer offers a visual presentation, including an on-the-fly, intuitive drag and drop interface for rapid development of application pages. The enhanced code editor provides SQL and PL/SQL validation with inline errors, auto completion, syntax highlighting, search and replace with regex support, complete with undo and redo support. New Application Builder Design In addition to the new Application Builder UI being designed for easy navigation, simplicity of access for visual design and reduced clutter, it includes a new color palette, icons for easy, visual identification, intuitive workflow-based menus, and improved keyboard and accessibility support. Mobile Reporting The new mobile reporting capability allows developers to build reports that can display on nearly all mobile devices. For small devices with limited screen displays, intelligent Column Toggle and Reflow Table Wrap features allow developers to specify the display of important data and hide less important information for easy viewing.  Modal Dialogs Modal and Non-Modal dialogs enable developers to rapidly define dialogs using standard Application Express pages. To use Modal Dialogs, developers simply select the display type and template of the page, and Application Express completes the process of rendering the page appropriately. All New Calendar The new calendar component comes with built-in support for Month, Week, Day, and Agenda views and is much easier to customize. The calendar is based on the popular FullCalendar library and supports drag and drop and time-based events. Packaged Applications  Oracle Application Express 5 includes a broad collection of Packaged Applications. These are Application Express applications that can be used out-of-the-box and are supported by Oracle. Examples include Project Tracking, Survey Builder, Meeting Minutes, and Group Calendar. There are 19 Packaged Applications in all. Additionally, there are 16 sample applications which are used to showcase the features of Oracle Application Express, from Sample Charts to Sample Data Loading. There's even a sample application demonstrating the powerful spatial capabilities in the Oracle Database. "An interesting dimension of the Oracle APEX technology is how cloud-friendly it is.  Oracle APEX is browser-based, making developer and deployment access seamless (never leave your browser). Oracle APEX is available in multiple Oracle Database Cloud service offerings today. Oracle APEX is one of the rare database tools you can use equally well on the cloud, your company's server, and your laptop," added Hichwa. Additional Information To learn more about Oracle Application Express, visit apex.oracle.com.  To learn more about Oracle Database Cloud, visit cloud.oracle.com/database. 

Oracle Application Express 5 is now generally available! The result of a two and a half year engineering effort, it represents the greatest advancement of Oracle Application Express (APEX) in its 10...

I Want You! (to get a job with Oracle Application Express)

Very often, we, at Oracle, get asked about the marketability of Oracle Application Express skills, and if it's something that can help someone find employment and grow their career.  And without question, the answer is always an ethusiastic "Yes!" Fundamentally, an Oracle Application Express developer is someone who is proficient at using the rich and powerful Oracle Database as their application development platform.  An Oracle Application Express developer posseses skills in one or more of the following areas: Oracle Application Express Data modeling Oracle SQL Oracle PL/SQL HTML5 CSS Elegant Web design Data security Web security Oracle Database performance and tuning Most successful Oracle Application Express developers are proficient in at least a couple of these areas.  And ultimately, the title "Oracle Application Express developer" is a description for someone who is capabable of making the Oracle Database sing! So is anyone really hiring for APEX experts today, right now?  Absolutely!  And to convey this point, we informally reached out by email to a small number of partners and companies to gauge their demand.  With their permission, we are presenting the following job vacancies, looking for skilled APEX developers today: MT AG - Junior Berater Oracle Application Express (APEX) Pharmasol - Senior Web Developer, creators of psiXchange, written in APEX and Oracle Inoapps - Graduate Developer Inoapps is looking for outstanding University graduates to join our graduate training program in our Linlithgow office. The training program will focus on Oracle Application Express and you will be learning from some of the best Oracle Application Express developers in the UK.  Keil KTM GmbH - Experienced Oracle developer with in-depth APEX knowledge We are regularly looking for experienced developers to support us in our APEX projects. We are an SME consulting company working for big international groups as well as for medium size companies. APEX gains importance for many of our clients, across industries and of all sizes.  In Munich, we look for an experienced Oracle developer with in-depth APEX know how.  Insum Solutions - Oracle APEX Developer Hepart AG - A Swiss pharmaceutical company situated near the lake of Constance, Hepart AG has developed software for creating and distributing personalized micronutrients.  We are looking for experienced APEX and PL/SQL developers to strengthen our development team.  If you are interested, contact hr@hepart.ch Yellowstar Solutions - Oracle APEX Developer APEX R&D - Oracle APEX Developer iAdvise - Junior APEX Developers and Senior APEX Developers The Ohio State University - Systems Developer/Engineer C2 Consulting - Consultants & Developers that are also Strategists & Business Managers FOEX GmbH - Software Developer Itium - Oracle Developer And this is only a small sample of the Oracle APEX jobs available today, gathered through a very informal email request.  There is continued growing adoption of the Oracle Database Cloud Database Schema Service (in production since 2012).  More and more customers are realizing how easy it is to extend Oracle eBusiness Suite with Oracle Application Express.  As mentioned in an earlier blog post, Oracle APEX content at Oracle conferences continues to grow (including the introduction of another APEX-only conference, APEX Connect).  And with the game-changing Application Express 5 on the horizon, the job prospects for Oracle Application Express developers have never looked brighter!

Very often, we, at Oracle, get asked about the marketability of Oracle Application Express skills, and if it's something that can help someone find employment and grow their career.  And without...

Application Express 5 Early Adopter 3 - A preview of modern database web application development

Announcing Application Express 5 Early Adopter 3!  Oracle Application Express 5 Early Adopter 3 was announced and made available on January 31, 2015.  The Early Adopter program is the "hosted beta" for APEX, where the pre-production version of Oracle Application Express is made available on the Internet for evaluation, testing and your feedback.  In a mere 7 days, we've had 1,502 customers sign up for a workspace and take APEX5 EA3 for a spin.  Everyone is welcome to participate.  Simply go to http://apexea.oracle.com and sign up for a workspace, to get a preview of what modern database web application development looks like.  Page Designer: The World's Most Advanced Browser-based IDE Page Designer is a completely new IDE designed to greatly improve developer's productivity, allowing you to very quickly enhance and maintain pages within Application Express. Cohesive User Experience With Page Designer, there is no context-switching when modifying multiple components. It is no longer necessary to drill into component pages simply to make changes. All modifications occur within Page Designer, where you can undo and redo changes as necessary, before saving your page. Better Visual Representation You can gain an immediate understanding of how your regions and items are laid out within the Grid Layout. Changes to components on your page are immediately reflected across the Tree and Grid Layout Page Designer. Additionally, errors and warnings are immediately visible. Intuitive Drag and Drop Page Designer completely reimagines 'drag and drop' and provides an entirely new way to develop pages on the fly. In the Grid Layout pane, you can drag new components from the Component Gallery, or move and copy existing components around. In the Tree pane, you can drag to move and copy multiple components at a time. Enhanced Code Editor It's never been easier to write code in Application Express. The new code editor provides SQL and PL/SQL validation with inline errors, auto completion, syntax highlighting, search and replace with regex support, complete with undo and redo support. You can even specify your preferences for tabs, indentation, and color scheme.  Universal Theme: a better UI for your apps Universal Theme is an all-new user interface for your applications that has been built from the ground up for Application Express 5. It is a simpler, yet more capable theme that does away with excessive templates and enable effortless customization with Theme Roller, Template Options, and Theme Styles. Universal Theme is the culmination of all of our UI enhancements and aims to empower developers to build modern, responsive, sophisticated applications without requiring expert knowledge of HTML, CSS, or JavaScript.  Theme Roller This flagship feature of Universal Theme enables easy customization of the look and feel of an application. You can tweak a number of style attributes and see changes applied to your application in real time. Once you are satisfied with the result, you can save your changes as a Theme Style directly to your app. Template Options Easily customize the way buttons, regions, lists, and other template-based components appear. A single template can be displayed in several different ways using these options. Universal Theme enables developers to use fewer templates but build far more versatile applications. Absolutely Responsive Universal Theme has been designed to be responsive from the very beginning. You can build fully responsive apps that work flawlessly across a variety of screen sizes and form factors. Universal Theme includes key enhancements such as enabling complex form layout and using flexible grids. Accessible Universal Theme delivers most accessible user interface for applications built with Application Express. Theme Roller can be used to build accessible Theme Styles and includes built-in WCAG 2.0 color-contrast checking.  Additional Features in Application Express 5 Release 5 includes many additional features, enhancements, and more! Here are some highlights: All New Design We designed a completely new user interface for Application Express that is focused on improving user experience through simplicity and removing clutter. The new design uses a beautiful new color palette, carefully crafted icons, vastly improved menus and navigation, better accessibility and keyboard support, more intuitive page layouts and much more... User Interface Enhancements With the development of Universal Theme, we also added several enhancements to how Themes and Templates are handled. This release has features such as Theme Subscriptions, Template Options, Theme Styles, and much finer control over templates and the HTML emitted by the APEX engine. It is now easier than ever to fully control the UI of your application. Multiple Interactive Reports The most requested feature for Application Express is here. Easily create multiple interactive reports on a single page, and gain insights into your data like never before. We've also completely revamped the user interface to provide a consistent experience, and made end user customization even easier with modal dialogs. Modal Dialogs Now you can easily define modal (and non-modal) pages complete with the ability to utilize standard page processes. No longer will you need to hack a page using JavaScript and performing a lot of hand-crafting. Instead just set the display type and appropriate template and let Application Express take care of the rest. Better Navigation Static or dynamic lists can be used as the basis for your application's navigation menu and navigation bar. Navigation Menus can be positioned to the top or side of your pages, and can be rendered based on any List template, enabling nested navigation items, keyboard support, and greater accessibility. More Interactive Report Enhancements Interactive Reports feature an all-new user interface for displaying your data, report settings, support for fixed column headers, and is more accessible than ever before. Additionally, end users can now define a pivot report at runtime, which is similar to defining a group report. All New Calendar The new calendar component comes with built in support for Month, Week, Day, and Agenda views and is much easier to customize. The calendar is based on the popular FullCalendar library and supports drag and drop, time-based events, and is even responsive. Mobile Reporting You can now build reports that display all of your data on any mobile device, by using reflow table or column toggle. Reflow table wraps each column or changes to displaying multiple lines on very small screens. Column toggle allows you to specify the most important columns, and those which will be hidden as necessary on smaller screens. Static File Upload Files uploaded into shared components, plug-ins, or themes can be cached by your browser and are now referenced using relative file urls. This also allows you to upload a zip file and the contents of it will be automatically extracted, and relative file references will stay intact. Want to bundle up your files? No worries, you can also zip them all up and produce a single downloadable zip file. 

Announcing Application Express 5 Early Adopter 3!  Oracle Application Express 5 Early Adopter 3 was announced and made available on January 31, 2015.  The Early Adopter program is the "hosted beta" for...

Oracle Application Express and 2015: Fasten Your Seat Belt

2015 will be remembered as the year that Oracle Application Express took off like a rocket.  This isn't a mere wish.  We, at Oracle, know it.  Get ready for a monumental year for all of us in the Oracle Application Express community. The long-awaited Oracle Application Express 5.0 will be released in 2015.  Oracle Application Express 5.0 is the largest and most significant release in the 10-year production history of Oracle Application Express, measured in both man years of development and financial investment.  While Oracle Application Express 5.0 is a dramatic leap in developer productivity, Oracle Application Express 5.0 is also a huge step forward in the easy development of beautiful, modern, responsive Web applications.  If you're a database developer building Web applications or looking to extend the Oracle eBusiness Suite with Oracle Application Express, get ready for a banner year.  And get accustomed to being the hero. If you're interested in learning about Oracle Application Express, or if you're an experienced user wishing to learn how you can capitalize on Oracle Application Express 5.0, there are a number of conferences you should circle on your calendar now: APEX World (March 25, 2015), Rotterdam, The Netherlands - 6th annual conference, advertised as "the largest APEX-only conference in the galaxy" with world-class speakers.ODTUG APEXposed! 2015 (May 6, 2015), Montreal, Canada - This action-packed conference returns to Montreal with sessions that will span a wide range of APEX skill sets, from introductory to expert.   2015 Great Lakes Oracle Conference (May 18, 2015 - May 20, 2015), Cleveland, Ohio, USA - A handful of Oracle Application Express sessions in addition to a pre-conference Oracle Application Express workshop.  APEX Connect 2015 (June 9, 2015 - June 10, 2015), Düsseldorf, Germany - This DOAG-sponsored event offers 45 sessions over 2 days from an impressive array of world-class speakers, primarily for the German-speaking community. ODTUG Kscope15 (June 21, 2015 - June 25, 2015), Hollywood, Florida, USA - 45 sessions over 5 days, and as a bonus, the Oracle Application Express product development team exclusively presents on the must-attend Sunday Symposium.  If you're into APEX, Kscope15 cannot be missed. DOAG Konferenz + Ausstellung (November 17, 2014 - November 20, 2015), Nürnberg, Germany - The ever-growing and passionate APEX community in Germany comes together at this very technical and professionally delivered conference. UKOUG Technology Conference and Exhibition (December 7, 2015 - December 9, 2015), Birmingham, England - The Oracle Application Express community in the United Kingdom converges here, and with hopes for a dedicated APEX track in 2015. In addition to these conferences, don't forget the Oracle APEX Meetup groups, which are taking off across the globe.  To date, there are 25 groups across 4 continents, and growing.  Come join one of the most passionate and enthusiastic user communities on the planet!

2015 will be remembered as the year that Oracle Application Express took off like a rocket.  This isn't a mere wish.  We, at Oracle, know it.  Get ready for a monumental year for all of us in the...

Oracle

Integrated Cloud Applications & Platform Services