X

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

  • March 19, 2018

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

Carsten Czarski
Consulting Member of technical Staff

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

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:

  1. Change the URL Path Prefix to users/:github_user/repos

     
  2. 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.

Join the discussion

Comments ( 1 )
  • Jens Gauger Thursday, June 28, 2018
    Hi Carsten,

    i tried to implement a web source following your example. I got it running within the Application Builder. I configured a web source and added the credentials. After that i was able to fetch data by creating a classic report using the web source.

    Now i tried to do the same using the APEX_EXEC package, just to learn how this works. I copied your code block and adjusted the parameters and variables. But i always get an error ORA-20987: APEX - Web source module not found when using APEX_EXEC.open_web_source_query. I checked the static id of the web source module twice and also the Database Action of the GET-Operation. It's "Fetch rows".

    What could be the issue here?

    Regards,
    Jens
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.Captcha
Oracle

Integrated Cloud Applications & Platform Services