Craig’s Blog

a Relational Database Theory, Design, and Application Development Resource

Overriding Methods in SQL

leave a comment »

I’ve been working on an application that uses lots of table stored configuration parameters that are used in code modules to determine program behaviour at runtime. This leads to writing a lot of ‘generic’ code and a fair amount of dynamic pl/sql and sql. However there are conditions where the code needs to be more specific and a particular function or procedure must be coded.

In the spirit of this application I considered a procedure that I could pass in the numberic identifier for a thing/object that I wanted the application to process (a person record for example) and have this procedure determine which process to execute based on properties of the object – some objects (person records) would utilise generic code, others would have a function specifically written.

Now these specific functions would be added over time to the application. I want to minimise recompilation of dependent objects, so my first thoughts were that a package specification would hold the top level entry point having a formal parameter to accept the identifier. The implementation in the body would work out the properties of the thing (select from a table) and then use conditional logic to call either the generic code or a specific private function. Then I could add functions as required to the body and just recompile that. Not needing to recompile the specification would minimise invalidating dependent objects (changes to a package body do not require recompiling invoking subprograms).

Also, I’ve been reading about the SQL Object Model (introduced first in SQL 1999) recently and remembered an example demonstrating dynamic polymorphism (method overidding) that I read in a recent paper on otnHow to write injection-proof PL/SQL (pdf). I thought this could be a good technique to use for the problem at hand.

This post illustrates these two approaches to coding the requirement for deciding, at runtime, which particular routine to execute in a manner that minimises the need to recompile existing stored objects as new specific functions are added during the live implementation of an application;

  • Firstly taking advantage of the separation of package specification and body that would require recompilation of the body as new routines are added.
  • Secondly using object oriented techniques (inheritance hierarchies and method overriding) that requires the compilation of new user defined types as and when new specific functioality is required in the application.

After implementating a basic program to illustrate the techniques I will add in code to capture execution times and run time statistics to compare the approaches.

For the example consider a multi-tenant SAAS application and a job that runs once each day. The job must output an XML document for sending information to each client of the application. Most clients accept a standardized document and therefore one generic/standard block of code is sufficient, other clients have specific requirements that we need to accommodate.

To keep things simple we have a small Client table …

create table Client(
  Client_Id   integer,
  Client_Name varchar2(30)
);
alter table Client
  add constraint Client_Pk primary key (Client_Id) enable;

and if a Client has specific requirements we record this fact in the Client_Req table

create table Client_Req(
  Client_Id         integer,
  Specific_Document varchar2(1)
);
alter table Client_Req
  add constraint Client_Req_Pk primary key (Client_Id) enable;

alter table Client_Req
  add constraint Client_Req_Fk foreign key (Client_Id)
  references Client(Client_Id) enable;

alter table Client_Req
  add constraint Client_Req_Chk check (Specific_Document in ('Y','N')) enable;

We have four Clients …

insert into Client values(1,'ABC Ltd.');
insert into Client values(2,'ACME Corp');
insert into Client values(3,'XYZ Plc');
insert into Client values(4,'Z Tech');

two of which have specific documentation requirements

insert into Client_Req values(3, 'Y');
insert into Client_Req values(4, 'Y');
commit;

Approach 1 – taking advantage of the separation of Package Spec and Body

Here is a public procedure that we can call passing in the Client_Id for the client whos document we wish to produce …

create or replace package Client_Report_Pkg
is

  procedure Produce_Document(Client_Id in integer);

end Client_Report_Pkg;
/
show errors;

in the package body the approach is to select the client details into local variables and use these to decide whether to run either the standard routine or to call a specific local function written specifically for the client

create or replace package body Client_Report_Pkg
is
  /*
   * Private Global Variables, Types, Functions and Procedures
   */

  /*
   * Specific Routines.
   * These can be added to as required.
   */
  procedure Produce_XYZ
  is
  begin
    DBMS_OUTPUT.Put_Line('> called: Produce_XYZ');
  end Produce_XYZ;

  procedure Produce_ZTech
  is
  begin
    DBMS_OUTPUT.Put_Line('> called: Produce_ZTech');
  end Produce_ZTech;


  /*
   * Public Functions and Procedures
   */
  procedure Produce_Document(Client_Id in integer)
  is
    Client_Name  Client.Client_Name%type;
    Specific_Doc Client_Req.Specific_Doc%type;
  begin

    select upper(c.Client_Name), coalesce(cr.Specific_Doc, 'N') as Specific_Doc  
      into Produce_Document.Client_Name, Produce_Document.Specific_Doc  
      from Client c  
           left outer join Client_Req cr on c.Client_Id = cr.Client_Id  
     where c.Client_Id = Produce_Document.Client_Id;


    if Produce_Document.Specific_Doc = 'N' then
      -- Process generic routine
      DBMS_OUTPUT.Put_Line('> running Generic Routine: '||Produce_Document.Client_Name);
      
    else
      -- Calls to specific private procedures
      -- for Clients that have non-standard requirements      
      case Produce_Document.Client_Name
        when 'XYZ PLC' then Produce_XYZ;
        when 'Z TECH'  then Produce_ZTech;
        else null;
      end case;
    end if;

  exception
    when no_data_found then
      DBMS_OUTPUT.Put_Line('No such Client (Client_Id: '||Produce_Document.Client_Id||')');
  end Produce_Document;

end Client_Report_Pkg;
/
show errors;

This is a straighforward implementation and I assume a very common approach.

It has the downside of hardcoded values in the code. We could use the client_ids (PKs) instead of the names as the PKs are (supposed to be) immutable.

craig@ORADB1> exec Client_Report_Pkg.Produce_Document(1);
> running Generic Routine: ABC LTD.

PL/SQL procedure successfully completed.

craig@ORADB1> exec Client_Report_Pkg.Produce_Document(3);
> called: Produce_XYZ

PL/SQL procedure successfully completed.

Approach 2 – using Object Types and Method Overriding

An SQL object type is a user defined type that has attributes and can have methods that operate on a value of the type. Object types can be constructed in an inheritance hierarchy just as a class can in Java (the SQL object model follows closely that of the Java language). The example here constructs a type hierarchy which enables method overriding.

I’ve found the Oracle Database Object-Relational Developer’s Guide, 11g Release 1 (11.1) documentation to be a useful introduction and reference.

The approach here is to first create a user defined type that will be the super type (parent) in a type hierarchy. This supertype will not be used to hold any value (it is declared not instantiable) nor will any method be implemented in the supertype (though we do define a method signature). Instead subtypes (children) of the supertype will be created to implement the specific functionality we require for different clients, one type for each client requiring specific handling. The Client_Req table is modified to hold a value of a subtype rather than a varchar2(1) with a ‘Y’ / ‘N’ value as in approach 1. This value of the user defined type is selected into a PL/SQL variable and the method implemented in the subtype can then be invoked. I hope the code makes more sense than my attempt at describing this in English!

Here is the definition of the supertype.

create type Dynamic_Method_Dispatch_Typ authid Current_User as object (
  Dummy number,
  not instantiable member function Produce_Specific_Document(
    Client_Id in integer ) return integer
)
not final
not instantiable
/

I think of this user defined type much like a package specification. It simply defines a type with one attribute (of type number) and one function accepting one parameter and returning an integer (the not final clause allows for the creation of subtypes of the type). Actually there are two parameters as member methods have a built-in parameter named SELF that denotes the object instance (value) currently invoking the method – see Chpt 2 of the Object-Relational Developer’s Guide.

Now we create a subtype of Dynamic_Method_Dispatch_Typ …

create or replace type Produce_XYZ_Document_Typ under Dynamic_Method_Dispatch_Typ (
  overriding member function Produce_Specific_Document(
    Client_Id in integer ) return integer
)
/

and for this type we also create its implementation or body …

create or replace type body Produce_XYZ_Document_Typ is
  overriding member function Produce_Specific_Document(
    Client_Id in integer ) return integer
  is
  begin
    DBMS_OUTPUT.Put_Line('> called: Produce_XYZ_Document_Typ.Produce_Specific_Document');
    return Client_Id;
  end Produce_Specific_Document;
end;
/

Note that the subtype inherits the attribute (Dummy) of its supertype and also how it implements the Produce_Specific_Document function as an overriden method; it has the same name and parameters which is something you cannot do in a package. You can overload a procedure or function in a package but the parameter(s) must differ in order for SQL to know which one to call.

The table to record the fact that a client has specific requirements has an attribute of type Dynamic_Method_Dispatch_Typ (the supertype) in which we can store a value of the supertype or any of its subtypes

create table Client_Req_M2(
  Client_Id         integer,
  Specific_Doc_Typ  Dynamic_Method_Dispatch_Typ
);
alter table Client_Req_M2
  add constraint Client_Req_M2_Pk primary key (Client_Id) enable;

insert into Client_Req_M2 values(3, Produce_XYZ_Document_Typ(0) );
commit;

The package to tie it all together …

create or replace package Client_Report_M2_Pkg
is

  procedure Produce_Document(Client_Id in integer);

end Client_Report_M2_Pkg;
/
show errors;
create or replace package body Client_Report_M2_Pkg
is
  /*
   * Private Global Variables, Types, Functions and Procedures
   */

  /*
   * Function to dynamically dispatch call to Object Type method.
   */   
  function Call_Object_Method(
    Client_Id in integer  
   ,Obj       in Dynamic_Method_Dispatch_Typ)
    return integer
  is
  begin

    return Obj.Produce_Specific_Document(Call_Object_Method.Client_Id);

  end Call_Object_Method;

  
  /*
   * Public Functions and Procedures
   */
  procedure Produce_Document(Client_Id in integer)
  is
    Client_Name       Client.Client_Name%type;
    Specific_Doc_Typ  Dynamic_Method_Dispatch_Typ;

    Call_Return integer;
  begin
    
    select upper(c.Client_Name), Specific_Doc_Typ as Specific_Doc_Typ
      into Produce_Document.Client_Name, Produce_Document.Specific_Doc_Typ
      from Client c
           left outer join Client_Req_M2 cr2 on c.Client_Id = cr2.Client_Id
     where c.Client_Id = Produce_Document.Client_Id;

    if Produce_Document.Specific_Doc_Typ is null then
      -- Process generic routine
      DBMS_OUTPUT.Put_Line('> running Generic Routine: '||Produce_Document.Client_Name);
      
    else
      -- Call to function that dispatches to the approprite type's method
      Call_Return := Call_Object_Method(
                       Client_Id => Produce_Document.Client_Id
                      ,Obj       => Produce_Document.Specific_Doc_Typ );
    end if;

  exception
    when no_data_found then
      DBMS_OUTPUT.Put_Line('No such Client (Client_Id: '||Produce_Document.Client_Id||')');
  end Produce_Document;

end Client_Report_M2_Pkg;
/
show errors;
craig@ORADB1> exec Client_Report_M2_Pkg.Produce_Document(1);
> running Generic Routine: ABC LTD.

PL/SQL procedure successfully completed.

craig@ORADB1> exec Client_Report_M2_Pkg.Produce_Document(3);
> called: Produce_XYZ_Document_Typ.Produce_Specific_Document

PL/SQL procedure successfully completed.

To add the second client’s bespoke requirements to the application requires creating a new subtype of Dynamic_Method_Dispatch_Typ …

create or replace type Produce_ZTech_Document_Typ under Dynamic_Method_Dispatch_Typ (
  overriding member function Produce_Specific_Document(
    Client_Id in integer ) return integer
)
/
create or replace type body Produce_ZTech_Document_Typ is
  overriding member function Produce_Specific_Document(
    Client_Id in integer ) return integer
  is
  begin
    DBMS_OUTPUT.Put_Line('> called: Produce_ZTech_Document_Typ.Produce_Specific_Document');
    return Client_Id;
  end Produce_Specific_Document;
end;
/

and then inserting a value of the new type into the Client Requirements table …

insert into Client_Req_M2 values(4, Produce_ZTech_Document_Typ(0) );
commit;
craig@ORADB1> exec Client_Report_M2_Pkg.Produce_Document(4);
> called: Produce_ZTech_Document_Typ.Produce_Specific_Document

PL/SQL procedure successfully completed.

Written by Craig

May 28, 2009 at 9:11 pm

Interval Datatypes and Date Arithmetic (ORA-01839)

leave a comment »

I have been using the INTERVAL YEAR TO MONTH datatype to represent the period of time that a course gives a particular qualification. During my testing I hit a problem caused by the way this type behaves when performing date arithmetic. For example;

select to_date('29-feb-2008','dd-mon-yyyy') + interval '1-0' year(1) to month
  from dual
/

2008 being a leap year what should the answer be? 28 Feb 2009 perhaps. The server tells us:

select to_date('29-feb-2008','dd-mon-yyyy') + interval '1-0' year(1) to month
                                            *
ERROR at line 1:
ORA-01839: date not valid for month specified

I have used the built in ADD_MONTHS function to perform the needed arithmetic. For instance in the where clause I am restricting rows to those where the projected ‘Expiry Date’ (the Date_Attended + the Interval value) is greater than today.

As an example;

create table Course (
  Course_Id   integer
 ,Course_Name varchar2(20)
 ,Max_Lapsed_Time interval year(2) to month);
 
create table Attendance (
  Course_Id     integer
 ,Employee      varchar2(50)
 ,Date_Attended date );
 
insert into Course values (1, 'Cooking', interval '1-0' year(2) to month);

insert into Attendance values (1, 'Jane', to_date('01-Feb-2007','dd-Mon-yyyy'));
insert into Attendance values (1, 'John', to_date('29-Feb-2008','dd-Mon-yyyy'));
insert into Attendance values (1, 'Paul', to_date('19-Apr-2008','dd-Mon-yyyy'));

commit;
col Max_Lapsed_Time format a18
col Employee        format a10
select * from Course;

COURSE_ID COURSE_NAME     MAX_LAPSED_TIME
---------- --------------- ------------------
         1 Cooking         +01-00

select * from attendance;

COURSE_ID EMPLOYEE   DATE_ATTEND
---------- ---------- -----------
         1 Jane       01-Feb-2007
         1 John       29-Feb-2008
         1 Paul       19-Apr-2008

To start building the required resultset, I first project the date the course attendance will no longer be valid (this is 1 year after the Date_Attended).

select c.Course_Name
      ,a.Employee
      ,a.Date_Attended
      ,c.Max_Lapsed_Time
      ,a.Date_Attended + c.Max_Lapsed_Time as Course_Expires
  from Course c
       inner join Attendance a on c.Course_Id = a.Course_Id
/
ERROR at line 5:
ORA-01839: date not valid for month specified

As expected we get the friendly ORA-01839

We can use the ADD_MONTHS built in instead, along with EXTRACT to construct the number of months to add from the interval;

select c.Course_Name
      ,a.Employee
      ,a.Date_Attended
      ,c.Max_Lapsed_Time
      ,Add_Months(
         a.Date_Attended
        ,(extract(year from c.Max_Lapsed_Time) * 12
          + extract(month from c.Max_Lapsed_Time))
       ) as Course_Expires
  from Course c
       inner join Attendance a on c.Course_Id = a.Course_Id
/
COURSE_NAME     EMPLOYEE   DATE_ATTEND MAX_LAPSED_TIME    COURSE_EXPI
--------------- ---------- ----------- ------------------ -----------
Cooking         Jane       01-Feb-2007 +01-00             01-Feb-2008
Cooking         John       29-Feb-2008 +01-00             28-Feb-2009
Cooking         Paul       19-Apr-2008 +01-00             19-Apr-2009

And then

select t.*
  from (select c.Course_Name
              ,a.Employee
              ,a.Date_Attended
              ,c.Max_Lapsed_Time
              ,Add_Months(
                 a.Date_Attended
                 ,(extract(year from c.Max_Lapsed_Time) * 12
                   + extract(month from c.Max_Lapsed_Time))
               ) as Course_Expires
          from Course c
               inner join Attendance a on c.Course_Id = a.Course_Id
       ) t
 where t.Course_Expires > sysdate
/
COURSE_NAME     EMPLOYEE   DATE_ATTEND MAX_LAPSED_TIME    COURSE_EXPI
--------------- ---------- ----------- ------------------ -----------
Cooking         Paul       19-Apr-2008 +01-00             19-Apr-2009

So we can find Attendances that are still current / valid and if a course happened to be run on the 29 Feb of a Leap year our code does not suffer a run time error!

Written by Craig

March 26, 2009 at 2:27 pm

Posted in Datatypes, SQL

Tagged with ,

Apex Query By Example Report

with one comment

This post demonstrates how I built an APEX Query by Example Report using PL/SQL Best Practices as illustrated in a recent paper by Bryn Llewellyn1 and utilising a Global Application Context to dynamically bind the where clause to a compile time static SQL statement text.

I used a few database techiques to implement this search report that provides for;

  • Protection against SQL injection attacks.
  • Performance (and scalability) – use of an application context to dynamically bind search criteria so that costly hard parsing of the dynamically generated SQL statements is minimised.
  • Storage of code in PL/SQL packages rather than within Apex report regions.

Please do comment / critique where you find any issues with this approach or have ideas for improvement.

To implement the search report requires development of the following components;

The search form is based on the HR.Employees table. The search parameters would be on attributes such as employee name, department, salary and so on. The final result would look similar to the following;

Completed Search Report

Completed Search Report

Creating the Global Application Context

The first step is to create the Global Application Context. (For details see Using Application Contexts to Retrieve User Information, Chapter 6 of Oracle Database Security Guide (11g Release 1).

create or replace context Employee_QBE_Ctx
 using Employee_QBE_Ctx_Pkg
 accessed globally;

top

Creating Schema Level Object Types

I like to keep most PL/SQL code in server side packages rather than sprinkled across the various regions of the Apex application. I found one benefit to this is that it becomes easier to maintain the application as a change to the package can easily change the data the application uses.

In this search page, to retrieve data, I experimented using a Packaged Function that returns a schema level collection typed on a user defined object type. The function which has a type of the user defined collection can be treated as a table using the TABLE expression and therefore ‘selected’ from. First we need the object representing an employee and a collection typed on this object.

create or replace type Employee_Obj as Object (
 Employee_Id       number(6)
,First_Name        varchar2(20)
,Last_Name         varchar2(25)
,Email             varchar2(25)
,Phone_Number      varchar2(20)
,Hire_Date         date
,Job_Title         varchar2(35)
,Salary            number(8,2)
,Commission_Pct    number(2,2)
,Manager_Full_Name varchar2(46)
,Department_Name   varchar2(30)
);
/

create or replace type Employee_Vat as Varray(100) of Employee_Obj;
/

There are two packages in this solution.

  1. Employee_QBE_Ctx_Pkg – this is the package that manages the Application Context. It sets the name/value pairs that are stored in the SGA. It also builds the dynamic WHERE clause based on the users search criteria.
  2. Employee_Data_Access_Pkg – holds the function that returns the collection of employee objects. This function is called from the Apex application and has formal parameters to pass in the user values for the search.

Both packages make use of two collections; one that holds the attribute name and datatype of the attributes on which the user can search, and one that holds the actual search value(s) and the relational operator to apply (equality, greater than, less than, between and so on). This is the technique used in the PL/SQL Best Practices paper1 that I mentioned above.

create or replace type Attribute_Obj is object (
 Attribute_Name  varchar2(30)
,Attribute_Type  varchar2(30));
/

create or replace type Attribute_Ntt is table of Attribute_Obj;
/

create or replace type Where_Elements_Obj is object (
 Criteria            varchar2(4000),
 Relational_Operator varchar2(100));
/

create or replace type Where_Elements_Ntt is table of Where_Elements_Obj;
/

top

PL/SQL Packages

Security Package

The package that manages the context is basically the same as the security context package as per the documentation2. The difference here is in the Set_Ctx function – this is used both to set the name/value pairs and also to build the dynamic where clause that binds values for the user specified criteria.

create or replace package Employee_QBE_Ctx_Pkg
as
 procedure Set_Session_Id (Session_Id in number);

 function Set_Ctx (Attr in Attribute_Ntt
                  ,Whr  in Where_Elements_Ntt)
   return varchar2;

 procedure Clear_Session (Session_Id in number);

 procedure Clear_Ctx_Attribute (Session_Id in number default null
                               ,Attribute in varchar2);

 procedure Clear_Ctx;

end Employee_QBE_Ctx_Pkg;
/
show errors;

Security Package Body

create or replace package body Employee_QBE_Ctx_Pkg
as

 G_Session_Id varchar2(200);

 procedure Set_Session_Id (Session_Id in number)
 is
 begin
   G_Session_Id := Session_Id;
   DBMS_Session.Set_Identifier(Session_Id);
 end Set_Session_Id;

 function Set_Ctx (Attr in Attribute_Ntt
                  ,Whr  in Where_Elements_Ntt)
   return varchar2
 as
   Where_Clause varchar2(4000);
 begin

   for i in 1 .. Set_Ctx.Attr.COUNT loop

   case upper(Attr(i).Attribute_Type)
     when 'VARCHAR2' then
       DBMS_SESSION.Set_Context('EMPLOYEE_QBE_CTX', Attr(i).Attribute_Name
                                ,upper(Whr(i).Criteria)
                                ,USER, Employee_QBE_Ctx_Pkg.G_Session_Id);
       Where_Clause := Where_Clause || ' and '
     || 'upper('||Attr(i).Attribute_Name||')'
     || case Whr(i).Relational_Operator
          when 'EQUALITY' then ' = '
          when 'LIKE'     then ' like '
        end
     || 'SYS_CONTEXT(''EMPLOYEE_QBE_CTX'', '
     || ''''||Attr(i).Attribute_Name||''') ';

     when 'NUMBER' then
       DBMS_SESSION.Set_Context('EMPLOYEE_QBE_CTX', Attr(i).Attribute_Name
                                ,Whr(i).Criteria
                                ,USER, Employee_QBE_Ctx_Pkg.G_Session_Id);
       Where_Clause := Where_Clause || ' and '
     || Attr(i).Attribute_Name
     || case Whr(i).Relational_Operator
          when 'EQUALITY'           then ' = '
          when 'GREATER_THAN'       then ' > '
          when 'LESS_THAN'          then ' < '
          when 'GREATER_THAN_EQUAL' then ' >= '
          when 'LESS_THAN_EQUAL'    then ' <= '
        end
     || 'to_number(SYS_CONTEXT(''EMPLOYEE_QBE_CTX'', '
     || ''''||Attr(i).Attribute_Name||''') ) ';

     when 'DATE' then
       DBMS_SESSION.Set_Context('EMPLOYEE_QBE_CTX', Attr(i).Attribute_Name
                                ,to_char(Whr(i).Criteria)
                                ,USER, Employee_QBE_Ctx_Pkg.G_Session_Id);
       Where_Clause := Where_Clause || ' and '
     || Attr(i).Attribute_Name
     || case Whr(i).Relational_Operator
          when 'EQUALITY'           then ' = '
          when 'GREATER_THAN'       then ' > '
          when 'LESS_THAN'          then ' < '
          when 'GREATER_THAN_EQUAL' then ' >= '
          when 'LESS_THAN_EQUAL'    then ' <= '
        end
     || 'to_date( SYS_CONTEXT(''EMPLOYEE_QBE_CTX'', '
     ||            ''''||Attr(i).Attribute_Name||'''),'
     ||            '''dd-MON-yyyy'')';

   end case;

   end loop;

   return Where_Clause;

 end Set_Ctx;

 procedure Clear_Session (Session_Id in number)
 is
 begin
   DBMS_Session.Set_Identifier(Session_Id);
   DBMS_Session.Clear_Identifier;
 end Clear_Session;

 procedure Clear_Ctx_Attribute (
   Session_Id in number default null
  ,Attribute  in varchar2)
 is
 begin
   DBMS_Session.Clear_Context(
     'EMPLOYEE_QBE_CTX'
    ,nvl(to_char(Clear_Ctx_Attribute.Session_Id), G_Session_Id)
    ,Clear_Ctx_Attribute.Attribute
   );
 end Clear_Ctx_Attribute;

 procedure Clear_Ctx
 is
 begin
   DBMS_Session.Clear_All_Context(namespace => 'EMPLOYEE_QBE_CTX');
 end Clear_Ctx;

end Employee_QBE_Ctx_Pkg;
/
show errors;

Note in the DBMS_SESSION.Set_Context call that both the client_id and username are set. Setting the username ensures that only the user who set the application context can access the context.

top

Employee Data Access Package

This package has the function that is called from the Apex report region. The function accepts the user search criteria, adds these values into a Where_Elements_Ntt collection and passes this to the security context package function. The security context builds the dynamic where clause at the same time as it sets the name/value pairs in the global application context. The where clause is returned to the data access packaged function which concatenates it into the compile time static SQL statement text, executes the query and then passess back the collection of employee objects to the Apex environment.

create or replace package Employee_Data_Access_Pkg
as

 function Get_Employees (
   Slice_No        in integer
  ,Rows_To_Display in integer
  ,Last_Name       in varchar2
  ,Last_Name_RelOp in varchar2
  ,Hire_Date       in date
  ,Hire_Date_RelOp in varchar2
  ,Job_Id          in varchar2
  ,Salary          in number
  ,Salary_RelOp    in varchar2 )
 return Employee_Vat;

end Employee_Data_Access_Pkg;
/
show errors;
Employee Data Access Package Body
create or replace package body Employee_Data_Access_Pkg
as

  function Where_Clause(
    Last_Name       in varchar2
   ,Last_Name_RelOp in varchar2
   ,Hire_Date       in date
   ,Hire_Date_RelOp in varchar2
   ,Job_Id          in varchar2
   ,Salary          in number
   ,Salary_RelOp    in varchar2
   ,Element_Cnt    out integer
   ,Attr_Names     out Attribute_Ntt)
  return varchar2
  is
    Clause        varchar2(32767);
    
    Array_Indx    pls_integer := 0;
    Whr_Elements  Where_Elements_Ntt := Where_Elements_Ntt();
  begin
    Attr_Names := Attribute_Ntt();
    
    if Where_Clause.Last_Name is not null then
      Array_Indx := Array_Indx +1;
      Attr_Names.EXTEND(1);
      Attr_Names(Array_Indx) := Attribute_Obj('LAST_NAME', 'VARCHAR2');
      Whr_Elements.EXTEND(1);

      case Where_Clause.Last_Name_RelOp
        when 'LIKE' then
          Whr_Elements(Array_Indx) := Where_Elements_Obj(
            '%'||Where_Clause.Last_Name||'%'
           ,Where_Clause.Last_Name_RelOp);
        else
          Whr_Elements(Array_Indx) := Where_Elements_Obj(
            Where_Clause.Last_Name
           ,Where_Clause.Last_Name_RelOp);
      end case;
    end if;
      
    if Where_Clause.Hire_Date is not null then
        Array_Indx := Array_Indx +1;
        Attr_Names.EXTEND(1);
        Attr_Names(Array_Indx) := Attribute_Obj('HIRE_DATE', 'DATE');
        Whr_Elements.EXTEND(1);
        Whr_Elements(Array_Indx) := Where_Elements_Obj(
          to_char(Where_Clause.Hire_Date,'dd-MON-yyyy')
         ,Where_Clause.Hire_Date_RelOp);
    end if;
      
    if Where_Clause.Job_Id is not null then
        Array_Indx := Array_Indx +1;
        Attr_Names.EXTEND(1);
        Attr_Names(Array_Indx) := Attribute_Obj('JOB_ID', 'VARCHAR2');
        Whr_Elements.EXTEND(1);
        Whr_Elements(Array_Indx) := Where_Elements_Obj(
          Where_Clause.Job_Id
         ,'EQUALITY');
    end if;
      
    if Where_Clause.Salary is not null then
        Array_Indx := Array_Indx +1;
        Attr_Names.EXTEND(1);
        Attr_Names(Array_Indx) := Attribute_Obj('SALARY', 'NUMBER');
        Whr_Elements.EXTEND(1);
        Whr_Elements(Array_Indx) := Where_Elements_Obj(
          to_char(Where_Clause.Salary, 'TM9', ' NLS_NUMERIC_CHARACTERS = ''.,'' ')
         ,Where_Clause.Salary_RelOp);
    end if;
      
    Clause := Employee_QBE_Ctx_Pkg.Set_Ctx(Attr => Attr_Names
                                          ,Whr  => Whr_Elements);

    Element_Cnt := Array_Indx;
    return Clause;
      
  end Where_Clause;

  
  function Get_Employees (
    Slice_No        in integer
   ,Rows_To_Display in integer
   ,Last_Name       in varchar2
   ,Last_Name_RelOp in varchar2
   ,Hire_Date       in date
   ,Hire_Date_RelOp in varchar2
   ,Job_Id          in varchar2
   ,Salary          in number
   ,Salary_RelOp    in varchar2 )
  return Employee_Vat
  is
    Result_Cursor  sys_refcursor;
    Emp_Var        Employee_Vat;
    lb constant    integer := Rows_To_Display * (Slice_No - 1) + 1;
    ub constant    integer := lb + Rows_To_Display - 1;
    Element_Cnt    integer;
    Attr_Names     Attribute_Ntt;

    SQL_Stmt constant varchar2(32767) :=
    'with Emp as (
         select emp.Employee_Id, emp.First_Name, emp.Last_Name
               ,emp.Email, emp.Phone_Number, emp.Hire_Date
               ,(select j.Job_Title from HR.Jobs j
                  where j.Job_Id = emp.Job_Id
                ) as Job_Title
               ,emp.Salary, emp.Commission_Pct
               ,coalesce(
                  (select e.First_Name||'' ''||e.Last_Name
                     from HR.Employees e
                    where e.Employee_Id = emp.Manager_Id)
                 ,''- No Manager -''
                ) as Manager
               ,(select d.Department_Name
                   from HR.Departments d
                  where d.Department_Id = emp.Department_Id
                ) as Department
               ,Rownum r
           from (select * from HR.Employees
                  where 1=1 '
		  || Where_Clause(
                       Last_Name
                      ,Last_Name_RelOp
                      ,Hire_Date
                      ,Hire_Date_RelOp
                      ,Job_Id
                      ,Salary
                      ,Salary_RelOp
                      ,Element_Cnt, Attr_Names ) ||
                ' order by Employee_Id) emp
      )
      select Employee_Obj(
             Emp.Employee_Id, Emp.First_Name, Emp.Last_Name
            ,Emp.Email, Emp.Phone_Number, Emp.Hire_Date
            ,Emp.Job_Title, Emp.Salary, Emp.Commission_Pct
            ,Emp.Manager, Emp.Department)
        from Emp
       where Emp.r between '||Get_Employees.lb||' and '
                            ||Get_Employees.ub;

    
  begin
    DBMS_OUTPUT.Put_Line(sql_stmt);

    open Result_Cursor for SQL_Stmt;
    fetch Result_Cursor bulk collect into Get_Employees.Emp_Var;
    close Result_Cursor;

    -- clear the context attributes
    for i in 1 .. Get_Employees.Element_Cnt loop
      Employee_QBE_Ctx_Pkg.Clear_Ctx_Attribute (
	Attribute => Attr_Names(i).Attribute_Name);
    end loop;

    return Get_Employees.Emp_Var;
    
  end Get_Employees;

end Employee_Data_Access_Pkg;
/
show errors;

top

Building the Apex Report

We have created the neccessary server side code now and can proceed to build the Apex Report.

Employee QBE Report Page

Create a Page named ‘Employee_QBE_Report’, and add a HTML region named ‘Employee Search Criteria’ to hold the search criteria items that the user can complete before submitting the report.

Page Items for User Search Criteria

Taking the empoyee surname as the first item to bind to the where clause we can simply add a text item to the page;

  • Text – PX_LAST_NAME
    • No label

SQL Report Region

Add a SQL Report region named ‘Search Results’ and paste in the following SQL statement (there are hard coded values in the call to the function at this stage, this is to get the basics of the report in place and will be adjusted as the page is built up);

select t.Employee_Id, t.First_Name, t.Last_Name
     ,t.Email, t.Phone_Number, t.Hire_Date
     ,t.Job_Title, t.Salary, t.Commission_Pct
     ,t.Manager_Full_Name, t.Department_Name
 from TABLE(cast(Employee_Data_Access_Pkg.Get_Employees(
                   1
                  ,15
                  ,:PX_LAST_NAME, 'LIKE'
                  ,null, null, null, null, null
           ) as Employee_Vat )) t

Referring back to the specification of the Employee_Data_Access_Pkg.Get_Employees function

function Get_Employees (
   Slice_No        in integer
  ,Rows_To_Display in integer
  ,Last_Name       in varchar2
  ,Last_Name_RelOp in varchar2
  ,Hire_Date       in date
  ,Hire_Date_RelOp in varchar2
  ,Job_Id          in varchar2
  ,Salary          in number
  ,Salary_RelOp    in varchar2 )
 return Employee_Vat;

we see that the SQL statement calls the function passing in values for the first 4 parameters. The collection returned by the function is CAST (…) AS Employee_Vat and wrapped in a TABLE expression that enables us to treat the function like a table.

The first two parameters define the slice / or segment of data we wish to retrieve – so we are requesting the first 15 records (passing in 2 and 50 say would be asking for records 51 to 100). These parameters are set up later using a custom pagination mechanism.

PL/SQL Page Process to set the session id

When we build the dynamic statement and set the context we must associate the context with our session. We can use a PL/SQL Page Process that will execute in the same process as the function call to achieve this. Create a process named ‘PX_SET_SESSION_ID’, as an On Load – Before Header process, and include a call to set the session identifier to the current user’s session.

begin
 Employee_QBE_Ctx_Pkg.Set_Session_Id( :APP_SESSION );
end;

Add a “SUBMIT” button to the ‘Employee Search Criteria’ region that branches to Page X when pressed (‘Branch to Page(when button pressed)’)

Running the page now (after adding formatting) should give a functional search report.

Basic QBE Search Report

Completed Search Report

Now we can add further search items for the other required employee attributes and a pagination scheme. My second post will complete this example by adding the pagination scheme.

top

References

  1. Doing SQL from PL/SQL: Best and Worst Practices, Bryn Llewellyn, PL/SQL Product Manager, Oracle [Available at http://www.oracle.com/technology/tech/pl_sql/pdf/doing_sql_from_plsql.pdf%5D Follow this link to download.
  2. For details on Global Application Context see Using Application Contexts to Retrieve User Information, Chapter 6 of Oracle Database Security Guide (11g Release 1).
  3. A use case I read a while back that first highlighted use of an Application Context for effective (using bind variables) dynamic SQL – Ask Tom “CURSOR”

Written by Craig

March 23, 2009 at 10:42 pm

Posted in Apex, Oracle, PL/SQL

Tagged with ,