Avoiding ORA-04068

This is a nasty error that can occur when a package is recompiled during an Oracle session. I work now in an environment where this occasionally happens and sometimes minutes of data entry are lost.

The same statement will execute without problem when re-issued but for this the software needs to be  thoroughly revised. 

The ORA-04068 problem is explained very well in a number of posts like Avoiding ORA-04068: existing state of packages has been discarded. One of the solutions is avoiding state fullness of packages, i.e. no global constants or variables. When inspecting the packages in the system I found that quite a number of packages only had global constants and no variables. At first I thought about moving the constants to a separate package. Then only changes to this package would cause a ORA-04068. The drawback of this solution is that code throughout the packages would have to be modified. 

Another solution is to replace the constants by functions. This way there is no global state and no code to be changed when the functions get the same name as the constants. This also works for public constants. So

 
 LF      VARCHAR2(10) := CHR(10);

is changed to

 
  FUNCTION LF RETURNS VARCHAR2 IS
  BEGIN
    RETURN(CHR(10));
  END;

To make things even easier I have coded a conversion utility where you can input your constant declarations a retrieve the corresponding functions. You find it here.

Enjoy!

The advantage of fonts over images

-->

Imagine your boss wants you to create an application with iconic buttons, He has an image that is to be used for menu buttons, and for the landing page, and as an image on some pages.

You start editing the image with photoshop creating the neccesary images. After some time you are ready. Some days later your boss says, there has bene a feedback session with the user and they want the buttons changed, different color with gradient effect and rouneded corners, and they should be a bit larger:

Again you start up PhotoShop and change the images. And you are lucky if this is the only time the specifications change.

All this image editing could be avoided when you would use images from a font. In a previous post I described how to create iconic buttons using existing fonts.  But it is also possible to define your own custom font containing the images you need ( how do you think Font Awesome is made?). 

Above appearances are all images generated using CSS and only one character in a custom font ( unfortunately I am not able to load the font in this blog).

The images you see in this blog are font characters styled with CSS. It is very easy to change the color and size of the image or the background, just change the CSS properties.

When you have a single color image you can incorporate it in a font. You need the image in SVG format, You can use tools like Inkscape to convert your image to SVG.

With your images in SVG you can build a font from them using icomoon.

It takes some time and effort to creatie a custom font. I did not have the time nor the patience to do it so I asked my 17-year old son Michiel to do it for me. He is  very good at this kind of thing (amongst others) and he has introduced me to many interesting new developments, like AngularJS and NodeJS. If you can use his help you can reach him here.

Splitting an Apex report into several columns

The other day I was asked to create a page where a large number of properties should be connected to an object. I created a report consisting of the rows with the property name and a check box. This resulted in a very long and narrow report. All of the data could not be seen without scrolling. So I decided the report should be split into a number of columns. And I wanted to avoid special constructs in Apex and SQL (I have done that before and it was very compex). So I decided to use JavaScript to move the rows. By using JavaScript the Apex transaction mechanism keeps functioning, Apex does not know or notice that anything in the layout of the page has been changed.

I want to achieve the right side situation starting with the left report ( that by the way continues a long way below where the picture ends)

With JavaScript a number of extra table elements are created on the same level as the table element containing the report. The thead element of the original table element will be copied to the other table nodes to provide the same heading and a tbody element will be created to house the rows. After that the rows can be distributed among the tables. With CSS the table elements are positioned next to each other. The only limit to the number of columns is the available horizontal space.
You should call the JavaScript function below at startup or in the refresh event when using partial page rendering.

function reportToColumns ( tabSelector, numCols )
{ var numRows = 0;
  // bereken aantal rijen per kolom
  var rowsPerCol = Math.ceil( ( $(tabSelector+' tr').length-1 )/numCols);
  var baseName = 'reportColumn'; 

  $(tabSelector).addClass('reportColumn');
  // maak kolommen aan
  for ( i = 2; i <= numCols; i++)
  { $(tabSelector).parent().append('
');
    $(tabSelector+' thead').clone().appendTo( $('#'+baseName+i) );
    $('#'+baseName+i).append('');
  }

  // verdeel de rijen over de kolommen
  var id = 2;
  var dest = '';
  $(tabSelector+' tr').each( function(index)
  { if (index > rowsPerCol)
    { dest = '#'+baseName+id+' tbody';
      $(dest).append( $(this) );
      numRows = numRows + 1;
      // wissel de kolom als rijen per kolom is bereikt
      if ( numRows >= rowsPerCol) 
      { id = id + 1;
        numRows = 0;
      }
    }
  } ); 
}

I used classes for the layout of a specific report. You might need to change that according to your needs.
To make sure the div’s are placed next to each other a little bit of CSS is needed:

.reportColumn {
    display: block;
    float: left;
    margin-right: 30px;
    position: relative;
}

You can see this in action right here.
Note 03-10-2015: The software is dependent of the template used. The link above is suited for Theme 25. You will find a version for the Universal Theme here.

This method can be used on either Apex Reports or Apex Tabular Forms. In fact it is not a specific Apex solution and can be used on any HTML table. Happy Apexing

Removing old public Interactive Reports

By saving public Apex Interactive Reports you can offer various custom reports to users without programming. The user opens the page and can choose the reports from the Reports drop down list.
New reports can be added by defining the new report and then exporting the application and importing it in the production environment. Changing existing reports can in some cases cause double entries in the Reports drop down list. This is because the changed report has received a new ID and therefor the original report is not overwritten.
The following procedure can be run for each relevant page before importing the application. It deletes the existing public reports for a specific application and page.

create or replace 
  procedure delete_public_ir_report
                ( p_app_id      in  number
                , p_page_id     in  number
                ) is
begin
  dbms_output.put_line('Deleting public IR reports for application '||p_app_id||' page '||p_page_id);
  for r in ( select * 
             from   apex_application_page_ir_rpt
             where  application_id = p_app_id
               and  page_id        = p_page_id
               and  report_type    = 'PUBLIC'
           ) 
  loop
    dbms_output.put_line('Deleting report '||nvl(r.report_name,'with id '||r.report_id));
    apex_util.ir_delete_report(r.report_id);
  end loop;
end;
/

This procedure works in my situation but each situation is different. Be careful to apply this procedure to your environment and test the results thoroughly before applying it to a production environment.

Happy apexing

Automatically generating images for styles

Lately I have been developing a kind of SaaS website with Apex. Several companies were to be represented in this website and each company wanted to have its own logo and colors. Should be a piece of cake with CSS, shouldn’t it?

But the requirements said rounded corners for tabs and buttons, and support for IE7. That rules out HTML5 :-(.
Using plugins for rounded corners I was not able to get a stable result, so I turned to the good old sliding windows solution using background images. This way the website was stable and reliable. Below two examples of look-and-feel using the same HTML.

The only drawback of this was that a set of 12 images was needed for each company. Creating these images manually using drawing software is a time consuming, tedious and error-prone process.
So I created a webpage containing SVG-images. The webmaster could enter the colors and generate the images. Then these images could be grabbed and cut on the surrounding dotted lines in GIMP. Sounds a lot like kindergarten, doesn’t it? This was already better, but still a tedious and error-prone.

Then my 15 year old son –who is a computer programming addict like myself ;-)- came up with a solution. The open source program ImageMagick is able to convert images including SVG to PNG. The only challenge was that it runs on the command line and Apex is webbased!

The solution is to generate a Windows BAT-file that can be run on a client where ImageMagick is installed. In the BAT-file the SVG images are generated with ECHO-statements. After this ImageMagick is called to do the conversion to PNG. With this solution the neccesary images can be generated in a few minutes, just the time needed to enter the colors and to execute the BAT-file. Below is an example of the generation of the image for a button background.

echo <svg height="22px" version="1.1" width="400px" xmlns="http:www.w3.org2000svg">                  >button.svg
echo </svg>      >>button.svg

convert button.svg button.png
convert button.png -transparent white button.png

The echo statements generate the 2 lines SVG file. The first convert statement converts the SVG file to a PNG. The second convert command changes the white background to transparent in order to use the button on other than white backgrounds.

Happy Apexing! (but you can use this for any kind of website)

Weekday Numbers in Apex sensitive to Application Primary Language

The weekday in Apex is dependent on of the language set in the Application Properties. For that you cannot rely on TO_CHAR(datevalue,’d’), but should use TRUNC(datevalue)-TRUNC(datevalue,'iw')+1 to get ISO weekdays, that are independent of the language set. SQL Workshop did not show differences because the language for SQL Workshop remained the same. This made the analysis more difficult as the results of queries in Apex and SQL Workshop were inconsistent.

I noticed this after having changed the language from English (us) to Dutch(nl) in an application for registering the hours spent and declared on assignments. The screen for registering the declared shows an enterable fields for each day of week to accept the hours worked that day. This screen is showed in the picture below.

Before the change of language this page worked flawlessly. After having set the language to dutch the page did not function like I did before.
At the moment I have the luxury of working four days a week ;-), so I had filled in 8 hours for Monday up to Thursday. After saving this data the application returns to the menu. When I retrieved the page once more, I noticed that the screen showed Sunday up to Wednesday as days.
The data was stored using an offset from the first day of the ISO week. As the ISO week returns the same value regardless of the language the right data were saved. To retrieve the data a view is used in which I used SUM(DECODE(TO_CHAR(datefield,’d’)),2,hours) AS hours_monday to determine the number of hours worked on Monday. With the language set to English, this yielded the correct result. After the language was changed to Dutch, the value for Tuesday was retrieved, as tuesday is the second day in the ISO week.
The number of the weekday differs on the basis of the language. This may not be a surprise, but it was hard to find, because as it was a remote site I was using SQL Workshop to perform SQL statements to check. And apparently SQL Workshop is set to English, regardless of the language of the application (sounds logical). This meant that in SQL Workshop I still got the right results, while in the application, the days were shifted.
The solution was to calculate the weekday number by referring to the start of the ISO-week. The latter number is independent of the language used. We subtract the start of the ISO-week from a given date (use TRUNC to eliminate the time fraction) and add 1 to get the weekday number. The query

SELECT TRUNC(sysdate)-TRUNC(sysdate,'iw')+1 FROM dual

will yield the correct (ISO) day of the week.

As you may have noticed I am developing an Apex application for iPhone. For this I use iWebkit, which results in really nice and usable mobile applications. I hope to blog more about this in the near future.

Having fun Apexing

Retaining session state with disabled items

This morning I was developing a page on which some input items could be prefilled from a Before Region Process and should be disabled if containing a value. After some Googling I soon found out that this could be accomplished by setting the disabled attribute.
And indeed, the items with a value appeared correctly with a gray background and non-enterable. But when I submitted the form, the value of the items appeared to be empty.

After half an hour of trying all kinds of things I, examining session state and going through the debug output, I finally found the solution:
- when an item is disabled it's value is set to empty...

So the solution was not to use the attribute disable, but the attribute readonly in combination with the style "background-color:#CCCCCC".

With this solution, the session state is retained.
Visually the result is the same, the only difference is that the cursor can be placed inside the item, but no changes to the content can be made.

Happy Apexing