Wednesday, 2 July 2008

PHP: Number of rows from query

The idea comes from the developers of Oracle ADF, I have seen it work on JDeveloper and I thought that I can use it in PHP. The trick here is to place the entire query inside a SELECT count(*) from ($query) statement.

Once you know what to do implementation is as easy as witting the code below. The only comment is that the function references an already initialized external mysqli class variable that connects to the database, which if you feel like it may also be passed as a function parameter.

    /**
      * returns the number of records  of the query passed as parameter 
      */
    function numRecordsOfQuery( $a_query) 
    {
        global $mysqli;

        $count_query = " 
                   SELECT count(*) as TotalLines 
                     FROM ($a_query) MyQuery";
        $result = $mysqli->query($count_query);
        // echo $count_query;
        $row = $result->fetch_array( MYSQLI_ASSOC);
        $numRecords = $row['TotalLines'];
        $result->close();
        
        return $numRecords;
    }

Note The MyQuery references is mandatory. MySQL will return an error if the enclosed query is not referenced.

Wednesday, 18 June 2008

CentOS: Manual install of plugins for Mozilla Firefox

This is just a note to myself and everyone else in the same spot.

Flash Player

Manual installation of the Flash player plugin for a new Firefox installation is done like this :

[root@lxbakalidis ~]# cd /opt/mozilla/firefox/plugins
[root@lxbakalidis plugins]# ln -sf /usr/lib/flash-plugin/libflashplayer.so 
libflashplayer.so

Detailed instructions for installing flash-player from Adobe are available here.

Java

The java plugin gets installed by linking libjavaplugin_oji.so located in $JAVA_HOME/jre/plugin/i386/ns7/ in your plugin dir.

Real Player

For real Player the plugin name is nphelix.so and the plugin location is /opt/RealPlayer//mozilla/.

Friday, 30 May 2008

Oracle ADF: Enriching the actions of a ViewObject

Based on the technique described two posts back regarding creation of a custom ViewObject that offers ready to drag actions, I now give you another simple yet very handy function. The showAllRows() function when added to the AB_BaseViewObject methods, allows you to create a button in a search page that removes all view criteria and executes the current query, very useful for cases when uses enter lots of view criteria and then need to display the entire contents of the entire row with a single click.

    public void showAllRows()
    {
        clearViewCriterias();
        executeQuery();
    }

Remember that in order for the action to appear in the JDeveloper Data Control Palette like in the following picture, you must include the showAllRows() function to the view objects client interface.

Wednesday, 21 May 2008

Oracle ADF: Duplicating a ViewObject's current row

I wanted to add a Create Like functionality that would allow a new row to be inserted in a ViewObject then drive the user to an edit page where he or she would make ant changes required for valid insertion of the new row to the database and avoid having to re-enter everything from scratch.

In order to accomplish this I created the following method in the ViewObject's implementation class.

import oracle.jbo.AttributeDef;
import oracle.jbo.Row;
import oracle.jbo.ReadOnlyAttrException;
import oracle.jbo.TooManyObjectsException;

. . .

    /**
     * create and insert a row similar to the current
     */
    public void createLike()
    {
        // get the current row to dublicate
        Row currentRow = getCurrentRow();
        if (currentRow == null)
            return;
            
        // create new one
        Row newRow = createRow();
        
        // get the attribute values for the current attribute
        Object[] myValues = currentRow.getAttributeValues();
        
        for (int i = 0; i < currentRow.getAttributeCount(); i++)
            try {
                AttributeDef d = getAttributeDef(i);
                    
                if (d.getUpdateableFlag() != AttributeDef.READONLY)                
                    newRow.setAttribute( i, myValues[i]);                
            }    
            catch (ReadOnlyAttrException roe) {
                continue;
            }
            catch (Exception e) {
                // it is almost certain that the primary key will be
                // violated so the user must do something in the 
                // page following execution of the method.
                if (!(e instanceof TooManyObjectsException))
                    System.out.println(e.toString());
            }
            
        insertRow(newRow);        
    }

The code works fine and copies the values all columns from the current row into a new one except that of the last column that constitutes the primary key. That way the user will have to make the appropriate changes to the new record, so it can be inserted correctly.

There are a few points worth mentioning in this apprach

  • if your view object contains attributes from multiple entity objects then the the key attributes of the referenced entities that gets inserted by the framework appear to be updatable -- hence the catch (ReadOnlyAttrException roe) line. I have brought this to the JDeveloper forum but got no suggestions.
  • Using the above technique requires that you expose the createLike() method to the ViewObject's client interface. See the previous post for details.
  • The most probable use of this method will be to place a command button into an <af:tableSelectOne> facet in order to drive your users to an edit page with values already set. On most occasions the edit page will contain a commit button that will perform the final post. My suggestion is to use a backing bean to invoke the commit action like I discuss in a previous article named ADF/JSF Avoiding uncaught exceptions after commit that will prevent the controller layer to change back to the browse page in case of any errors.

Thursday, 15 May 2008

Oracle ADF: A view object with cancelInsert and cancelEdit operations

I have already discussed the problem of providing a cancel button in an ADF/JSF edit page. Here is an alternative that allows the usage of a base view object that provides both a cancel edit and a cancel insert method.

I have come to believe that this method is the most appealing so here is a brief outline of how to use it :

First create the following class in the queries package of you model application.

package myapp.model.queries;

import oracle.jbo.Row;
import oracle.jbo.server.ViewObjectImpl;

public class AB_BaseViewObject
    extends ViewObjectImpl {

    public AB_BaseViewObject()
    {
    }
    
    /**
     * Refresh the view obejct's current row from the dataset practically 
     * undoing any changes.
     */
    public void cancelEdits() 
    {
        Row r = getCurrentRow();

        if (r != null) 
            r.refresh( Row.REFRESH_WITH_DB_FORGET_CHANGES);
    }
    
    /**
     * Undo changes and get rid of new rows
     */
    public void cancelInsert()
    {
        clearCache();      
    }
}

Next create Java Classes that extend the AB_ViewObject for all the view objects that require editing in your project. Publish the cancelXXX() methods on the view interface like in following picture.

Finally create buttons on the edit or create pages in the ViewController project that call the methods.

Wednesday, 14 May 2008

Amarok on CentOS

Today I found the time to compile amarok on my x86_64 CentOS 5.1 machine at work. It was a long time since I compiled any version of amarok, thanks to Packman and openSUSE, but it worked. The truth of the matter is that the sound produced by the xine engine is a bit over bassed, still I enjoy very much the amarok experience that I may very well start getting used to it.

Anyway, here are the steps I followed. After installing the amarok sources in /usr/src I run

[root@lxbakalidis amarok-1.4.9.1]#yum install gcc-c++.x86_64
libstdc++-devel.x86_64 kdelibs-devel.x86_64 alsa-lib-devel.x86_64
zlib-devel.x86_64 taglib-devel.x86_64 ruby-libs.x86_64 ruby.x86_64
xine-lib-devel.x86_64 libXv-devel.x86_64 libXvMC-devel.x86_64
ruby-devel.x86_64 sqlite-devel.x86_64

Then I did a

[root@lxbakalidis amarok-1.4.9.1]# ./configure --disable-debug

Finally I run make and make install and there I have it.

PS 1: Thanks for the tip bmcl

PS 2:When compiling on the new CentOS 5.2 you will additionally need package libutempter-devel. A simple ...

# yum install libutempter-devel

... is enough

.

Wednesday, 23 April 2008

Java: Formatting a Number Using a Custom Format

The example comes form the The Java Developers Almanac 1.4 and I am adding it here to serve only as a quick reference note.

    // The 0 symbol shows a digit or 0 if no digit present
    NumberFormat formatter = new DecimalFormat("000000");
    String s = formatter.format(-1234.567);  // -001235
    // notice that the number was rounded up
    
    // The # symbol shows a digit or nothing if no digit present
    formatter = new DecimalFormat("##");
    s = formatter.format(-1234.567);         // -1235
    s = formatter.format(0);                 // 0
    formatter = new DecimalFormat("##00");
    s = formatter.format(0);                 // 00
    
    
    // The . symbol indicates the decimal point
    formatter = new DecimalFormat(".00");
    s = formatter.format(-.567);             // -.57
    formatter = new DecimalFormat("0.00");
    s = formatter.format(-.567);             // -0.57
    formatter = new DecimalFormat("#.#");
    s = formatter.format(-1234.567);         // -1234.6
    formatter = new DecimalFormat("#.######");
    s = formatter.format(-1234.567);         // -1234.567
    formatter = new DecimalFormat(".######");
    s = formatter.format(-1234.567);         // -1234.567
    formatter = new DecimalFormat("#.000000");
    s = formatter.format(-1234.567);         // -1234.567000
    
    
    // The , symbol is used to group numbers
    formatter = new DecimalFormat("#,###,###");
    s = formatter.format(-1234.567);         // -1,235
    s = formatter.format(-1234567.890);      // -1,234,568
    
    // The ; symbol is used to specify an alternate pattern for negative values
    formatter = new DecimalFormat("#;(#)");
    s = formatter.format(-1234.567);         // (1235)
    
    // The ' symbol is used to quote literal symbols
    formatter = new DecimalFormat("'#'#");
    s = formatter.format(-1234.567);         // -#1235
    formatter = new DecimalFormat("'abc'#");
    s = formatter.format(-1234.567);         // -abc1235

Thursday, 20 March 2008

Oracle ADF Two action listeners in one action

Some time ago, I run into a situation when I wanted to add two action listeners inside a single command element but I realized that this was just not possible. When I asked about that in the JDeveloper forums somebody told me that this could only be accomplished in code, but at that time my knowledge was just not enough

A couple of days ago I run into the same problem. I had a view page that when accessed wanted to know two things:

  • First was the ${row.rowKeyStr} of the row to display and ...
  • Next a session scope boolean parameter called #{sessionScope.returnHome} that indicated whether to return the user to the application home page or to another (the search in that case) page.

To summarize, I ended up with the a button called View record in two different pages both having a navigation case (also called view) linking to the view record page. in the application JSF diagram.

On both cases the expression #{row.rowKeyStr} had to be recorded in the JSF session scope along with a value for the returnHome flag that had to be true on one occasion and false on the other..

Following is the code I used to perform this. First the base class :

package myapp.view.backing;

import javax.faces.application.Application;
import javax.faces.context.FacesContext;
import javax.faces.el.ValueBinding;

class backing_SRListBase {

    public backing_SRListBase()
    {
    }
    
    public String viewButtonBase_action( boolean returnHome)
    {
        // access the faces context
        FacesContext fc = FacesContext.getCurrentInstance();
        Application app = fc.getApplication();
        
        // on both case row.rowKeyStr points to the current row key
        ValueBinding vbActRowKey = app.createValueBinding("#{row.rowKeyStr}");
        String rowKey = (String )vbActRowKey.getValue(fc);
                
        // in that case we must save that in sessionScope
        ValueBinding vbSessionScopeRowKey = app.createValueBinding("#{sessionScope.rowKey}");
        vbSessionScopeRowKey.setValue( fc, rowKey);
                
        // also save the return home boolean value
        ValueBinding vbReturnHome = app.createValueBinding("#{sessionScope.returnHome}");
        vbReturnHome.setValue( fc, new Boolean( returnHome));
        
        return "view";
    }
}

Finally, the backing bean for each of the two pages looks like this

package myapp.view.backing;

public class backing_OpenSRList 
    extends backing_SRListBase {

    public backing_OpenSRList()
    {
    }

    public String viewButton_action()
    {
        return viewButtonBase_action( false);
    }
}

Friday, 7 March 2008

Firefox 2.0.0.0.x on CentOS 5.1

Thanks to people like Diezel who posted the original article on nixadmins.net, we now know that in order for Firefox 2.0 to run on a CentOS (or Red Hat EL) version 5.1 machine, we need to install compat-libstdc++-33.

Using yum this is done as easy as entering

#yum install compat-libstdc++-33

s the root user. The rest is trivial.

Friday, 29 February 2008

Deploying Oracle ADF: What did I learn about caching

Our company's production application server is located at a site different than the one we do our development. Whenever we deployed a new application users were always complaining about long response times, but we usually blamed that on the network. When we moved away from jsps and started working with JSF pages things really got out of hand.

Response times took as much as 10 seconds when application pages where accessed via WAN, rising the user complaint level to maximum levels.

Additionally the network link appeared to be utilized below 80% and then application server response times as shown at OC4J administration website where much below 0.045 sec.

First thing we tried was to use Oracle's ADF Cache library as explained in Chapter 23 of the ADF Programmers guide for Forms/4GL Developers, without any success. To be precise the library was indeed incorporated into the application but the server response time was still as long as 9 or 10 seconds.

It was then when we asked for help from Oracle Support. So thanks to Kavintha we learned the following :

  • First thing we learned was that the problem was indeed related to caching. Every JSF page created using Oracle ADF utilizes a Javascript file named Common10_1_3_3_0.js with a file size of 120 KB. This is an external script referenced at every page. The problem was that this script was never cached so users had to download it every time causing the delays mentioned above. If this file -- and all related page images -- are cached, then the usual size of an ADF/JSF generated HTML code ranges between 1 and 2 KBytes.
    Enabling full caching options on the Oracle Apache Server can be done by uncommenting the # CacheNegotiatedDocs line of the httpd.conf file located in $ORACLE_HOME/Apache/Apache/conf.
  • Next thing was that if an application is protected by Single-Sign-On then in that case, no matter whether CacheNegotiatedDocs is commented out or not, Apache will always send no-cache headers practically undoing whatever the Apache configuration or the ADF Faces Cache library do.
    The way to allow caching for SSO protected applications is to modify mod_osso.conf located in $ORACLE_HOME/Apache/Apache/conf so the entry for each application looks like this :
            <Location /ProductionDelays>
                    OssoSendCacheHeaders off 
                    require valid-user
                    AuthType Basic
            </Location>
    
    More information about how to to disable the caching headers sent by SSO is available on Metalink note 226119.1. Also you may have a look at a one of my previous postings regarding using SSO which is available through here
  • Last thing we learned about this caching business was that when a JSF application is accessed via SSL, then again caching issues still apply. Firefox will disable caching by default whenever the user loads an https:// URL. IE on the other hand does cache SSL pages by default but this setting can change by a policy set by your system administrator. I realize that in this case Firefox uses the safest approach but unfortunately this will produce very poor results if ADF/JSF pages are to be accessed.
    Enabling disk caching of SSL pages in Firefox can be done by changing the value of the browser.cache.disk_cache_ssl parameter available from the about:config URL.
    IE on the other hand has an option called Do not save encrypted pages to disk available in the Security section of the Advanced internet properties tab. If you want IE to cache content from SSL pages this option must be unchecked.

Wednesday, 27 February 2008

Changing the JDeveloper IDE font size

If you are like me and wish that the JDeveloper IDE displayed using a bigger font, then thanks to Khaled from Oracle support all you need to do is shut down JDeveloper, and then add a line like the following in the file ide.properties located in \jdev\system\oracle.jdeveloper.10.1.3.X.Y.

Ide.FontSize=14

Finally start JDeveloper and you are done.

Notes

  1. The actual X, Y values in the oracle.jdeveloper.10.1.3.X.Y may vary depending on your version of JDeveloper. In 10.1.3.3 X=41 and Y=57, while in 10.1.3.4 X=42 and Y = 70. Anyway the safest approach is to have a look at the Oracle IDE version in the versions tab of the JDev about box as show in the image below.
  2. Needless to say that the same goes for SQLDeveloper. The only difference is that he file to edit is located in ~/.sqldeveloper/system/oracle.sqldeveloper.1.2.1.3213. If using SQLDeveoper version 1.5 that comes with the new JDev11g look and feel then again the file chek is .sqldeveloper/system1.5.1.54.40/o.sqldeveloper.11.1.1.54.40/ide.properties.
  3. On Jdeveloper 11g the ide.properties file location is ~/.jdeveloper/system11.1.1.0.31.51.56/o.jdeveloper. Once more the actul path may be determined by looking at the Oracle IDE version from the about box.

Saturday, 23 February 2008

Oracle 11g How to determine the database character set

This is only a quick note for something I keep forgetting. So to determine the oracle database character set use the following simple SQL statement.

   select *
      from v$nls_parameters
      where parameter='NLS_CHARACTERSET'

Monday, 11 February 2008

Oracle 10g Killing open sesions

We have a client program that leaves lots of open sessions. These sessions take up server resources and we wanted to find a way to locate and kill them.

Thanks to Natasa Oikonomidou from Softworks who provided the code, I ended up with the following solution:

Create a the following procedure on the sys schema.

create or replace
PROCEDURE inactive_session_killer
IS
  now DATE;
BEGIN
  now := sysdate;
  FOR rec IN
    (SELECT sid,
       serial#,
       username,
       schemaname,
       osuser,
       terminal,
       machine,
       PROGRAM,
       status,
       type,
       saddr,
       logon_time,
       now - last_call_et / 86400 last_activity
     FROM v$session
     WHERE username = 'XXXXX'   -- user name of open session "leaver"
           AND type = 'USER')
  LOOP

    IF rec.last_activity <( now  - 8 / 24) THEN
      -- inactive for more than 8 hours
      -- you can keep a log table here: inserrt into ...
      EXECUTE IMMEDIATE 'alter system kill session ''' ||  
                                        rec.sid || ',' || 
                                        rec.serial#    || 
                                        ''' immediate';

    END IF;

  END LOOP;
END;

Using SQLPlus or SQL Developer execute the following

 
VARIABLE jobno number;
BEGIN
   DBMS_JOB.SUBMIT(:jobno, 'inactive_session_killer;', SYSDATE, 'SYSDATE + 1/24');    -- runs every hour
   commit;
END;

... and that does it.

Wednesday, 23 January 2008

Oracle IAS 10g 10.1.3.1 Installation on Enterprise Linux 5

Yesterday I completed my first installation of Oracle SOA Suite 10.1.3.1 on Oracle Enterprise Linux 5. In order to complete the installation I followed Metalink Note:465159.1, which explains the exact procedure.

Now the only tip I can give you, is to make sure that the /etc/hosts file contains a line that resolves the locally or DNS assigned computer name and domain to the machines network IP address.

My up until now experience has been with SuSE systems only, so on my machine the /etc/hosts/ file always contains a line like

10.5.1.8 lxbakalidis.shelman.int lxbakalidis

An OEL installation hosts file only contains

# Do not remove the following line, or various programs
# that require network functionality will fail.
127.0.0.1       localhost.localdomain   localhost       lxBakalidis
::1     localhost6.localdomain6 localhost6

... but this is not enough. The installation completed successfully only after I added the line that resolved the external IP to my computer name. So the final etc/hosts file looked something like this :

# Do not remove the following line, or various programs
# that require network functionality will fail.
127.0.0.1       localhost.localdomain   localhost       lxBakalidis
::1     localhost6.localdomain6 localhost6
10.5.1.8 lxbakalidis.shelman.int lxbakalidis

The symptoms before that were: First OUI was taking a very long time to do the file copy operation and next when at some point installation would complete, then two out of three configuration assistants would fail.

Friday, 11 January 2008

ABAP Get the value of a date type characteristic

When an object is classified with characteristics of type date then, using BAPI BAPI_OBJCL_GETCLASSES these characteristics are returned as numeric. That means that entries for date characteristics are found in the valuesnum array which --as you remeber from a couple of postings back -- is defined to be a standard table of bapi1003_alloc_values_num.

When I first saw this I thought that the actual date value could be acquired by assigning the value_from part of the returned bapi1003_alloc_values_num structure to a date variable, but it was not at all like this. The actual value is stored like an integer representing the ABAP internal date fromat which is YYYYMMDD. So an integer value like 20080112 represents January 12th 2008.

In ABAP code this translates as follows

  DATA :
    temp_int TYPE i,
    temp_date_str(8) TYPE c, 
    my_date TYPE d.

  FIELD-SYMBOLS :
     TYPE bapi1003_alloc_values_num.

      READ TABLE valuesnum
        WITH KEY charact = 'MY_DATE_CHARACT'
        ASSIGNING .

      IF sy-subrc = 0.
        temp_int = -value_from.
        temp_date_str = temp_int.
        my_date = temp_date_str.
      ENDIF.

Java: Dates/Times and Calendars

The problem that many new Java programmers -- me included -- face when they start dealing with dates and times is : Given a java.util.Date object get month, date and year fields in separate variables.

The usual misunderstanding here is that the class java.util.Date is almost deprecated and all things you expect to be able to perform with dates are done using the java.util.Calendar; class.

Calendar is an abstract class whose javadoc can be found here. The class provides get and set methods that allow you to alter any of the fields that constitute a point in time. The following example demonstrates one way to calculate the date corresponding to the start of the current term, using the Calendar get and set functions. It also shows how to convert a Date to a Calendar and vice versa.

    private void calStartOfCurrentTerm()
    {
        // create a new Gregorian Calendar
        Calendar c = new GregorianCalendar();
        // you can synchronize a Calendar with a date by using the
        // setTime() method
        c.setTime( new Date());
        
        // once you get a Calendar extracting field information is 
        // as easy as ....
        int month = c.get( Calendar.MONTH);
        int day = c.get(Calendar.DAY_OF_MONTH);
        int year = c.get(Calendar.YEAR);
        
        // let's calculate the date that corresponds to the 
        // start of the current term
        int newMonth;
        
        switch (month) {
        case Calendar.JANUARY:
        case Calendar.FEBRUARY:
        case Calendar.MARCH:
            newMonth = Calendar.JANUARY;
            break;
        case Calendar.APRIL:
        case Calendar.MAY:
        case Calendar.JUNE:
            newMonth = Calendar.JANUARY;
            break;
        case Calendar.JULY:
        case Calendar.AUGUST:
        case Calendar.SEPTEMBER:
            newMonth = Calendar.JULY;
            break;
        default:
            newMonth = Calendar.OCTOBER;
        }
        
        // adjust the calendar to point to the new date
        c.set(Calendar.MONTH, newMonth);
        c.set(Calendar.DAY_OF_MONTH, 1);
        
        // get the information to a new date variable
        Date startOfTerm = c.getTime();
        
        // display results
        System.out.println("Day is " + day +" month is " + month + " year is " + year);
        System.out.println( startOfTerm.toString());
    }

Here is a slightly different version that returns an object of type oracle.jbo.domain.Date

import java.sql.Timestamp;

import java.util.Calendar;
import java.util.GregorianCalendar;

import oracle.jbo.domain.Date;

  private Date getStartOfTerm()
  {
    // create a new Gregorian Calendar
    Calendar c = new GregorianCalendar();

    // once you get a Calendar extracting field information is 
    // as easy as ....
    int month = c.get(Calendar.MONTH);

    // let's calculate the date that corresponds to the 
    // start of the current term
    int newMonth;

    switch (month) {
      case Calendar.JANUARY:
      case Calendar.FEBRUARY:
      case Calendar.MARCH:
        newMonth = Calendar.JANUARY;
        break;
      case Calendar.APRIL:
      case Calendar.MAY:
      case Calendar.JUNE:
        newMonth = Calendar.JANUARY;
        break;
      case Calendar.JULY:
      case Calendar.AUGUST:
      case Calendar.SEPTEMBER:
        newMonth = Calendar.JULY;
        break;
      default:
        newMonth = Calendar.OCTOBER;
    }

    // adjust the calendar to point to the new date
    c.set(Calendar.MONTH, newMonth);
    c.set(Calendar.DAY_OF_MONTH, 1);
    c.set(Calendar.HOUR_OF_DAY, 0);
    c.set(Calendar.MINUTE, 0);
    c.set(Calendar.SECOND, 0);
    c.set(Calendar.MILLISECOND, 0);
    
    // get the information to a new date variable
    java.util.Date javaDate = c.getTime();
    Timestamp st = new Timestamp(javaDate.getTime());
    return new Date(st);
  }

PS:. Finally There is one last thing I have to get used to. "Copy as HTML" on JDeveloper 10g, does not work on openSUSE 10.2.

Thursday, 10 January 2008

ABAP Obtaining characteristics of a document from the classification system

In addition to a previous post regarding getting classification information for objects of type Material or Batch and thanks to additional info and feedback that I received from our MM specialist Ms Marilena Svolaki, I will be able to provide an example of how to do the same thing with documents.

Master information about documents is found in the DRAW table. (This is short for German Dokumentinformationssatz). The key fields for each document are displayed in the following image :

Creating the object key for BAPI_OBJCL_GETCLASSES is performed by concatenating the document type (field DRAW-DOKAR), the document number (field DRAW-DOKNR), the document version (field DRAW-DOKVR) and the document part (field DRAW-DOKTL). In code this can be expressed as follows

 DATA
    wa_draw TYPE draw, 
    cur_bapi_objectkey LIKE inob-objek,

    class_list TYPE STANDARD TABLE OF bapi1003_alloc_list,
    valueschar TYPE STANDARD TABLE OF bapi1003_alloc_values_char,
    valuescurr TYPE STANDARD TABLE OF bapi1003_alloc_values_curr,
    valuesnum  TYPE STANDARD TABLE OF bapi1003_alloc_values_num,
    return     TYPE STANDARD TABLE OF bapiret2.

*   Make wa_draw somehow correspond to a valid document entry ....     
    CONCATENATE wa_draw-dokar
                wa_draw-doknr
                wa_draw-dokvr
                wa_draw-doktl
                INTO cur_bapi_objectkey.

*   Call BAPI to get the list of characteristics
    CALL FUNCTION 'BAPI_OBJCL_GETCLASSES'
    EXPORTING
      objectkey_imp         = cur_bapi_objectkey
      objecttable_imp       = 'DRAW'
      classtype_imp         = '017'
      read_valuations       = 'X'
      keydate               = sy-datum
*     language              = 'E'
    TABLES
      alloclist             = class_list
      allocvalueschar       = valueschar
      allocvaluescurr       = valuescurr
      allocvaluesnum        = valuesnum
      return                = return.

    READ TABLE return INDEX 1 ASSIGNING <ret>.

*   Check that object allocations exist
    IF  sy-subrc = 0 AND
        <ret>-id = 'CL' AND
        <ret>-number = 741.

*      Read characteristic values
       ...
    ENDIF

Wednesday, 12 December 2007

Oracle 10g SQL Mass rebuild and move the indexes of a specified user

Here is a function that rebuilds or coalesces the indexes of a specified user.If asked to rebuild the indexes then you can supply a table space to move the rebuild indexes into.

The function takes three parameters :

  • user_name : The name of the user whose indexes are to be updated
  • rebuild_type : Ether C or R to indicate Coalesce or Rebuild operation
  • tablespace_name : Optional parameter to indicate where to move the indexes in case of rebuild

The function should be executed by a user with DBA privileges and this code is as follows

create or replace PROCEDURE rebuild_user_indexes( user_name IN VARCHAR2, 
                                rebuild_type IN VARCHAR2, 
                                tablespace_name IN VARCHAR2 DEFAULT NULL) 
AS
  full_index_name VARCHAR2(100);
  index_command VARCHAR2(10);
  sql_str VARCHAR2(100);
  
  total_indexes Integer;
  updated_indexes Integer;
  
  CURSOR get_index_cursor IS
    SELECT index_name, index_type 
    FROM dba_indexes
    WHERE owner = user_name AND
          index_type in ( 
              'BITMAP', 
              'NORMAL', 
              'FUNCTION-BASED NORMAL', 
              'FUNCTION-BASED DOMAIN');
      
BEGIN
  /* check initial parameters */
  IF rebuild_type <> 'R' AND rebuild_type <> 'C' THEN
    raise_application_error( -20999, 'rebuild type should be "C" or "R"');
  ELSIF rebuild_type = 'R' THEN
    index_command := ' Rebuild';
    /* if a rebuild tablespace is also defined */
    IF tablespace_name <> '' THEN
      index_command := index_command || ' tablespace ' || tablespace_name;
    END IF;
  ELSE
    index_command := ' Coalesce';
  END IF;
  
  total_indexes := 0;
  updated_indexes := 0;
  /* get all indexes that belong to the specified user */
  FOR r IN get_index_cursor LOOP
    total_indexes := total_indexes + 1; 
    dbms_output.put_line( total_indexes || ' ' || 
                          r.index_name || ' ' || 
                          r.index_type);

    /* Coalescing works only on normal B-Tree indexes, 
     * while rebuilding works with everything.
     */    
    IF (rebuild_type = 'R') OR 
       (rebuild_type = 'C' AND instr( r.index_type, 'NORMAL') > 0) THEN
      
      full_index_name := user_name || '.' || r.index_name;
      sql_str := 'alter index ' || full_index_name || index_command;
      
      BEGIN
        /* attempt to modify the index */
        EXECUTE IMMEDIATE sql_str;
        updated_indexes := updated_indexes + 1;
      EXCEPTION
        WHEN OTHERS THEN
          /* display the command and the error that occured during index 
           * update 
           */
          dbms_output.put_line( sql_str);
          dbms_output.put_line( sqlcode || ' ' || sqlerrm);
          /* continue working */
          NULL;
      END;
    END IF;
  END LOOP;
    
  /* report results */
  dbms_output.put_line( 'Indexes examined :' || total_indexes);
  dbms_output.put_line( 'Indexes Updated  :' || updated_indexes);
END rebuild_user_indexes; 

Thanks to SQLDeveloper running the function can is as easy as copying and pasting the following in SQLPlus.

DECLARE
  USER_NAME VARCHAR2(200);
  REBUILD_TYPE VARCHAR2(200);
  TABLESPACE_NAME VARCHAR2(200);
BEGIN
  USER_NAME := NULL;       -- add your user here, for example 'HR'
  REBUILD_TYPE := NULL;    -- either 'C' or 'R'
  TABLESPACE_NAME := NULL; -- optional new tablespace name if desired

  REBUILD_USER_INDEXES(
    USER_NAME => USER_NAME,
    REBUILD_TYPE => REBUILD_TYPE,
    TABLESPACE_NAME => TABLESPACE_NAME
  );
END;

Note

The above code provides a very simplistic approach to index rebuilding as it rebuilds all indexes of a user. In order to determine the indexes in your database that would really benefit from rebuilding you need to run the script available from Oracle Metalink Note:122008.1.

Tuesday, 11 December 2007

JDeveloper OC4J Changing the language that eror messages are displayed

I have had this problem many times. During development when something went wrong with my application, all I could get after the Internal server error would be JBO:XXXX error code with numerous question marks instead of the actual error message.

Some people advised me to change the preferred language/locale of my WEB browser and make it English, so as to force the display of error messages in this language, but that didn't work either.

Today I found a remedy. Open the project properties Go to Run/Debug click edit and add -Duser.language=en -Duser.country=US in the java command line options, like I show you in the following pciture

... and that does it.

Thursday, 6 December 2007

ADF BC Canceling edits in the current record of a ViewObject

The number of things that one man misses becomes frightening especially when something is as crucial as a cancel edits button in a record display form.

Up until now I have been using a Rollback action binding to cancel any edits. Of course that was also casing current record information to be lost, so I had to rely on Steve Muench's Restore Current Row After Rollback from his Not Yet Documented ADF Sample Applications in order to keep my data in sync..

Revelation came as I as was browsing for the 9999th time the 10.1.3.1 version of the Tutorial for Forms/4GL Developers. The magic function that makes a view record refresh and forget any changes is as simple as that.

public void cancelEditsToCurrentServiceRequest() {
    getServiceRequestMain().getCurrentRow().refresh( 
                            Row.REFRESH_WITH_DB_FORGET_CHANGES);
}

Shame on me!

On second thought Muench's approach is more centric and may come in handy especially if a program has many view objects through which users enter data. Of course writing cancelEdits functions is clearer but has to be done for every view object that we require to use. The choice is still ours.