Wednesday 18 March 2009

BPM-suite tutorial

Today i finished the BPM Suite tutorial on OTN.
I must say that I was impressed by the tool. The tutorial is quite clear and you can create a nice process-flow with the turn of your hand. Well, a little more. I spend over a day to finish it, but that was also due to system halts and the fact that you need time to install the tool.

To do the tutorial you need to install the BPM Suite Studio 10.3. You can download it here. Mark that there is also an enterprise version, either standalone or for a J2EE webserver (Oracle BEA Weblogic, IBM Websphere). For development it is suggested to use the BPM Suite Studio.

At startup of the studion in the welcomepage you can choose from the profiles:
  • Business Analyst
  • Business Architect
  • Developer

You can make the welcome screen visible again via help->welcome. Then it is possible to choose another profile.

The tutorial is based on the Developer profile. For a tech-guy as me usually the better choice.

At adding an activity on a transition (eg. from the begin to the end-activity) Studio might ask for an auto-layout. Check the “do not show this message again” option and click no. IF you do click on yes, then the auto-layouter will put all the activities behind eachother and more inconveniently hide empty swimming lanes.

Most of the tutorial is straight forward. However I encountered a few little problems and other experiences.

BPM Objects
When to add new attributes to a BPM-Object, the tutorial everytime asks you to right-click on the bpm-object and choose new->attribute. It can be done quicker and (I find) more convenient by opening the object te openen and choose the structure tab (the tabs are below the screens). That screen gives a tabel with all the attributes. The main-properties of each attribute you can edit by clicking on it. Right of the table there's also a 'plus' and 'minus'-icon, for adding and deleting attributes. But also a file-open icon. With that you can open the attribute on a new tab and edit all the properties. Leaving the opened tabs allows you to get to the attributes quickly. At adding the attribute via the plus-icon, you'll get after the naming of the attribute and the choise of the datatype als a check-box with the cryptical name ‘open’. When you check it, the property tab of the attribute is opened after confirmation.

Screenflow
The tutorial states that when creating the (first) screenflow, you'll have to add the variable reportSf as an instance variable to the Expense Report process. (Activity 3, Part ‘Creating a Screenflow’, step 12). I struggled with that step for some time, allthough it seemd illogical to me. It turns out as a document-flaw, because to mee it seems that you have to add the variable to the Submit Report (screenflow) process.

At the attribute mapping in the Create Expense Report activity of the Expense Report process it is said to choose 'Submit Report' in the left column. (Activity 3, Part 'Designing a Screenflow', Step 17). I think it is meant to select 'Submit Report in'. Then it is said to enter the constructor 'ExpenseReport()' as a value, but it is forgotten to name the input arguments. I presume that it has to be the only in the list: reportSFArg.

Conclusion
A very nice tool and a good tutorial that shows the basic use and the easy development of the tool. To call automated tasks, call services, more is needed though (it is not explained).

Tuesday 17 March 2009

Oracle Type inheritance and advanced type casting

A few years ago I wrote an article on Oracle Object types. You can find it on our whitepaper page, referenced as "Object Oriented Pl/sql". It's still Dutch, maybe I should make some time to translate it.

In E-Business Suite nowadays (R12) the developers also found the advantage of using object types. One of the comments I had on the TCA (Tracing Community Architecture)-API's I had was that they were too granular. From BPEL PM you had to do several (about 4) calls with the EBS or DB-adapter to add a Party with it's address. Now there are BO (Business Object) API's, based on hierarchies of types.

These types are quite sober, consisting only of attributes and sometimes a static function that returns an instantiated type (somehow they choose not to create a constructor).

I wanted to extend some of these types to add functionality. Basically it's not too hard, but I ran into some challenges.

Extending base types
First let's create a parent type as an example.
create or replace type xxx_parent as object
(
-- Author  : MAKKER
-- Created : 17-03-2009 08:10:32
-- Purpose :

-- Attributes
id number,
name varchar2(30),
description varchar2(100),
-- Member functions and procedures
constructor function xxx_parent return self as result
)
/
create or replace type body xxx_parent is

-- Version : $Id$
/* Member constructor, procedures and functions  */
constructor function xxx_parent return self as result is
begin
  return;
end;
end;
/

As you can see it's a simple type with a few attributes and a parameterless constructor. I'm used to allways add such a parameterless constructor. It often turns out handy in Pl/Sql since a type allways has a default constructor with all the attributes as a parameter. But often you want to instantiate a type and fill (a few of) the attributes later on.

I want to extend this type with a child. But to do so it must be 'not final'. By default, because of backwards compatibility, it is declared final.
You can declare a type explicitly being not final by adding the 'not final' keywords to the type specification. For example (from the oracle docs):
CREATE TYPE person_typ AS OBJECT (
idno           NUMBER,
name           VARCHAR2(30),
phone          VARCHAR2(20),
MAP MEMBER FUNCTION get_idno RETURN NUMBER,
STATIC FUNCTION show_super (person_obj in person_typ) RETURN VARCHAR2,
MEMBER FUNCTION show RETURN VARCHAR2)
NOT FINAL;
/

But in the end I want to extend the Oracle EBS BO-types, without specifically modifying the source. The other way is by altering the type:
alter type xxx_parent not final;

This command will be added as a seperate line to the source. Try for example:
select type, text from user_source where name = 'XXX_PARENT'
order by type, line
Now I can create a child object that extends this one:
create or replace type xxx_child under xxx_parent
(
-- Author  : MAKKER
-- Created : 17-03-2009 08:12:43
-- Purpose :

member procedure show,

-- Member functions and procedures
constructor function xxx_child return self as result
)
/
create or replace type body xxx_child is
-- Version : $Id$
/* Member constructor, procedures and functions  */
member procedure show is
begin
  dbms_output.put_line('Id: ' || self.id || ', name: ' || self.name ||
  ', description: ' || self.description);
end;
constructor function xxx_child return self as result is
begin
  return;
end;
end;
/
This type extends the parent by adding just a show method, showing the attributes.

Casting super-types to sub-types v.v.
Now the following works:
declare
  l_child xxx_child;
  l_parent xxx_parent;
begin
  l_child := xxx_child(id => 1, name => 'Martien', description => 'Dad');
  l_child.show;
end;

With as output:
Id: 1, name: Martien, description: Dad

However, what I wanted to achieve was something like this:
declare
  l_child xxx_child;
  l_parent xxx_parent;
begin
  l_parent := xxx_parent(id => 1, name => 'Martien', description => 'Dad');
    l_child := l_parent; -- PLS-00382: expression of wrong type
  l_child.show;
end;

But you cannot assign a super-type to a sub-type, when it's instantiated as a super-type.
You can do this though:
declare
  l_child xxx_child;
  l_parent xxx_parent;
begin
  l_parent := xxx_child(id => 1, name => 'Martien', description => 'Dad');
  l_child := treat(l_parent as xxx_child);
  l_child.show;
end;

In the assignment you explicitly tell Pl/Sql to treat the parent as a child object. You can do this only if the parent was explicitly instantiated as a child on before hand. If you instantiate it as a parent and try to treat is as a child you get the message: "ORA-06502: PL/SQL numeric or value error: cannot assign supertype instance to subtype".

Challenge: how to cast a super-type to a sub-type
My problem is thus: "How do I cast my super-type to a sub-type". When I create for example a customer account site in EBS using the BO-API's then I have no problem. I create a sub-type of my own extending the EBS BO and when I instantiate it as my own child object type and hand it over to the api, it should work. Since it's instantiated as my sub-type. But the problem lies in the update and get API's. For doing an update I should first do a get, retrieving the BO from EBS. The get-api will instantiate a type as an EBS BO-type. And I want to cast that to my own custom types to use my own methods. And basically this is impossible.

However, I found myself a trick. What I should do is to instantiate a child object with the attribute values of the parent. So if I have a collection of parents like:
create or replace type xxx_parent_tbl is table of xxx_parent
I can do:
declare
  l_child xxx_child;
  l_parent xxx_parent;
  l_parent_tbl xxx_parent_tbl;
begin
  l_parent_tbl := xxx_parent_tbl();
  l_parent := xxx_parent(id => 1, name => 'Martien', description => 'Dad');
  l_parent_tbl.extend;
  l_parent_tbl(l_parent_tbl.count) := l_parent;
  select xxx_child( ID,NAME,DESCRIPTION)
  into l_child
  from table(l_parent_tbl)    
  where rownum = 1;
  l_child.show;
end;
Now since this works fine, I want to do it more dynamically. The objects of EBS can have a large number of attributes and I don't want to name them explicitly. An upgrade of EBS would force me to upgrade my custom types too. Also I want my casting solution portable and reusable.

To get the attributes of my parent I can do the following query:
select attr_name
from user_type_attrs att
where att.type_name = 'XXX_PARENT'
order by attr_no;
The order by is important, since the default constructor has all the attributes as a parameter in this order.

To do an execute immediate selecting from my parent-table into my child object I need a sql statement like the following:
begin select xxx_child( ID,NAME,DESCRIPTION) into :1  from table(:2)  where rownum = 1;   end;
The where clause is not necessary because my collection will contain only one entry. But I added it to explicitly guarantee that the select into will result in only one row.
Now I create a function that generates this sql:
create or replace function xxx_get_type_cast_sql(p_parent in varchar2,  p_child  in varchar2) return varchar2 is
  l_sql varchar2(32767);
  cursor c_att(b_parent varchar2) is
  select *
  from all_type_attrs
  where type_name = b_parent
  order by attr_no;
begin
  l_sql := 'begin
  select '||p_child||'( ';
  for r_att in c_att(b_parent=>p_parent) loop
    if c_att%rowcount = 1 then
      l_sql := l_sql || r_att.attr_name;
    else
      l_sql := l_sql ||','|| r_att.attr_name;
    end if;
  end loop;
  l_sql := l_sql || ') into :1  from table(:2)  where rownum = 1;   end;';
  return(l_sql);
end xxx_get_type_cast_sql;

With this I can add another constructor to my child object with the parent object as a parameter:
create or replace type xxx_child under xxx_parent
(
-- Author  : MAKKER
-- Created : 17-03-2009 08:12:43
-- Purpose :

member procedure show,

-- Member functions and procedures
constructor function xxx_child return self as result,
constructor function xxx_child(p_parent xxx_parent) return self as result
)
/
create or replace type body xxx_child is

-- Version : $Id$
/* Member constructor, procedures and functions  */
member procedure show is
begin
  dbms_output.put_line('Id: ' || self.id || ', name: ' || self.name ||
  ', description: ' || self.description);
end;
constructor function xxx_child return self as result is
begin
  return;
end;
constructor function xxx_child(p_parent xxx_parent) return self as result is
  l_sql        varchar(32767);
  l_parent_tab xxx_parent_tbl;
begin
  -- Put parent in a collection;
  l_parent_tab := xxx_parent_tbl();
  l_parent_tab.extend;
  l_parent_tab(l_parent_tab.count) := p_parent;
  -- Create the sql
  l_sql := xxx_get_type_cast_sql(p_parent => 'XXX_PARENT'
  ,p_child  => 'XXX_CHILD');
  -- executed it dynamically
  execute immediate l_sql
  using in out self, in out l_parent_tab;
  return;
end;
end;
/

And now I can succesfully cast my parent to a child and use the childs methods:
declare
  l_parent xxx_parent;
  l_child xxx_child;
begin
  l_parent := xxx_parent(1, 'Berend', 'Son');
  l_child :=  xxx_child(p_parent=>l_parent);
  l_child.show();
end;

Which shows the following output:
Id: 1, name: Berend, description: Son
Conclusion and final thoughts
Casting from super-types to sub-types is not possible. Actually I'm not casting of course, but dynamically instantiating a child from the attributes of the parent. The way I do it requires that the child does not add attributes, or at least has a constructor with the same parameters as the parent. Maybe with some extra thinking I could make it a little smarter to work away this requirement.

At each instantiation the sql-statement is build using the function and the query on user_type_attrs. Actually you have to do it only once. If you encounter performance problems you can run the function once and copy and paste the outcome into the source of the object.

Use execute immediate constructs with care. In this case I'm sure it will not lead in particular performance problems. The sql resulting from the query will allways be the same as long as the parent-object does not change in attributes. Since I also use bind-variables in the execute immediate (through the 'using...' clause) the sql will be in the SQL-Area only once.

Thursday 12 March 2009

Oracle SQL Datamodeler - Import from Oracle Designer

Oracle SQL Datamodeler (OSDM) is a new tool that Oracle acquired a few months ago from a belgium company called cdw4all. This tool will be bundled with sql developer and could be the new Oracle tool for datamodeling.
There is no production version for the tool yet (thats scheduled for calendar year 2009), but there are early adopter versions available. The current early adopter version is EA2.
It is possible to import data from a designer environment and in this post I will describe how this works. I must say, the process of importing designer objects is straight forward.
I'll import from an Oracle Designer 10gR2 with versioning disabled. When you want to import from a versioned repository make sure that your objects are checked-in. I installed a new Designer repository, created a new application system with two entities (emp and dept with a relationship) and used the database design transformer to create two tables with a corresponding foreign key.




After that I started OSDM and chose File --> Import --> Oracle Designer model.
First you have to make a database connection:



Select the correct workarea



Select the application system(s) with the workarea



Select the objects you want to import (in this case entities and tables)


and the last step: Generate Design



We are finished. Let's look and the results. In the logical model (Entity Relational Model) I got my two entities and in the Relational Model (data diagram) I got my two tables:





Wow that was easy!

This little demonstration was made with Early Adopter version 1. I tried it with EA2 but in this version I could only import entities...

Friday 6 March 2009

Whitepapers on Darwin-IT.nl

Today I was busy with creating some how-to documents on managing our website. Our new website on www.darwin-it.nl. We use Typo3 on to support our website. Typo3 is a great, powerfull content management system that even runs on Oracle technology. For midsize companies that run on Oracle, Typo3 is also a great way to create an intranet.

I explored the way Typo3 can create a page with a list of file links. So I created a page to deliver some of our whitepapers we wrote in the past. Some of them are also mentioned on this blog. But are now grouped on one page.

You can find the overview on http://www.darwin-it.nl/index.php?id=whitepapers.

Wednesday 4 March 2009

Undebug Pl/Sql objects

Yesterday I wrote about how you can (re)compile an object with the debug option. Now it might be handy to recompile an object with the debug option off. But to do that automatically you need to know which objects are compiled with the debug option. For you don't want to recompile your whole schema do you? I don't want it, since my schema is the APPS schema of EBS and it contains a billion packages...

So how would you do that? I was looking for a neat view that gives me this information. And browsing through all the 'ALL_%' views I stumbled on the nice view: all_stored_settings. It contains all kinds of parameters about the stored pl/sql objects.
The one I wanted to query on is the plsql_debug parameter. Mark that the parameter names are lower-case while the True/False values are Upper-case.

Besides Owner, Object_Name, Object_Type columns it also has the Object_id column. So to getall the packages with the debug option on simply join the view with the all_objects view:
select obj.* 
from all_objects obj
, all_stored_settings ssg
where ssg.object_id = obj.object_id
and ssg.param_name = 'plsql_debug'
and ssg.param_value = 'TRUE'
and obj.object_type in ('PACKAGE', 'PACKAGE BODY')

So to recompile all these packages I changed the script of yesterday:
declare
  cursor c_obj 
  is select obj.* 
  from all_objects obj
  , all_stored_settings ssg
  where ssg.object_id = obj.object_id
  and ssg.param_name = 'plsql_debug'
  and ssg.param_value = 'TRUE'
  and obj.object_type in ('PACKAGE', 'PACKAGE BODY');
  l_command varchar2(32767);
  procedure put_line(p_text in varchar2) is
    l_text varchar2(32767) := p_text;
    l_line varchar2(255);
  begin
    while length(l_text) > 0 loop
      l_line := substr(l_text, 1, 255);
      dbms_output.put_line(l_line);
      l_text := substr(l_text, 256);
    end loop;
  end put_line;
begin
  for r_obj in c_obj
  loop
    begin
      l_command := 'alter package ' ||r_obj.object_name || ' compile';
      if r_obj.object_type = 'PACKAGE BODY' then
        l_command := l_command || ' body';
      end if;
      put_line('Executing: '||l_command);
      execute immediate l_command;
      put_line('Succesfully compiled '||lower(r_obj.object_type||' '||r_obj.object_name|| '.'));
    exception
      when others then
        put_line('Error compiling '||lower(r_obj.object_type||' '||r_obj.object_name));
    end;
  end loop;
end;


Of course this script can also easily be changed to add debug info for those packages that haven't yet...

Tables and synonyms in EBS R12

I'm busy with interfacing on E-Business Suite R12, Accounts Receivables and Account Payables, using the api's. I found that there are also neat get-api's to fetch the created customer-accounts and customer-account-sites.

I created an customer-account-site, using the api and all went well in creating the underlying objects. But getting the site using the hz_cust_acct_site_bo_pub.get_cust_acct_site_bo api gave me an error to give the proper identifiers. Strangely enough you have not only to pass the p_cust_acct_site_id but also a p_cust_acct_site_os and p_cust_acct_site_osr. You have to now that the os and osr suffixes stand for "origininal system" and "original system reference", that relate to the entry in hz_orig_sys_references table for the site (owner_table_name='HZ_CUST_ACCT_SITES_ALL'). I expected that passing only the account-site-id would be enough, but the api checks it against the original system reference.

I found that there is also a validation-query done on the hz_cust_acct_sites table, with a cursor where originally the org_id was a parameter, but it is not used in the where clause anymore. The object hz_cust_acct_sites turns out to be a synonym on hz_cust_acct_sites_all, but allthough the latter gives me rows, the first one doesn't. Strange, since hz_cust_acct_sites is certainly not a view.

It turns out that IN EBS R12 a row level security policy is applied on the table, leveraging the VPD possibilities of Oracle DB 10g. And under water for the synonyms on the '%_all' tables an extra where clause condtion is applied using the org-id. This can be seen for example with:
select * from all_policies
where object_name = 'HZ_CUST_ACCT_SITES'
This shows that the mo_global.org_security function is used to return an extra where-clause-condition on org-id for the table.

But how to set the orig-id?
There are several options to do that. Earlier with 11.5.9 I created a package that set the responsibility for me from the initialisation block. Just using a function in that package (eg. get_responsibility) causes to execute the initialisation block (the last block in a package body) only once. When I call the get_responsibility function from the initialisation block of every package, I'm asured that the responsibility is set only once. The responsiblity can be set using the fnd_global.apps_initialize api. This needs some setup info ( user id , responsibility id, responsibility application id) and then you need to be sure that you have the appropriate organisation unit and thus org-id set.

For now I only have to have the orig-id set. I found with some tips that it is enough to do:

begin
mo_global.set_policy_context_server(p_access_mode => 'S'
,p_org_id => 204);
end;
As access-mode you can give up 'S' for single, 'M' for multiple and 'A' for all. So you can set multiple org-id's. Well and the org-id of course applies to the value in hz_cust_acct_sites_all.org_id.

Executing the block above before doing your query would give results from hz_cust_acct_sites. And thus the get-api would have to give the appropriate result.

There are many other '%_ALL' tables with corresponding synonyms that work the same way.

Tuesday 3 March 2009

Apex 3.2: My first Oracle Forms conversion

I developed Oracle Forms applications for many years. I also did some minor APEX projects. I read about the ability of Apex 3.2 to migrate Oracle Forms applications and I was wondering how this is working.

First I upgraded my Apex 3.1.2. to 3.2. This was quite easy. Download the zipfile from OTN, extract it, started the good old sqlplus against my local XE database, ran a script and everything was ok.
Then I started to read the manual about the migration process. The picture that I found there was very clear:


and there was a little roadmap as well.

First I had to create a little form. I decided to create an extremely simple form. I wondered how the migration works I'm not (yet) interested in migrating complex forms.
(when I look at the image above this should be possible as well. Pll, olb and mmb files can be migrated too).
Using the Oracle Forms wizards a simple form on dept was created. When I run the form it looks like this:



A simple CRUD (create,retrieve, update, delete) screen on Dept. The resulting fmb is darwin.fmb.

The first step in the migration process is creating an XML file of the fmb file using frmf2xml.bat.
This command does the job:
C:\oracle\10gR2\bin\frmf2xml.bat darwin.fmb

After this I started APEX (I allready had a workspace and a user) and on the righthand side of the screen there is a new option: Application Migration



After clicking this I had to create a new project and after that I had to import the previously created darwin_fmb.xml file:


After pressing [next], I get a summary of the imported XML file:



The original form consist of 1 block, 1 base table block (this is nice, the converter is able to dsitinguish beteen base table and non base table blocks!) and three items. In this form are no triggers, no list of values, no alerts and no program units. In this simple case I'm not interested in the last objects but I have to admit that I'm curious what will happen with these objects.

I have the option to upload another XML file but I choose for creating the application.

This results in the following steps:

[Next]

[Next] Note: By default Apex creates a Report followed by a Table layout


A tabular Form


With the first theme.



And after a while there is the application.
When I run it I see that the application is migrated very smoothly. This looks very promising for more complex forms!



Monday 2 March 2009

Debug Compile

The very main reason that I got to use and love Pl/Sql Developer years ago is it's debug capabilities. Back then I used Toad freeware like my colleagues while we had a PSD-license on the project. I found out that PSD had a debugger, a tool that I missed in writing Pl/Sql. Surprisingly I thought, because in my student-years I programmed Turbo Pascal under Dos and even that had a powerful debugger.

Before you can debug you have to add debug info to your pl/sql. By default Pl/Sql Developer compiles the packages with the debug option on. Until last week I did not know what the appropriate statement-clause was. But since PSD does it for me, I did not care.
It turns out to be:
alter package compile debug body

I'm currently working again with the Pl/Sql public api's of E-Business Suite. And I have to find out what particular values I have to pass. So being able to debug into the EBS packages turns out to be helpfull. However recompiling the ebs-packages invalidates referencing packages. Now that's not too much of a problem, but some of them fail with a "PLS-00123:program too large" error.
I was very surprised since I thought that was a pre-8i error (we're working with DB10gR2).

I did not dug to deep in it, but found it very unconvenient to find out what packages could and which couldn't be compiled with debug option. So today I created a script, actually a script that I created many times before, but now in a little smarter way.

It uses a cursor to list all the invalid packages and package-bodies that are invalid. In the loop it tries to compile them with the debug option. If that fails than it tries to compile them without the debug option. If that fails again, then there is something more problematic wrong.

Here is the script:
declare
  cursor c_obj 
  is select * from all_objects obj
  where obj.object_type in ('PACKAGE', 'PACKAGE BODY')
  and obj.status = 'INVALID';
  l_command varchar2(32767);
  procedure put_line(p_text in varchar2) is
    l_text varchar2(32767) := p_text;
    l_line varchar2(255);
  begin
    while length(l_text) > 0 loop
      l_line := substr(l_text, 1, 255);
      dbms_output.put_line(l_line);
      l_text := substr(l_text, 256);
    end loop;
  end put_line;
begin
  for r_obj in c_obj
  loop
    begin
      -- First try with debug
      l_command := 'alter package ' ||r_obj.object_name || ' compile debug';
      if r_obj.object_type = 'PACKAGE BODY' then
        l_command := l_command || ' body';
      end if;
      put_line('Executing: '||l_command);
      execute immediate l_command;
      put_line('Succesfully compiled '||lower(r_obj.object_type||' '||r_obj.object_name|| ' with debug option.'));
    exception
      when others then
      begin
        -- Then try without debug
        l_command := 'alter package ' ||r_obj.object_name || ' compile ';
        if r_obj.object_type = 'PACKAGE BODY' then
          l_command := l_command || ' body';
        end if;
        put_line('Executing: '||l_command);
        execute immediate l_command;
        put_line('Succesfully compiled '||lower(r_obj.object_type||' '||r_obj.object_name));
      exception
        when others then
          put_line('Error compiling '||lower(r_obj.object_type||' '||r_obj.object_name));
      end;
    end;
  end loop;
end;