How to Generate Arabic PDF Reports with Oracle Reports 9i

Follow the following process

1. Set NLS_LANG=ARABIC_United Arab Emirates.AR8MSWIN1256

2. Set REPORTS_PATH to the system font directory such as D:\WINNT\Fonts

3. Open uifont.ali under $ORACLE_HOME\tools\common90\, set the font alias

   These font name should be the one you are using in your report.

Note: The entries are case sensitive thus file name and font name mustmatch here

   Arial..italic.Bold.. = "arialbi.ttf"

   Arial..italic... = "ariali.ttf"

   Arial...Bold.. = "arialbd.ttf"

   Arial = "arial.ttf"

   "Andale Duospace WT J" = "Aduoj.ttf"

   "Albany WT J"="AlbanWTJ.ttf"

   "Arabic Transparent"="artro.ttf"

4. Ensure that Default Printer Driver used is not an ASCII/Generic driver.

5. Run the report with DESTYPE=FILE and DESFORMAT=PDF

How to Export oracle report data from Oracle Applications and into Excel

Oracle Applications does not have a feature or option for exporting report data directly into Excel. Exporting report data directly into Excel is not supported, however. The XML Publisher product, an exception, can be installed in order to create custom reports that produce Excel output

Validating Parameters in Oracle Reports using PL/SQL with Example

Example 1:

----------------------------------------------------------------------------

This trigger aborts the report execution if no rows match the query criteria 

once the user has entered a value for param_sal.

----------------------------------------------------------------------------

function PARAM_SALValidTrigger return boolean is

hold_count number(4);

hold_sal  number(10);

begin

  hold_sal := :param_sal;

  select count(*) into hold_count from emp where sal > hold_sal; 

  if hold_count = 0 then

     srw.message(001,'this report returns no employees');

     raise srw.program_abort;

  end if;

  return(true);

end;

Validating Parameters in Oracle Reports using PL/SQL

Parameters can be populated and validated using various srw pl/sql triggers.

The following gives examples of: 

Validation trigger in parameter form 
 
Before parameter form trigger
 
After parameter form trigger
 
Before report trigger   
 

Examples of validation triggers on the property sheet for parameter 

PARAM_SAL. 

Query: select * from emp where sal > :PARAM_SAL 

These functions validate just this one trigger. The validation occurs when 

the user hits next field after inputting a value for the parameter. When the 

trigger is failed it returns to the parameter form.

How to trim ant property string value

When writing ant build files, I often need to trim the string value of an ant property. But I couldn't find a simple task to do that. So I wrote the following ant macrodef for this purpose. In short, it saves the property value into a temp file, trims lines while moving the file, and loads the file back into a new property.
<project basedir="." default="trim" name="test"&gt;
<target name="trim">
<trim input="${text}" property="text.trimmed">
<echo message="original text='${text}'">
<echo message="trimmed text='${text.trimmed}'">
</echo>

<macrodef name="trim">
<attribute name="input">
<attribute name="property">
<sequential>
<tempfile property="temp.file">
<echo file="${temp.file}" message="@{input}">
<move file="${temp.file}" tofile="${temp.file}.2">
<filterchain>
<trim>
</trim>
</filterchain>
<loadfile property="@{property}" srcfile="${temp.file}.2">
<delete failonerror="false" file="${temp.file}.2">
</delete>
</loadfile>
</move></echo></tempfile></sequential></attribute></attribute></macrodef></echo></trim></target>
To run this target:

/tmp > ant -Dtext=' a b c '
Buildfile: build.xml

trim:
    [move] Moving 1 file to /tmp
  [delete] Deleting: /tmp/null1355243589.2
    [echo] original text=' a b c '
    [echo] trimmed  text='a b c'

BUILD SUCCESSFUL
Total time: 0 seconds

The ant macro named trim declares the trim operation, while the trim target shows an example of calling the trim macro.

There are other solutions to achive the same result, e.g., write a custom ant task, or use ant-contrib tasks. But the advantage of this sample is that it only uses built-in ant tasks and no other library jars are needed.

Simple Asynchronous methods in EJB 3.1

EJB 3.1 introduced a simple mechanism for asynchronous invocation. This post presents such an example with a Singleton session bean and its JUnit test case. 2 styles of asynchronous EJB invocations are used:

(1) fire-and-forget asynchronous methods having void return type.

(2) retrieve-result-later asynchronous methods having Future return type.

package test;

import java.util.concurrent.Future;
import javax.ejb.Asynchronous;
import javax.ejb.AsyncResult;
import javax.ejb.Singleton;

@Singleton
@Asynchronous
public class AsyncBean {
    @Asynchronous  //this line can be omitted
    public void ignoreResult(int a, int b) {
        // the client doesn't care what happens here
    }

    @Asynchronous  //this line can be omitted
    public Future longProcessing(int a, int b) {
        return new AsyncResult(a * b);
    }
}

Since the class is annotated with @Asynchronous, all business methods are asynchronous.  The method-level @Asynchronous is therefore redundant, and kept there for illustration purpose.package test;

import java.util.concurrent.ExecutionException;
import java.util.concurrent.Future;
import javax.ejb.embeddable.EJBContainer;
import javax.naming.Context;
import junit.framework.TestCase;

public class AsyncBeanTest extends TestCase {
    private EJBContainer container;
    private Context namingContext;
    private AsyncBean asyncBean;

    @Override
    protected void setUp() throws Exception {
        super.setUp();
        container = EJBContainer.createEJBContainer();
        namingContext = container.getContext();
        asyncBean = (AsyncBean) namingContext.lookup("java:global/testApp/AsyncBean");
    }

    @Override
    protected void tearDown() throws Exception {
        super.tearDown();
        container.close();
    }

    public final void testIgnoreResult() {
        asyncBean.ignoreResult(0, 0); // fire and forget
        System.out.println("Proceed without waiting for the async method result.");
    }

    public final void testLongProcessing() throws InterruptedException, ExecutionException {
        Future futureResult = asyncBean.longProcessing(8, 9);
        System.out.println("Proceed to other tasks and check async method result later.");
        Integer intResult = futureResult.get();
        System.out.println("The prior async method returned " + intResult);
    }
} 

Execute Immediate in Oracle Reports 11g

Using the EXECUTE IMMEDIATE command directly in Reports is not possible, but you can work around this by creating a stored procedure in the database and then using this procedure in Reports. 

The database stored procedure can be created as follows: 
CREATE OR REPLACE PROCEDURE DYNAMIC_SQL(STMNT char) IS 
begin 
     BEGIN 
         EXECUTE IMMEDIATE stmnt; 
     exception 
         when others then 
             dbms_output.put_line('hello'); 
     end; 
end; 

Now, this procedure can be called in Reports. 
For example: 
In the BeforeParam Trigger, the Procedure can be called as: 
DYNAMIC_SQL('drop table TEST'); 
This will drop the table 'TEST' from the database.

%ROWTYPE with REF CURSOR in Oracle PL/SQL

In the previous section, I retrieved only one column (ename) of information using REF CURSOR.  Now I would like to retrieve more than one column (or entire row) of information using the same.  Let us consider the following example:
declare
  type r_cursor is REF CURSOR;
  c_emp r_cursor;
  er emp%rowtype;
begin
  open c_emp for select * from emp;
  loop
      fetch c_emp into er;
      exit when c_emp%notfound;
      dbms_output.put_line(er.ename || ' - ' || er.sal);
  end loop;
  close c_emp;
end;
In the above example, the only crucial declaration is the following:
  er emp%rowtype;
The above declares a variable named "er," which can hold an entire row from the "emp" table.  To retrieve the values (of each column) from that variable, we use the dot notation as follows:
      dbms_output.put_line(er.ename || ' - ' || er.sal);

REF CURSOR in Oracle PL/SQL

A REF CURSOR is basically a data type.  A variable created based on such a data type is generally called a cursor variable.  A cursor variable can be associated with different queries at run-time.  The primary advantage of using cursor variables is their capability to pass result sets between sub programs (like stored procedures, functions, packages etc.).
Let us start with a small sub-program as follows:
declare
  type r_cursor is REF CURSOR;
  c_emp r_cursor;
  en emp.ename%type;
begin
  open c_emp for select ename from emp;
  loop
      fetch c_emp into en;
      exit when c_emp%notfound;
      dbms_output.put_line(en);
  end loop;
  close c_emp;
end;
Let me explain step by step.  The following is the first statement you need to understand:
  type r_cursor is REF CURSOR;
The above statement simply defines a new data type called "r_cursor," which is of the type REF CURSOR.  We declare a cursor variable named "c_emp" based on the type "r_cursor" as follows:
  c_emp r_cursor;
Every cursor variable must be opened with an associated SELECT statement as follows:
  open c_emp for select ename from emp;
To retrieve each row of information from the cursor, I used a loop together with a FETCH statement as follows:
  loop
      fetch c_emp into en;
      exit when c_emp%notfound;
      dbms_output.put_line(en);
  end loop;
I finally closed the cursor using the following statement:
  close c_emp;

Double click in NetBeans 6.5 & 6.8 editor

NetBeans 6.x editors handle double-click differently from other applications. In firefox, thunderbird, eclipse, or even terminal, I can doulble-click anywhere in a word to select the whole word, keep the mouse button pressed, and slide forward to expand the selection.

In NetBeans, doing the same will drag the selected word, just like moving the word elsewhere. While it seems cool to be able to drag-and-drop, I don't see any practical value. In java editor, I always avoid drag-and-drop anything, since the selected element often lands in the wrong place when your mouse/pad reaches its end.

Portable JNDI names in EJB 3.1

Java EE 6 made some efforts to standardize JNDI names at global,application, and module levels. Now EJB deployed to EJB-3.1-capable server will have various standard JNDI names registered. EJB 3.1 spec defines the following 3 levels of JNDI names for EJB:

Global JNDI name:

java:global[/]//[!]

Application-scope JNDI name:

java:app//[!]

Module-scope JNDI name:

java:module/[!]

The following exampels shows various EJB JNDI names, depending upon how the EJB is packaged, its business interface(s), and bean class:

Bean class: test.TestBean
business interface: none

EJB lite, JPA, DataSource embedded in java application

This post demonstrates how to put EJB lite, JPA, DataSource together in a standalone java application, using standard JavaEE 6 API in GlassFish. The application source consists of 4 files: persistence.xml, JPA entity, no-interface EJB, and main class.

Transaction is managed by embeddable EJB container. The persistence unit is backed by an application-scoped JTA DataSource declared with annotation on EJB class. No need to package or deploy anything. Client and server pieces are all in a single JVM.
classes/META-INF/persistence.xml :

  java:app/jdbc/test