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.

Thursday 29 November 2007

ADF-BC/JSF Performing case insensitive searches

I have been asked to implement a service request system that will allow a company to record service requests from customer via a telephone line. The difference with oracle's SRDemo application is that in my case SR's will be entered by telephone operators and be assigned to departments and not to technicians

Anyway the application still offers a SRSearch page that allows telephone operators and managers to filter the service request list and here is where my problem started since they asked me if the QBE system would match parts of the requester first or last name case insensitively.My first reaction was to tell them to enter everything in capital letters, but then I though I might give it a try and ended up like this

first I added two additional fields in my query named SearchFirstName and SearchLastName defined like this

  SELECT 
       .....
       ServiceRequest.REQUESTER_FIRST_NAME, 
       UPPER( REQUESTER_FIRST_NAME) AS SEARCH_FIRST_NAME, 
       ServiceRequest.REQUESTER_LAST_NAME, 
       UPPER( REQUESTER_LAST_NAME) AS SEARCH_LAST_NAME, 
       ......

then changed I used JavaScript in the SRSearch page to make sure that whatever the user enters in the SearchFierstName and SearchLastName fields is converted to upper case before posting.

The javascript file itself is as simple as this

/* ---------------------------------------------------------------------------
 * file SRSearchSubmit.js
 * contains code that allows to submit the SRSearch form with the first name
 * and last name fields converted to upercase.
 *
 * Date 03-12-2007
 * ---------------------------------------------------------------------------
 */
 function convert()
 {
    var firstName = document.forms["myADFForm"].elements["myADFForm:searchFirstNameField"].value;
    var lastName =  document.forms["myADFForm"].elements["myADFForm:searchLastNameField"].value;

    document.forms["myADFForm"].elements["myADFForm:searchFirstNameField"].value
      = firstName.toUpperCase();
    document.forms["myADFForm"].elements["myADFForm:searchLastNameField"].value
      = lastName.toUpperCase();

    return true;
 }

No the two final points. First we need to include the javascript file at the top of the page inside a <f:verbatim> tag. Next in order to get the names of the elements right, we need to set the id attribute of both the <h:form> and the <af:inputText> elements.

The page header part of the resulting JSF page looks like this

    <afh:html>
      <f:loadBundle basename="SRKomo" var="res"/>
      <afh:head title="#{res['application.title']}">        
        <meta http-equiv="Content-Type" content="text/html; charset=utf-8"/>
        <link href="../css/oracle.css" rel="stylesheet" media="screen"/>
        <f:verbatim>
          <script src="../javascript/SRSearchSumbit.js" type="text/javascript"></script>
        </f:verbatim>
      </afh:head>
      <afh:body>
        <af:messages/>
        <h:form id="myADFForm" onsubmit="convert();">
        ...

And the definitions for the each of th two search fields like this

              <af:panelLabelAndMessage label="#{bindings.AllRequestsListSearchFirstName.label}">
                <af:inputText value="#{bindings.AllRequestsListSearchFirstName.inputValue}"
                              simple="true"
                              required="#{bindings.AllRequestsListSearchFirstName.mandatory}"
                              columns="#{bindings.AllRequestsListSearchFirstName.displayWidth}"
                              styleClass="searchTextField"
                              id="searchFirstNameField">
                  <af:validator binding="#{bindings.AllRequestsListSearchFirstName.validator}"/>
                </af:inputText>
              </af:panelLabelAndMessage>

and that did it.

Notes

  • The original info about how to place javascript into a JSF document comes from the Frank Nimphius' Blogbuster article ADF Faces: Submit a form with the Enter key.
  • I have found it very easy to understand the HTML produced by the Oracle JSF engine using Firebug. Especially the "Inspect element" function will locate the HTML code of each eleent you click on and that can save you an awfull lot of decoding trouble.

Wednesday 28 November 2007

Where is the Oracle Database alert log located ?

This is something I keep forgetting so I think that I may as well add it here.

On my Linux system the alert log is located in $ORACLE_HOME/admin/$ORACLE_SID/bdump while the exact same path -- with "\" instead of "/" -- is used for Windows.

The directory where the alert.log found is determined by the background_dump_dest initialization parameter: So the ultimate way to find it is by issuing an SQL command like :

select value 
    from v$parameter 
    where name = 'background_dump_dest';

Needless to say that the alert.log's name is alert.log and that it is a simple text file that can be opened using any text editor and that you can delete it or shrink it online.

Friday 9 November 2007

ADF/JSF Getting the current row with code executed from a table bucking bean

The following code comes from the JDeveloper Forums courtesy of Frank Nimphus. If added to any event listener fired from "inside" an ADF Table, it prints the row's 1'st attribute and the rowKey.

        FacesContext fctx = FacesContext.getCurrentInstance();
        ValueBinding vb = (ValueBinding) fctx.getApplication().createValueBinding("#{row}");
        JUCtrlValueBindingRef rwbinding = (JUCtrlValueBindingRef) vb.getValue(fctx);
        System.out.println("--- Key as String --"+rwbinding.getRow().getKey().toStringFormat(true));
        System.out.println("--- First Attribute ---"+rwbinding.getRow().getAttribute(1));

Thanks again Frank

ADF/JSF Avoiding uncought exeptions after commit.

When using Oracle JDeveloper 10.1.3.x, you can find it very tempting to create "Create/Edit Record" pages by dropping an ADF Form on a blank JSF page and then adding commit and rollback buttons in the place of the ok and cancel buttons with an action property that returns your users to the browse page.

So let's assume that your faces-config file contains a navigation case labeled "return", that leads from you edit page back to your browse page. In cases like these, the code for the commit button will more or less look like this

                      <af:commandButton 
                                     actionListener="#{bindings.Commit.execute}"
                                     text="Ok" 
                                     action="return"
                                     id="commitButton"
                                     disabled="false"/>

This approach, presents a very serious hazard, which is a bug in Oracle's JSF implementation. If any exception is thrown after commit, the framework moves to the page designated by the navigation case and the error messages are not being displayed at all.

To make matters worse, you end up with on open transaction and nothing else entered in this manner ever gets stored in your database while you users have no way to realize that something has gone wrong. I had raised an SR for this that ended up in a one off patch (5630740) for JDeveloper 10.1.3.2.

I run into this problem again in am application that I developed recently using JDeveloper 10.1.3.3. And then it hit me. The solution was as simple as the Egg of Columbus. The only thing that needs to be done is to bind the commit button action in a backing bean. JDeveloper will then change the button tag into something like :

                      <af:commandButton 
                                    text="Accept"
                                    id="commitButton"
                                    disabled="false"
                                    action="#{backing_Edit.commitButton_action}"/>

... create the following code :

    public String commitButton_action()
    {
        BindingContainer bindings = getBindings();
        OperationBinding operationBinding =
            bindings.getOperationBinding("Commit");
        Object result = operationBinding.execute();
        if (!operationBinding.getErrors().isEmpty()) {
            return null;
        }
        return "return";
    }

... and that does it. Commit exception messages are displayed correctly and everyone is happy.

In case you have many edit pages with different navigation case labels then you might wish to create a solution that is a little more sophisticated and closer to the OO paradigm. In a case like this I suggest that you create a base class to be the parent of all your page backing beans and add the following method.

public class MyBackingBase
{
    protected String executeCommit( BindingContainer bindings, String successValue)
    {
        OperationBinding commitBinding =
            bindings.getOperationBinding("Commit");
        
        commitBinding.execute();
                
        return commitBinding.getErrors().isEmpty() ? successValue : null;
    }
 }

Having done that the actual backing bean code could be something as simple as

    public String commitButton_action()
    {
        return executeCommit( getBindings(), "return");
    }

... and one more thing. The Copy as HTML function is not available again. I guess I 'll have to start looking why .. :-(

Wednesday 7 November 2007

JSF Passing parameters to included jsp pages

The question was brought up in the JDeveloper forum. When I started creating my first JSF pages I wanted to be able to create a menu that I could edit and make make the changes propagate to all pages immediately. So What I wanted back then was amethod to include a jsp to all my pages but also be able to pass parameters from the including page to the one being included.

The answer came from the book Mastering JavaServer Faces by Bill Dudney, Jonathan Lehr, Bill Willis and LeRoy Mattingly.

The approach is to create a subview and then include the page using a jsp:include tag. Inside the include you must use a jsp:param tag where you define the name and value of the expected JSP parameter. To cut the long story short, I tested the solution with JDeveloper 10.1.3.3 and here is the code fragment to use.

            <f:subview id="header">
                <jsp:include page="/header.jsp" flush="true">
                    <jsp:param name="pageTitle" value="Welcome to my first JSF Application"/>
                </jsp:include>
            </f:subview>
 

The included page should be surrounded by an f:subview tag. The external parameter can be accessed by an EL expression like "#{param.paramName}" and a simple example would be something like :

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
"http://www.w3.org/TR/html4/loose.dtd">
<%@ page contentType="text/html;charset=ISO-8859-7"%>
<%@ taglib uri="http://java.sun.com/jsf/core" prefix="f"%>
<%@ taglib uri="http://xmlns.oracle.com/adf/faces" prefix="af"%>
<%@ taglib uri="http://xmlns.oracle.com/adf/faces/html" prefix="afh"%>
<f:subview id="header">
  <afh:html>
    <afh:head>
      <meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-7"/>
    </afh:head>
    <afh:body>
      <af:panelHeader text="#{param.pageTitle}"/>      
    </afh:body>
  </afh:html>
</f:subview>

... and one last comment. I guess that this is the first time that the Copy as HTML command works correctly in JDeveloper on Linux. :-)

Tuesday 6 November 2007

BAPI / RFC with Delphi 2006 / 2007

Six months ago I wrote an article regarding how to install and use SAP's Active X components using Delphi 6. Here is the sequel regarding Delphi 2006 and 2007.

The basic idea is still the same you only need to install SAP Logon Control version 1.1, SAP BABPI Control Version 1.2 and finally import the SAP Remote Function Call Controll (Version 5.0) type library.

To complete the task in Delphi 2006, use the following procedure:

  1. Start Delphi 2006 and create a new package using the menu File → New → Package for Delphi Win 32.
  2. Save the package and the project using a name like SAPControls
  3. From the Delphi main menu select Component → Import Component.
  4. Locate the SAP Logon Control as show in the following image and click next
  5. In the following Screen just type SAP at the pallete page name ... and press next
  6. On the next screen select Add unit to Package radio item. ... and click Finish
  7. Repeat the previous steps and install the SAP BAPI Control and the Remote function type library. Unlike Delphi 6 you should get no compiler error messages.
  8. When you are finished, right click on the SAPControls package on the project manager window and select Install from the pop up menu.
  9. That does it. Create a new project and use the components of the new SAP pallete Category as you please.

Don't worry if you misplaced your components in an other category. You can drug them into any category you wish afterwards.

Friday 19 October 2007

JDeveloper 10.1.3.3 JSP Compiler Errors in JSF faces

I have run into the following error while modifying JSF pages in JDeveloper 10.1.3.3 on openSUSE. While modifying the page contents using the JDeveloper editor, the page stops working when accessed from the browser and you get an Internal Server Error with a JspCompileException but nothing else. The page was still accessible from the internal JDeveloper Design editor and would complie correctly from inside JDeveloper.

A remedy that sometimes worked was to remove a few components from the page put them back and then hope it would work.

Eventually I came up with the following simple trick. Just remove all contents from ~/jdevhome/mywork/WorkSpace/ViewController/classes/.jsps and rebuild your project.

Thursday 18 October 2007

Delphi: How to install BDE on a client machine

Suppose you have a Delphi application that access data using the Borland Database Engine (BDE) and you wish to deploy it on a user's client computer. If you compile the project without runtime packages and no BDE support is required, then merely copying the EXE file on the target computer is usually enough.

Now installing BDE on a client computer is performed as follows :

  1. Locate the file bdeinst.cab located in C:\Program Files\Common Files\Borland Shared\BDE.
  2. Open it using winZip or something equivalant.
  3. The .cab file contains only one file BDEINST.DLL. Extract this to the users client computer.
  4. Use the command regsvr32.exe bdeinst.dll from the command prompt to perform the installation.
  5. Run your application

BDE un-installation can be performed by folloowing these steps :

  1. Delete the folder containing BDE data
  2. Delete the BDEADMIN.CPL file in the Wiindows\System folder, in order to get rid of the control file applet
  3. Use regedit in order to delete the key HKEY_CURRENT_USER\Software\Borland\BDEAdmin
  4. Next locate HKEY_LOCAL_MACHINE\Software\Borland and delete the subkeys BLW32, Borland Shared and Database engine.
  5. Reboot your machine

Note : Having said all that I would like to give you an extract from the file bdedeploy.txt located at the BDE installation directory.

2. BORLAND-CERTIFIED INSTALLATION PROGRAMS
===========================================================
Borland products which include redistribution rights
include an Borland-certified install program, such as
InstallShield Express, to ensure proper installation and
uninstallation of your applications so that they co-exist
well with other applications which use the Borland Database
Engine (BDE) including those created with Visual dBASE,
Paradox, Delphi, and C++Builder.

Borland has also provided BDE and SQL Links installation
information to other install vendors such as Sax Software,
WISE Software, Great Lakes Business Systems (GLBS) makers
of the WISE install tool and Eschalon Development so that
their products can also be ensured to be fully compatible
with the BDE.

From time to time, Borland Software Corporation may, 
at its discretion, certify additional installation programs 
for use as the Borland Certified Install Program for this 
product.
  
Also check the Borland-sponsored announcement newsgroups:

  news:borland.public.announce
  news:borland.public.bde

3. BDE DEPLOYMENT (ALL DATABASE APPLICATIONS)
===========================================================
3.1 Deploying the BDE
---------------------
A Borland-certified installation program provides all
needed functionality and steps for redistributing the
Borland Database Engine (BDE), including:

  * Selecting files to redistribute
  * Determining final directory locations
  * Comparing versions of BDE files
  * Creation of BDE aliases
  
Follow the instructions of the particular installation
program used for specific steps to create an installation
program that includes the BDE.

Wednesday 17 October 2007

JSF How to create and use dependent list boxes

Back in 2006, Frank Nimphius has written two excellent articles about using AJAX style combo (or list) boxes in a web page that filter one another based on values of a master detail relationship. I have to admit that thanks to him, my users have one less reason to grumble :-)

The first one is entitled ADF Faces: How-to build dependent lists boxes with ADF and ADF Faces and the second one ADF Faces: How-to build dependent lists boxes with ADF and ADF Faces Part - II

Thank you Frank.

ABAP: How to tell if user input is a number

ABAP casts between types by just sequentially copying (as many as the size of the target type) bytes from one memory area to an other and then checks if the new contents of the target memory area can be perceived as the appropriate type. As a general rule strings and character objects are always copied left while numbers get copied right justified)

This little code fragment will get a number out of a string and convert it to practically any numeric format.

        DATA :
           temp_str(10) type c value '1234,34'.
           my_number TYPE f.

*       If the value of temp_str is comming from user input of from any other
*       user or ABAP dictionary type then it is also wise to execute something like
        CONDENSE temp_str.

*       You may want to execute this if you --like me -- your 
*       decimal symbol is a comma
        REPLACE ',' WITH '.' INTO temp_str.

        CATCH SYSTEM-EXCEPTIONS conversion_errors = 4.
          my_number = temp_str.
        ENDCATCH.
        IF sy-subrc <> 0.
          MESSAGE e888(sabapdocu) WITH temp_str ' is not a number'.
        ENDIF.

Friday 12 October 2007

ABAP Obtaining an object's characteristics from the classification system

The easiest way to get a list of all the characteristics of an object is to use the BAPI_OBJCL_GETCLASSES BAPI. The function requires that you supply an object key, the database table where the object is stored, the class type of the obect to look for -- e.g. '001' for material or '023' for batch -- and finally whether or not to return the entire list of characteristics.

The return value consists of a table containing all the classes the object is currently assigned and if requested three additional tables with values of the object characteristics, one for string valued, one for numeric and one for currency.

Information about objects, db tables and corresponding class types is stored in the inob table. So one way of getting all this would be to write code like :

  DATA : 
    wa_inob TYPE inob, 
    my_object LIKE inob-objek.

  CLEAR wa_inob.
  CONCATENATE my_matnr my_batch INTO my_object.
  SELECT SINGLE * 
    FROM inob 
    INTO wa_inob
    WHERE objek = my_object.

So calling the BAPI would be as straight forward as ....

  DATA :
    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.

  CALL FUNCTION 'BAPI_OBJCL_GETCLASSES'
    EXPORTING
      objectkey_imp         = wa_inob-objek
      objecttable_imp       = wa_inob-obtab
      classtype_imp         = wa_inob-klart
      read_valuations       = 'X'
      keydate               = sy-datum
*     language              = 'E'
    TABLES
      alloclist             = class_list
      allocvalueschar       = valueschar
      allocvaluescurr       = valuescurr
      allocvaluesnum        = valuesnum
      return                = return.

Determining if an object has indeed been assigned classification information is performed by checking the return table value. Correct classification means that the return table contains one line with field "TYPE" set to 'S', "ID" set to 'CL' and NUMBER set to 741. (When no allocations are found number is 740). So before trying to read any characteristic values is safer to test like this.

     FIELD-SYMBOLS
        <ret> TYPE bapiret2.

     READ TABLE return INDEX 1 ASSIGNING <ret>.

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

      ....

Now getting a specific value from let's say the string valued characteristics can be accomplished by code similar to following, which gets the value of a characteristic named QUALITY:

  FIELD-SYMBOLS :
    <fs> TYPE bapi1003_alloc_values_char.

  READ TABLE valueschar WITH KEY charact = 'QUALITY' ASSIGNING <fs>.

  if <fs> IS ASSIGNED.
*   The language independent value of the characteristic is 
    my_quality_id = <fs>-value_neutral.
*   The language dependent i.e. the one that will appear in the
*   result table of CL30N is 
    my_quality_descr = <fs>-value_char.
  ENDIF.

NOTE: The following approach is generic and is supposed to work fine on all systems. In our system however (Release 4.6C) the inob table does not have an index on inob-objek, so the SELECT SINGLE statement is executed by performing sequential reads. One way to overcome this would be to manually create the index. The other, as far as I am concerned, is to know beforehand the type of objects and classes that your program deals with, and therefore hard code the database table to 'MARA' or MCH1' and the class type to '001' and '023' for materials and batches respectively.

Friday 5 October 2007

HTML Code for searching your site with Google.

I had seen this done in many sites and to be honest I always thought of it as a fancy trick for showing off HTML skills. Lately while using Steve Muench's Blog I realized that this little HTML form turns out to be of ultimate use. Then I said ok let's do it and ended up looking at HTML code from various sites....Finally I ended up with the following solution, that you can copy at use as is, Just replacing my site with yours.


<form action="http://www.google.com/search" method="get">
    <table border="0" align="center">
        <tr>
            <td>
                <input maxlength="255" value="" name="q" size="31" type="text"/>
                <input value="Google Search" type="submit"/>
            </td>
        </tr>
        <tr>
            <td>
                <input value="" name="sitesearch" type="radio"/>
                The Web
                <input checked value="abakalidis.blogspot.com" name="sitesearch" type="radio"/>
                This Blog
            </td>
        </tr>
    </table>
</form>

Thank you Kate for the messy HTML color syntax.:-)

Wednesday 3 October 2007

Java: How to tell if an LDAP user is member of a group

Here is a Java class that I use in order to determine if a user of an LDAP server is a member of a group. The class uses the Mozilla LDAP SDK available for download from Mozilla

In many JSF applications this class lies in the heart of my UserInfo managed beans allowing or f orbiting access to various parts of the application.

Things you need to fill before using it are :

  • server name or IP address
  • Server port
  • A user name and a password that will allow your code to execute queries on the LDAP Server
  • The base DN to start the search at

The usual place to find these values is the LDAP server itself. If you server is an Oracle IAS 10.2 infrastructure then check the file $ORACLE_HOME/install/portlist.ini.


package ab.util.ldap;

import java.util.Enumeration;

import netscape.ldap.LDAPAttribute;
import netscape.ldap.LDAPConnection;
import netscape.ldap.LDAPEntry;
import netscape.ldap.LDAPException;
import netscape.ldap.LDAPReferralException;
import netscape.ldap.LDAPSearchResults;

public class MyLDAP 
{
    public static final String ldapHost = "ldap.shelman.int";
    public static final int ldapPort = 3060;
    private static final String authid = "cn=orcladmin";
    private static final String authpw = "my_password";
    private static final String base = "cn=groups,dc=shelman,dc=int";

    /**
     * isGroupMember -- determine if user belongs to an LDAP group
     *
     * #param group name of group to examine
     * #param user name user to search for emembership+-
     */
    public static boolean isGroupMember(String group, String user) 
    {
        String member = "cn=" + user.toLowerCase();
        String filter = "(cn=" + group + ")";
        String[] attrs = { "uniquemember" };
        boolean result = false;

        LDAPConnection ld = new LDAPConnection();
        // System.out.println("Attempting to connect :"  + today.toString());
        try {
            // connect to server and authenticate
            ld.connect(ldapHost, ldapPort, authid, authpw);
            // issue the search request
            LDAPSearchResults res =
                ld.search(base, ld.SCOPE_SUB, filter, attrs, false);
            // loop on results until complete
            while (res.hasMoreElements()) {
                try {
                    LDAPEntry entry = res.next();
                    LDAPAttribute atr = entry.getAttribute(attrs[0]);
                    Enumeration enumVals = atr.getStringValues();

                    while (enumVals != null && enumVals.hasMoreElements()) {
                        String val = (String)enumVals.nextElement();
                        // convert to lower case
                        val = val.toLowerCase();

                        if (val.indexOf(member) >= 0) {
                            result = true;
                            break;
                        }
                    }

                } catch (LDAPReferralException ex) {
                    // ignore referal exceptions
                    continue;
                } catch (LDAPException ex) {
                    System.out.println(ex.toString());
                    continue;
                }
            }
        } catch (Exception ex) {
            System.out.println("Error communicating with LDAP Server " +
                               ldapHost + " Port " + ldapPort);
            System.out.println(ex.toString());
        }

        // finished searching. try to disconnect
        if (ld != null && ld.isConnected()) {
            try {
                ld.disconnect();
            } catch (LDAPException ex) {
                System.out.println(ex.toString());
            }
        }

        // return whatever found
        return result;
    }
}

The package ldapjdk-4.17-38 is included in the distribution of all openSUSE systems. For openSUSE 10.2 the package is available for download from here.

Thursday 27 September 2007

ADF Tables. Changing the colour of negative valued columns

My users asked me if it would be possible to display negative values of an ADF table using a different colour than the rest.

The solution turned out to be something as simple as defining two CSS classes like this :

.redFont {
    color: Red;
}
.blueFont {
    color: Navy;
}

The last step was to bind the StyleClass property of the adf:outputText elements using the following EL expression :

#{row.myColumn.value > 0 ? 'blueFont' : 'redFont'}

Monday 17 September 2007

SuSE Linux Yast Repositories URLs changed

During the last week, I have have noticed a change in the URLs of various package repositories in openSUSE. The correct URLs are still available at the Additional YaST Package Repositories page in the openSUSE website.

If your update system starts complaining about unaccessible repositories,then I suggest you have a look. The good news is that if you start the Add remove update sources applet from YaST and select ignore or skip from the dialog boxes complaining about faulty URL's then the system gives you a chance to remove all non working update sources immediately.

Thursday 30 August 2007

JSF Basics Access the HTTP Session from a backing bean

Here is something I always seem to forget and never realy got to use :-)
Code to gain access to the HTTP session from a inside backing bean :

FacesContext fc = FacesContext.getCurrentInstance();
HttpServletRequest request = (HttpServletRequest)fc.getExternalContext().getRequest();
HttpSession session = request.getSession();

Tuesday 21 August 2007

ABAP: How to get the Screen Table row that the cursor is in

Getting the row that the user cursor is in accomplished by using the GET CURSOR LINE statement. As always, we need to declare a global field named something like cur_tbl_line to hold the current table line. Then during PAI processing we need to issue statements like the following : (tc_mydata is the name of the screen table control and XXX is the screen number)

MODULE user_command_XXX INPUT.

* set up the current line variable
  GET CURSOR LINE cur_tbl_line.
  cur_tbl_line = tc_mydata-top_line + cur_tbl_line - 1.

* do the classic stuff
  ok_code = ok_code_XXX.
  CLEAR ok_code_XXX.

  ...
ENDMODULE.

Using this approach, you can get the selected line of the tbl_mydata table by writing code like this

FORM sync_mydata_with_selection
     CHANGING
        f_none_selected TYPE c.

  FIELD-SYMBOLS
    <fs> LIKE LINE OF tbl_mydata.

  CLEAR f_none_selected.

* give precedence to the table control selection
  READ TABLE tbl_mydata
  WITH KEY sel = 'X'
  ASSIGNING  <fs>.

  IF sy-subrc <> 0.
*   No selection was made so get the current row using the
*   cursor
    IF cur_tbl_line > 0.
      READ TABLE tbl_packages INDEX cur_tbl_line
        ASSIGNING .
    ELSE.
      f_none_selected = 'X'.     
    ENDIF.
  ENDIF.
ENDFORM.

After this is executed, the header line of tbl_mydata contains the selected row and if your user has not selected anything then f_none_selected will have a value of 'X'.

Friday 3 August 2007

ABAP: Gettting the selected column

Every TABLEVIEW control defined in an ABAP program contains a cols field that contains information about selected columns. Whether or not column selection is allowed is set by the tableview object's properties in the screen painter. (see also the relevant image for the posting How to setup a Screen containing a Table a few days back.

One common use for column selection is sorting, so an example sorting function that sorts a global table named tbl_my_data based on the selected column might look like this :

FORM sort_table USING  sort_mode LIKE sy-ucomm.
  FIELD-SYMBOLS
     TYPE cxtab_column.

* find out if a column is selected.
  READ TABLE tc_mytab-cols
    WITH KEY selected = 'X'
    ASSIGNING <selected_col>.

  IF sy-subrc <> 0.
    MESSAGE s888(sabapdocu) WITH 'No column is selected'.
    EXIT.
  ENDIF.

* sort according to the selected column
  CASE sort_mode.
    WHEN 'SORT_ASC'.
      CASE <selected_col>-index.
        WHEN 1.
          SORT tbl_mydata ASCENDING BY field1.
        WHEN 2.
          SORT tbl_mydata ASCENDING BY field2.
        WHEN OTHERS.
          MESSAGE s888(sabapdocu) WITH 'Not yet implemented'.
      ENDCASE.
    WHEN 'SORT_DSC'.
      CASE <selected_col>-index.
        WHEN 1.
          SORT tbl_mydata DESCENDING BY field1.
        WHEN 2.
          SORT tbl_mydata DESCENDING BY field2.
        WHEN OTHERS.
          MESSAGE s888(sabapdocu) WITH 'Not yet implemented'.
      ENDCASE.
  ENDCASE.
ENDFORM.                    " sort_table

Thursday 2 August 2007

SuSE Linux: Speeding up boot time by getting rid of zmd

Here is a cool post I found at linuxQuestions.org.. If you feel that your openSUSE 10.x Linux box is taking too much time to boot, then it might be a good idea to remove ZMD altogether.

The actual details can be found here

Tuesday 31 July 2007

ABAP: Responding to changes on screen fields

The general case for checking user input during PAI can be summarized as follows :

PROCESS AFTER INPUT.

  FIELD fieldA MODULE check_fieldA ON REQUEST.
  ...
  FIELD fieldB MODULE check_fieldB ON REQUEST.

Any messages of type e or w executed during execution of the corresponding modules will cause SAPGui to stop processing, return focus to the appropriate field and execute the module again until no messages are thrown.

If you wish to perform the same check on both fields then the fields can be chained.

PROCESS AFTER INPUT.
  CHAIN.
    FIELD :
            fieldA,
            fieldB

    MODULE check_fieldA_and_B ON CHAIN-REQUEST.
  ENDCHAIN.

if the field to be checked belongs to an internal table accessed via a table control then the checking and processing phase should be carried out as follows:

PROCESS AFTER INPUT. 
  ...
  LOOP AT tbl_mydata.
    CHAIN.
      FIELD
              tbl_mydata-my_field.

      MODULE check_tbl_mydata_my_field ON CHAIN-REQUEST.
    ENDCHAIN.

    MODULE write_table_line.

  ENDLOOP.

Monday 30 July 2007

ABAP: How to setup a Screen containing a Table

This little guide aims to serve as a general template regarding how to create a SAP screen in a dialog module or a report program that will allow the user to edit data stored in an internal table using a table control grid. This may not appear to be the most elegant of approaches to programming but that is the way ABAP works.

First of all lets keep in mind that: in order to pass data to and from screen fields, they must have the same name as a global variable. Having said that we must define a global array containing our data and a global table view control that will be used to program the data transfers.

Let us start with the data. Assuming that the table you wish to work with corrsponds to an ABAP dictionary structure named ZMY_STRUCT, then the table definition might look something like the following:

* Basic ITAB
DATA  BEGIN OF tbl_mydata OCCURS 0.
DATA:  sel TYPE c.
       INCLUDE STRUCTURE zmy_struct.
DATA:  END OF tbl_mydata.

One can reasonably ask why a table with a header line and again why an ABAP dictionary structure. The answer to both questions will be given shortly afterwards but for now let's just say that things work much easier this way, or otherwise they don't work at all :-). The table control definition should be something like

CONTROLS :
 tc_mydata TYPE TABLEVIEW USING SCREEN 0200.

Make sure that the screen number corresponds to the actual screen number of your program. Next move to the screen and press the layout button to invoke the screen painter. Inside the screen drop a table control item and name it TC_MYDATA. Size it so that it fits your screen according to your needs. Double click on the table control to bring up the properties dialog box.

The sel field of the tbl_mydata table will mark the user selected lines. Getting a field symbol to point at the selected line is as easy as writing something like :

 FIELD-SYMBOLS
   <fs> LIKE LINE OF tbl_mydata.

 READ TABLE tbl_mydata ASSIGNING <fs> WITH KEY sel = 'X' .
 IF NOT <fs> IS ASSIGNED.
   MESSAGE s888(sabapdocu) WITH text-e01. " No Selection
 ELSE.
*   Do what ever you want with -...

 ENDIF.

Now press F6 to invoke the screen painter Dict.Program Fields window. In the field named Table field Name enter a search patter like like TBL_MYDATA-* and press the button labeled Get from program to display the matching table entries. Select the ones you wish to add to you screen and press the green ok button at the bottom. Then click inside the table view control to create the appropriate columns. Had the tbl_mydata table been declared any other way -- i.e. using a TYPES section or without the header line, then the process or field selection through F6 would not work.

Note: At this point just save the screen and exit screen painter without performing any kind of syntax check or activation.

Moving back to the screen properties, the basic flow logic should at least contain the following


PROCESS BEFORE OUTPUT.

 MODULE status_0200.

 LOOP AT tbl_mydata WITH CONTROL tc_mydata
                        CURSOR tc_mydata-current_line.

   MODULE read_tbl_line.
 ENDLOOP.


PROCESS AFTER INPUT.

 MODULE exit_screen_0200 AT EXIT-COMMAND.

 LOOP AT tbl_mydata.

   MODULE write_tbl_line.
 ENDLOOP.

 MODULE user_command_0200.

The basic idea is that during PBO the contents of the entire table are copied from the table to the table control. Then during PAI the contemns of the table control will be copied from the control back to the table.

Before copying any data though, we must first set the size of the table control. The best place to do this is probably at the status module. Now, although my mother told me never to use global variables, the usual approach to setting the table size during PBO, starts by declaring a global field named somthing liketotal_entries or table_size being of type i. Having done that your status_XXX module should at least contain the following.

MODULE status_0200 OUTPUT.
 SET TITLEBAR 'TB_200'.
 SET PF-STATUS 'SCREEN-0200'.

 DESCRIBE TABLE tbl_istat LINES total_entries.
 tc_mydata-lines = total_entries.
ENDMODULE.                 " status_0200  OUTPUT

To create the read_tbl_line module, double click on the read_tbl_line inside the screen flow editor. A message will pop up asking if the a module named read_tbl_line should be created. Answer yes and depending on the type of program you are creating select the appropriate file. After you press ok, change the text in the editor so it looks like this.

MODULE read_tbl_line OUTPUT.
 MOVE-CORRESPONDING tbl_mydata TO tc_mydata.
ENDMODULE.                 " read_tbl_line  OUTPUT

Finally the write_tbl_line PAI module does the exact opposite. It moves the data from the table control back to the internal table.

MODULE write_tbl_line INPUT.
 MODIFY tbl_mydata INDEX tc_mydata-current_line.

 IF sy-subrc <> 0.
   APPEND tbl_mydata.
 ENDIF.

ENDMODULE.                 " write_tbl_line  INPUT

From now on any code executing during the user_command_XXX module will get a consistent copy of the data.

Screen programming in ABAP is a complex subject. This post provides only the basic template for minimal operations. More post will follow explaining how to respond to data changes, sort tables based on selected columns and dynamically changing your screen.

Tuesday 24 July 2007

PL/SQL: User Privileges are not inherited from roles

FACT: When executing DDL from withing PL/SQL using NDS, then the user running the procedure must have the appropriate privilege assigned directly to create a whatever object the DDL is trying to create.

Privileges are not inherited from a role if a procedure is used.

Tuesday 17 July 2007

PL/SQL: Collection Types

All programming languages need arrays. Oracle PL/SQL provides three types. The following post aims to serve as a quick reference and provide some minor examples for each type.

VARRAY's
VARRAY's are the closest to the Pascal or C motion of an array. They are always defined to be of fixed length and start at index 1. VARRAY's can be used on both SQL or PL/SQL and whenever they are stored into the databse, they preserve the order of their elements.

Declaring a VARRAY can be performed as follows

    declare
      TYPE week_type IS VARRAY(7) of VARCHAR2(30);
      week_day week_type := week_type();
  
Nested Tables

Nested tables are much like VARRAY's in the sense that they are single dimensional arrays that can be used in both SQL and PL/SQL, but they do not posses the maximum length limitation. Unlike VARRAY's, nested tables are multisets which means that there is no inherent order to their elements

Both VARRAY's and nested tables must be initialized with the default collection-type constructor, meaning that all declarations of the form my_collection CollectionType must end like := collectionType().

Both VARRAY's and nested tables must be extended to accumulate space for their data. No matter wheither you define the maximum size of an array or not, array slots start with a null value. So you cannot just start assigning values to slots unless you create the appropriate space first. See the examples later on.

Nested tables like their "Associative Array" cousins that we shall discuss shortly are what Steven Feuersteinin refers to as sparse data structures. This means that elements can be removed from nested tables leaving an empty slot in the middle of the array. For a demonstration of the concept, Sse the example on nested tables later on.

Declaring a nested table can be performed as follows

    DECLARE
      TYPE string30_table IS TABLE OF Varchar2(30);
      week_day week_type := tring30_table();
  
Associative Arrays
Associative Arrays are single dimensional arrays with a unique index only available in PL/SQL code. Their index can be of any type may that be Integer or Varchar2. Associative Arrays do not need to be initialized using any default constructor. You just declare them and user them.

Declaring an associative array can be performed as follows

    DECLARE
      TYPE string30_array IS TABLE OF VARCHAR2(30)
            INDEX BY BINARY_INTEGER;
      week_day string30_array;
  

Steven Feuersteinin his excelant Oracle PL/SQL Programming 4th Edition book presents the following example regarding usage of associative arrays.

DECLARE
  TYPE list_of_names_t IS TABLE OF VARCHAR2(50)
           INDEX BY PLS_INTEGER;
  happy_family list_of_names_t;
  l_row PLS_INTEGER;
BEGIN
  happy_family(202020202) := 'Eli';
  happy_family(-15070) := 'Steven';
  happy_family(-90900) := 'Chris';
  happy_family(88) := 'Neva';

  l_row := happy_family.FIRST;
  WHILE l_row IS NOT NULL
  LOOP
    DBMS_OUTPUT.put_line( happy_family(l_row));
    l_row := happy_family.NEXT(l_row);  
  END LOOP;
END;

When dealing with VARRAY's and nested tables then the type definition may be stored at schema level. Again a small example this time from Oracle PL/SQL for Dummies . Notice the different naming styles between the two.

DECLARE 
  TYPE month_nt IS TABLE OF VARCHAR2(50);
  v_month_nt month_nt := month_nt();
  i NUMBER;
BEGIN
  v_month_nt.extend(3);
  v_month_nt(1) := 'January';
  v_month_nt(2) := 'February';
  v_month_nt(3) := 'March';

  v_month_nt.DELETE(2);
  DBMS_OUTPUT.PUT_LINE('Count ' || v_month_nt.COUNT);
  DBMS_OUTPUT.PUT_LINE('Last ' || v_month_nt.LAST);

  i := v_month_nt.FIRST;
  LOOP
    DBMS_OUTPUT.PUT_LINE(v_month_nt(i));
    i := v_month_nt.NEXT(i);

    IF i IS NULL THEN
      EXIT;
    END IF;

  END LOOP;
END;

The output of the previous script is ...

Count 2
Last 3
January
March

... meaning that we have an array with three slots and a gap between them. I wanted to take this example a little bit further so I changed the definition of the array type to VARRAY., making the program look like this. (Notice the standard FOR loop used for traversing a VARRAY.

DECLARE 
  TYPE month_va IS VARRAY(20) OF VARCHAR2(50);
  v_month_va month_va := month_va();
  i PLS_INTEGER;
BEGIN
  v_month_va.extend(3);
  v_month_va(1) := 'January';
  v_month_va(2) := 'February';
  v_month_va(3) := 'March';

  DBMS_OUTPUT.PUT_LINE('Count ' || v_month_va.COUNT);
  DBMS_OUTPUT.PUT_LINE('Last ' || v_month_va.LAST);

  FOR i IN v_month_va.FIRST .. v_month_va.LAST
  LOOP
    DBMS_OUTPUT.PUT_LINE(v_month_va(i));
  END LOOP;
END;

Wednesday 11 July 2007

SuSE Linux: Running SQLDeveloper 1.2 on openSUSE 10.2

I have just downloaded and installed SQL developer version 1.2 from Oracle.OTN .

Immediately noticeable improvement are the new GUI, the support for two additional RDBMS's like MySQL and SQL Server and also the improved data export capability. In my opinion, the support offered by the previous version was not working correctly since it offered on DML statements for creating the tables you might have wanted to copy or move to an other database using SQL.

I had one small problem though. It seems to me that sql developer is checking the values of the JAVA_HOME and JDK_HOME variables in order to determine the JDK to use. openSUSE defaults these to the /usr/lib/jvm/java path which contains a 1.4.2 jdk, so the workaround was to modify sqldeveloper.sh file so it looks like this :

#!/bin/bash
export JAVA_HOME=/home/thanassis/java/jdk1.5.0_12
export JDK_HOME=/home/thanassis/java/jdk1.5.0_12

cd "`dirname $0`"/sqldeveloper/bin && bash sqldeveloper $*

Tuesday 3 July 2007

Oracle Table containing the list of all countries

I wanted to create a table containing all country codes and countries.so I set off looking for the list of all two letter country codes. I have to admit that today was my lucky day since I ended up finding not only the list but also ready made SQL code to create and populate a MySQL table ready to cut and paste into your PHPMyAdmin SQL window. The link for the MySQL version can be found here

The truth of the matter was that I needed this for Oracle, so cutting, pasting and dining a bit of editing resulted to the following code :


CREATE TABLE COUNTRY (
 COUNTRY_ID NUMBER(*,0) NOT NULL ENABLE,
 COUNTRY_CODE VARCHAR2(10 BYTE) NOT NULL ENABLE,
 COUNTRY VARCHAR2(4000 BYTE) NOT NULL ENABLE,
 CURRENCY VARCHAR2(4 BYTE),
 CONSTRAINT "COUNTRY_PK" PRIMARY KEY (COUNTRY_ID) ENABLE,
 CONSTRAINT "COUNTRY_CODE_UNIQUE" UNIQUE (COUNTRY_CODE) ENABLE
);

-- INSERTING into COUNTRY
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (1,'ac','Ascension Island',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (2,'ad','Andorra',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (3,'ae','United Arab Emirates',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (4,'af','Afghanistan',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (5,'ag','Antigua and Barbuda',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (6,'ai','Anguilla',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (7,'al','Albania',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (8,'am','Armenia',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (9,'an','Netherlands Antilles',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (10,'ao','Angola',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (11,'aq','Antartica',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (12,'ar','Argentina',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (13,'as','American Samoa',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (14,'au','Australia',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (15,'aw','Aruba',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (16,'az','Azerbaijan',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (17,'ba','Bosnia and Herzegovina',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (18,'bb','Barbados',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (19,'bd','Bangladesh',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (20,'be','Belgium',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (21,'bf','Burkina Faso',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (22,'bg','Bulgaria',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (23,'bh','Bahrain',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (24,'bi','Burundi',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (25,'bj','Benin',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (26,'bm','Bermuda',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (27,'bn','Brunei Darussalam',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (28,'bo','Bolivia',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (29,'br','Brazil',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (30,'bs','Bahamas',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (31,'bt','Bhutan',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (32,'bv','Bouvet Island',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (33,'bw','Botswana',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (34,'by','Belarus',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (35,'bz','Belize',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (36,'ca','Canada',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (37,'cc','Cocos (Keeling) Islands',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (38,'cd','Congo, Democratic People''s Republic',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (39,'cf','Central African Republic',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (40,'cg','Congo, Republic of',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (41,'ch','Switzerland',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (42,'ci','Cote d''Ivoire',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (43,'ck','Cook Islands',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (44,'cl','Chile',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (45,'cm','Cameroon',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (46,'cn','China',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (47,'co','Colombia',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (48,'cr','Costa Rica',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (49,'cu','Cuba',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (50,'cv','Cap Verde',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (51,'cx','Christmas Island',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (52,'cy','Cyprus',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (53,'cz','Czeck Republic',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (54,'de','Germany',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (55,'dj','Djibouti',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (56,'dk','Denmark',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (57,'dm','Dominica',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (58,'do','Dominican Republic',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (59,'dz','Algeria',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (60,'ec','Ecuador',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (61,'ee','Estonia',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (62,'eg','Egypt',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (63,'eh','Western Sahara',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (64,'er','Eritrea',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (65,'es','Spain',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (66,'et','Ethiopia',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (67,'fi','Finland',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (68,'fj','Fiji',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (69,'fk','Falkland Islands (Malvina)',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (70,'fm','Micronesia, Federal State of',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (71,'fo','Faroe Islands',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (72,'fr','France',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (73,'ga','Gabon',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (74,'gd','Grenada',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (75,'ge','Georgia',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (76,'gf','French Guiana',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (77,'gg','Guernsey',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (78,'gh','Ghana',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (79,'gi','Gibraltar',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (80,'gl','Greenland',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (81,'gm','Gambia',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (82,'gn','Guinea',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (83,'gp','Guadeloupe',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (84,'gq','Equatorial Guinea',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (85,'gr','Greece',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (86,'gs','South Georgia and the South Sandwich Islands',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (87,'gt','Guatemala',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (88,'gu','Guam',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (89,'gw','Guinea-Bissau',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (90,'gy','Guyana',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (91,'hk','Hong Kong',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (92,'hm','Heard and McDonald Islands',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (93,'hn','Honduras',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (94,'hr','Croatia/Hrvatska',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (95,'ht','Haiti',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (96,'hu','Hungary',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (97,'id','Indonesia',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (98,'ie','Ireland',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (99,'il','Israel',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (100,'im','Isle of Man',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (101,'in','India',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (102,'io','British Indian Ocean Territory',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (103,'iq','Iraq',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (104,'ir','Iran (Islamic Republic of)',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (105,'is','Iceland',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (106,'it','Italy',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (107,'je','Jersey',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (108,'jm','Jamaica',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (109,'jo','Jordan',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (110,'jp','Japan','Yen');
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (111,'ke','Kenya',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (112,'kg','Kyrgyzstan',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (113,'kh','Cambodia',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (114,'ki','Kiribati',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (115,'km','Comoros',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (116,'kn','Saint Kitts and Nevis',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (117,'kp','Korea, Democratic People''s Republic',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (118,'kr','Korea, Republic of',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (119,'kw','Kuwait',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (120,'ky','Cayman Islands',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (121,'kz','Kazakhstan',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (122,'la','Lao, People''s Democratic Republic',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (123,'lb','Lebanon',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (124,'lc','Saint Lucia',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (125,'li','Liechtenstein',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (126,'lk','Sri Lanka',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (127,'lr','Liberia',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (128,'ls','Lesotho',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (129,'lt','Lithuania',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (130,'lu','Luxembourg',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (131,'lv','Latvia',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (132,'ly','Libyan Arab Jamahiriya',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (133,'ma','Morocco',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (134,'mc','Monaco',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (135,'md','Moldova, Republic of',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (136,'mg','Madagascar',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (137,'mh','Marshall Islands',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (138,'mk','Macedonia, Former Yugoslav Republic',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (139,'ml','Mali',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (140,'mm','Myanmar',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (141,'mn','Mongolia',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (142,'mo','Macau',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (143,'mp','Northern Mariana Islands',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (144,'mq','Martinique',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (145,'mr','Mauritania',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (146,'ms','Montserrat',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (147,'mt','Malta',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (148,'mu','Mauritius',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (149,'mv','Maldives',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (150,'mw','Malawi',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (151,'mx','Mexico',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (152,'my','Malaysia',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (153,'mz','Mozambique',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (154,'na','Namibia',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (155,'nc','New Caledonia',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (156,'ne','Niger',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (157,'nf','Norfolk Island',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (158,'ng','Nigeria',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (159,'ni','Nicaragua',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (160,'nl','Netherlands',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (161,'no','Norway',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (162,'np','Nepal',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (163,'nr','Nauru',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (164,'nu','Niue',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (165,'nz','New Zealand',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (166,'om','Oman',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (167,'pa','Panama',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (168,'pe','Peru',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (169,'pf','French Polynesia',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (170,'pg','Papua New Guinea',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (171,'ph','Philippines',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (172,'pk','Pakistan',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (173,'pl','Poland',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (174,'pm','St. Pierre and Miquelon',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (175,'pn','Pitcairn Island',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (176,'pr','Puerto Rico',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (177,'pt','Portugal',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (178,'pw','Palau',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (179,'py','Paraguay',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (180,'qa','Qatar',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (181,'re','Reunion Island',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (182,'ro','Romania',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (183,'ru','Russian Federation',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (184,'rw','Rwanda',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (185,'sa','Saudi Arabia',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (186,'sb','Solomon Islands',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (187,'sc','Seychelles',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (188,'sd','Sudan',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (189,'se','Sweden',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (190,'sg','Singapore',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (191,'sh','St. Helena',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (192,'si','Slovenia',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (193,'sj','Svalbard and Jan Mayen Islands',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (194,'sk','Slovak Republic',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (195,'sl','Sierra Leone',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (196,'sm','San Marino',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (197,'sn','Senegal',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (198,'so','Somalia',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (199,'sr','Suriname',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (200,'st','Sao Tome and Principe',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (201,'sv','El Salvador',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (202,'sy','Syrian Arab Republic',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (203,'sz','Swaziland',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (204,'tc','Turks and Ciacos Islands',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (205,'td','Chad',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (206,'tf','French Southern Territories',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (207,'tg','Togo',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (208,'th','Thailand',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (209,'tj','Tajikistan',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (210,'tk','Tokelau',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (211,'tm','Turkmenistan',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (212,'tn','Tunisia',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (213,'to','Tonga',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (214,'tp','East Timor',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (215,'tr','Turkey',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (216,'tt','Trinidad and Tobago',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (217,'tv','Tuvalu',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (218,'tw','Taiwan',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (219,'tz','Tanzania',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (220,'ua','Ukraine',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (221,'ug','Uganda',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (222,'uk','United Kingdom',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (223,'gb','United Kingdom',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (224,'um','US Minor Outlying Islands',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (225,'us','United States',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (226,'uy','Uruguay',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (227,'uz','Uzbekistan',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (228,'va','Holy See (City Vatican State)',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (229,'vc','Saint Vincent and the Grenadines',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (230,'ve','Venezuela',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (231,'vg','Virgin Islands (British)',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (232,'vi','Virgin Islands (USA)',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (233,'vn','Vietnam',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (234,'vu','Vanuatu',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (235,'wf','Wallis and Futuna Islands',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (236,'ws','Western Samoa',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (237,'ye','Yemen',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (238,'yt','Mayotte',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (239,'yu','Yugoslavia',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (240,'za','South Africa',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (241,'zm','Zambia',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (242,'zr','Zaire',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (243,'zw','Zimbabwe',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (250,'at','Austria',null);
Insert into COUNTRY (COUNTRY_ID,COUNTRY_CODE,COUNTRY,CURRENCY) values (251,'ps','Palestine',null);

Enjoy ..

Wednesday 27 June 2007

JSF Basics Getting the value of an EL expression in code

You can use the following code to get the value of any EL expression defined inside your JSF page For example the following checks the value of the expression #{requestScope.requestRowKey}

        // access the faces context
        FacesContext fc = FacesContext.getCurrentInstance();

        ValueBinding vb = fc.getApplication().createValueBinding("#{requestScope.requestRowKey}");
        String rowKey = (String )vb.getValue(fc);

Assigning a new value can be preformed by using vb.setValue( Object o)

Tuesday 26 June 2007

Wrong date and time format when depolying an Application

All the credit for this post should go to John Stegeman for providing the solution. I will just copy the main points here, so I can find it more easily.

The story goes like this. People are seeing different date and time formats after deploying and running their applications on an Oracle IAS rather than their embedded OC4J where the dates and times display correctly.

The solution appears to be the addition of a <locale-config> tag inside the faces-config.xml file. The entire thread that explains everything can be accessed from here. Furthermore John also posted a link with all the two letter ISO country codes which is available from http://www.bcpl.net/~j1m5path/isocodes-table.html

One final remark. I have been deploying applications on Oracle IAS running on Linux (SuSE Linux 8 and SLES9), since version 9i and never run into anything like it. Could that be a Windows issue ? :-)

Monday 18 June 2007

ADF Tables. Setting an alternative sort property

Suppose you have an ADF table somewhere inside a JSF page and you wish it to be sortable. This is fine as long as you define two things.

  • The sortproperty property of the adf:column tag which has to be one of the column names of the table binding and tell the system how to change the order by clause of the underlying view.
  • The sortbale which has to be set to true or false and determines whether to allow sorting by clicking on the column header or not

So this is a perfectly good column definition for an attribute called Matnr athat corresponds to the notorious SAP material number.

<af:column sortProperty="Matnr" sortable="true"
    headerText="#{bindings['PF_ForSale'].labels.Matnr}">
    <af:outputText value="#{row.Matnr}"/>
</af:column>

If you now happen to have a transient attribute and you wish to sort the table based on this attribute, then you just can't do it. The way I see it, sorting means changing the order by clause and re executing the entire query. ADF complains that there is simply no attribute by that name and throws an SQLException that comes straight from the JDBC driver.

My suggestion for a workaround that works most of the times is this : Since 90% pf transient attributes usually serve as simple lexical transformations of real database attributes, changing the sortProperty of the adf:column tag to the value of the real column might solve the problem

Let's suppose that the ViewObject of the previous example has a transient attribute called DisplayMatnr that simply removes the leading zeros of the SAP oriented material number stored in column Matnr. It goes without saying that if Matnr is sorted then so is DisplayMatnr. Therefore writing something like ...

<af:column sortProperty="Matnr" sortable="true"
    headerText="#{bindings['PF_ForSale'].labels.DisplayMatnr}">
    <af:outputText value="#{row.DisplayMatnr}"/>
</af:column>

... might bring the desired effect. There is one last warning though the attribute that you wish to sort by must be a part of the table definition in the bindings page

Sunday 17 June 2007

Nvidia Howto for SuSE Linux

Tried upgrading my home Linux KDE today. Not much of of update that was but since the wind of change was already blowing into my system, I thought of updating my NVidia driver as well.

Well it worked... YaST said there was a newer version, I selected it, installed it and failed. There was no way to start X again with Sax2 complaining about amissing screen configuration. Then I did two things.

For everyone else into the same kind of trouble my suggestion is Think before you click and the all time classic.RTFM.

Friday 8 June 2007

Setting up a Virtual Host in Oracle IAS 10.1.3.1

Sooner or later it would have to come to this. We decided we wanted to setup our application server so that we would end up with two virtual hosts

  • One providing a set of j2ee applications that will be accessible via port 80. This will be used by intra net users
  • Another virtual host, providing an application named MyApp deployed into an OC4J Instance called OC4J_MYAPP that will be accessible via a different DNS name and used by internet users. The actual application will be accessed via SSL provided by a firewall / SSL Converter hosted on a different machine.

The first thing that needed to be done was to enable Apache to run as root and change the listening port to 80. See previous post Oracle IAS 10g 10.1.3.x Enable Apache to run as root and set the default HTTP port to 80 (non SSL)

The next thing was to set up the virtual host. environment. This includes creating a location for the static documents and he log files. In our case I created a websites directory and added a virtual-host-name directory with two subdirs named htdocs and logs.

You then have to go through the patching part and there is no way you can know this unless somebody from Oracle Support tells you. (Thanks Dan!) Out of the box 10.1.3.1 Oracle IAS needs two patches to be applied one 5555875 is the one that will allow your virtual host to be able to mount OC4J exported applications and the second one 5669155 will be the one that will patch the opatch utility so it will install the first one. Both patchsets are available from Oracle metalink.

After you have successfully applied patch numbers 5669155 and 5555875 in sequence, you will have to modify the the httpd.conf file located in $ORACLE_HOME/Apache/Apache/conf/httpd.conf and add a VirtualHost entry to specify the details of your virtual host. My file looks more or less like this

# Defualt virtual host
NameVirtualHost *

# here is where you must enable acess to EM website and other applications 
# so they may be available from the default host
<VirtualHost *>

    Oc4jMountCopy on
    Oc4jMount /em home
    Oc4jMount /em/* home
</VirtualHost>

# Virtual host for example.com

Oc4jRoutingMode Static

<VirtualHost *>
#    Unique location where a unique index.html file exists:
     DocumentRoot /home/oracle/websites/www.example.com/htdocs

#    Name used for this VirtualHost:
     ServerName www.example.com

#    For this purpose, we may need also access on port 80:
     Port 80

     ServerAdmin        me@example.com

#    Must appear in one line
     CustomLog "|/home/oracle/product/10.1.3.1/OracleAS_1/Apache/Apache
                 /bin/rotatelogs /home/oracle/websites/www.example.com/logs/access_log                     
                 43200" common

    Oc4jMountCopy off
    Oc4jMount /MyApp OC4J_MYAPP
    Oc4jMount /MyApp/* OC4J_MYAPP
</VirtualHost>

More or less that does it.

The details can be found in the Apache documentation for virtual hosts, from where the basic idea is summarized as follows:

When a request arrives, the server will first check if it is using an IP address that matches the NameVirtualHost. If it is, then it will look at each section with a matching IP address and try to find one where the ServerName or ServerAlias matches the requested hostname. If it finds one, then it uses the configuration for that server. If no matching virtual host is found, then the first listed virtual host that matches the IP address will be used.

As a consequence, the first listed virtual host is the default virtual host. The DocumentRoot from the main server will never be used when an IP address matches the NameVirtualHost directive. If you would like to have a special configuration for requests that do not match any particular virtual host, simply put that configuration in a &th;VirtualHost> container and list it first in the configuration file.

So to finish it, Just restart the HTTP_Server ...

opmnctl restartproc process-type=HTTP_Server

and it works.

Just one final touch. Since the virtual server will be the home for a single j2ee application, it might be convinient that the static index.html page of the new virtual host directly sends visitors to the j2ee application URL. This is as easy as adding

<meta http-equiv="refresh" content="0; url=/MyApp">

And think that now we are ready.