Design patterns in Java - Decorator

Introduction


As holy grail of Java OO design book (GoF - Elements Of Reusable OO Software) say: "Decorator design pattern is intended to add additional responsibility to an object dynamically. This design pattern is also known as Wrapper".

So in another words this design pattern enable object inheritance at runtime. It achieve this by using interface inheritance and class composition.

Implementation

In Java classes java.io package also use decorator design pattern. For example:

File f = new file("d:\something.txt");
//Here FileInputStream "inherits" File class.
FileInputStream is = new FileInputStream(f);
//Here we add buffering capability to FileInputStream using decorator.
BufferedInputStream bis = new BufferedInputStream(fi);

I must point out that Decorator design pattern can also be used to remove responsibility from object (our example will not include this case).

Following code represent simple implementation of this pattern in Java.

Interface that will be our default "object". This object will be decorated (extended).

/**
 * Basic phone that can do only basic ringing.
 */
public interface IPhone {
    
    void announceCall();

    void announceMessage();

}

Decorator interface that extends our default "object" interface. So instead of static inheritance in implementation we create interface inheritance.

/**
 * Decorator that adds additional functionality to basic Phone.
 */
public interface IPhoneDecorator extends IPhone {

    void vibrate(int milliseconds);

    void flashLED();
}

Concrete implementation of our "default" object (nothing interesting, just simple interface implementation).

public class Phone implements IPhone {

    /*
     * This Phone announce call only by ringing.
     */
    public void announceCall() {
        System.out.println("Ring!");
    }

    /*
     * Phone announce message by makeing some sound.
     */
    public void announceMessage() {
        System.out.println("Make a sound!");
    }
}

Concrete decorator implementation that implements all operations. It delegate part of functionality to our "default" object and add more functionality of his own. It has one filed named phone which represent our "default" object implementation to which we will delegate stuff.

/**
 * Concrete decorator class that adds additional functionality.
 * @author jan.krizan
 */
public class UpgradePhoneDecorator implements IPhoneDecorator {

    private IPhone phone;

    /**
     * Constructor through which client put
     * instance that will be decorated.
     * @param phone
     */
    public UpgradePhoneDecorator(IPhone phone) {
        super();
        this.phone = phone;
    }

    public void vibrate(int milliseconds) {
        System.out.println("Vibrating for " + milliseconds + " milliseconds.");
    }

    public void flashLED() {
        System.out.println("Make a LED flash!");
    }

    public void announceCall() {
        //Delegate basic functionality to Phone clase.
        phone.announceCall();
        //Add additional functionality.
        vibrate(1500);
    }

    public void announceMessage() {
        //Delegate basic functionality to Phone clase.
        phone.announceMessage();
        //Add additional functionality.
        flashLED();
    }
}

We can now see what can our "default" object do before and after it has been decorated. You can see that we provide phone instance to decorator constructor and by using polymorphism (dynamic binding) we delegate basic work to our "default" object.

public static void main(String[] args) {
        IPhone oldPhone = new Phone();
        System.out.println("What can old phone do!");
        oldPhone.announceCall();
        oldPhone.announceMessage();
        System.out.println();
        IPhoneDecorator decoratedPhone = new UpgradePhoneDecorator(oldPhone);
        System.out.println("What can new phone do!");
        decoratedPhone.announceCall();
        decoratedPhone.announceMessage();
    }

Hope you like this!


Here is output (example uses ANT):

run:
What can old phone do!
Ring!
Make a sound!

What can new phone do!
Ring!
Vibrating for 1500 milliseconds.
Make a sound!
Make a LED flash!
BUILD SUCCESSFUL (total time: 0 seconds)

Faces generator for Oracle ADF (ver. 0.5)

Introduction


Hello everyone. I wish to present you one small project that I worked on in spare time (which I don't have plenty). The ultimate purpose of this "generator project" is to create JDeveloper plugin that will generate complete view controller project for Oracle ADF using just model project. This generator will enable programmer to create complete view controller (or several view controllers) project(s) in any state of application model project. So for example we can in about half an hour create model from database schema and create complete view controller based on that model. Then we can show that prototype to our customer and let him to decide what are necessary changes we need to perform.

Beside prototyping this can also help us in overcome many problems that JDeveloper programming has to offer. :) I know what you may be thinking: "Yes I think this idea is similar to existing Oracle product named HeadStart". But  this generator is flexible and can be customized to organization needs and to organization usual development methods for Oracle ADF. Also I think that programmers like (go figure) to program, so they will not just be clicking but they will create business logic and unit tests on model and that is important. You can also leverage existing skills inside your organization using generator and there is practically no learning curve as opposite to HeadStart.

You can watch how generator work by watching these two videos on youtube:

Hope you like it, full source code for project that is used in demonstration can be download from here.

Complete Android game

Introduction

When developing android games you can decide to use OpenGL (for 3D games development) or Java drawing interfaces for simpler 2D games.
In this example I will show you how you can develop android 2D game. This game is similar to game "hexxagon" which I played a long ago on my old DOS box (eh...memories: 386SX, 2MB RAM, Trident 512KB, 42MB HDD, BASIC, TURBO PASCAL...that was the times!).

Main goal in this game is to have as many as possible balls at the end. There are following rules in this game: You have balls and you can move them or you can copy them (only to length of 3 places). Will the ball perform move or copy depends on distance that it need to travel. Only if distance is 1 then ball is copied. You perform move into destination empty field. Every opponent ball that is around destination empty field will be "transformed"
to you ball color.

Because I understand that this explanation is not so great I made simple video example on youtube and please see how it works, hope it will help you to better understand basic game logic (at least it is better than this horrible description :) ).

Class design

I put many comments in code to make it more readable and useful and if you are interested in details please look there. I will here only explain main class organization and design decisions. You can download full source code from my google code repository.
Please note that this is really far away from any finished or polished code. But enough with excuses let's begin!

We separated game implementation into two main classes. First class (BoardView) is custom view that is responsible for animation, drawing and for handling user input (touch on screen). Second class is main activity class and is responsible for game flow, saving state, restoring state and initialization. This is also only activity in this game. There is also third less important class that handle game logic.

There was strong motivation to separate view from game-flow controller in this implementation. BoardView class handle only stuff that is closely coupled with animation and drawing while activity (MinMaxBalls) handle game logic-flow.

I must admit something. The title of game can confuse experienced programmer that this game implementation uses MinMax algorithm to calculate next move. In first iteration of game implementation this algorithm was present but it use too much of CPU power and in real life we cannot predict that user will choose best move possible so algorithm perform strangely when user choose to perform illogical moves. So I decided that is best to exclude it for now. I will do another blog post about this algorithm but in some simpler scenario game (maybe tic tac toe or something like that).

MinMaxBalls main activity

Either way, here is the code for MinMaxBalls main activity:

package org.codingwithpassion.minmaxballs;

import java.text.DecimalFormat;
import java.text.NumberFormat;
import org.codingwithpassion.minmaxballs.BoardView.MoveStageListener;
import android.app.Activity;
import android.app.AlertDialog;
import android.app.Dialog;
import android.content.DialogInterface;
import android.content.Intent;
import android.os.Bundle;
import android.text.SpannableString;
import android.text.method.LinkMovementMethod;
import android.text.util.Linkify;
import android.util.Log;
import android.view.Menu;
import android.view.MenuItem;
import android.widget.TextView;
import android.widget.Toast;

/**
 * Main activity for game. It is really unnecessary complex and
 * need to be refactored at some point in future.
 * @author jan.krizan
 */
public class MinMaxBalls extends Activity {

 // This is actual location where we store state of board. It is here because
 // of save instance state stuff and decoupling between view/controler and model. 
 // So this activity is something like model.
 private State[][] positions = new State[BoardView.BOARD_SIZE][BoardView.BOARD_SIZE];

 // Game instance that is used mainly for calculating computer moves.
 private Game game = new Game();
 
 // Two TextView views that we use for tracking current score.
 private TextView humanScore;
 private TextView computerScore;
 
 // Dialog and menu id-s.
 private static final int DIALOG_RESET = 1;
 private static final int DIALOG_ABOUT = 2;
 private static final int MENU_RESET = 3;
 private static final int MENU_ABOUT = 4;

 // Intent parameter id.
 private static final String DIFFICULTY = "minmaxdifficulty";
  
 private boolean isFinish = false;

 private NumberFormat numberFormat = new DecimalFormat("00");
 
 // Instance of our view.
 private BoardView boardView;

 /*
  * OK, main activity, nothing clever.
  */
 @Override
 public void onCreate(Bundle savedInstanceState) {
  super.onCreate(savedInstanceState);

  setContentView(R.layout.main);

  boardView = (BoardView) findViewById(R.id.boardView);
  humanScore = (TextView) findViewById(R.id.humanScore);
  computerScore = (TextView) findViewById(R.id.compScore);

  boardView.setFocusable(true);
  boardView.setFocusableInTouchMode(true);
  boardView.setMoveStageListener(new CellSelected());
  
  // Initialize positions.
  Game.setEmptyValues(positions);
  Game.setSolidSquares(positions);
  Game.initializeStartPositions(positions);
  
  // Initial game difficulty.
  int difficulty = Game.MEDIUM_DIFFICULTY;

  Bundle extras = getIntent().getExtras();
  if (extras != null) {
   difficulty = Integer.parseInt(extras.getString(DIFFICULTY));
   Log.d("difficult", String.valueOf(difficulty));
  }
  
  // We set difficulty to know what kind (which "hard-nest") of moves to perform.
  game.setDifficulty(difficulty);
  
  // Bind positions table to View values.
  boardView.setPositions(positions);
    
  refreshScore();
 }
 
 /*
  * Simple but effective refresh score.
  */
 private void refreshScore() {
  humanScore.setText(numberFormat.format(Game.countPlayerBalls(positions,
    State.HUMAN)));
  computerScore.setText(numberFormat.format(Game.countPlayerBalls(
    positions, State.COMP)));
 }
 
 @Override
 protected Dialog onCreateDialog(int id) {
  if (id == DIALOG_RESET) {
   String easy = String
     .valueOf(this.getText(R.string.difficulty_easy));
   String hard = String
     .valueOf(this.getText(R.string.difficulty_hard));
   String medium = String.valueOf(this
     .getText(R.string.difficulty_medium));

   final CharSequence[] items = { easy, medium, hard };

   AlertDialog.Builder builder = new AlertDialog.Builder(this);
   builder.setTitle(R.string.game_difficulty);
   builder.setItems(items, new DialogInterface.OnClickListener() {
    public void onClick(DialogInterface dialog, int item) {
     String difficulty = String.valueOf(Game.MEDIUM_DIFFICULTY);
     if (item == 0) {
      difficulty = String.valueOf(Game.EASY_DIFFICULTY);
     } else if (item == 1) {
      difficulty = String.valueOf(Game.MEDIUM_DIFFICULTY);
     } else if (item == 2) {
      difficulty = String.valueOf(Game.HARD_DIFFICULTY);
     }
     Intent intent = getIntent();
     intent.putExtra(DIFFICULTY, difficulty);
     finish();
     startActivity(intent);
    }
   });
   return builder.create();
  } else if (id == DIALOG_ABOUT) {      
   final TextView message = new TextView(this);   
   final SpannableString s = new SpannableString(
     this.getText(R.string.blog_link));
   Linkify.addLinks(s, Linkify.WEB_URLS);
   message.setText(s);
   message.setMovementMethod(LinkMovementMethod.getInstance());
   AlertDialog.Builder builder = new AlertDialog.Builder(this);   
   builder.setView(message)
     .setCancelable(true)
     .setIcon(android.R.drawable.stat_notify_error)
     .setNegativeButton("OK",
       new DialogInterface.OnClickListener() {
        public void onClick(DialogInterface dialog,
          int id) {
         dialog.cancel();
        }
       });
   return builder.create();
  }
  return null;
 }

 @Override
 public boolean onCreateOptionsMenu(Menu menu) {
  super.onCreateOptionsMenu(menu);

  int groupId = 0;

  MenuItem menuItemReset = menu.add(groupId, MENU_RESET, Menu.NONE,
    R.string.new_game);
  menuItemReset.setIcon(android.R.drawable.star_big_on);
  MenuItem menuItemAbout = menu.add(groupId, MENU_ABOUT, Menu.NONE,
    R.string.about);
  menuItemAbout.setIcon(android.R.drawable.stat_notify_error);

  return true;
 }

 @Override
 public boolean onOptionsItemSelected(MenuItem item) {
  super.onOptionsItemSelected(item);

  if (item.getItemId() == MENU_RESET) {
   showDialog(DIALOG_RESET);
   return true;
  } else if (item.getItemId() == MENU_ABOUT) {
   showDialog(DIALOG_ABOUT);
   return true;
  }

  return false;
 }

 private void showToast(int message) {
  Toast.makeText(getApplicationContext(), message, Toast.LENGTH_LONG)
    .show();
 }

 @Override
 public void onSaveInstanceState(Bundle bundle) {
  super.onSaveInstanceState(bundle);

  if (game.getMove().player == State.HUMAN) {
   if (!isFinish) {
    int source_i = game.getMove().sourceMove.i;
    int source_j = game.getMove().sourceMove.j;
    if (source_i != -1 && source_j != -1) {
     if (positions[source_i][source_j] == State.SELECTED) {
      positions[source_i][source_j] = State.HUMAN;
     }
    }
    game.bestMove(positions, State.COMP);
    game.getMove().player = State.COMP;
    performMove(game.getMove(), false);
    changecolors(game.getMove(), false);
   }
  }

  for (int i = 0; i < positions.length; i++) {
   int[] pos = new int[positions.length];
   for (int j = 0; j < positions.length; j++) {
    pos[j] = positions[i][j].getValue();
   }
   bundle.putIntArray("pos" + i, pos);
  }
 }

 @Override
 public void onRestoreInstanceState(Bundle bundle) {
  for (int i = 0; i < BoardView.BOARD_SIZE; i++) {
   for (int j = 0; j < BoardView.BOARD_SIZE; j++) {
    positions[i][j] = State
      .fromInt(bundle.getIntArray("pos" + i)[j]);
   }
  }
  refreshScore();
 }
 
 /*
  * OK, the real meat of game-flow control. It uses semaphore-like
  * poor design to control flow and it is crucially important to
  * preserve order of if-s and boolean fields checking.
  * Very, very poor design (dragons live here kind of design!) so:
  * TODO: Please refactor this I get pretty nasty poor design smell here!
  */
 private class CellSelected implements MoveStageListener {
  
  // Semaphore-like boolean fields.
  private boolean isCompMove = false;
  private boolean isCompSelected = false;

  private boolean isHumanBallChange = false;
  private boolean isCompBallsChange = false;

  private boolean isCompVictory = false;
  private boolean isHumanVictory = false;
  
  /* 
   * React on user click on the board. If user clicks on her/his
   * ball then select that ball, of she/he select empty field then
   * move ball, else display error by displaying error animation
   * on that square.
   */
  public void userClick(int i, int j) {
   Coordinate humanSource = game.getMove().sourceMove;
   if (!isFinish && positions[i][j] == State.HUMAN) {
    game.getMove().player = State.HUMAN;
    // If we already selected ball and now we change our mind.
    if (humanSource.isNotEmpty()) {
     positions[humanSource.i][humanSource.j] = State.HUMAN;
    }
    positions[i][j] = State.SELECTED;
    boardView.selectBall(i, j, State.SELECTED);
    humanSource.i = i;
    humanSource.j = j;
   } else if (!isFinish
     && humanSource.isNotEmpty()
     && positions[i][j] == State.EMPTY
     && Game.isAllowedDistance(i, j, humanSource.i,
       humanSource.j, 2)) {
    game.getMove().destinationMove.i = i;
    game.getMove().destinationMove.j = j;
    performMove(game.getMove(), true);
    isHumanBallChange = true;
   } else {
    boardView.error(i, j);
    isHumanBallChange = false;
   }

  }
  
  /*
   * If animation is complete, then it is obvious we need to do something.
   * What will be done is decided by checking various boolean fiels.
   */
  public void animationComplete() {
   // TODO: refactor:
   // Excessive conditional logic, must preserve conditions order of 
   // conditions...bad, bad design. :(
   if (isCompVictory) {
    changeAllColors(State.HUMAN, State.COMP);
    isCompVictory = false;
    // stop all activity
    isCompSelected = false;
   }
   if (isHumanVictory) {
    changeAllColors(State.COMP, State.HUMAN);
    isHumanVictory = false;
    // stop all activity
    isCompSelected = false;
   }

   if (isCompBallsChange) {
    changecolors(game.getMove(), true);
    isCompBallsChange = false;
    refreshScore();
    game.deleteMove();
    checkWin();
   }

   if (isCompMove) {

    performMove(game.getMove(), true);
    isCompMove = false;
    isCompBallsChange = true;
   }
   if (isCompSelected) {

    // Calculate move for computer.
    game.bestMove(positions, State.COMP);
    game.getMove().player = State.COMP;
    Coordinate compSelect = game.getMove().sourceMove;
    boardView.selectBall(compSelect.i, compSelect.j, State.SELECTED);
    positions[compSelect.i][compSelect.j] = State.SELECTED;
    isCompSelected = false;
    isCompMove = true;
   }

   if (isHumanBallChange) {
    changecolors(game.getMove(), true);
    isHumanBallChange = false;
    isCompSelected = true;
    refreshScore();
    game.deleteMove();
    checkWin();
   }
  }

  private void checkWin() {
   if (game.isWin(positions, State.HUMAN)) {
    checkWhoWin();
   }
   if (game.isWin(positions, State.COMP)) {
    checkWhoWin();
   }
  }

  private void checkWhoWin() {
   if (Game.countPlayerBalls(positions, State.HUMAN) >= Game
     .countPlayerBalls(positions, State.COMP)) {
    isHumanVictory = true;
    showToast(R.string.human_wins);
    isCompVictory = false;
    isFinish = true;
   } else {
    isCompVictory = true;
    showToast(R.string.comp_wins);
    isHumanVictory = false;
    isFinish = true;
   }
  }
 }
 
 private void changecolors(Move move, boolean withAnimation) {
  State toWho = move.player;
  State fromWho = toWho == State.HUMAN ? State.COMP : State.HUMAN;
  boolean[][] changeThese = Game.changeColors(positions,
    move.destinationMove.i, move.destinationMove.j, fromWho);

  for (int i = 0; i < BoardView.BOARD_SIZE; i++) {
   for (int j = 0; j < BoardView.BOARD_SIZE; j++) {
    if (changeThese[i][j]) {
     positions[i][j] = toWho;
    }
   }
  }
  if (withAnimation)
   boardView.changeColors(changeThese, fromWho, toWho);
 }

 private void changeAllColors(State fromWho, State toWho) {
  boolean[][] changeThese = Game.changeAllColors(positions, fromWho);

  for (int i = 0; i < BoardView.BOARD_SIZE; i++) {
   for (int j = 0; j < BoardView.BOARD_SIZE; j++) {
    if (changeThese[i][j]) {
     positions[i][j] = toWho;
    }
   }
  }

  boardView.changeColors(changeThese, fromWho, toWho);

 }

 private void performMove(Move move, boolean withAnimation) {
  int start_i = move.sourceMove.i;
  int start_j = move.sourceMove.j;
  int dest_i = move.destinationMove.i;
  int dest_j = move.destinationMove.j;

  State who = move.player;
  if (Game.getDistance(start_i, start_j, dest_i, dest_j) > 1) {
   if (withAnimation)
    boardView.moveBall(start_i, start_j, dest_i, dest_j, who);
   positions[start_i][start_j] = State.EMPTY;
  } else {
   if (withAnimation)
    boardView.createBall(dest_i, dest_j, who);
   positions[start_i][start_j] = who;
  }
  positions[dest_i][dest_j] = who;
 }

}

You can see that there is one inner class named CellSelected. It's responsibility is to control game flow and to handle "messages" from view that are send by method calls. It also save instance field data and perform additional moves when is necessary (when user tilt screen for example) in it's onSaveInstanceState method.

BoardView View class

The second interesting class is BoardView class.

package org.codingwithpassion.minmaxballs;

import android.content.Context;
import android.graphics.Canvas;
import android.graphics.Color;
import android.graphics.Paint;
import android.graphics.Paint.Style;
import android.os.Handler;
import android.os.Message;
import android.os.Handler.Callback;
import android.util.AttributeSet;
import android.view.MotionEvent;
import android.view.View;

/**
 * Implementation of board view/controller. It draws board, handle user events
 * (touch), rotating screen and animation.
 * 
 * @author jan.krizan
 */
public class BoardView extends View {

 public static final int BOARD_MARGIN = 10;
 public static final int BOARD_SIZE = 7;
 public static final int GRID_SIZE = 2;

 private static final int MSG_ANIMATE = 0;

 private final Handler animationHandler = new Handler(
   new AnimationMessageHandler());

 private MoveStageListener moveStageListener;

 /**
  * Listener interface that send messages to Activity. Activity then handle
  * this messages.
  */
 public interface MoveStageListener {

  // Fires when user click's somewhere on board.
  void userClick(int i, int j);

  // When animation complete at same current move stage is complete.
  void animationComplete();
 }

 public void setMoveStageListener(MoveStageListener selectionListener) {
  this.moveStageListener = selectionListener;
 }

 /**
  * Animation interface that control animation handler.
  */
 public interface Animation {
  // This is called on onDraw method.
  void animate(Canvas canvas);

  // Say if animation should end.
  boolean isFinish();

  // Control which cells will be animated and hence should be
  // ignored when we draw grid.
  boolean skip(int i, int j);

  // How much frames per second we will use for our animation.
  int fps();
 }

 private Animation animation = new NullAnimation();

 // Here we store animation board state with all players and intermediate
 // states for cells.
 private State[][] positions;

 public void setPositions(State[][] positions) {
  this.positions = positions;
 }

 // Paint for board table line. It is here because onPaint is
 // using it several time per frame.
 private Paint boardLinePaint;

 // Width of board is also calculated dynamically when screen
 // size changes.
 private float boardWidth;

 // Maximum radius of ball - calculated dynamically also...
 private float maxRadius;

 // Can freely be here because it is calculated every time screen size
 // changes.
 private float cellSize;

 public BoardView(Context context, AttributeSet attrs) {
  super(context, attrs);
  requestFocus();
  boardLinePaint = new Paint();
  boardLinePaint.setColor(0xFFFFFFFF);
  boardLinePaint.setStrokeWidth(GRID_SIZE);
  boardLinePaint.setStyle(Style.STROKE);
 }

 /*
  * Classic onDraw. It paints table and ball states. When we need to animate
  * stuff we call it to refresh canvas state (easy as in classic Java 2D
  * graphics animation).
  */
 @Override
 protected void onDraw(Canvas canvas) {
  super.onDraw(canvas);
  float offsetBoardWidth = boardWidth - BOARD_MARGIN;
  canvas.drawRect(BOARD_MARGIN, BOARD_MARGIN, offsetBoardWidth,
    offsetBoardWidth, boardLinePaint);

  for (int i = 0; i < BOARD_SIZE; i++) {
   float cellStep = BOARD_MARGIN + (i * cellSize);
   canvas.drawLine(cellStep, BOARD_MARGIN, cellStep, offsetBoardWidth,
     boardLinePaint);
   canvas.drawLine(BOARD_MARGIN, cellStep, offsetBoardWidth, cellStep,
     boardLinePaint);
  }

  setValuesFromDatas(canvas);

  animation.animate(canvas);

 }

 /*
  * Set values from board state structure and skip animated items.
  */
 private void setValuesFromDatas(Canvas canvas) {
  for (int i = 1; i < BOARD_SIZE + 1; i++) {
   for (int j = 1; j < BOARD_SIZE + 1; j++) {
    // If this are currently animated squares, do not
    // draw them!
    if (!animation.skip(i - 1, j - 1))
     drawBall(i, j, positions[i - 1][j - 1], maxRadius, canvas,
       255);
    drawSolidSquare(canvas, i, j, positions[i - 1][j - 1]);
   }
  }
 }

 /*
  * Method for drawing filled square (when user touch inappropriate section
  * of table). It is stupid to create Paint object every time, but it is here
  * for readability and encapsulation reasons.
  */
 private void drawWhiteSquare(Canvas canvas, int i, int j, int alpha) {
  Paint paint = new Paint();
  paint.setColor(Color.WHITE);
  paint.setStyle(Style.FILL);
  paint.setAlpha(alpha);
  drawCustomRect(i, j, canvas, paint, 0);
 }

 private void drawCustomRect(int i, int j, Canvas canvas, Paint paint,
   float shrink) {
  canvas.drawRect(i * cellSize + GRID_SIZE + BOARD_MARGIN + shrink, j
    * cellSize + GRID_SIZE + BOARD_MARGIN + shrink, (i + 1)
    * cellSize - GRID_SIZE + BOARD_MARGIN - shrink, (j + 1)
    * cellSize + BOARD_MARGIN - GRID_SIZE - shrink, paint);
 }

 /*
  * Draw fancy "disabled" and solid square. Same story here for Paint object
  * as in drawWhiteSquare method.
  */
 private void drawSolidSquare(Canvas canvas, int i, int j, State who) {
  if (who == State.BLOCK) {

   Paint paintBigger = new Paint();
   paintBigger.setColor(0xFFA800A8);
   paintBigger.setStyle(Style.FILL);

   drawCustomRect(i - 1, j - 1, canvas, paintBigger, 0);

   Paint paintSmaller = new Paint();
   paintSmaller.setColor(0xFFFC54FC);
   paintSmaller.setStyle(Style.FILL);

   float shrink = cellSize * 0.15f;

   drawCustomRect(i - 1, j - 1, canvas, paintSmaller, shrink);

   canvas.drawLine((i - 1) * cellSize + GRID_SIZE + BOARD_MARGIN,
     (j - 1) * cellSize + GRID_SIZE + BOARD_MARGIN, (i - 1)
       * cellSize + GRID_SIZE + BOARD_MARGIN + shrink,
     (j - 1) * cellSize + GRID_SIZE + BOARD_MARGIN + shrink,
     paintSmaller);

   canvas.drawLine(i * cellSize - GRID_SIZE + BOARD_MARGIN, (j - 1)
     * cellSize + GRID_SIZE + BOARD_MARGIN, i * cellSize
     - GRID_SIZE + BOARD_MARGIN - shrink, (j - 1) * cellSize
     + GRID_SIZE + BOARD_MARGIN + shrink, paintSmaller);

   canvas.drawLine(i * cellSize - GRID_SIZE + BOARD_MARGIN, j
     * cellSize - GRID_SIZE + BOARD_MARGIN, i * cellSize
     - GRID_SIZE + BOARD_MARGIN - shrink, j * cellSize
     - GRID_SIZE + BOARD_MARGIN - shrink, paintSmaller);

   canvas.drawLine((i - 1) * cellSize + GRID_SIZE + BOARD_MARGIN, j
     * cellSize - GRID_SIZE + BOARD_MARGIN, (i - 1) * cellSize
     + GRID_SIZE + BOARD_MARGIN + shrink, j * cellSize
     - GRID_SIZE + BOARD_MARGIN - shrink, paintSmaller);
  }

 }

 /*
  * Draw custom balls. We can change balls alpha and radius in animation.
  */
 private void drawBall(int i, int j, State who, float radius, Canvas canvas,
   int alpha) {

  // Calculate where we will put ball in our grid based on coordinates in
  // grid.
  float x = cellSize * (i - 1) + cellSize / 2 + BOARD_MARGIN;
  float y = cellSize * (j - 1) + cellSize / 2 + BOARD_MARGIN;
  // Skip empty every time.
  if (who != State.EMPTY && who != State.BLOCK) {
   Paint smallBall = new Paint();

   int color = Color.RED;
   if (who == State.SELECTED)
    color = Color.BLACK;
   else if (who == State.COMP)
    color = Color.BLUE;
   smallBall.setColor(color);
   smallBall.setStyle(Style.FILL);
   smallBall.setAlpha(alpha);

   Paint bigBall = new Paint();
   bigBall.setColor(Color.WHITE);
   bigBall.setStyle(Style.FILL);
   bigBall.setAlpha(alpha);

   // Smaller ball is 15% smaller than bigger.
   canvas.drawCircle(x, y, radius * 1.15f, bigBall);

   canvas.drawCircle(x, y, radius, smallBall);
  }
 }

 /*
  * Select ball action operation (ball become black).
  */
 public void selectBall(int i, int j, State who) {
  animation = new PutBall();
  PutBall putBall = (PutBall) animation;
  putBall.alpha = 0;
  putBall.i = i;
  putBall.j = j;
  putBall.who = State.SELECTED;

  animationHandler.sendEmptyMessage(MSG_ANIMATE);
 }

 /*
  * Create new ball operation (on empty square in grid).
  */
 public void createBall(int i, int j, State who) {
  animation = new CreateBallAnimation();
  CreateBallAnimation createBallAnimation = (CreateBallAnimation) animation;
  createBallAnimation.radius = 0;
  createBallAnimation.i = i;
  createBallAnimation.j = j;
  createBallAnimation.who = who;

  animationHandler.sendEmptyMessage(MSG_ANIMATE);
 }

 /*
  * Paint square in white block operation (along with alpha animation) when
  * user perform illegal move.
  */
 public void error(int i, int j) {
  animation = new FillSquareAnimation();
  FillSquareAnimation fillSquareAnimation = (FillSquareAnimation) animation;
  fillSquareAnimation.i = i;
  fillSquareAnimation.j = j;
  fillSquareAnimation.alpha = 255;

  animationHandler.sendEmptyMessage(MSG_ANIMATE);
 }

 /*
  * Move ball from one place to another operation (with animation also).
  */
 public void moveBall(int i1, int j1, int i2, int j2, State who) {
  animation = new MoveBallsAnimation();
  MoveBallsAnimation createBallAnimation = (MoveBallsAnimation) animation;
  createBallAnimation.radius = maxRadius;
  createBallAnimation.moveFrom[i1][j1] = true;
  createBallAnimation.moveTo[i2][j2] = true;
  createBallAnimation.whoFrom = who;
  createBallAnimation.whoTo = who;

  animationHandler.sendEmptyMessage(MSG_ANIMATE);
 }

 /*
  * Change colors for all balls operation that have same coordinates as true
  * values in "changeThem" matrix. Animation is same as for move operation.
  */
 public void changeColors(boolean[][] changeThem, State whoFrom, State whoTo) {
  animation = new MoveBallsAnimation();
  MoveBallsAnimation createBallAnimation = (MoveBallsAnimation) animation;
  createBallAnimation.radius = maxRadius;
  createBallAnimation.moveFrom = changeThem;
  createBallAnimation.moveTo = changeThem;
  createBallAnimation.whoFrom = whoFrom;
  createBallAnimation.whoTo = whoTo;

  animationHandler.sendEmptyMessage(MSG_ANIMATE);
 }

 @Override
 public boolean onTouchEvent(MotionEvent event) {
  if (animation.isFinish()) {
   int action = event.getAction();

   int i = (int) ((event.getX() - BOARD_MARGIN) / cellSize);
   int j = (int) ((event.getY() - BOARD_MARGIN) / cellSize);

   if (i >= 0 && i <= (BOARD_SIZE - 1) && j >= 0
     && j <= (BOARD_SIZE - 1)) {

    // If user just click, then we will show painted square.
    if (action == MotionEvent.ACTION_DOWN) {
     moveStageListener.userClick(i, j);
     return true;
    }
   }
  }

  return false;
 }

 /*
  * Recalculate fields based on current screen size.
  */
 @Override
 protected void onSizeChanged(int w, int h, int oldw, int oldh) {
  super.onSizeChanged(w, h, oldw, oldh);
  boardWidth = w < h ? w : h;
  cellSize = (boardWidth - GRID_SIZE * BOARD_MARGIN) / BOARD_SIZE;

  maxRadius = cellSize * 0.68f / 2;
 }

 /*
  * Set dimension of current view.
  */
 protected void onMeasure(int widthMeasureSpec, int heightMeasureSpec) {
  int w = MeasureSpec.getSize(widthMeasureSpec);
  int h = MeasureSpec.getSize(heightMeasureSpec);
  int d = w == 0 ? h : h == 0 ? w : w < h ? w : h;
  setMeasuredDimension(d, d);
 }

 /**
  * Inner animation handler. This handler call itself several times during
  * animation and in every pass invalidates current view (calls onDraw method
  * of View). It is controlled by Animation interface and hence concrete
  * implementation of Animation interface. This implementation "tells" it
  * when to stop.
  */
 private class AnimationMessageHandler implements Callback {
  public boolean handleMessage(Message msg) {
   if (msg.what == MSG_ANIMATE) {
    BoardView.this.invalidate();
    if (!animationHandler.hasMessages(MSG_ANIMATE)) {
     if (animation.isFinish()) {
      animationHandler.removeMessages(MSG_ANIMATE);
      moveStageListener.animationComplete();
     } else {
      animationHandler.sendEmptyMessageDelayed(MSG_ANIMATE,
        animation.fps());
     }
    }
    return true;
   }
   return false;
  }
 }

 /**
  * This animation doesn't do anything - null animation.
  */
 private class NullAnimation implements Animation {
  public void animate(Canvas canvas) {
   // do nothing
  }

  public boolean isFinish() {
   return true;
  }

  public boolean skip(int i, int j) {
   return false;
  }

  public int fps() {
   return 1000 / 1;
  }
 }

 /**
  * Create ball animation (balls pops-up up in empty square).
  */
 private class CreateBallAnimation implements Animation {

  public int i;
  public int j;
  public State who;
  public float radius;

  public void animate(Canvas canvas) {
   drawBall(i + 1, j + 1, who, radius, canvas, 255);
   radius += 8;
   if (radius >= BoardView.this.maxRadius)
    radius = BoardView.this.maxRadius;
  }

  public boolean isFinish() {
   return radius >= BoardView.this.maxRadius;
  }

  public boolean skip(int i, int j) {
   return (this.i == i && this.j == j);
  }

  public int fps() {
   return 1000 / 16;
  }
 }

 /**
  * Move ball animation that moves current ball from one square to another
  * altogether with pop-ing-up effect. :) It can be use for one ball or ball
  * set (represented by coordinate matrix).
  */
 private class MoveBallsAnimation implements Animation {
  public boolean[][] moveFrom = new boolean[BOARD_SIZE][BOARD_SIZE];
  public boolean[][] moveTo = new boolean[BOARD_SIZE][BOARD_SIZE];
  public State whoFrom;
  public State whoTo;
  public float radius;

  public boolean firstPahseFinish;
  public boolean secondPhaseFinish;

  public void animate(Canvas canvas) {
   if (!firstPahseFinish) {
    for (int i = 0; i < BOARD_SIZE; i++) {
     for (int j = 0; j < BOARD_SIZE; j++) {
      if (moveFrom[i][j])
       drawBall(i + 1, j + 1, whoFrom, radius, canvas, 255);
     }
    }

    radius -= 8;
    if (radius <= 0) {
     radius = 0;
     firstPahseFinish = true;
    }
   } else {

    for (int i = 0; i < BOARD_SIZE; i++) {
     for (int j = 0; j < BOARD_SIZE; j++) {
      if (moveTo[i][j])
       drawBall(i + 1, j + 1, whoTo, radius, canvas, 255);
     }
    }

    radius += 8;
    if (radius >= maxRadius) {
     radius = maxRadius;
     secondPhaseFinish = true;
    }
   }
  }

  public boolean isFinish() {
   return firstPahseFinish && secondPhaseFinish;
  }

  public boolean skip(int i, int j) {
   return moveFrom[i][j] || moveTo[i][j];
  }

  public int fps() {
   return 1000 / 16;
  }
 }

 /**
  * Paint square with white gradually disappeared white inner square.
  */
 private class FillSquareAnimation implements Animation {

  public int i;
  public int j;

  public int alpha;

  public void animate(Canvas canvas) {
   drawWhiteSquare(canvas, i, j, alpha);
   alpha -= 75;
   if (alpha <= 0)
    alpha = 0;
  }

  public boolean isFinish() {
   return alpha <= 0;
  }

  public boolean skip(int i, int j) {
   return false;
  }

  public int fps() {
   return 1000 / 16;
  }
 }
 
 /**
  * And last but not the least animation that gradually change ball
     * color.  
  */
 private class PutBall implements Animation {

  public int i;
  public int j;
  public State who;
  public int alpha;

  public void animate(Canvas canvas) {
   drawBall(i + 1, j + 1, who, maxRadius, canvas, alpha);
   alpha += 100;
   if (alpha >= 255)
    alpha = 255;
  }

  public boolean isFinish() {
   return alpha >= 255;
  }

  public boolean skip(int i, int j) {
   return (this.i == i && this.j == j);
  }

  public int fps() {
   return 1000 / 16;
  }
 }
}

This class extends View and overrides onDraw method. This is rudimentary animation implementation in android and it uses Java 2D interfaces so that means that it is almost same as Core Java 2D animation.

It defined two interfaces. MoveStageListener interface listener is used for sending messages to activity (MinMaxBalls) class. Second interface Animation is used to send messages to Message Handler. Message Handler is used to perform animation loop and to invalidate view (so it calls onDraw method). We can do this also using another thread, but in this way we have slightly cleaner code.

Animation interfaces "tells" Message Handler when to stop, how much fps it should use and stuff like that. We have several implementation of this Animation interfaces and all perform some simple animations.

And that's it. Please feel free to put comments if you want ask/suggest anything and I will do as much as I can to notice that comments (It is hard to notice comments on blog, because blogger do not send emails when someone put comment -- they shoud!) :) And yes, I will also try to answer them.

XPath in Java

XPath is support in Java by default. In other to use is, you need to parse XML document using DOM (or maybe SAX -- I don't know if that works). Either way, when you do that, just call XPathFactory.newInstance().newXPath() and call evaluate method, like in following example:

public static Object xPathQuery(String expression, File file) {
         Document document;
         DocumentBuilderFactory dBF = DocumentBuilderFactory.newInstance();
         Object result = null;
         try {
             DocumentBuilder builder = dBF.newDocumentBuilder();
             document = builder.parse(file);
             
             XPath xpath = XPathFactory.newInstance().newXPath();
             XPathExpression expr = xpath.compile(expression);

             result = expr.evaluate(document, XPathConstants.NODESET);             
         } catch (Exception e) {
             e.printStackTrace();
         }
         return result;
     }

You can see that this method returns Object. This object can be cast to boolean, String or (more useful) NodeList. Be careful, this type depends of type of your XPath query. So for example to extract NodeList from XPath query that returns multiple nodes will look like this:

NodeList nodes = (NodeList) xPathQuery("//employees", new File("C:/some_xml"));

int j = nodes.getLength();

for (int i = 0; i < j; i++) {
    System.out.println(nodes.item(i).getTextContent());
}

And that's it!

Saxon XSLT Java example

I was looking for simple Saxon XSLT transformation example using Java and I could not find one that is simple and descriptive enough, so I made one and I hope it will helpful for someone. So here it is :

import java.io.File;
import javax.xml.transform.Transformer;
import javax.xml.transform.TransformerFactory;
import javax.xml.transform.stream.StreamResult;
import javax.xml.transform.stream.StreamSource;

public class Main {

    /**
     * Simple transformation method.
     * @param sourcePath - Absolute path to source xml file.
     * @param xsltPath - Absolute path to xslt file.
     * @param resultDir - Directory where you want to put resulting files.
     */
    public static void simpleTransform(String sourcePath, String xsltPath,
                                       String resultDir) {
        TransformerFactory tFactory = TransformerFactory.newInstance();
        try {
            Transformer transformer =
                tFactory.newTransformer(new StreamSource(new File(xsltPath)));

            transformer.transform(new StreamSource(new File(sourcePath)),
                                  new StreamResult(new File(resultDir)));
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static void main(String[] args) {
        //Set saxon as transformer.
        System.setProperty("javax.xml.transform.TransformerFactory",
                           "net.sf.saxon.TransformerFactoryImpl");

        simpleTransform("d:/project/hob/AppModule.xml",
                        "d:/project/hob/create-fragment.xslt", "C:/");

    }
}

You just need to put Saxon library into project CLASSPATH. This example create resulting file(s) on the root of C: drive.

Have fun!

Slow JDeveloper

Speed up JDeveloper


There are times when JDeveloper slow down so much that it becomes unusable. For example you need to wait 5-30 seconds for simple text change or you wait for structure window to refresh. This can happen when you have complex .jspx pages and when all your components are binds to backing bean.

There are some serious issues with JDeveloper memory management. This can be due to Java Swing API, but this is just wild guest and I really don't know nothing about this.

Eater way, to solve this problem you have two choices:
Use external editor (This is not so good solution).
Other (much better) is to disable automatic component binding by removing binding line from your jspx page. This will rise performance to acceptable level (noting spectacular).

General tip is not to have complex Java backing bean pages and complex .jspx pages (use fragments to simplify layout).

PL/SQL Cursors example

Introduction

PL/SQL provides a number of different ways for data retrieval all of which include working with cursors. You can think of cursor as a pointer to the results of a query run against one or more tables in current database. PL/SQL cursor and Java Database Connectivity (JDBC) cursors also share some similarities. Now when Oracle buys Sun it is only matter of time when we will have natural mapping between JDBC cursors and PL/SQL cursors! :) Only kidding here, this things should never be mixed together because JDBC spec should be independent from vendor and it is designed to prevent vendor locking and whole point of Java are clever interfaces and delegation of vendor specific stuff to vendor.

Ok, let's get back to point of this tutorial.

Why use cursors? Well when you retrieve subset of data from table (or whole table), then that data remains stored in SGA (Shared memory) until cursor is closed, so in this way you cache data and caching on database is good idea.

Choosing explicit or implicit cursor in your PL/SQL program?

Implicit cursors are used when you have a simple SELECT ... INTO single row of data into local program variables. It's the easiest path to your data, but it can often lead to coding the same or similar SELECTs in multiple places in your code.

Explicit cursors are defined in declaration section (package or block) and in this way, you can open and fetch from cursor in one or more places.

Implicit cursor will run more efficient than equivalent explicit cursor (from Oracle 8 Database onwards). So is there reasons to use explicit cursors at all? Off course. Explicit cursor can still be more efficient and they off course offer much programmatic control.

Implicit cursor

Implicit cursors are used when you need to retrieve single row from database. If you want to retrieve more than one row, then you must use either an explicit cursor or bulk collect.

Here one example of implicit cursor usage:

SET serveroutput on;

DECLARE

   PROCEDURE find_employee (employee_id_v employees.employee_id%TYPE)
   IS
      --Record in which we will fetch entire row.  
      emp_rec   employees%ROWTYPE;
   BEGIN
      --Begining of implicit cursor statement.
      SELECT *
        INTO emp_rec --Fetch into record.
        FROM employees
       WHERE employee_id = employee_id_v;
       --Write result.
      DBMS_OUTPUT.put_line (emp_rec.employee_id || ' ' || emp_rec.first_name);
   --Catch exception when there is no such employee.
   EXCEPTION
      WHEN NO_DATA_FOUND
      THEN         
         DBMS_OUTPUT.put_line ('Unknown employee with id: ' || employee_id_v);
   END find_employee;
BEGIN
   find_employee (101);
   find_employee (102);
   --This one will produce exeption (OK, only if you do not have employee  with id 1021).
   find_employee (1021);
END;

We encapsulate query with function (this is _aways_ a good idea). This function print employee information from database to output. Also we introduce some exception handling (when
no employee is found).

Because PL/SQL is so tightly integrated with the Oracle database, you can easily retrieve complex datatypes (entire row for example - as we did in our example).

You can see that using implicit cursor is quite simple (with basic understanding of SQL) we just create simple select statement and insert rowset into record (that we declared as local variable).

Explicit cursor

Explicit cursor is explicitly defined in the declaration section. With explicit cursor, you have complete control over the different PL/SQL steps involved in retrieving information from the database. You decide when to open, when fetch and how many records and when to close cursor. Information about the current state of cursor is available through examination of cursor attributes.

Example:

SET SERVEROUTPUT on;

DECLARE
   PROCEDURE get_all_employees
   IS
      --Employee record variable.
      employee_rec   employees%ROWTYPE;
      --Cursor variable for explicit use.
      CURSOR employee_cur
      IS
         SELECT *
           FROM employees;
   BEGIN
      --Open cursor so you can use it.      
      OPEN employee_cur;
      --Go through all employees.
      LOOP
         --Load current row from cursor into employee record. 
         FETCH employee_cur
          INTO employee_rec;
         --Loop until cursor attribute signals that no rows are found.
         EXIT WHEN employee_cur%NOTFOUND;
         DBMS_OUTPUT.put_line (   employee_rec.employee_id
                               || ', '
                               || employee_rec.first_name
                              );
      END LOOP;

      CLOSE employee_cur;
   EXCEPTION
      --Remember to close cursor even if there was some error.
      WHEN OTHERS
      THEN
         IF employee_cur%ISOPEN
         THEN
            CLOSE employee_cur;
         END IF;
   END get_all_employees;
BEGIN
   get_all_employees ();
END;

This PL/SQL block performs following:
Declare the cursor.
Declare а record based on that cursor.
Open the cursor.
Fetch rows until there are no rows left.
Close cursor.
Handle exception and close cursor if it is not closed.

You can see that in this way we have complete control of cursor variable and cursor initialization, fetching and so on.

PL/SQL (Forms) Word and Excel manipulation using OLE (OLE2)

Introduction

Several years ago I was given requirement for excel/word reporting through PL/SQL Oracle Forms 10g application. One catch was that these reports need to fill existing reports with data and I can not use reporting tool of any kind. Because I came from Java and OO word I started developing Java bean that will be inserted into Forms. I had idea to create reports through this bean (using Apache POI) and then I will fuse this bean into Forms applet. I was almost done, but then I find out that Oracle deliver library that can (among other things) handle Forms <-> Word or Excel communication called WEBUTIL. So I start to leverage this library and came up with following PL/SQL package for Word and Excel communication using this package and OLE2. I hope you will find them useful I will not comment them because they are quite complex, but if you have questions please comment (or ask me privately) and I will try to response in shortest time possible.

Excel package
Package specification:

PACKAGE excel
IS
   /*
             Global excel.Application Object --> this represent excel Object.
     */
   appl_id   client_ole2.obj_type;

   /*
           Open file that act as template. Parameter are:
           _application_ -- global word parameter that we initialize at 
           begining.
           _file_ -- file name we wish to open --> it can be from database, or filesystem...
   */
   FUNCTION file_open (application client_ole2.obj_type, FILE VARCHAR2)
      RETURN client_ole2.obj_type;

   /*
           Close current file.
   */
   PROCEDURE file_close (document client_ole2.obj_type);

   /*
           Saves current file (It is useful if we need to save current 
           file using another name)
   */
   PROCEDURE file_save_as (document client_ole2.obj_type, FILE VARCHAR2);

   /*
           Isert number (not formated)
           x - horizontal axei.
           y - vertical axis.
           v - value.
   */
   PROCEDURE insert_number (
      worksheet   client_ole2.obj_type,
      x           NUMBER,
      y           NUMBER,
      v           NUMBER
   );

   /*
           Insert number and format it as decimal value. 
           x - horizontal axei.
           y - vertical axis.
           v - value.
           Napomena: !!!THIS DOES NOT WORK IN EXCEL 2007!!!
   */
   PROCEDURE insert_number_decimal (
      worksheet   client_ole2.obj_type,
      x           NUMBER,
      y           NUMBER,
      v           NUMBER
   );

   /*
           Insert characters  (not formated)
           x - horizontal axei.
           y - vertical axis.
           v - value.
   */
   PROCEDURE insert_char (
      worksheet   client_ole2.obj_type,
      x           NUMBER,
      y           NUMBER,
      v           VARCHAR2
   );

   /*
           Insert character - formated
           color - numbers (15 for example is gray)           
           style - BOLD' or 'ITALIC'
           x - horizontal axei.
           y - vertical axis.
           v - value.
   */
   PROCEDURE insert_char_formated (
      worksheet   client_ole2.obj_type,
      x           NUMBER,
      y           NUMBER,
      v           VARCHAR2,
      color       NUMBER,
      style       VARCHAR2
   );

   /*
           Set autofit on whole sheet.
   */
   PROCEDURE set_auto_fit (worksheet client_ole2.obj_type);

   /*
           Set autofit for range r. For example. r can be: 'A2:E11'
   */
   PROCEDURE set_auto_fit_range (worksheet client_ole2.obj_type, r VARCHAR2);

   /*
           Put decimal format (0.00) on range r.
   */
   PROCEDURE set_decimal_format_range (
      worksheet   client_ole2.obj_type,
      r           VARCHAR2
   );

   /*
           Create new workbook.
   */
   FUNCTION new_workbook (application client_ole2.obj_type)
      RETURN client_ole2.obj_type;

   /*
           Create new worksheet.
   */
   FUNCTION new_worksheet (workbook client_ole2.obj_type)
      RETURN client_ole2.obj_type;

   /*
           Saves file in client tempfolder (It is necessary to save file if edit template).
   */
   FUNCTION download_file (
      file_name         IN   VARCHAR2,
      table_name        IN   VARCHAR2,
      column_name       IN   VARCHAR2,
      where_condition   IN   VARCHAR2
   )
      RETURN VARCHAR2;

   /*
           Run macro on client excel document.
   */
   PROCEDURE run_macro_on_document (
      document   client_ole2.obj_type,
      macro      VARCHAR2
   );

   /*
           Limit network load...not important.
   */
   PROCEDURE insert_number_array (
      worksheet   client_ole2.obj_type,
      x           NUMBER,
      y           NUMBER,
      v           VARCHAR2
   );
END;

Package body:

PACKAGE BODY excel
IS
   FUNCTION file_open (application client_ole2.obj_type, FILE VARCHAR2)
      RETURN client_ole2.obj_type
   IS
      arg_list    client_ole2.list_type;
      document    client_ole2.obj_type;
      documents   client_ole2.obj_type;
   BEGIN
      arg_list := client_ole2.create_arglist;
      documents := client_ole2.invoke_obj (application, 'Workbooks');
      client_ole2.add_arg (arg_list, FILE);
      document := client_ole2.invoke_obj (documents, 'Open', arg_list);
      client_ole2.destroy_arglist (arg_list);
      client_ole2.RELEASE_OBJ (documents);
      RETURN document;
   END file_open;

   PROCEDURE file_save_as (document client_ole2.obj_type, FILE VARCHAR2)
   IS
      arg_list   client_ole2.list_type;
   BEGIN
      arg_list := client_ole2.create_arglist;
      client_ole2.add_arg (arg_list, FILE);
      client_ole2.invoke (document, 'SaveAs', arg_list);
      client_ole2.destroy_arglist (arg_list);
   END file_save_as;

   FUNCTION new_workbook (application client_ole2.obj_type)
      RETURN client_ole2.obj_type
   IS
      workbook    client_ole2.obj_type;
      workbooks   client_ole2.obj_type;
   BEGIN
      workbooks := client_ole2.get_obj_property (application, 'Workbooks');
      workbook := client_ole2.invoke_obj (workbooks, 'Add');
      client_ole2.RELEASE_OBJ (workbooks);
      RETURN workbook;
   END new_workbook;

   FUNCTION new_worksheet (workbook client_ole2.obj_type)
      RETURN client_ole2.obj_type
   IS
      worksheets   client_ole2.obj_type;
      worksheet    client_ole2.obj_type;
   BEGIN
      worksheets := client_ole2.get_obj_property (workbook, 'Worksheets');
      worksheet := client_ole2.invoke_obj (worksheets, 'Add');
      client_ole2.RELEASE_OBJ (worksheets);
      RETURN worksheet;
   END new_worksheet;

   PROCEDURE file_close (document client_ole2.obj_type)
   IS
   BEGIN
      client_ole2.invoke (document, 'Close');
   END file_close;

   /*
       Macro:    Cells(3, 4).Value = 3
                   Cells(3, 4).Select
                   Selection.NumberFormat = "0.00"
   */
   PROCEDURE insert_number_decimal (
      worksheet   client_ole2.obj_type,
      x           NUMBER,
      y           NUMBER,
      v           NUMBER
   )
   IS
      args        client_ole2.list_type;
      cell        client_ole2.obj_type;
      selection   client_ole2.obj_type;
   BEGIN
      IF v IS NOT NULL
      THEN
         args := client_ole2.create_arglist;
         client_ole2.add_arg (args, x);
         client_ole2.add_arg (args, y);
         cell := client_ole2.get_obj_property (worksheet, 'Cells', args);
         client_ole2.destroy_arglist (args);
         client_ole2.set_property (cell, 'Value', v);
         client_ole2.invoke (cell, 'Select');
         selection := client_ole2.invoke_obj (appl_id, 'Selection');
         client_ole2.set_property (selection, 'Numberformat', '#.##0,00');
         client_ole2.RELEASE_OBJ (selection);
         client_ole2.RELEASE_OBJ (cell);
      END IF;
   END;

   /* Macro:
                       Cells(x, y).Value = v
   */
   PROCEDURE insert_number (
      worksheet   client_ole2.obj_type,
      x           NUMBER,
      y           NUMBER,
      v           NUMBER
   )
   IS
      args   client_ole2.list_type;
      cell   ole2.obj_type;
   BEGIN
      IF v IS NOT NULL
      THEN
         args := client_ole2.create_arglist;
         client_ole2.add_arg (args, x);
         client_ole2.add_arg (args, y);
         cell := client_ole2.get_obj_property (worksheet, 'Cells', args);
         client_ole2.destroy_arglist (args);
         client_ole2.set_property (cell, 'Value', v);
         client_ole2.RELEASE_OBJ (cell);
      END IF;
   END;


   PROCEDURE insert_char (
      worksheet   client_ole2.obj_type,
      x           NUMBER,
      y           NUMBER,
      v           VARCHAR2
   )
   IS
      args   client_ole2.list_type;
      cell   client_ole2.obj_type;
   BEGIN
      IF v IS NOT NULL
      THEN
         args := client_ole2.create_arglist;
         client_ole2.add_arg (args, x);
         client_ole2.add_arg (args, y);
         cell := client_ole2.get_obj_property (worksheet, 'Cells', args);
         client_ole2.destroy_arglist (args);
         client_ole2.set_property (cell, 'Value', v);
         client_ole2.RELEASE_OBJ (cell);
      END IF;
   END;

  
   /*
           Macro:
                       Cells(x, y).Value = v
                       Cells(x, y).Select
                       Selection.Interior.ColorIndex = color
                       if (style in 'BOLD')
                           Selection.Font.Bold = True
                       else if (style in 'ITALIC')
                           Selection.Font.Italic = True
   */
   PROCEDURE insert_char_formated (
      worksheet   client_ole2.obj_type,
      x           NUMBER,
      y           NUMBER,
      v           VARCHAR2,
      color       NUMBER,
      style       VARCHAR2
   )
   IS
      args        client_ole2.list_type;
      cell        client_ole2.obj_type;
      selection   client_ole2.obj_type;
      font        client_ole2.obj_type;
      interior    client_ole2.obj_type;
   BEGIN
      IF v IS NOT NULL
      THEN
         args := client_ole2.create_arglist;
         client_ole2.add_arg (args, x);
         client_ole2.add_arg (args, y);
         cell := client_ole2.get_obj_property (worksheet, 'Cells', args);
         client_ole2.destroy_arglist (args);
         client_ole2.set_property (cell, 'Value', v);
         client_ole2.invoke (cell, 'Select');
         selection := client_ole2.invoke_obj (appl_id, 'Selection');
         font := client_ole2.invoke_obj (selection, 'Font');
         interior := client_ole2.invoke_obj (selection, 'Interior');

         IF UPPER (style) IN ('BOLD', 'ITALIC')
         THEN
            client_ole2.set_property (font, style, TRUE);
         END IF;

         client_ole2.set_property (interior, 'ColorIndex', color);
         client_ole2.RELEASE_OBJ (interior);
         client_ole2.RELEASE_OBJ (font);
         client_ole2.RELEASE_OBJ (selection);
         client_ole2.RELEASE_OBJ (cell);
      END IF;
   END;

   /*
           Macro:
                       Range(r).Select
                       Selection.Columns.AutoFit
                       Cells(1,1).Select
   */
   PROCEDURE set_auto_fit_range (worksheet client_ole2.obj_type, r VARCHAR2)
   IS
      args        client_ole2.list_type;
      --range
      rang        client_ole2.obj_type;
      selection   client_ole2.obj_type;
      colum       client_ole2.obj_type;
      cell        client_ole2.obj_type;
   BEGIN
      args := client_ole2.create_arglist;
      client_ole2.add_arg (args, r);
      rang := client_ole2.get_obj_property (worksheet, 'Range', args);
      client_ole2.destroy_arglist (args);
      client_ole2.invoke (rang, 'Select');
      selection := client_ole2.invoke_obj (appl_id, 'Selection');
      colum := client_ole2.invoke_obj (selection, 'Columns');
      client_ole2.invoke (colum, 'AutoFit');
      --now select upper (1,1) for deselection.      
      args := client_ole2.create_arglist;
      client_ole2.add_arg (args, 1);
      client_ole2.add_arg (args, 1);
      cell := client_ole2.get_obj_property (worksheet, 'Cells', args);
      client_ole2.invoke (cell, 'Select');
      client_ole2.destroy_arglist (args);
      client_ole2.RELEASE_OBJ (colum);
      client_ole2.RELEASE_OBJ (selection);
      client_ole2.RELEASE_OBJ (rang);
   END set_auto_fit_range;

   /*
           Macro:
                       Range(r).Select
                       Selection.Numberformat = "0.00"
                       Cells(1,1).Select
   */
   PROCEDURE set_decimal_format_range (
      worksheet   client_ole2.obj_type,
      r           VARCHAR2
   )
   IS
      args        client_ole2.list_type;
      --range
      rang        client_ole2.obj_type;
      selection   client_ole2.obj_type;
      --colum Client_OLE2.Obj_Type;
      cell        client_ole2.obj_type;
   BEGIN
      args := client_ole2.create_arglist;
      client_ole2.add_arg (args, r);
      rang := client_ole2.get_obj_property (worksheet, 'Range', args);
      client_ole2.destroy_arglist (args);
      client_ole2.invoke (rang, 'Select');
      selection := client_ole2.invoke_obj (appl_id, 'Selection');
      --colum:= Client_OLE2.invoke_obj(selection, 'Columns');
      client_ole2.set_property (selection, 'Numberformat', '#.##0,00');
      --Client_OLE2.invoke(colum, 'AutoFit');
      --now select upper (1,1) for deselection.      
      args := client_ole2.create_arglist;
      client_ole2.add_arg (args, 1);
      client_ole2.add_arg (args, 1);
      cell := client_ole2.get_obj_property (worksheet, 'Cells', args);
      client_ole2.invoke (cell, 'Select');
      client_ole2.destroy_arglist (args);
      --Client_OLE2.release_obj(colum);
      client_ole2.RELEASE_OBJ (selection);
      client_ole2.RELEASE_OBJ (rang);
   END set_decimal_format_range;

   /*
           Macro:Cells.Select
                       Selection.Columns.AutoFit
                       Cells(1,1).Select
   */
   PROCEDURE set_auto_fit (worksheet client_ole2.obj_type)
   IS
      args        client_ole2.list_type;
      cell        client_ole2.obj_type;
      selection   client_ole2.obj_type;
      colum       client_ole2.obj_type;
   BEGIN
      cell := client_ole2.get_obj_property (worksheet, 'Cells');
      client_ole2.invoke (cell, 'Select');
      selection := client_ole2.invoke_obj (appl_id, 'Selection');
      colum := client_ole2.invoke_obj (selection, 'Columns');
      client_ole2.invoke (colum, 'AutoFit');
      --now select upper (1,1) for deselection.      
      args := client_ole2.create_arglist;
      client_ole2.add_arg (args, 1);
      client_ole2.add_arg (args, 1);
      cell := client_ole2.get_obj_property (worksheet, 'Cells', args);
      client_ole2.invoke (cell, 'Select');
      client_ole2.destroy_arglist (args);
      client_ole2.RELEASE_OBJ (colum);
      client_ole2.RELEASE_OBJ (selection);
      client_ole2.RELEASE_OBJ (cell);
   END set_auto_fit;

   PROCEDURE run_macro_on_document (
      document   client_ole2.obj_type,
      macro      VARCHAR2
   )
   IS
      arg_list             client_ole2.list_type;
      bookmarkcollection   client_ole2.obj_type;
      bookmarkobj          client_ole2.obj_type;
      selectionobj         client_ole2.obj_type;
   BEGIN
      arg_list := client_ole2.create_arglist;
      client_ole2.add_arg (arg_list, macro);
      client_ole2.invoke (excel.appl_id, 'Run', arg_list);
      client_ole2.destroy_arglist (arg_list);
   END;

   FUNCTION download_file (
      file_name         IN   VARCHAR2,
      table_name        IN   VARCHAR2,
      column_name       IN   VARCHAR2,
      where_condition   IN   VARCHAR2
   )
      RETURN VARCHAR2
   IS
      l_ok          BOOLEAN;
      c_file_name   VARCHAR2 (512);
      c_path        VARCHAR2 (255);
   BEGIN
      SYNCHRONIZE;
      c_path := client_win_api_environment.get_temp_directory (FALSE);

      IF c_path IS NULL
      THEN
         c_path := 'C:\';
      ELSE
         c_path := c_path || '\';
      END IF;

      c_file_name := c_path || file_name;
      l_ok :=
         webutil_file_transfer.db_to_client_with_progress
                                                   (c_file_name,
                                                    table_name,
                                                    column_name,
                                                    where_condition,
                                                    'Transfer on file system',
                                                    'Progress'
                                                   );
      SYNCHRONIZE;

      IF NOT l_ok
      THEN
         msg_popup ('File not found in database', 'E', TRUE);
      END IF;

      RETURN c_path || file_name;
   END download_file;
END;

Word package
Package specification

PACKAGE word
IS
   /*
           Global Word.Application Object --> represent word object.
   */
   appl_id   client_ole2.obj_type;

   /*
           Open file that act as template. Parameter are:
          _application_ -- global word parameter that we initialize at
          begining.
          _file_ -- file name we wish to open --> it can be from database, or filesystem...
   */
   FUNCTION file_open (application client_ole2.obj_type, FILE VARCHAR2)
      RETURN client_ole2.obj_type;

   /*
           Close current file.
   */
   PROCEDURE file_close (document client_ole2.obj_type);

   /*
           Saves current file (It is useful if we need to save current
          file using another name)
   */
   PROCEDURE file_save_as (document client_ole2.obj_type, FILE VARCHAR2);

   /*
           (Bizniss end of this whole package;) ) Inserts value in specific word bookmark.
           _dokcument_ -- Word document.
           _bookmark_ -- Name of bookmark that is defined in word template,
           _content_ --  Content we wish to insert into bookmark.
   */
   PROCEDURE insertafter_bookmark (
      document   client_ole2.obj_type,
      bookmark   VARCHAR2,
      content    VARCHAR2
   );

   /*
           InsertAfter_Bookmark insert after bookmark and then delete that bookmark and this is not
           good if you itarate through values, so this one do not delete bookmark after insert.
           same paramters as previous one.
   */
   PROCEDURE replace_bookmark (
      document   client_ole2.obj_type,
      bookmark   VARCHAR2,
      content    VARCHAR2
   );

   /*
           Saame as previous procedure but it handle next for you.
   */
   PROCEDURE insertafter_bookmark_next (
      document   client_ole2.obj_type,
      bookmark   VARCHAR2,
      content    VARCHAR2
   );

   /*
           This one after value insert move itself on next row into table. When I say next I mean next-down.
           This is essential for iterating through word table (one row at the time)
           We need manualy create new row if it does not exists.!!!
   */
   PROCEDURE insertafter_bookmark_down (
      document   client_ole2.obj_type,
      bookmark   VARCHAR2,
      content    VARCHAR2
   );

   /*
           Easy...delete bookmark,
   */
   PROCEDURE delete_bookmark (document client_ole2.obj_type, bookmark VARCHAR2);

   /*
           Create new table row (see InsertAfter_Bookmark_Next)
   */
   PROCEDURE insert_new_table_row (
      document   client_ole2.obj_type,
      bookmark   VARCHAR2
   );

   /*
           Move bookmakr (ONLY IN TABLE) left, right, up, down.
           _direction_ can have following valyes'UP', 'DOWN', 'LEFT', 'RIGHT'
   */
   PROCEDURE move_table_bookmark (
      document    client_ole2.obj_type,
      bookmark    VARCHAR2,
      direction   VARCHAR2
   );

   /*
           File download.
           parametar _file_name_  -- client file name (name on client)
           _table_name_ -- Table name for where BLOB column is.
           _column_name_ -- BLOB column name that holds Word template.
           -where_condition_ -- filter.
   */
   FUNCTION download_file (
      file_name         IN   VARCHAR2,
      table_name        IN   VARCHAR2,
      column_name       IN   VARCHAR2,
      where_condition   IN   VARCHAR2
   )
      RETURN VARCHAR2;

   /*
           Calling macro's on bookmarks...only for test.
   */
   PROCEDURE run_macro_on_bookmark (
      document   client_ole2.obj_type,
      bookmark   VARCHAR2,
      macro      VARCHAR2
   );

   PROCEDURE run_macro_on_document (
      document   client_ole2.obj_type,
      macro      VARCHAR2
   );
END;

Package body

PACKAGE BODY word
IS
   FUNCTION file_open (application client_ole2.obj_type, FILE VARCHAR2)
      RETURN client_ole2.obj_type
   IS
      arg_list    client_ole2.list_type;
      document    client_ole2.obj_type;
      documents   client_ole2.obj_type;
   BEGIN
      arg_list := client_ole2.create_arglist;
      documents := client_ole2.invoke_obj (application, 'documents');
      client_ole2.add_arg (arg_list, FILE);
      document := client_ole2.invoke_obj (documents, 'Open', arg_list);
      client_ole2.destroy_arglist (arg_list);
      client_ole2.RELEASE_OBJ (documents);
      RETURN document;
   END file_open;

   PROCEDURE file_close (document client_ole2.obj_type)
   IS
   BEGIN
      client_ole2.invoke (document, 'Close');
   --CLIENT_OLE2.RELEASE_OBJ(document);
   END file_close;

   PROCEDURE file_save_as (document client_ole2.obj_type, FILE VARCHAR2)
   IS
      arg_list   client_ole2.list_type;
   BEGIN
      arg_list := client_ole2.create_arglist;
      client_ole2.add_arg (arg_list, FILE);
      client_ole2.invoke (document, 'SaveAs', arg_list);
      client_ole2.destroy_arglist (arg_list);
   --CLIENT_OLE2.RELEASE_OBJ(document);
   END file_save_as;

   PROCEDURE replace_bookmark (
      document   client_ole2.obj_type,
      bookmark   VARCHAR2,
      content    VARCHAR2
   )
   IS
      arg_list             client_ole2.list_type;
      bookmarkcollection   client_ole2.obj_type;
      bookmarkobj          client_ole2.obj_type;
      selectionobj         client_ole2.obj_type;
   BEGIN
      bookmarkcollection :=
                     client_ole2.invoke_obj (document, 'Bookmarks', arg_list);
      arg_list := client_ole2.create_arglist;
      client_ole2.add_arg (arg_list, bookmark);
      bookmarkobj :=
                client_ole2.invoke_obj (bookmarkcollection, 'Item', arg_list);
      client_ole2.destroy_arglist (arg_list);
      client_ole2.invoke (bookmarkobj, 'Select');
      selectionobj := client_ole2.invoke_obj (appl_id, 'Selection');
      arg_list := client_ole2.create_arglist;
      client_ole2.add_arg (arg_list, content);
      client_ole2.invoke (selectionobj, 'Delete');
      client_ole2.invoke (selectionobj, 'InsertAfter', arg_list);
      client_ole2.destroy_arglist (arg_list);
      client_ole2.RELEASE_OBJ (selectionobj);
      client_ole2.RELEASE_OBJ (bookmarkobj);
      client_ole2.RELEASE_OBJ (bookmarkcollection);
   END replace_bookmark;

   PROCEDURE insertafter_bookmark (
      document   client_ole2.obj_type,
      bookmark   VARCHAR2,
      content    VARCHAR2
   )
   IS
      arg_list             client_ole2.list_type;
      bookmarkcollection   client_ole2.obj_type;
      bookmarkobj          client_ole2.obj_type;
      selectionobj         client_ole2.obj_type;
   BEGIN
      bookmarkcollection :=
                     client_ole2.invoke_obj (document, 'Bookmarks', arg_list);
      arg_list := client_ole2.create_arglist;
      client_ole2.add_arg (arg_list, bookmark);
      bookmarkobj :=
                client_ole2.invoke_obj (bookmarkcollection, 'Item', arg_list);
      client_ole2.destroy_arglist (arg_list);
      client_ole2.invoke (bookmarkobj, 'Select');
      selectionobj := client_ole2.invoke_obj (appl_id, 'Selection');
      arg_list := client_ole2.create_arglist;
      client_ole2.add_arg (arg_list, content);
      client_ole2.invoke (selectionobj, 'InsertAfter', arg_list);
      client_ole2.destroy_arglist (arg_list);
      client_ole2.RELEASE_OBJ (selectionobj);
      client_ole2.RELEASE_OBJ (bookmarkobj);
      client_ole2.RELEASE_OBJ (bookmarkcollection);
   END insertafter_bookmark;

   PROCEDURE insertafter_bookmark_next (
      document   client_ole2.obj_type,
      bookmark   VARCHAR2,
      content    VARCHAR2
   )
   IS
      arg_list             client_ole2.list_type;
      bookmarkcollection   client_ole2.obj_type;
      bookmarkobj          client_ole2.obj_type;
      selectionobj         client_ole2.obj_type;
   BEGIN
      bookmarkcollection :=
                     client_ole2.invoke_obj (document, 'Bookmarks', arg_list);
      arg_list := client_ole2.create_arglist;
      client_ole2.add_arg (arg_list, bookmark);
      bookmarkobj :=
                client_ole2.invoke_obj (bookmarkcollection, 'Item', arg_list);
      client_ole2.destroy_arglist (arg_list);
      client_ole2.invoke (bookmarkobj, 'Select');
      selectionobj := client_ole2.invoke_obj (appl_id, 'Selection');
      arg_list := client_ole2.create_arglist;
      client_ole2.add_arg (arg_list, content || CHR (13));
      client_ole2.invoke (selectionobj, 'InsertAfter', arg_list);
      client_ole2.destroy_arglist (arg_list);
      client_ole2.RELEASE_OBJ (selectionobj);
      client_ole2.RELEASE_OBJ (bookmarkobj);
      client_ole2.RELEASE_OBJ (bookmarkcollection);
   END insertafter_bookmark_next;

   PROCEDURE insertafter_bookmark_down (
      document   client_ole2.obj_type,
      bookmark   VARCHAR2,
      content    VARCHAR2
   )
   IS
      arg_list             client_ole2.list_type;
      bookmarkcollection   client_ole2.obj_type;
      bookmarkobj          client_ole2.obj_type;
      selectionobj         client_ole2.obj_type;
   BEGIN
      bookmarkcollection :=
                     client_ole2.invoke_obj (document, 'Bookmarks', arg_list);
      arg_list := client_ole2.create_arglist;
      client_ole2.add_arg (arg_list, bookmark);
      bookmarkobj :=
                client_ole2.invoke_obj (bookmarkcollection, 'Item', arg_list);
      client_ole2.destroy_arglist (arg_list);
      client_ole2.invoke (bookmarkobj, 'Select');
      selectionobj := client_ole2.invoke_obj (appl_id, 'Selection');
      arg_list := client_ole2.create_arglist;
      client_ole2.add_arg (arg_list, content);
      client_ole2.invoke (selectionobj, 'InsertAfter', arg_list);
      client_ole2.invoke (selectionobj, 'Cut');
      client_ole2.invoke (selectionobj, 'SelectCell');
      client_ole2.invoke (selectionobj, 'MoveDown');
      client_ole2.invoke (selectionobj, 'Paste');
      client_ole2.destroy_arglist (arg_list);
      client_ole2.RELEASE_OBJ (selectionobj);
      client_ole2.RELEASE_OBJ (bookmarkobj);
      client_ole2.RELEASE_OBJ (bookmarkcollection);
   END insertafter_bookmark_down;

   PROCEDURE delete_bookmark (document client_ole2.obj_type, bookmark VARCHAR2)
   IS
      arg_list             client_ole2.list_type;
      bookmarkcollection   client_ole2.obj_type;
      bookmarkobj          client_ole2.obj_type;
      selectionobj         client_ole2.obj_type;
   BEGIN
      bookmarkcollection :=
                     client_ole2.invoke_obj (document, 'Bookmarks', arg_list);
      arg_list := client_ole2.create_arglist;
      client_ole2.add_arg (arg_list, bookmark);
      bookmarkobj :=
                client_ole2.invoke_obj (bookmarkcollection, 'Item', arg_list);
      client_ole2.destroy_arglist (arg_list);
      client_ole2.invoke (bookmarkobj, 'Select');
      selectionobj := client_ole2.invoke_obj (appl_id, 'Selection');
      client_ole2.invoke (selectionobj, 'Delete');
      client_ole2.RELEASE_OBJ (selectionobj);
      client_ole2.RELEASE_OBJ (bookmarkobj);
      client_ole2.RELEASE_OBJ (bookmarkcollection);
   END delete_bookmark;

   PROCEDURE run_macro_on_bookmark (
      document   client_ole2.obj_type,
      bookmark   VARCHAR2,
      macro      VARCHAR2
   )
   IS
      arg_list             client_ole2.list_type;
      bookmarkcollection   client_ole2.obj_type;
      bookmarkobj          client_ole2.obj_type;
      selectionobj         client_ole2.obj_type;
   BEGIN
      bookmarkcollection :=
                     client_ole2.invoke_obj (document, 'Bookmarks', arg_list);
      arg_list := client_ole2.create_arglist;
      client_ole2.add_arg (arg_list, bookmark);
      bookmarkobj :=
                client_ole2.invoke_obj (bookmarkcollection, 'Item', arg_list);
      client_ole2.destroy_arglist (arg_list);
      client_ole2.invoke (bookmarkobj, 'Select');
      selectionobj := client_ole2.invoke_obj (appl_id, 'Selection');
      arg_list := client_ole2.create_arglist;
      client_ole2.add_arg (arg_list, macro);
      client_ole2.invoke (word.appl_id, 'Run', arg_list);
      client_ole2.destroy_arglist (arg_list);
      client_ole2.RELEASE_OBJ (selectionobj);
      client_ole2.RELEASE_OBJ (bookmarkobj);
      client_ole2.RELEASE_OBJ (bookmarkcollection);
   END;

   PROCEDURE run_macro_on_document (
      document   client_ole2.obj_type,
      macro      VARCHAR2
   )
   IS
      arg_list             client_ole2.list_type;
      bookmarkcollection   client_ole2.obj_type;
      bookmarkobj          client_ole2.obj_type;
      selectionobj         client_ole2.obj_type;
   BEGIN
      --bookmarkCollection := CLIENT_OLE2.INVOKE_OBJ(document, 'Bookmarks', arg_list);
      --arg_list := CLIENT_OLE2.CREATE_ARGLIST;
      --CLIENT_OLE2.ADD_ARG(arg_list, bookmark);
      --bookmarkObj := CLIENT_OLE2.INVOKE_OBJ(bookmarkCollection, 'Item',arg_list);
      --CLIENT_OLE2.DESTROY_ARGLIST(arg_list);

      --CLIENT_OLE2.INVOKE(bookmarkObj, 'Select');
      --selectionObj := CLIENT_OLE2.INVOKE_OBJ(appl_id, 'Selection');
      arg_list := client_ole2.create_arglist;
      client_ole2.add_arg (arg_list, macro);
      client_ole2.invoke (word.appl_id, 'Run', arg_list);
      client_ole2.destroy_arglist (arg_list);
   --CLIENT_OLE2.RELEASE_OBJ(selectionObj);
   --CLIENT_OLE2.RELEASE_OBJ(bookmarkObj);
   --CLIENT_OLE2.RELEASE_OBJ(bookmarkCollection);
   END;

   PROCEDURE insert_new_table_row (
      document   client_ole2.obj_type,
      bookmark   VARCHAR2
   )
   IS
      arg_list             client_ole2.list_type;
      bookmarkcollection   client_ole2.obj_type;
      bookmarkobj          client_ole2.obj_type;
      selectionobj         client_ole2.obj_type;
   BEGIN
      bookmarkcollection :=
                     client_ole2.invoke_obj (document, 'Bookmarks', arg_list);
      arg_list := client_ole2.create_arglist;
      client_ole2.add_arg (arg_list, bookmark);
      bookmarkobj :=
                client_ole2.invoke_obj (bookmarkcollection, 'Item', arg_list);
      client_ole2.destroy_arglist (arg_list);
      client_ole2.invoke (bookmarkobj, 'Select');
      selectionobj := client_ole2.invoke_obj (appl_id, 'Selection');
      arg_list := client_ole2.create_arglist;
      client_ole2.add_arg (arg_list, 1);
      client_ole2.invoke (selectionobj, 'InsertRowsBelow', arg_list);
      client_ole2.destroy_arglist (arg_list);
      client_ole2.RELEASE_OBJ (selectionobj);
      client_ole2.RELEASE_OBJ (bookmarkobj);
      client_ole2.RELEASE_OBJ (bookmarkcollection);
   END insert_new_table_row;

   PROCEDURE move_down_table_bookmark (
      document   client_ole2.obj_type,
      bookmark   VARCHAR2
   )
   IS
      arg_list             client_ole2.list_type;
      bookmarkcollection   client_ole2.obj_type;
      bookmarkobj          client_ole2.obj_type;
      selectionobj         client_ole2.obj_type;
   BEGIN
      bookmarkcollection :=
                     client_ole2.invoke_obj (document, 'Bookmarks', arg_list);
      arg_list := client_ole2.create_arglist;
      client_ole2.add_arg (arg_list, bookmark);
      bookmarkobj :=
                client_ole2.invoke_obj (bookmarkcollection, 'Item', arg_list);
      client_ole2.destroy_arglist (arg_list);
      client_ole2.invoke (bookmarkobj, 'Select');
      selectionobj := client_ole2.invoke_obj (appl_id, 'Selection');
      client_ole2.invoke (selectionobj, 'Cut');
      client_ole2.invoke (selectionobj, 'SelectCell');
      client_ole2.invoke (selectionobj, 'MoveDown');
      client_ole2.invoke (selectionobj, 'Paste');
      client_ole2.RELEASE_OBJ (selectionobj);
      client_ole2.RELEASE_OBJ (bookmarkobj);
      client_ole2.RELEASE_OBJ (bookmarkcollection);
   END move_down_table_bookmark;

   PROCEDURE move_up_table_bookmark (
      document   client_ole2.obj_type,
      bookmark   VARCHAR2
   )
   IS
      arg_list             client_ole2.list_type;
      bookmarkcollection   client_ole2.obj_type;
      bookmarkobj          client_ole2.obj_type;
      selectionobj         client_ole2.obj_type;
   BEGIN
      bookmarkcollection :=
                     client_ole2.invoke_obj (document, 'Bookmarks', arg_list);
      arg_list := client_ole2.create_arglist;
      client_ole2.add_arg (arg_list, bookmark);
      bookmarkobj :=
                client_ole2.invoke_obj (bookmarkcollection, 'Item', arg_list);
      client_ole2.destroy_arglist (arg_list);
      client_ole2.invoke (bookmarkobj, 'Select');
      selectionobj := client_ole2.invoke_obj (appl_id, 'Selection');
      client_ole2.invoke (selectionobj, 'Cut');
      client_ole2.invoke (selectionobj, 'SelectCell');
      client_ole2.invoke (selectionobj, 'MoveUp');
      client_ole2.invoke (selectionobj, 'Paste');
      client_ole2.RELEASE_OBJ (selectionobj);
      client_ole2.RELEASE_OBJ (bookmarkobj);
      client_ole2.RELEASE_OBJ (bookmarkcollection);
   END move_up_table_bookmark;

   PROCEDURE move_left_table_bookmark (
      document   client_ole2.obj_type,
      bookmark   VARCHAR2
   )
   IS
      arg_list             client_ole2.list_type;
      bookmarkcollection   client_ole2.obj_type;
      bookmarkobj          client_ole2.obj_type;
      selectionobj         client_ole2.obj_type;
   BEGIN
      bookmarkcollection :=
                     client_ole2.invoke_obj (document, 'Bookmarks', arg_list);
      arg_list := client_ole2.create_arglist;
      client_ole2.add_arg (arg_list, bookmark);
      bookmarkobj :=
                client_ole2.invoke_obj (bookmarkcollection, 'Item', arg_list);
      client_ole2.destroy_arglist (arg_list);
      client_ole2.invoke (bookmarkobj, 'Select');
      selectionobj := client_ole2.invoke_obj (appl_id, 'Selection');
      client_ole2.invoke (selectionobj, 'Cut');
      client_ole2.invoke (selectionobj, 'SelectCell');
      client_ole2.invoke (selectionobj, 'MoveUp');
      client_ole2.invoke (selectionobj, 'Paste');
      client_ole2.RELEASE_OBJ (selectionobj);
      client_ole2.RELEASE_OBJ (bookmarkobj);
      client_ole2.RELEASE_OBJ (bookmarkcollection);
   END move_left_table_bookmark;

   PROCEDURE move_table_bookmark (
      document    client_ole2.obj_type,
      bookmark    VARCHAR2,
      direction   VARCHAR2
   )
   IS
      arg_list             client_ole2.list_type;
      bookmarkcollection   client_ole2.obj_type;
      bookmarkobj          client_ole2.obj_type;
      selectionobj         client_ole2.obj_type;
   BEGIN
      bookmarkcollection :=
                     client_ole2.invoke_obj (document, 'Bookmarks', arg_list);
      arg_list := client_ole2.create_arglist;
      client_ole2.add_arg (arg_list, bookmark);
      bookmarkobj :=
                client_ole2.invoke_obj (bookmarkcollection, 'Item', arg_list);
      client_ole2.destroy_arglist (arg_list);
      client_ole2.invoke (bookmarkobj, 'Select');
      selectionobj := client_ole2.invoke_obj (appl_id, 'Selection');

      IF UPPER (direction) IN ('UP', 'DOWN', 'LEFT', 'RIGHT')
      THEN
         client_ole2.invoke (selectionobj, 'Cut');
         client_ole2.invoke (selectionobj, 'SelectCell');
         client_ole2.invoke (selectionobj, 'Move' || direction);
         client_ole2.invoke (selectionobj, 'Paste');
      END IF;

      client_ole2.RELEASE_OBJ (selectionobj);
      client_ole2.RELEASE_OBJ (bookmarkobj);
      client_ole2.RELEASE_OBJ (bookmarkcollection);
   END move_table_bookmark;

   FUNCTION download_file (
      file_name         IN   VARCHAR2,
      table_name        IN   VARCHAR2,
      column_name       IN   VARCHAR2,
      where_condition   IN   VARCHAR2
   )
      RETURN VARCHAR2
   IS
      l_ok          BOOLEAN;
      c_file_name   VARCHAR2 (512);
      c_path        VARCHAR2 (255);
   BEGIN
      SYNCHRONIZE;
      c_path := client_win_api_environment.get_temp_directory (FALSE);

      IF c_path IS NULL
      THEN
         c_path := 'C:\';
      ELSE
         c_path := c_path || '\';
      END IF;

      c_file_name := c_path || file_name;
      l_ok :=
         webutil_file_transfer.db_to_client_with_progress
                                                   (c_file_name,
                                                    table_name,
                                                    column_name,
                                                    where_condition,
                                                    'Transfer on file system',
                                                    'Progress'
                                                   );
      SYNCHRONIZE;
      RETURN c_path || file_name;
   END download_file;
END;

Simple test

PROCEDURE Call(c_prog IN VARCHAR2,param1 IN VARCHAR2 DEFAULT NULL,value1 IN VARCHAR2 DEFAULT NULL, 
                                    param2 IN VARCHAR2 DEFAULT NULL,value2 IN VARCHAR2 DEFAULT NULL) IS 
  list_id Paramlist; 
BEGIN 
   --Check if list exists. 
   list_id := Get_Parameter_List('param_list'); 
   IF NOT Id_Null(list_id) THEN 
     Destroy_Parameter_List(list_id); -- Ako postoji, unisti je! 
   END IF; 
 
   list_id := Create_Parameter_List('param_list'); 
 
   Add_Parameter(list_id, 'ps_sif',TEXT_PARAMETER, :Global.ps_sif); 
   Add_Parameter(list_id, 'frm_sif',TEXT_PARAMETER, :Global.frm_sif); 
   Add_Parameter(list_id, 'god_sif',TEXT_PARAMETER, :Global.god_sif); 
   Add_Parameter(list_id, 'ana_id',TEXT_PARAMETER, :Global.ana_id); 
   Add_Parameter(list_id, 'id_radnik',TEXT_PARAMETER, :Global.id_radnik); 
   Add_Parameter(list_id, 'forma',TEXT_PARAMETER, UPPER(c_prog)); 
 
   IF param1 IS NOT NULL THEN 
     Add_Parameter(list_id, param1,TEXT_PARAMETER, value1); 
   END IF; 
 
   IF param2 IS NOT NULL THEN 
     Add_Parameter(list_id, param2,TEXT_PARAMETER, value2); 
   END IF; 
 
 
   CALL_FORM(c_prog || '.FMX', NO_HIDE, DO_REPLACE, NO_QUERY_ONLY, list_id); 
 
END; 

Design Patterns in Java (Java EE) - Model View Controller (aka MVC)

MVC Concept

MVC pattern is quite simple and very useful pattern for context representation. Representation can be of any kind (text based consoles - OBERON OS for example, or modern GWT based applications).

Please download sample code from my google code repository.

MVC approach divides components into three kinds:
Model that holds model of data we wish to represent, it represents the business logic of an application. It encapsulate business rules into components (JavaBeans in JSP/Servlets MVC application).
View that present data on screen (render model). Model is rendered in such way that it enable interaction with model data and model operations.
Controller that react to user input and based on what is selected it make decisions for making specific calls on model components (update model for example) and selecting appropriate view for displaying updated or existing data.

Because of limitations of web architecture (page are refreshed only when use request them), we cannot use push model of MVC. With this model data has "power" to updates model. For example if you have two simultaneous users that are accessing same web page and same data, if one of them update part of the data, that update will be available immediately to other user. We all know that this does not happen, but there are some tweaks (please see this oracle description for techniques that enable this - long polling, etc.).

Ok, but what can we do to have full functional MVC design in our JEE application. We can (as many framework do) use pull model design concept. With this model view pulls data from model as user initiate request. Or you can wait for WebSocket to be properly (by complying to HTML5 spec.) implemented by all browser vendors and as we know until now, all browser are beautifully aligned with W3C spec, so no problem here! :)
Note: If you still want to use some of cutting edge HTML5 stuff, please use libraries (like jQuery, prototype) right away because then when HTML5 spec changes (and it will!) you just need to upgrade you library. :)
I will also do some tutorials later on jQuery (maybe jQuery mobile?) and full fat client JavaScript application that can leverage HTML5 stuff.

Whatever strategy we use, MVC architecture still delivers its key benefits. This are:

• Each component has clear responsibility;
• models contain no view-specific code;
• view contain no control code or data-access code and concentrate on data display;
• controllers create and updates models, they do not depend on particular view implementations.

MVC using JSP and servlets

Servlet are good at data processing (reading data, communication with database, invoking business service). JSPs on other hand are good at presentation of data (building HTML to present result of request). So it is natural to combine these two Java EE core concepts for MVC implementation.

The original request is handled by servlet. The servlet invokes the business services (we can use Spring for initializing business service object, but it will be overkill for this example) components which will perform data access and create beans to represent the result (this is the model). Then the server decides which JSP page is appropriate to present those particular result and forwards the request there (this is where JSP pages play they part - view). Servlet decide what business logic code applies and which JSP page should preset (so servlet is the controller).

Simple (but really simple) MVC example

In this example some user will chose product to buy on first page. When he select (or submit without selecting anything) product than the servlet RequestDispatcher (controller) will choose to which JSP page (view) will send request and put product transfer object - DTO (model) to request scope attribute.

Here are couple of images:
First image show list of products, second image show JSP page that is displayed after controller decide which view to show (you can see here that we do not have enough amount of that product on stock, so we suggest customer to buy another product).

Here is controller servlet code:

protected void processRequest(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {

        //get parameter
        String param = request.getParameter("productId");
        //check if null
        int productId = param != null ? Integer.parseInt(param) : 0;

        Product product = Product.findProduct(productId);

        String forwardAddress = "/NoProduct.jsp";

        if (product != null) {
            request.setAttribute("currentProduct", product);

            if (product.getAmount() < 10) {
                forwardAddress = "/LowAmount.jsp";
            } else if (product.getAmount() > 10) {
                forwardAddress = "/SufficientAmount.jsp";
            }
        }        
        RequestDispatcher dispatcher =
                request.getRequestDispatcher(forwardAddress);
        dispatcher.forward(request, response);
    }

As you can see, first we get selected product id (if any) and then we choose appropriate JSP page for that product based on it's amount. If user do not choose any product page "NoProduct.jsp" will be displayed.

Product class is quite simple (it's DTO with static initializing block):

package org.codingwithpassion.model;

import java.util.HashMap;
import java.util.Map;

/**
 *
 * @author jan.krizan
 */
public class Product {

    private static Map products;

    /*
     * Execute on object create (only once).
     */
    static {
        products = new HashMap();

        products.put(1, new Product(1, "Square ball", 12, 122));
        products.put(2, new Product(2, "Invisible cup", 1223, 12234));
        products.put(3, new Product(2, "Vitamins", 1, 2));

    }
    private int id;
    private String name;
    private int price;
    private int amount;

    public int getAmount() {
        return amount;
    }

    public void setAmount(int amount) {
        this.amount = amount;
    }

    public Product(int id, String name, int age, int amount) {
        this.id = id;
        this.name = name;
        this.price = age;
        this.amount = amount;
    }

    /*
     * Required no-argument constructor.
     */
    public Product() {
    }

    public int getAge() {
        return price;
    }

    public void setAge(int age) {
        this.price = age;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public static Product findProduct(int id) {
        return (Product) products.get(id);
    }
}

Final remarks

It is not necessary to use MVC in whole web application (GOD servlet is not a good idea). You can use MVC on places you think you will get most from this pattern and on other places use simple JSP.

Controller servlet in MVC do not create any output, the output should be handled only by JSP pages (or any other view technology). So remember, servlet do not call response.setContentType, response.getWriter, etc.

You can forward (using dispatcher) or you can redirect (using request.sendRedirect) to transfer control from controller servlet to view (JSP page).
When you use forward then control is transfered entirely on the server (no network traffic) and user do not see that address of destination JSP page. You should put JSP pages into WEB-INF if those JSP pages make sense only in the context controller servlet.
When you use redirect then control is transfered by sending client a 302 status code together with Location response header. This require an additional network traffic and user sees the address of destination JSP page.

Final remark (of final remarks) is about pointing out that you are not limited for usingrequest scope, but also you can use session or application scope, but remember to synchronize scope declaration in servlet and JSP page.

Out of the box MVC frameworks

I must admit that I'm not a big fan on Java web frameworks. I understand that they help in organization and work load distribution in complex development undertaking, but for small project they are obviously overkill. They bring complexity but on other hand they bring good patterns and common sense guidelines.

This Model-2 MVC architecture is quite powerful and good as core pattern for most simple to mid size project. If you are developing reasonably complex Java web application please see Struts 2 or Spring MVC (the most popular out of action-based frameworks).

Using jQuery JavaScript library in Oracle ADF Faces (JDeveloper) application

Why jQuery and ADF Faces?

jQuery (as we all know) is quite useful js library and you can do amazing things with it. Oracle ADF Faces (which is based on Apache MyFaces Trinidad JSF library) is very useful and future rich JSF implementation. But, there are times when you do not want to follow all ADF framework bits and pieces and just solve your problem by programing stuff on client side using JavaScript and jQuery.

Steps

Off course, first download jQuery from jQuery site (I prefer non compressed version, because we are using ADF Faces mainly for application inside firewall and with high bandwidth).

Then create lib folder in your ADF application (inside View Controller Project in Web Content folder). Put jQuery library there.

If you want to use have access to jQuery library in all your jspx pages then put following tag in you template (before af:pageTemplateDef):

In this way you load jQuery js library from real path (using request object).

If you want to include jQuery in single page (jspx) then use af:resource (inside af:document) like so:

Using metaContainer facet and af:group is recommended Oracle method for loading js libraries.

Finding components

For finding specific ADF components (or plain simple HTML components), you can use id property from ADF components or you can use class attributes that are defined by ADF Faces.

It can be little tricky to find specific component (because ADF faces is not so simple on browser end), but tools like firebug can help you by identifying specific component and show it's name or id value.