Oracle Apps: Search for records using BETWEEN in oracle apps forms

First press F11 in sales order screen then enter this in sales order number field #between 10 and 20. execute using CONTROL + F11, It will return the sales order number between 10 to 20.

Activating OLD used password for FND User oracle apps

Oracle Applications by default restrict you to reset the old earlier used password. The reason being all OLD passwords are stored in FND_USER_PREFERENCES table. We can use the following queries to see all the entries (though all passwords will be in encrypted format)

select *
from applsys.fnd_user_preferences
where user_name = 'SYSADMIN'
and module_name = 'PWDVALREUSE'

In order to reset/reuse the earlier USED password, one can delete all the entries from above mentioned table

DELETE
from applsys.fnd_user_preferences
where user_name = 'SYSADMIN'
and module_name = 'PWDVALREUSE';

COMMIT;

Oracle Apps: How to change Oracle application user password from plsql

Generally Oracle applications user password is reset/changed through the System administrator> Define user form.
But sometimes developers/testers need to do a quick test using some user's credentials in dev/test instance and in these situations, you lose time if you follow the above procedure just for resetting the user password.

To address this pain, developers can use fnd_user_pkg.changepassword API to change the password of an Oracle application user.

This returns boolean value and has two input parameters,

1. User name
2. Password

Following PL/SQL block can be used to reset password to welcome@123,

begin
if fnd_user_pkg.changepassword('USER_NAME','welcome@123') then
null;

end if;
end;

Create oracle database manualy 9i

I supposed that the Oracle Database Software is already installed.

1- Creat the required directories
    mkdir -p /OraData/drportal/CtlF    
    mkdir -p /OraData/drportal/DataF   
    mkdir -p /OraData/drportal/OnRdLog
    mkdir -p /oracle/OraHome9i/drportal/bdump
    mkdir -p /oracle/OraHome9i/drportal/cdump
    mkdir -p /oracle/OraHome9i/drportal/udump

2- Set the instance name:
export ORACLE_SID=DRPORTAL


3- create the init file /oracle/OraHome9i/dbs/initDRPORTAL.ora
control_files = (/OraData/drportal/CtlF/control01.ctl,
                 /OraData/drportal/CtlF/control02.ctl,
                 /OraData/drportal/CtlF/control03.ctl)
undo_management = auto
db_name         = DRPORTAL
instance_name   = DRPORTAL
db_block_size       = 8192
background_dump_dest = /oracle/OraHome9i/drportal/bdump
core_dump_dest = /oracle/OraHome9i/drportal/cdump
user_dump_dest = /oracle/OraHome9i/drportal/udump
COMPATIBLE=9.2.0
PROCESSES=150
UNDO_TABLESPACE=undotbs1


4- Start the instance

       sqlplus / as sysdba
       select  instance_name from v$instance;
       startup nomount pfile='/oracle/OraHome9i/dbs/initDRPORTAL.ora';

5- Create the database as the following:

CREATE DATABASE DRPORTAL
   USER SYS IDENTIFIED BY system12
   USER SYSTEM IDENTIFIED BY system12
   LOGFILE GROUP 1 ('/OraData/drportal/OnRdLog/redo01.log') SIZE 10M,
           GROUP 2 ('/OraData/drportal/OnRdLog/redo02.log') SIZE 10M,
           GROUP 3 ('/OraData/drportal/OnRdLog/redo03.log') SIZE 10M
   MAXLOGFILES 192
   MAXLOGMEMBERS 5
   MAXLOGHISTORY 37394
   MAXDATAFILES 500
   MAXINSTANCES 32
   CHARACTER SET WE8ISO8859P1
   DATAFILE '/OraData/drportal/DataF/system.dbf' SIZE 300M REUSE
   EXTENT MANAGEMENT LOCAL
   DEFAULT TEMPORARY TABLESPACE temp
      TEMPFILE '/OraData/drportal/DataF/temp01.dbf'
      SIZE 500M REUSE
   UNDO TABLESPACE undotbs1
      DATAFILE '/OraData/drportal/DataF/undotbs01.dbf'
      SIZE 2000M REUSE AUTOEXTEND Off ;

6- Create the spfile for this database:
    create spfile from pfile;

7- Post steps for the database creation are to run:
-- As sys  user
@$ORACLE_HOME/rdbms/admin/catalog.sql
@$ORACLE_HOME/rdbms/admin/catproc.sql

Error while running NETCA or DBCA with XMING on Unix AIX

 I got this error while I'm using the XMING to lunch DBCA and NETCA
$ netca

Oracle Net Services Configuration:
Warning: Cannot convert string "-*-lucida-medium-r-*-*-*-*-*-*-*-*-iso8859-1" to type FontStruct


java.lang.InternalError: java/langNullPointerException
        at sun.awt.motif.MComponentPeer.pSetFont(Native Method)
        at sun.awt.motif.MComponentPeer.setFont(MComponentPeer.java:308)
        at sun.awt.motif.MWindowPeer.init(MWindowPeer.java:144)
        at sun.awt.motif.MFramePeer.(MFramePeer.java:91)
        at sun.awt.motif.MToolkit.createFrame(MToolkit.java:301)
        at java.awt.Frame.addNotify(Frame.java:502)
        at java.awt.Window.pack(Window.java:470)
        at oracle.ewt.wizard.WizardDialog.runDialog(Unknown Source)
        at oracle.net.ca.NetCA.start(Unknown Source)
        at oracle.net.ca.NetCA.(Unknown Source)
        at oracle.net.ca.NetCA.main(Unknown Source)
Oracle Net Services configuration failed.  The exit code is -1

The solution is very simple. Just need to install the missing font at your PC (Not the server)

Oracle database lost Temp datafile

1- Shutdown the database if its open.
     Shutdown abort;

2- Startup the database in mount state:
    startup mount;

3- Drop the missing tempfile:
    Alter database datafile '/OraData/DBPORTAL/temp03.dbf' offline drop;

4- Open the database normally:
    alter database open;

Oracle Applications: Change IP Address

1. Change the IP Address in the Server;

2. Verify the current ip address setup in the Oracle Applications environment. Connect as apps user into SQL*Plus and run:

select NODE_NAME, STATUS, NODE_MODE, NODE_ID,SERVER_ADDRESS, HOST, DOMAIN, WEBHOST, VIRTUAL_IP

from fnd_nodes

where node_name = 'HOSTNAME';

3. Run the following command to remove the old ip address from the Oracle Applications tables:

Approach 1:
perl $AD_TOP/bin/adgentns.pl appspass=apps contextfile=$APPL_TOP/admin/_hostname.xml -removeserver

replace _hostname.xml for the context file name under the $APPL_TOP/admin directory;

then connect to SQL*Plus as apps user and run:

begin
FND_NET_SERVICES.remove_server('', '');
end;
/
commit;
/

Approach 2:
SQL> EXEC FND_CONC_CLONE.SETUP_CLEAN;
SQL> COMMIT;

4. Run autoconfig to populate the values using the new IP Address.

5. Confirm the ip address has been changed to the new value changed in the step 1:

select NODE_NAME, STATUS, NODE_MODE, NODE_ID,SERVER_ADDRESS, HOST, DOMAIN, WEBHOST, VIRTUAL_IP from fnd_nodes where node_name = 'HOSTNAME';