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