BI Publisher OC4J as a Windows service

To install Bi Publisher as a Windows service and forgot about OC4Jstart.cmd skript You need to do following:

• Download Java service

                     http://forge.ow2.org/projects/javaservice/

• Copy Java service to a folder on Oracle Application server (OAS)

         C:\oracle\javaservice

• Install Java JDK 1.6u45 on OAS

•  Go to BI Publisher OC4J folder  c:\ORACLE\Bipublisher\oc4j_bi\bin and check oc4j.cmd for JVMARGS :

         Set JVMARGS=-XX:MaxPermSize=128m  –Xmx512m  %OC4J_JVM_ARGS

•  Go to c:\oracle\javaservice in command prompt and run

javaservice -install "ORACLE BIP OC4J" "C:\Program Files\Java\jdk1.6.0_45\jre\bin\client\jvm.dll" -XX:MaxPermSize=128m -Xmx512m "-Djava.class.path=C:\ORACLE\BIPUBLISHER\oc4j_bi\j2ee\home\oc4j.jar" -start oracle.oc4j.loader.boot.BootStrap -out "C:\bip.log.txt" -err "C:\bip.err.txt" -description "Oracle BI EE OC4J Service"

• Go to sevices.msc and start ORACLE BIP OC4J service

Restricting users from some components in Apex 4.2 application

• Create Groups to differentiate users:

• Edit users and add them to Groups. If a user is Administrator than put it in Admin group.
• Go to Application builder  -> Application -> Shared Components -> Authorization Schemes and create a new Authorization Scheme.
• Select From Scratch
• In Authorization Scheme put following PL/SQL

• Go to the Application page that you want to prevent all users seeing (only Admin group)  and in Security choose the scheme you want:

How to create test user in test database from production database with impdp

Some time ago i had a need to create identical user on a test database every hour. To solve that i used impdp ability to use transfer over database link.
I have two databases . Production "db_prod" and test database "db_test". User that i am transferring has to preserve its name for easier testing.

1. Create public database link on db_test as SYSTEM:
 create a public database link DB_PROD connect to system identified by xxxxx using ‘DB_PROD_ALIAS’;

2. Insert data (create users and all) from db_prod to db_test with impdp:
impdp system/xxxxxx@db_test schemas=user_prod network_link=db_prod_alias

This way there is no need to use remap_schema since both user names are the same. There is no need to use data_pump_dir because all transfer is done by database link.
There is some more job to be done. I ran script by hand at first to see what objects are giving me warning (Roles, references to another user) and to create a script to prepare test database for fresh injection of a test user. That means usually to drop existing user from db_test, create roles if needed (impdp will fill them with appropriate grants).

Install Oracle Developer 6i on Windows 7 x64

I have a need to install Oracle Forms and Reports Developer suite 6i  with patch 14 on much newer WIndows 7 Professional 64-bit. At first attempt i tried and succeeded to install it all in 64 bit environment using Compatibility options but when i started to work with the tools i encountered many problems.
Next step was to install it in Windows Virtual PC )XP Mode). Steps to do so are:

1. Install Windows XP mode and Virtual PC as in instructions: http://windows.microsoft.com/en-us/windows7/install-and-use-windows-xp-mode-in-windows-7
2. Download and install XP mode : http://www.microsoft.com/en-us/download/details.aspx?id=8002
3. Download and install VirtualPC : http://www.microsoft.com/en-us/download/details.aspx?id=3702
4. Create default XP machine and edit settings (i usually get RAM to 1GB):

After that i started XP mode, set up initial password for XPMode (this is required. Windows XP mode remembers this password, but in later actions it can ask for it. Sometimes when that password is blank, XPmode throws an error. On this site there is a manual how to reset: http://www.mydigitallife.info/resetand-fix-incorrect-or-wrong-password-for-windows-xp-mode-xpmuser/) and install Developer 6i. After that i put valid tnsnames.ora and sqlpnet.ora at %ORACLE_HOME%\network\admin directory.

Now Developer tools could be run directly in XPmode machine (some people find it easier) or from Windows 7 Start menu:

FRM-92095 and Oracle Forms 10G Builder

Recently i was doing a test to move files from Application server to File server and than to get them using webutil. But in meantime i installed Windows7 x64 and reinstall forms 10g builder on my new PC.
When i try to run form locally with OC4j i got FRM-92095 although i had installed 32 bit versions of multiple 1.3.x.x. Jinitiators.
The problem was that only JRE and JDK installed on PC was JDK7u21 (x64) and when i try to run form i got following:

After i got that error i installed JRE 1.6.u45 from :

http://www.oracle.com/technetwork/java/javasebusiness/downloads/java-archive-downloads-javase6-419409.html#jre-6u45-oth-JPR

The next step was either:

• Set up JRE 1.6u45 as default Java either in System and User enviroment through Control Panel
• or uninstall 64-bit Java version

Setting new Font in BI Publisher 10.1.3.4.1

From time to time there is a need to set up a new font for RTF,HTML or PDF reports in BI Publisher. Fonts could be inserted system wide or per a report.

To insert font for only one report to get it in all those types of reports:

--> Copy font (*.ttf file) to the directory : C:\ORACLE\BIPUBLISHER\jdk\lib\fonts where c:\oracle\bipublisher is where BI Publisher is installed

-->  edit xdo.cfg file of report that is found in c:\ORACLE\BIPUBLISHER\xmlp\XMLP\Reports\"Report Folder"\"Report Name":

<config version="1.0.0" xmlns="http://xmlns.oracle.com/oxp/config/">
    <!-- Properties -->
    <properties>
        <!-- System level properties -->
        <!-- PLEASE SELECT A VALID TEMPFILE DIRECTORY!!! -->
        <property name="system-temp-dir">c:/Temp</property>
        <property name="rtf-circlefull-glyph">Wingdings 2;002;154</property>
        <property name="rtf-circleempty-glyph">Wingdings 2;002;152</property>
        <property name="rtf-checkbox-glyph">Wingdings 2;152;153</property>
 <!-- Font setting -->
    <fonts>
      <!-- Windows Wingdings font -->
      <font family="WingDings 2" style="normal" weight="normal">
       <truetype path="wingdng2.ttf"/> 
      </font
    </fonts>

</config>

Note: in this case WingDings2 font is used. Best case is to use lowecase in scripts and file name on OS side.

--> Edit or check configuration of report. If there is no value put this:

How to reset Apex internal password with a script.

This is the script i use to reset Apex password in Apex version 4.x.

• Login to db as sysdba
• Check what verison is APEX user (mine case is 4.2 or APEX_040200)
• Execute Script

Script:

set define '&'

set verify off

alter session set current_schema = APEX_040200;

prompt ...changing password for ADMIN

begin

  wwv_flow_security.g_security_group_id := 10;

  wwv_flow_security.g_user := 'ADMIN';

  wwv_flow_security.g_import_in_progress := true;

  for c1 in (select user_id from wwv_flow_fnd_user

      where security_group_id = wwv_flow_security.g_security_group_id

      and user_name = wwv_flow_security.g_user) loop

     wwv_flow_fnd_user_api.edit_fnd_user(

       p_user_id => c1.user_id,

       p_user_name => wwv_flow_security.g_user,

       p_web_password => '&1',

       p_new_password => '&1');

  end loop;

  wwv_flow_security.g_import_in_progress := false;

end;

/

commit;

/