Sunday, 22 March 2015

APEX 5 Give me Interactive Reports

One of the statements of direction for APEX 5 is multiple interactive reports on a single page.

Multiple Interactive Reports – Allow any number of Interactive Reports to be defined on a single page.

So, time to explore that functionality.

One pattern I've used in the past is loading particular saved reports (rather than the default primary report). You may have some pre-defined filters, a row highlight, specific columns. So for demonstrations, I've built an IR on the emp table.

Then I go and apply some filters, and save an alternate report:




Then in the page designer assign an alias to that report.





As you can see, that allows me to enter in the request portion of the URL: IR_SAL_TOTAL. However, if we add a second IR to the page and try to access that saved report, we would receive an error "Report does not exist". This is because it tries to find that that report name in both IR's.

To get around this, you MUST set a static ID property for your IR regions. Then the request portion of the URL would become: IR[<static ID>]_<ALIAS>. So I gave a static id: empIr, thus the request portion would be: IR[empIr]_SAL_TOTAL, to link to the report I previously saved.



The same concept applies to filtering your IR in the URL. The supported URL operations can be reviewed in the (beta) docs:

  • C = Contains
  • EQ = Equals (this is the default)
  • GTE = Greater than or equal to
  • GT = Greater Than
  • LIKE = SQL Like operator
  • LT = Less than
  • LTE = Less than or equal to
  • N = Null
  • NC = Not Contains
  • NEQ = Not Equals
  • NLIKE = Not Like
  • NN = Not Null
  • NIN = Not In (escape the comma separated values with backslash, \)
  • IN = In (escape the comma separated values with backslash, \)
  • ROWFILTER = Row Text Contains (this searches all columns displayed in the report with type STRING or NUMBER)

So, in the itemNames portion of the URL, you would spacify IR[<static id>]<operator as above>_<column>. So for example, if we wanted employees with a salary greater than 2000 we would use IR[empIr]GT_SAL giving us a URL like so:

f?p=101:1:12313123::::IR[empIr]GT_SAL:2000

Friday, 20 March 2015

Identifying functions and procedures without arguments

I wanted to find a report on all procedures in my schema, that accepted zero arguments.

There are two views that are useful here:
  1. USER_PROCEDURES
  2. USER_ARGUMENTS
With USER_PROCEDURES, if the row is referring to a subprogram in a packaged, object_name is the package name, and procedure_name is the name of the subprogram. With any other subprogram out of the context of a package, the object_name is the name of the subprogram and procedure_name returns NULL.

With user_argument, object_name becomes the name of the subprogram, with package_name being NULL when you are not dealing with a package's subprogram. 

In the case of subprograms out of the package context, no rows are returned in the user_arguments  view. That differs from a subprogram in a package - you get a row, but argument_name is set to NULL. You will never get a NULL argument if there is at least one argument.

In the case of functions, you will get an additional argument with argument_name set to NULL that relates to the return type. 

So with that information in mind, we can identify procedures like:


select user_procedures.object_name
from 
    user_procedures
    left outer join user_Arguments proc_arguments on (user_procedures.object_name = proc_arguments.object_name )
where 
    proc_arguments.object_id IS NULL
    and user_procedures.object_Type = 'PROCEDURE'
    

Then, we can get functions:

with func_arg_count as (
select func_args.*, count(*) over (partition by func_args.object_id) arg_count
from 
    user_procedures
    left outer join user_Arguments func_args on (user_procedures.object_name = func_args.object_name )
where 
    user_procedures.procedure_name  IS NULL and func_args.package_name is null
    and user_procedures.object_Type = 'FUNCTION'
)
select object_name
from func_arg_count
where argument_name is null 
and data_Type is not null
and arg_count = 1
    

Giving us a consolidated query for functions and procedures of:

with func_arg_count as (
    select 
        user_procedures.object_name
      , user_procedures.object_id  
      , user_procedures.object_Type
      , func_args.argument_name
      , func_args.data_Type
       --func_args.*
      , count(case when func_args.object_id is not null then 1 end) over (partition by user_procedures.object_id) arg_count
    from 
        user_procedures
        left outer join user_Arguments func_args on (user_procedures.object_name = func_args.object_name )
    where 
        user_procedures.procedure_name  IS NULL and func_args.package_name is null
)
select object_name
from func_arg_count
where (
        ( object_Type = 'FUNCTION' and argument_name IS NULL and ARG_COUNT = 1)
        or 
        ( object_Type = 'PROCEDURE' and arg_count = 0 )
    )    

Then we can do the packages like so:

with pkgs as (
    select 
        user_procedures.object_name
      , user_procedures.object_id  
      , user_procedures.object_Type
      , user_procedures.procedure_name
      , func_args.in_out
      , func_args.argument_name
      , func_args.data_Type
      , func_args.subprogram_id
    , count(*) over (partition by user_procedures.object_id, func_args.subprogram_id) arg_count
    from 
        user_procedures
        join user_Arguments func_args on (user_procedures.object_name = func_args.package_name and user_procedures.procedure_name = func_args.object_name)
    where 
        user_procedures.object_Type = 'PACKAGE'
)
select object_name pkg, procedure_name 
from pkgs
where argument_name is NULL and arg_count = 1

Monday, 9 February 2015

Reset an Interactive Report (IR)

To reset an IR back to the default settings, you may know you can go to the actions menu, and hit reset:




If you inspect the apply button you will see it's calling gReport.reset()


And there are a bunch of examples using this gReport object both for resetting the report and other IR functions.

The problem? This is not documented, and with APEX 5 supporting multiple IRs, this will no longer work. In your console, if you enter gReport, you will see that object no longer exists.

The other technique you can use is the clear cache portion of the URL. According to the docs:

To reset an interactive report in a link, use the string "RIR" in the Clear-Cache section of a URL. This is equivalent to the end user choosing the Reset option from the interactive report actions menu on the target page. The report is returned to the default report settings specified by the developer or saved by the user.

Just testing this on a 4.2 instance, this this only resets the PRIMARY report, and takes you back to the PRIMARY report i.e. any filters added on saved reports will remain. The reset button in the actions menu on the other hand just clears the filters added on the saved report, and you remain on that saved report.

So, the best way to programatically reset an IR?

First, I suggest giving your IR a region static ID so you can easily query the apex dictionary.

Then if we look at the APEX_IR API, we can see it has the procedure (2) RESET_REPORT, which accepts 3 parameters - page id, region_id and report_id. It is acceptable to pass in NULL to p_report_id, and it will just use the last viewed report for that region - otherwise, you'd need to make use of APEX_IR.GET_LAST_VIEWED_REPORT_ID.

Then, we can have a dynamic action with 2 true actions: Execute PL/SQL code and Refresh region, with the PL/SQL code looking something like this:


declare
    l_region_id apex_application_page_regions.region_id%type;
begin

    select region_id into l_region_id
    from apex_application_page_regions
    where application_id = :APP_ID
    and page_id = :APP_PAGE_ID
    and static_id = 'region-static-id';--update this accordingly
    
    apex_ir.reset_report(
        p_page_id => :APP_PAGE_ID
      , p_region_id => l_region_id
      , p_report_id => NULL
    );
    
end;

This re-iterates the importance of only using documented functions.

APEX 5 API changes

Based on the current beta API docs: https://docs.oracle.com/cd/E59726_01/doc.50/e39149/toc.htm, here is what's changed.

APEX_APPLICATION_INSTALL
  • Function GET_AUTO_INSTALL_SUP_OBJ added 
  • Procedure SET_AUTO_INSTALL_SUP_OBJ added

APEX_CUSTOM_AUTH
  • LOGOUT procedure deprecated

APEX_ESCAPE
  • Function JSON added
  • Function REGEXP added

APEX_INSTANCE_ADMIN
  • Procedure CREATE_SCHEMA_EXCEPTION added
  • Procedure FREE_WORKSPACE_APP_IDS added
  • Function GET_WORKSPACE_PARAMETER added
  • Procedure REMOVE_SCHEMA_EXCEPTION added
  • Procedure REMOVE_SCHEMA_EXCEPTIONS added
  • Procedure REMOVE_WORKSPACE_EXCEPTIONS added
  • Procedure RESERVE_WORKSPACE_APP_IDS added
  • Procedure RESTRICT_SCHEMA added
  • Procedure SET_WORKSPACE_PARAMETER added
  • Procedure UNRESTRICT_SCHEMA added

APEX_IR
  • Procedure CHANGE_SUBSCRIPTION_EMAIL added
  • Procedure CHANGE_REPORT_OWNER added

APEX_LDAP
  • Function SEARCH added

APEX_PLUGIN_UTIL
  • Function GET_ATTRIBUTE_AS_NUMBER added

APEX_UTIL
  • Procedure CLOSE_OPEN_DB_LINKS added
  • Procedure IR_CLEAR deprecated
  • Procedure IR_DELETE_REPORT deprecated
  • Procedure IR_DELETE_SUBSCRIPTION deprecated
  • Procedure IR_FILTER deprecated
  • Procedure IR_RESET deprecated
  • Procedure PUBLIC_CHECK_AUTHORIZATION deprecated
  • Procedure RESET_AUTHORIZATIONS deprecated
  • Procedure SET_GROUP_GROUP_GRANTS added
  • Procedure SET_GROUP_USER_GRANTS added

APEX_WEB_SERVICE
  • Function MAKE_REST_REQUEST_B added

Packages
  • Package APEX_SPATIAL added
  • Package APEX_ZIP added
  • Package APEX_JSON added

JavaScript APIS

New namespaces
  • apex.da  
  • apex.debug 
  • apex.lang 
  • apex.util 

apex.navigations additions
  • apex.navigation.dialog
  • apex.navigation.dialog.cancel
  • apex.navigation.dialog.close
  • apex.navigation.dialog.fireCloseHandler
  • apex.navigation.dialog.registerCloseHandler
  • apex.navigation.dialog.title
  • apex.navigation.openInNewWindow
  • apex.navigation.popup
  • apex.navigation.redirect

apex.server additions
  • apex.server.url


Saturday, 7 February 2015

APEX 5 creating regions on an existing page

In APEX 4.2, it's nice and simple. Right click on the body node of the page tree view, and click create:


After clicking create, you would be presented with all the possible region types:


The rest is just a matter of following the steps.

APEX 5, has a new page designer, and it took me a minute or so to figure out how to add a form region, since there seems to be a couple of options for adding regions!

Firstly, in the centre column down the bottom of the page, you have a grid view of (almost) all the region types you can add. You can either drag them onto the visual layout of your page above, or right click and select where to add it to.


The other option is on the left hand pane, right click and then select Create Region.

.


This will create a region with the type set as: "Static Content". 

You then need to change the type on the right hand pane, and then fill out any region specific settings.


At this point, it's probably also worth mentioning that the settings for the regions on the right pane might not be displaying all settings. There are two options in the toolbar:

1. Show common
2. Show all

E.g. region static ID is only displayed if you have `Show all` mode enabled:




Some regions should be created through the new page wizard, which you will notice if you try to select Tabular Form:



And form region is not listed there - well technically, it's not a region type, so that makes sense. On the top of your page, you will notice a plus icon button. And this is how we can add form regions to our page,



Which gives us all the wizard options we've come to expect:





disclaimer: This is based on the current early adopter (3) instance of apex

Tuesday, 3 February 2015

APEX 5 blob column uploads

Existing behaviour

In APEX 4.2, to upload a file into a BLOB column, there are two patterns for getting the file into your table. In settings, specify the storage type as:

  1. BLOB column specified in Item Source attribute
  2. Table WWV_FLOW_FILES 
I tend to use method 2, and will be focusing on that pattern.

This table can also be referred to with either:
  • WWV_FLOW_FILES
  • APEX_APPLICATION_FILES
  • HTMLDB_APPLICATION_FILES
Which you can see with the following query:

select *
from all_synonyms
where table_name = 'WWV_FLOW_FILES'

On your form page, whenever you have chosen a file to upload, and submit the page, the file will be uploaded into wwv_flow_files.

The name column is the value assigned to the page item, so to fetch that particular file upload you would have something like:

declare
    l_filerow apex_application_files%rowtype;
begin

    select *
    into l_filerow
    from apex_application_files
    where name = :Px_ITEM_NAME;
    
    --Do something with l_filerow
    --e.g. insert into my_Table (filename, mime_Type, file_contents) values (l_filerow.filename, l_filerow.mime_Type, l_filerow.blob_content);
end;

This could be in a validation or page process. Unless you want your files to linger in wwv_flow_files, you also need to be sure to delete the files, after a process/validation and also being sure to delete them it if any exceptions occurred.

New behaviour

In APEX 5, you still have the two options, but WWV_FLOW_FILES has changed to: APEX_APPLICATION_TEMP_FILES.











A neat new feature is the option to purge the file (at). This gives us two options:


  1. End of session
  2. End of request
So no more worrying about deleting that row yourself! You will see that if you specify `End of request` after all page processing, the file will no long live in that table/view.

There are also a reduced set of columns in APEX_APPLICATION_TEMP_FILES:

  • CREATED_ON
  • MIME_TYPE
  • FILENAME
  • NAME
  • APPLICATION_ID
  • ID
  • BLOB_CONTENT
Making the following unavailable (that are in wwv_flow_files):
  • LANGUAGE
  • CONTENT_TYPE
  • LAST_UPDATED
  • UPDATED_ON
  • UPDATED_BY
  • CREATED_ON
  • CREATED_BY
  • MIME_TYPE
  • FILENAME
  • NAME
  • ID

wwv_flow_files is still around, it just means that uploads in your systems will no longer be going there. 

note: Any existing applications migrated that reference WWV_FLOW_FILES will still be using that, with a note that it is deprecated. After you change it and save it, it will be gone as an option.









This is also covered in the (currently beta) release notes: http://docs.oracle.com/cd/E59726_01/doc.50/e39143/toc.htm#BAJJDJBA

File Browse Storage Type, Table WWV_FLOW_FILES - Any select, update or delete operations on WWV_FLOW_FILES should be changed to using the APEX_APPLICATION_TEMP_FILES table. Deletes are no longer necessary, as the file will automatically be purged after the request or when the session is purged.

disclaimer: This is based on the current early adopter (3) instance of apex

Monday, 2 February 2015

APEX 5 supporting file enhancements

Existing behaviour

In APEX 4.2, files can be uploading through shared components, and can either be associated with an application or workspace, and can be stored in either:


  1. Cascading Style Sheets
  2. Images
  3. Static Files












These files can generally be found in the view: APEX_WORKSPACE_FILES

Files are then referenced throughout the application by: #APP_IMAGES#<file_name> or #WORKSPACE_IMAGES#<file_name>

Files can also be included in application exports, by creating an installation script for these files (to ensure they are available when deployed):

Select Create Scripts to Install Files under tasks when creating a new installation script:








Then it's just a matter of selecting which files to include in the installation script

New behaviour

In the current form, this has changed to divide files up into application files and workspace files:








This file uploader is not a multi-file uploader control, but it now has support for uploading a zip, which can be extracted after upload - a nice way to upload multiple files in one hit. A word of warning, if you attempt to upload a file in the same path that already exists, it will simply be replaced, without warning.

These files would be referenced in two new views: APEX_APPLICATION_STATIC_FILES AND APEX_WORKSPACE_STATIC_FILES

This substitution string remains the same: #APP_IMAGES# and #WORKSPACE_IMAGES# accordingly.

Another new feature, is that relative paths are supported. So e.g you could have a zip with images, css directories, after you upload the zip, you can refer to files like so: #APP_IMAGES#css/<css file_name>

The docs say it not necessary to create that supporting object script any more for static files and indeed that seems to be the case - they are automatically included in application exports - with no apparent ability to disable this.

disclaimer: This is based on the current early adopter (3) instance of apex