Archive For Oracle Guys: January 2008

Tuesday, January 22, 2008

How do I see who is currently connected?

SELECT username, program FROM v$session WHERE username IS NOT NULL;

How to see the database version?

To view components and their versions, loaded into the database:

SELECT comp_name, status, version FROM dba_registry;

To view version numbers of core library components, including the database:

SELECT banner FROM v$version;

To display options that are installed (the value column is ‘FALSE’ if the option is not installed)

SELECT parameter, value FROM v$option;

Friday, January 18, 2008

How to set a table in read-only mode ?

ALTER TABLE emp
ADD CONSTRAINT read_only CHECK (1=1) DISABLE VALIDATE;

It is now, impossible to insert, update or delete anything with this table.

Oracle Stored procedures JAVA

Purpose : To demonstrate that Oracle database has an embedded JVM

Objective: PL/SQL is great, but it is an Oracle Proprietary language that cannot be ported to other database platforms,, Java is portable

Environment : The example uses SQL*Plus, but other tools also work

Oracle Version: I Used Oracle9i, Oracle8i should work similarly

SQL> CREATE OR REPLACE and RESOLVE JAVA SOURCE NAMED "Hello" AS
public class Hello {
static public String Msg(String tail) {
return "Hello " + tail;
}
}
/
Java created.

SQL> CREATE OR REPLACE FUNCTION hello( str VARCHAR2 )
RETURN VARCHAR2 AS
LANGUAGE JAVA NAME
'Hello.Msg (java.lang.String)
return java.lang.String';
/

SQL>SELECT hello (ENAME) from EMP;

HELLO(ENAME)
-----------------------------
Hello SMITH
Hello ALLEN
Hello WARD
Hello JONES
Hello MARTIN
Hello BLAKE
Hello CLARK
Hello SCOTT
Hello KING
Hello TURNER
Hello ADAMS

HELLO(ENAME)
-----------------------------
Hello JAMES
Hello FORD
Hello MILLER

Using Loggers in Java Applications

In order to add logging features to your applications, you can use apache log4j API

You need 3 things

1) have access to log4j jar file

2) have a properties file that will act as a configuration environment for the logging process

3) write the appropriate Java code

here is more details using the Oracle Jdeveloper IDE

Download log4j1.3.jar from http://logging.apache.org

Put the log4j.jar in the classpath, or add it to the Jdeveloper project as shown

You need to specify where the properties file is located, otherwise logging will fail

As an example, I have create a properties file and called it log4j.properties and added placed the file at C:\

You can use the project properties dialogue to define the location of the Properties file

Invoke project properties à Run/Debug à Edit the default setting à and add the Java option shown below

-Dlog4j.configuration = file:c:/log.properties

The following is a sample properties file in which the logging output is specified (log4j.appender.R.File=C:\x.log)

Log.properties

log4j.rootLogger=INFO, R

# first appender writes to a file

log4j.appender.R=org.apache.log4j.RollingFileAppender

#RollingFileAppender OPTIONS

log4j.appender.R.Threshold=INFO

log4j.appender.R.ImmediateFlush=true

log4j.appender.R.File=C:\x.log

log4j.appender.R.MaxFileSize=6000KB

log4j.appender.R.MaxBackupIndex=2

#log4j.debug=true

#log4j.disable=fatal

# Pattern to output the caller's file name and line number.

log4j.appender.R.layout=org.apache.log4j.PatternLayout

log4j.appender.R.layout.ConversionPattern=%p %c %d{dd/MM/yyyy HH:mm:ss} - %m%n

#log4j.appender.stdout.layout.ConversionPattern=%5p [%t] (%F:%L) - %m%n

#log4j.appender.R.layout.ConversionPattern=%p %t %c - %m%n

Finally, this is an example code

package model;

import org.apache.log4j.Logger;

public class Class1 {

public Class1() {

Logger log = Logger.getLogger(Class1.class.getName());

log.warn("hello warning");

}

public static void main(String[] args) {

Class1 class1 = new Class1();

}

Note: the (Class1.class.getName()) retrieves the name of the current class that is being executes. Every logging message entry will have this variable printed so that the users can know which class was responsible for this entry.. off course, if you choose to use a string value instead, that strng value shall appear in each logging entry.

Thanks to Mr. Ammar for this Article..

Showing Calendar in SQL*Plus

Similar to UNIX cal command

Thanks to Tony Davis for this tip

Thanks to Mr. Ammar too

Thursday, January 17, 2008

Hidden columns

Interestingly, dbms_stats will collect statistics on hidden columns, eg, from a function based index, but it doesn't appear that you can actually retrieve them

SQL> create table T ( x number ) ;

Table created.

SQL> create index TX on T ( x+10 );

Index created.

SQL> select column_name from dba_tab_cols
 2  where table_name = 'T'
 3  and owner = user;

COLUMN_NAME
------------------------------
SYS_NC00002$
X

SQL> declare
 2   srec dbms_stats.statrec;
 3   DISTCNT number;
 4   DENSITY number;
 5   NULLCNT number;
 6   AVGCLEN number;
 7  begin
 8   dbms_stats.GET_COLUMN_STATS
 9   (OWNNAME=>user
10   ,TABNAME=>'T'
11   ,COLNAME=>'SYS_NC00002$'
12   ,DISTCNT=>distcnt
13   ,DENSITY=>density
14   ,NULLCNT=>nullcnt
15   ,SREC=>srec
16   ,AVGCLEN=>avgclen);
17  end;
18  /
declare
*
ERROR at line 1:
ORA-20000: Unable to get values for column SYS_NC00002$
ORA-06512: at "SYS.DBMS_STATS", line 3976
ORA-06512: at "SYS.DBMS_STATS", line 3991
ORA-06512: at line 8


SQL> exec dbms_stats.gather_table_stats(user,'T',cascade=>true);

PL/SQL procedure successfully completed.

SQL> select column_name , num_distinct
 2  from dba_tab_cols
 3  where table_name = 'T'
 4  and owner = user;

COLUMN_NAME                    NUM_DISTINCT
------------------------------ ------------
SYS_NC00002$                              0
X                                         0

Bulk collect on records

Lets start with a working example of bulk collect into records.

SQL> create table T ( c1 number, c2 number );

Table created.

SQL> declare
 2    type r is record (
 3       x number,
 4       y number );
 5
 6    type rt is table of r;
 7
 8    d rt;
 9
10  begin
11    select rownum, rownum
12    bulk collect into d
13    from all_Objects
14    where rownum <= 20;
15
16    forall i in 1 .. 20
17      insert into T values d(i);
18
19  end;
20  /

PL/SQL procedure successfully completed.

But what if table T has three columns, and we wanted to add the constant value "10" when we insert. Then we have problems because it would look like this:

SQL> declare
 2    type r is record (
 3       x number,
 4       y number );
 5
 6    type rt is table of r;
 7
 8    d rt;
 9
10  begin
11    select rownum, rownum
12    bulk collect into d
13    from all_Objects
14    where rownum <= 20;
15
16    forall i in 1 .. 20
17      insert into T values ( d(i).x, d(i).y, 10);
18
19  end;
20  /
insert into T values ( d(i).x, d(i).y, 10);
       *
ERROR at line 17:
ORA-06550: line 17, column 28:
PLS-00436: implementation restriction: cannot reference .... etc

However, what we CAN do is use objects and then apply SQL to them. All we need is some objects to mimic our PLSQL types

SQL> create or replace type r is object ( x number, y number );
 2  /

Type created.

SQL> create or replace type rt is table of r;
 2  /

Type created.

SQL> declare
 2    d rt; -- this is now pointing to a database definition not a plsql definition
 3
 4  begin
 5    select r(rownum, rownum)
 6    bulk collect into d
 7    from all_Objects
 8    where rownum <= 20;
 9
 10    insert into T
 11    select x,y,10
 12    from table(d);
 13
 14  end;
 15  /

PL/SQL procedure successfully completed.

Importing Shapefiles into Oracle

shp2sdo.exe is the conversion tool for the specific platform. It can be downloaded at Shape2SDO Download and is available in /home/oracle/shp2sdo on taifun. A readme file is there, too.

Running shp2sdo.exe (even on LINUX) without any command line parameter works fine. You'll be asked a couple of questions. Here's an example:

oracle@taifun:~/shp2sdo$ shp2sdo.exe
shp2sdo - Shapefile(r) To Oracle Spatial Converter
Version 2.15 21-May-2004
Copyright 1997,2004 Oracle Corporation
For use with Oracle Spatial.
Input shapefile (no extension): ../tmpdata/gg_limburg
   Shape file ../tmpdata/gg_limburg.shp contains 47 polygons
Output table [../tmpdata/gg_limburg]: gg_limburg
Output data model [O]:
Geometry column [GEOM]:
ID column []:
Use a spatial reference system ID (SRID) ? [N]:
Change tolerance value from the default (0.00000005) ? [N]:
Generate data inside control files ? [N]:
Target database Oracle8i? [N]:
Spatial Data requires more than 6 digits precision? [N]:
Bounds: X=[167507.730000,213448.380000] Y=[306838.822000,421214.230000]
Override ? [N]:
Processing shapefile ../tmpdata/gg_limburg into spatial table GG_LIMBURG
Data model is object-relational
  Geometry column is GEOM
  Points stored in SDO_POINT attributes
  Data is in a separate file(s)
  Control file generation for Oracle9i or higher
  Spatial data loaded with 6 digits of precision
Conversion complete : 47 polygons processed
The following files have been created:
  gg_limburg.sql :      SQL script to create the table
  gg_limburg.ctl :      Control file for loading the table
  gg_limburg.dat :      Data file

It does the same as:

shp2sdo.exe ../tmpdata/gg_limburg gg_limburg -g GEOM -s 90112

-- except that the -s parameter was specified here.

To load the data into Oracle...

oracle@taifun:~/shp2sdo$ sqlplus developer/dev @gg_limburg.sql
oracle@taifun:~/shp2sdo$ sqlldr developer/dev control=gg_limburg.ctl

Monday, January 14, 2008

ALTER DATABASE BEGIN BACKUP

Today I have learned a new thing about backup and recovery on how to keep the entire database in backup mode, instead of issuing separate BEGIN BACKUP statement for every tablespace.

I know Oracle strongly recommend of using RMAN for backup and recovery . I thought, this would be good for the DBAs who still use the legacy method of backup, i.e. ALTER TABLESPACE BEGIN/END BACKUP and if they are not aware of this new command in 9i and 10g versions.

Starting with version 9i(I dont know the exact release), Oracle gives the facility to put the entire database in backup by simply using the following command:

ALTER DATABASE BEGIN BACKUP;

ALTER DATABASE END BACKUP; -- to exit from the backup mode.

In version 9i, the above statement can be used only when the database is mounted, not opend. In 10g, this behavior changes. The statement can be executed while the database is open.

-- The following has done with Oracle 10gR1

SYS OCM AS SYSDBA>alter database begin backup;
Database altered.
SYS OCM AS SYSDBA>select file#,status from v$backup;
FILE# STATUS
---------- ------------------
1 ACTIVE
2 ACTIVE
3 ACTIVE
4 ACTIVE

-- All the datafiles are in backup mode now

SYS OCM AS SYSDBA>alter database end backup;
Database altered.
SYS OCM AS SYSDBA>select file#,status from v$backup;
FILE# STATUS
---------- ------------------
1 NOT ACTIVE
2 NOT ACTIVE
3 NOT ACTIVE
4 NOT ACTIVE

-- All the datafiles are out of backup mode now.

Happy reading,

Saturday, January 12, 2008

What is Oracle Apps (ERP)?

Lets take an example. Suppose you are running a small grocery shop named “Janata Grocery”, so the typical operation as a shop owner is you basically buy groceries from some big seller and stock it in your shop. Now people come to your shop for day-to-day needs and buy stuff from your shop at a slightly higher price than what you originally bought and stocked it in your shop.
Ocassionally you may not be carrying items or run out of stock that people ask for so you make a note of it and promise the person to come back tomorrow and they will get their item. So far so good, now lets name some entities before we proceed and things get complicated. The big seller from whom you buy stock is called as Vendor, the people who come to your shop to buy things are known as customers, the stock in your shop is known as inventory.

So far we have identified few entities that play an active role in your day-to-day operations. As time goes by, your business expands and now you take orders over the phone and provide service to deliver the items to your customers, so you hire people to help you out in maintaining the inventory, do the delivery part and all the necessary stuff to keep the business running smoothly. The people you hire are known as employees.
So in this small shop, you typically manage the bookkeeping activities by hand using a notepad or something similar. Now imagine the same setup on a larger scale where you have more than 10,000 customers, have more than 1000 vendors, have more than 1000 employees and have a huge warehouse to maintain your inventory. Do you think you can manage all that information using pen and paper? Absolutely no way! Your business will come to a sudden stop sign.
To facilitate big businesses, companies like Oracle Corporation have created huge software known in the category of ERP (Enterprise Resource Planning) as Oracle Applications. Now coming to think of it, Oracle Apps is not one huge software, instead it is a collection of software known as modules that are integrated and talk to each other.
Now what is meant by integrated? First let us identify the modules by entities. For e.g Purchasing and Account Payables deal with the vendors since you typically purchase from vendors and eventually have to pay the dues. Oracle Purchasing handles all the requisitions and purchase orders to the vendors whereas Oracle Accounts Payables handles all the payments to the vendors.

Similarly Oracle Inventory deals with the items you maintain in stock, warehouse etc. Dealing with customers is handled collectively with the help of Oracle Receivables and Oracle Order Management. Order Management helps you collect all the information that your customer is ordering over the phone or webstore etc whereas Receivables help you collect the money for the orders that are delivered to the customers.
Now who maintains the paychecks, benefits of the 1000 employees? right! it is managed by Oracle Human Resources. So you get the idea by now that for each logical function there is a separate module that helps to execute and maintain that function.
So all the individual functions are being taken care but how do I know if I am making profit or loss? That’s where integration comes into play. There is another module known as Oracle General Ledger. This module receives information from all the different transaction modules and summarizes them in order to help you create profit and loss statements, reports for paying Taxes etc.

Just to simplify the explaination, when you pay your employees that payment is reported back to General Ledgers as cost i.e money going out, when you purchase inventory items the information is transferred to GL as money going out, and so is the case when you pay your vendors. Similarly when you receive items in your inventory it is transferred to GL as money coming in, when your customer sends payment it is transfered to GL as money coming in. So all the different transaction modules report to GL (General Ledger) as either “money going in” or “money going out”, the net result will tell you if you are making a profit or loss.

All the equipment, shops, warehouses, computers can be termed as Assets and they are managed by Oracle Fixed Assets. Initially Oracle Applications started as bunch of modules and as time passed by they added new modules for different and new functions growing out of the need for today’s internet world.

So if you come across a module that you are trying to learn and work on, first try to understand what business need is it trying to fulfill and then try to understand what the immediate modules that it interacts with. For e.g lets say you come across Oracle Cost Management module, you will learn that it helps to maintain the costs of items in your inventory and the immediate modules that it interacts with are Oracle Inventory (ofcourse), Oracle Bills of Material, Order Management and so on..

There is more to ERP than this layman explanation of a complex beast that does not justify a single bit but I wished I had this knowledge when I was thrown into Oracle Applications right after I graduated from college. Back then the only piece of software I had known to write was implementing binary trees, infix, prefix, postfix notations in pascal and TSRs (Terminate and Stay resident) using assembly.

Thursday, January 10, 2008

Installing Oracle Applications E-Business 11i on Windows XP / Windowws 2003 Server

Hi! you will be able to install EBS 11i on Windows XP/ Windows 2003 Server, if you just follow these instructions else you should not get into it.

System Requirements Hardware used.

Windows XP (professional or Home) Service Pack 2 with 1 GB RAM ( you can do it on 512 MB also but not at all efficient) and make sure you have NTFS file system.
HD min 150 GB (c’mon your system and staging also need some space don’t agree with who says it work 100 GB with vision DB). You can use external hard drive too.
Don’t listen to those who advise you to install EBS 11i without vision database. I know
you are installing all this for learning etc.

Very Important.

Make sure there is no oracle product installed on your system. No entry of oracle should be in windows registry except system default by windows itself. So it is recommended to use fresh system for this purpose. If you tried a failed installation before on your PC then reinstall the OS (Win XP) again otherwise you will waste your time.

There should be !!!!NO!!! JDK installed on your machine. EBS 11i will install JDK version on your machine otherwise you will get errors I don’t want to discuss all this here. Just NOOOO JDK on your machine.

Pre-requisite software requirements.
1. Microsoft VC++ - http://msdn.microsoft.com/vstudio/express/visualc/download/

while installing VC++ make sure it is done in C:\V98. No spaces in any directory
structure of any software you install as pre-requisite for Oracle Apps. Opt for Register
Environment Variables while installing VC++.

If you installed MS Visual Studio C++ 6.0 then make sure you installed service pack 6.

http://msdn2.microsoft.com/en-us/vstudio/aa718364.aspx

Copy LINK.exe from C:\V98\bin to C:\WINDOWS\System32

2. Perl - http://www.activestate.com/Products/ActivePerl/

Version 5.8 or more and use MSI package type.

Perl is only needed if you are using Oracle Media Pack. If you are downloading from oracle site then you can skip this installation.

2. CYGWIN - http://cygwin.com/

- Download setup.exe to your machine.
Run setup.exe and click Next, which will bring up the Choose Installation Type screen.
Select -> Install from Internet and Click Next
Select -> Root Directory C:\cygwin and select radio button
Install for -> All Users and Default Text File Type -> DOS / text -> Next Local Package Directory can be any but C:\cygwin is recommended. -> Next Direct Connection.

Click on Next again to get to the Select Packages screen, and select the following packages (click on to toggle):
a. All Default
b. Archive Default, plus manually select the zip package
c. Base Default, plus manually select the following extra packages: ash, coreutils,
diffutils, findutils, gawk, grep, sed, tar and which
d. Devel Default, plus manually select binutils, gcc, gcc-core, gcc-g++, make and
mktemp
e. Doc Default, plus manually select cygwin-doc and man
f. Editors Default, plus manually select vim
g. Interpreters Default, plus manually select gawk
h. Shells Default, plus manually select ash and tcsh
i. Utils Default, plus manually select cygutils and file

Click on Next again to download the selected files. It will take some time depending upon mirror site you selected and after download cygwin setup will be automatically starts.

After installation completed

Open folder C:\cygwin\bin

And rename followings

gawk.exe to awk.exe
grep.exe to egrep.exe
make.exe to gnumake.exe
gcc.exe to cc.exe

You can override the name if system says that awk or egrep or cc already exits just remove or move to some other place.

Set the Path to include the C:\cygwin\bin

C:\> set PATH = %PATH%;c:\cygwin\bin

And then check your PATH to see that it include C:\cygwin\bin and C:\V98\bin

C:\>echo %PATH%

Oracle Media
You should be having Lasted available Oracle media pack (recommended) or Software download from oracle site and follow the unzipping instruction as per oracle documentation.

Domain Name Configuration

1. Open file c:\windows\system32\drivers\etc\hosts. Enter a domain next to localhost entry you will be using this domain while installing Oracle Applications. I used mydomain so you can replace what ever you want. 198.168.1.102 is IP Address of my PC you should enter your machine’s IP. If you don’t know it you can find it.

C:\>ipconfig

Example

127.0.0.1 localhost mydomain mydomain.com
198.168.1.102 mydomain mydomain.com

2. Go to Control Panel -> Systems -> Computer Name Tab -> Click on Change -> Click on More -> Enter Primary DNS Suffix as ‘mydomain’.

3. Open Network connections, Select your Connection on which your machine is connected (You will find Wireless or LAN) and right click and select properties. Select Internet Protocol( TCP/IP) and click on to properties. Click on Advanced button and go to DNS tab. Enter DNS suffix for this connection as ‘mydomain’.

4. Restart the PC.

.Staging the Oracle

If you have Oracle Media Pack then

Insert the Start Here DVD in DVD Rom and go to folder

Stage11i\startCD\Disk1\rapidwiz in DOS Prompt and type

D:\Stage11i\startCD\Disk1\rapidwiz\perl adautostg.pl
And follow the wizard.

Recommended Steps but not necessary.

There steps are only if your installation fails due to any reason so you can restore your registry and system to pre installation state.

When Staging is done successfully then export the registry and save it on a file.
Start -> Run -> regedit-> File -> Export and select the location where you want to store it .

Take a Computer Restore Point.
Start -> All Programs -> Accessories -> System Tools -> System Restore ->Create a Restore Point -> Enter name of restore point for example ‘PreOra’ and Press the Create.

Restart the PC.

Installing the the EBS 11i

So far you have done a great job. Now time to get the job done.

Go to folder C:\Stage11i\startCD\Disk1\rapidwiz or where you have done your staging on hard drive and just click RapidWiz

On welcome screen -> Next and Select

Install Oracle Applications E-Bussiness 11i Check on Use Express Configuration -> Next

In Express Configuration Information screen enter the following:

Database Type = Vision Demo Database
Database SID = VIS
MKS directory = C:\cygwin\bin or where “cygwin\bin” is
MSDEV directory = C:\V98 or where VC++ was installed
Domain = mydomain or what so ever is your domain.
Oracle Base directory =C:\oracle or where ever you want to install oracle
applications

Follow the wizard and you should pass all the Pre-Install Checks
If not then you did not follow instructions above and help yourself.

If you passed all the checks then just Click Next and follow the Wizard and installation will be done in 5 steps and take about 2-3 hrs depends upon the system.

After the installation is completed successfully (it should be) then Post-Installation Checks will be preformed. If you passed all the checks It is really a Wonderful and You Rocks

If your system don’t pass the Post-Installation Checks even then don’t worry just smile coz installation is successful you need to restart your PC and Open Command Prompt open to staging directory c:\Stage11i\startCD\Disk1\rapidwiz
And now just type rapidwiz –restart

Follow the Wizard and Enter all the info as you enter before Now This time it will take few minutes and you should pass Post-Installation Checks

If still a problem just repeat above step again you will get to it . Restart the PC and enter the URL in web browser

http://hostname.mydomain:8000/

E-Bussiness Login Username = sysadmin Password = sysadmin

These are the successful installation steps. I practically have done all that. Thanks to Mr. Naveed

Cheers …..

ORA-12571: TNS Packet writer failure

Strange error!

My developer was running fine . why I got this error.

I had tried many solutions provided on web by oracle experts but unable to connect my developer to database. I had tried to remove developer from C:\orant directory and also from registry. Then I re-installed it. But I got same error. I made parameter sqlnet authentication … to “None” in “SQLNET.ORA” file - but unable to solve this problem.

At last I analyzed that after which event I was getting this error. That was installation of a software - Download Manager.

Actually it was interrupting services. At last I removed this software and connected successfully developer to DB.

Hope it will be helpful.

Installation / Re-Installation of Oracle Database 10g on Windows

1. Take back-up of data (Full Database).
2. Shutdown your database. (Shutdown Abort).
3. Take back-up of SP file.
(If you have set some parameters previously according to your environment)
4. Note the size and names of all table spaces.
5. Delete Database through wizard (If previously installed).
6. Run Installer and uninstall Oracle from your system.
7. Remove Oracle entries from registry.
8. Restart your system.
9. Remove Oracle Directory from System.
10. Check services from Administrative tools. Please verify that there should not any Oracle related service existing in the list. No TNS, no oracle home etc.
11. Reboot your system.
12. Run Oracle Installer and start installation. Follow wizard and select options according to your requirements.
13. When Database is created, verify your work by connecting to database.
14. Shutdown Database.
15. Restore SP file.
16. Start Database.
(Note if you find any error on startup, then you have to generate SP file from init.ora file)
17. Install patch from its installer and run the related scripts. (It will take around 45 minutes so no need to worry).
18. Reboot your machine.
19. Create table spaces by same name and size as we noted in step 4.
20. Load (Import) backup from dmp file. Create log file as well.
(View log file. If you find any error of “table space already exists”, then don’t worry. It is because we have created the table spaces our selves.)

Good Luck!!!

Wednesday, January 9, 2008

Inconsistency between DBA_DATA_FILES, DBA_FREE_SPACE, and DBA_SEGMENTS views

A question has been raised on Technet forum that inconsistent information is being returned from Oracle dictionary views. OP is trying to relate used/free bytes returned by these dictionary views.

Information provided by OP:

Total bytes occupied by data files is: 11,010,048,000
Total bytes available are: 220,200,960
Total bytes used by objects: 10,989,076,480

Free Space + Used Space = 220,200,960 + 10,989,076,480 = 11,209,277,440

So, the sum of “Free Space” and “Used Space” is more than the “Total Space” available to the datafiles. The question is “How can I get more space than the existing one?”

Answer to the question goes here:

I have TEST user in my database and the default tablespace of this user is TEST_TS. I create couple of tables in this schema:

SQL> conn test/test
Connected.
SQL>
SQL> select * from tab;

TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
EMPDATA TABLE
TEST_ERRORS TABLE

SQL> create table test1 as select * from tab;

Table created.

SQL> create table test2 as select * from tab;

Table created.

SQL> create table test3 as select * from tab;

Table created.

SQL> select * from tab;

TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
TEST1 TABLE
TEST2 TABLE
TEST3 TABLE
EMPDATA TABLE
TEST_ERRORS TABLE

Now, I will drop these newly created tables:

SQL> drop table test1;

Table dropped.

SQL> drop table test2;

Table dropped.

SQL> drop table test3;

Table dropped.

Now, query the dictionary views for used, free and allocated bytes/blocks information:

SQL> select blocks from dba_data_files where tablespace_name = 'TEST_TS';

BLOCKS
----------
3712

SQL> select sum(blocks) from dba_free_space where tablespace_name = 'TEST_TS';

SUM(BLOCKS)
-----------
3680

SQL> select sum(blocks) from dba_segments where tablespace_name = 'TEST_TS';

SUM(BLOCKS)
-----------
48

Opps, we get more space (3680 + 48 = 3728) than being allocated to the datafiles. Probably, by now you might have arrived to the answer, but let me reveal it to you.

OP hasn’t mentioned the Oracle database version but I am pretty sure it’s Oracle 10g or above. With Oracle database 10g, Oracle has added a new feature of recycle bin. When you drop an object it goes and stays in your recycle bin and will occupy the same amount of space. You need to purge the object to reclaim the space.

This is what is happening in this case. Dropped objects are still lying in the TEST_TS tablespace and being counted by the DBA_SEGMENTS view, whereas, DBA_FREE_SPACE correctly report the free space available.

Let me purge the dropped objects out of the recycle bin and rerun the queries:

SQL> show recyclebin
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------

TEST1 BIN$BEExbY8xS0aXH3U+e9XPDg==$0 TABLE 2007-12-18:13:42:36

TEST2 BIN$WGV0P4B4TaCUukiPyctTPg==$0 TABLE 2007-12-18:13:42:38

TEST3 BIN$1P4aTA1IR8ijw4btdRkmzw==$0 TABLE 2007-12-18:13:42:39

SQL> purge recyclebin;

Recyclebin purged.

SQL> select blocks from dba_data_files where tablespace_name = 'TEST_TS';

BLOCKS
----------
3712

SQL> select sum(blocks) from dba_free_space where tablespace_name = 'TEST_TS';

SUM(BLOCKS)
-----------
3680

SQL> select sum(blocks) from dba_segments where tablespace_name = 'TEST_TS';

SUM(BLOCKS)
-----------
24

Wow, Oracle now reports correctly. The sum of “Free Space” and “Used Space” (3680 + 24 = 3704) is less than the “Total Space” (3712) available to the datafiles.

Simulating 11g Snapshot Standby Database feature on Oracle 10g?

As we all knew that the Oracle 11g improved the capabilities of standby database immensely, where a physical standby database can easily open in read-write mode, which can be ideally suitable for test and development environments. At the same time, it maintains protection by continuing to receive data from the production database, archiving it for later use.

What if you want to achieve the same on Oracle 10g? Well, I absolutely don’t have any clue about others, but, we have come across of such situation couple of days ago when our DR (Disaster Recovery Solution) team came to us with a request to test our standby database. They want the standby database in read write mode to do some real scenario tests and once the testing is done, they want the database to be back to standby mode.

We initially said, we can open the database in read only mode for their testing, but, the requirement demands the database to be in read write mode. We thought, we can break the standby database for their testing and once the testing is done, we can rebuild the standby database again. We know that this is very well possible with Oracle 11g but not with Oracle 10g. My colleague, Mr. Asif Momen, did some R&D come up with a solution where a Oracle 10g standby database can open in read write mode and can also be reverted back to standby mode.

The procedure as follows:

1. Set the following parameters on the standby database:

db_recovery_file_dest_size & db_recovery_file_dest

- Make sure the values are reflected.

2. Stop the media recovery process, if active.

3. When the standby is in MOUNT mode, Create a guaranteed restore point:

CREATE restore point before_rw guarantee flashback database;

3. Stop the log shipping on the primary database. (for safer side)

alter system archive log current;

alter system set log_archive_dest_state_2=DEFER;

4. Failover the standby database using the following command:

ALTER DATABASE ACTIVATE STANDBY DATABASE;

-Make sure the media recovery process is turned off

-Minimize the protection mode to MAXIMUM PERFORMANCE, if the mode is set other than the MAXIMUM PERFORMANCE.

5. Open the database (read write mode).

AT THIS POINT, YOU CAN USE THIS DATABASE AS NORMAL READ WRITE DATABASE.

Reverting the database back to standby mode:

Shutdown the database
Startup database in mount mode
Flash back database to restore point using the following:

FLASH BACK DATABASE TO RESTORE POINT before_rw;

Convert the database back to standby mode using the following:

ALTER DATABASE CONVERT TO PHYSICAL STANDBY;

Shutdown the standby database and remove the previously set parameters.
Start the standby database in mount state and drop the restored point.
Enable the Media Recovery on the Standby database.
Activate log shipping on the primary using the following:

ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENALE;

Since, we have done very little changes in the database, after converting to read write mode, the time which took to revert the database back to standby mode took few minutes only. Well, it is definitely need to be seen the time that take during the conversion to standby mode after huge changes in the read write database.

Tuesday, January 8, 2008

SQL Injection

Common Mistakes in Oracle PL/SQL Programming

Testing Oracle 10g RAC Scalability

SQL Injection.... WOW

With NO COMMENT

Monday, January 7, 2008

Oracle SOA Suite

SOA or Service Oriented Architecture allows organizations to reduce the complexity within there IT Architecture by adopting modular standards such as web services that are adaptable and flexible. The Service Oriented Architecture aims at achieving interoperability between various prosperity software that the organization might use. The SOA makes use of web service standards like Web Services Description Language (WSDL), extensible markup language (XML), and Simple Object Access Protocol (SOAP).

Oracle provides an SOA Suite provides the necessary infrastructure that enables you to create, deploy and managed these SOA complaint Web services.

The main components of the Oracle SOA Suite are

Integrated Service Environment which includes Oracle JDeveloper,Oracle Application Framework and Oracle Toplink.
Oracle BPEL Process Manager
Oracle Enterprise Service Bus
Oracle Business Rules
Oracle Business Monitoring
Oracle Web Services Manager
Oracle Application Server 10g Rel 3

Installing SOA Suite

The Oracle SOA suite needs an 10g infrastructure to which it can be integrated and installed. This can either be the infrastructure that come with the 10g Application Server or the Oracle Collaboration Suite infrastructure.However to install SOA suite the database of the infrastructure must be at 10.1.5 or higher.

Pre Requisites

A 10g Application Server infrastructure installed

As mentioned earlier the SOA suite will be installed on a existing infrastructure, you must install a 10g infrastructure of you already do not have one.

Upgrade Infrastructure Database

The infrastructure database that comes with Oracle Application Server or Oracle Collaboration Suite might be required to be upgraded to 10.1.5 at least.

Create the required SOA schemas In The Database

The SOA suite requires the ORABPEL,ORAESB and ORAWSM schemas to be loaded into your infrastructure database. The irca.sh script will create these schemas for you. You can find this script at install/soa_schemas/irca in your SOA Suite CD.Even if these schemas exist you must overwrite them in order for the SOA Suite to function properly.

Login to your infrastructure tier and execute this script after setting the environment.

$ ./irca.sh
Integration Repository Creation Assistant (IRCA) 10.1.3.1.0
(c) Copyright 2006 Oracle Corporation. All rights reserved.
Enter database "host port serviceName" [localhost 1521 orcl]: samlx.appsdbablog.com 1521 samdb.appsdbablog.com
Enter sys password:
Running IRCA for all product(s):
connection="samlx.appsdbablog.com 1521 samdb.appsdbablog.com", , orabpelUser=ORABPEL, esbUser=ORAESB, orawsmUser=ORAWSM
Validating database ...
Validating database character set ...
Running prerequisite checks for ORABPEL ...
WARNING: This script will overwrite the existing ORABPEL schema.
Do you wish to continue? (y/n) y
Enter password for ORABPEL:
Loading ORAESB schema (this may take a few minutes) ...
Running prerequisite checks for ORAESB ...
Enter password for ORAESB:
Loading ORAESB schema (this may take a few minutes) ...
Running prerequisite checks for ORAWSM ...
Enter password for ORAWSM:
Loading ORAWSM schema (this may take a few minutes) ...
INFO: ORABPEL schema contains 210 valid objects.
INFO: ORAESB schema contains 175 valid objects.
INFO: ORAWSM schema contains 90 valid objects.
IRCA completed.
Please check for any ERROR message above and also check the log file
irca2006-11-30_07-52-09PM.log for any error or other information.

Create OS User

Create a OS user through will the installation of SOA suite will happen.

$useradd -g dba -d /u02/orasoa orasoa

Starting The Installation

Login to the orasoa user and start the installer using the ./runInstaller command.

The first screen lets us choose between a basic installation and a advanced installation, we choose the advance installation option for more felxiblity.

The next screen presents us with the components of SOA suite which we wish to install, here we choose J2EE Server, Web Server and the SOA Suite option.

A pre installation check of the operating system is now carried out.

You must now provide a user with sys DBA privileges on your infrastructure database and the hostname for your infrastructure.

Now you must give the password for the schemas you created using the irca script.

Specify weather you want manual or automatic port selection.

Enter an instance name and the password for the OC4JADMIN super user.

In case you want this instance to be a part of the existing Application Server cluster, you must specify the details here.

The installer will now display the components that it will install and configure for you.

The OUI now start the installation showing the progress.

Like most oracle installation, the SOA also requires you to run the root.sh script as the root user.

The configuration Assistant will now start the configuration of the products and also start the associated services.

At the end of the installation information is displayed on how to access the instance.

You can access the SOA Suite with the URL from the previous screen.

The 11g OCP certification should be available this year. You can register for the production exam 1Z0-050 New Features on Prometrics and pass the exam on Mon Feb 18th or later, the OCA exams 1Z1-051 SQL Fundamentals and 1Z1-052 Admin I are in beta and the OCP exam 1Z1-053 Admin II is planned for early 2008 (well, the OCA exams are planned for late 2007, so do not rely on this timeframe).
Check the official page :
Oracle Education 11g certification

Saturday, January 5, 2008

the password is not longer displayed in dba_users.password in 11g

By reading Pete Finnigan’s Oracle security weblog today, I discovered that the password is no longer displayed in DBA_USERS in 11g.


select username,password
from dba_users
where username='SCOTT';
USERNAME PASSWORD
-------- ------------------------------
SCOTT

select name,password
from sys.user$
where name='SCOTT';
NAME  PASSWORD
----- ------------------------------
SCOTT F894844C34402B67

on the one hand, it is good for the security.

On the other hand, it is a huge change which is not documented (I immediately sent comments to the Security and Reference book authors) and it will make a lot of script failing (scripts that use to change the password to log in and change it back to the original value afterwards).

Protecting the hash is extremely important, check your scripts for 11g compatibility!

Why I hate PHP

echo (0 == null);
=> 1

To those unfamiliar with PHP, 1 stands for true in PHP.

NO COMMENT !!!!!!!

Installing Ruby on Rails on Ubuntu

While there is a nice tutorial in the Ruby on Rails wiki, it’s by no means complete. According to it, you should only type: apt-get install rails to have the newest Rails installed on Ubuntu. It installs both Ruby and Rails, but what about rubygems? Sorry, not this time. There is also another caveat. Although commands like rails test and ruby script/server are working properly, ruby/console is not. If you had the misfortune of experiencing the aforementioned behavior, then this tutorial is just for you.

Pre requirements:

nano /etc/apt/sources.list

Add the following at the end of the file (replace edgy with breezy if you are running Breezy, dapper for Dapper, etc.):

# All Ubuntu repositories
deb http://archive.ubuntu.com/ubuntu edgy main restricted universe multiverse

Update your apt sources:

apt-get update

Installation:

Install Ruby with developer’s libraries:

apt-get install ruby ri rdoc irb ri1.8 ruby1.8-dev libzlib-ruby zlib1g

Download and install Ruby Gems (no .deb package, unfortunately):

wget http://rubyforge.org/frs/download.php/17190/rubygems-0.9.2.tgz
tar xfvz rubygems-0.9.2.tgz
cd rubygems-0.9.2
ruby setup.rb

Update your RubyGems (also updates the gems cache):

gem update --system

If you get Could not find rubygems-update (> 0) in the repository or a similar error, you need to delete your RubyGems cache:

$ gem env gemdir
PATH_TO_DEFAULT_GEM_REPOSITORY
$ rm PATH_TO_DEFAULT_GEM_REPOSITORY/souce_cache

and

rm $HOME/.gem/source_cache

In the next step install the OpenSSL bindings for Ruby (needed to install signed gems). They are required if you get the following error: SSL is not installed on this system, while installing signed gems like rake:

apt-get install libopenssl-ruby

And the last one:

gem install rails -y

And this is basically it. There are, however, depending on your needs, some…

Additional steps:

One of them is setting up the Rails to connect to the MySQL database in a proper way. We will be using the MySQL C bindings, which, for one, support the MySQL old style passwords (which is set as default for Ubuntu 5.04), but are also significantly faster (in the 2-3x range) than the native Ruby MySQL bindings. First, we will need to install the gcc compiler (and libc6-dev if you don’t have it already installed). Although strange it may seem, as a default it is not installed on a clean Ubuntu installation.

apt-get install gcc libc6-dev

MySQL development libraries are also required (mysql_config plus mysql/include):

apt-get install libmysqlclient14-dev

(for MySQL 5.0 you might be better of with libmysqlclient15-dev).

And now we can install C MySQL bindings:

gem install mysql

If you get "sh: make: not found" do:

apt-get install make

or if you have it already installed, add it to your path:

export PATH=/usr/bin:"${PATH}"

And, of course, in the end install Mongrel:

gem install mongrel -y

And that’s it. Rails installation is complete. Complicated? Not really Happy coding!

Wednesday, January 2, 2008

Create Oracle FND_USER with System Administrator

If you have the Apps Password, its quite easy to create a FND_USER for yourself by using the API.
I find this script very useful when development environment gets cloned from Production(that is when i do not have FND_USER in Production.
Please note that:-
1. You will be allocated System Administrator by this script. Hence you can assign whatever responsibilities that you desire latter, after logging in.
2. The password will be set to oracle
3. You need apps password to run this script. Alternately you need execute permission on fnd_user_pkg from the user where this script will be run. If using some other user, please use apps.fnd_user_pkg.createuser
4. You need to do a COMMIT after this script has run. I have not included the commit within this script.
5. When running this script, you will be prompted to enter a user name.

--------Beging of script--------------
DECLARE
--By: Anil Passi
--When Jun-2001
v_session_id INTEGER := userenv('sessionid');
v_user_name VARCHAR2(30) := upper('&Enter_User_Name');
BEGIN
--Note, can be executed only when you have apps password.
-- Call the procedure to Creaet FND User
fnd_user_pkg.createuser(x_user_name => v_user_name
,x_owner => ''
,x_unencrypted_password => 'oracle'
,x_session_number => v_session_id
,x_start_date => SYSDATE - 10
,x_end_date => SYSDATE + 100
,x_last_logon_date => SYSDATE - 10
,x_description => 'appstechnical.blogspot.com'
,x_password_date => SYSDATE - 10
,x_password_accesses_left => 10000
,x_password_lifespan_accesses => 10000
,x_password_lifespan_days => 10000
,x_employee_id => 30 /*Change this id by running below SQL*/
/*
SELECT person_id
,full_name
FROM per_all_people_f
WHERE upper(full_name) LIKE '%' || upper('full_name') || '%'
GROUP BY person_id
,full_name
*/
,x_email_address => 'appstechnical.blogspot@gmail.com'
,x_fax => ''
,x_customer_id => ''
,x_supplier_id => '');
fnd_user_pkg.addresp(username => v_user_name
,resp_app => 'SYSADMIN'
,resp_key => 'SYSTEM_ADMINISTRATOR'
,security_group => 'STANDARD'
,description => 'Auto Assignment'
,start_date => SYSDATE - 10
,end_date => SYSDATE + 1000);
END;
/

Read Only Schema in Oracle APPS 11i

In this article I have discussed how to create and maintain a read only schema for APPS in Oracle eBusiness Suite. Whilst in the past I have known clients to implement this using synonyms. However the approach discussed below is designed without the need of having to create a single synonym in APPS_QUERYschema.

Step 1
Create the read-only schema, in this case lets call it APPS_QUERY.

Step 2.
Surely, the schema created in above Step 1 will be given read only grants to objects in apps. There will be cases where the grant command might fail. To monitor such failures create a table as below
conn xx_g4g/&2 ;
--For APPS_QUERY. This table will capture the exceptions during Grants
PROMPT create table XX_GRANTS_FAIL_APPS_QUERY
create table XX_GRANTS_FAIL_APPS_QUERY (
object_name VARCHAR2(100)
,sqlerrm varchar2(2000)
,creation_date DATE
);

grant all on XX_GRANTS_FAIL_APPS_QUERY to apps with grant option;

grant select on XX_GRANTS_FAIL_APPS_QUERY to apps_query ;

Step 3
In this step we grant select on all the existing views and synonyms in apps schema to apps_query.

conn apps/&1 ;

PROMPT This can take upto 15-30 minutes
PROMPT Granting SELECT on All synonyms and views to apps_query
DECLARE
--One off script to execute grants to apps_query
v_error VARCHAR2(2000);
BEGIN

FOR p_rec IN (SELECT *
FROM all_objects
WHERE owner = 'APPS'
AND object_type IN ('SYNONYM', 'VIEW')
AND object_name NOT LIKE '%_S')
LOOP
BEGIN
EXECUTE IMMEDIATE 'grant select on ' || p_rec.object_name ||
' to apps_query';
EXCEPTION
WHEN OTHERS THEN
v_error := substr(SQLERRM, 1, 2000);
INSERT INTO bes.XX_GRANTS_FAIL_apps_query
(object_name
,SQLERRM
,creation_date
)
VALUES
(p_rec.object_name
,v_error
,sysdate
);
END;
END LOOP;
COMMIT;
END;
/

Step 4
Write a after logon trigger on apps_query schema. The main purpose of this trigger is to alter the session to apps schema, such that the CurrentSchema will be set to apps for the session(whilst retaining apps_query restrictions).In doing so your logon will retain the permissions of apps_query schema(read_only). Howerver it will be able to reference the apps objects with exactly the same name as does a direct connection to apps schema.

conn apps/&1 ;
PROMPT CREATE OR REPLACE TRIGGER xx_apps_query_logon_trg
CREATE OR REPLACE TRIGGER xx_apps_query_logon_trg
--16Jun2006 By Anil Passi
--Trigger to toggle schema to apps, but yet retaining apps_query resitrictions
--Also sets the org_id
AFTER logon ON apps_query.SCHEMA
DECLARE
BEGIN
EXECUTE IMMEDIATE
'declare begin ' ||
'dbms_application_info.set_client_info ( 101 ); end;';
EXECUTE IMMEDIATE 'ALTER SESSION SET CURRENT_SCHEMA =APPS';
END;
/

Step 5
Create a Trigger on the apps schema to issue select only grants for all new views and synonyms. Please note that I am excluding grants for sequences. SELECT grants for views and synonyms will be provided to apps_query as and when such objects are created in APPS. Please note that, all the APPS objects (views and synonyms) that existed in APPS schema prior to the implementation of this design, would have been granted read-only access to apps_query in Step 2.

conn apps/&1 ;
PROMPT CREATE OR REPLACE TRIGGER xx_grant_apps_query
CREATE OR REPLACE TRIGGER xx_grant_apps_query
--16Jun2006 By Anil Passi
--
AFTER CREATE ON APPS.SCHEMA
DECLARE
l_str VARCHAR2(255);
l_job NUMBER;
BEGIN
IF (ora_dict_obj_type IN ('SYNONYM', 'VIEW'))
AND (ora_dict_obj_name NOT LIKE '%_S')
THEN
l_str := 'execute immediate "grant select on ' || ora_dict_obj_name ||
' to apps_query";';
dbms_job.submit(l_job, REPLACE(l_str, '"', ''''));
END IF;
END;
/

Some notes for this design

Note1
You need to ensure that the schema created in Step 1 has very limited permissions. Most importantly it must not be given grant for “EXECUTE/CREATE ANY PROCEDURE”. You will need to agree with your DBAs upfront for the permissions,

Note 2
Only views and synonyms will be granted access. Objects in your xx_g4g(bespoke) schema should have their synonyms in apps already in place.

Note 3
If your site has multi org enabled, you will then have to set the org I'd after loggiong on to apps query schema. In case you have only one single ORG_ID, then would have been set as in Step 4 above.

Note 4
ALTER SESSION SET CURRENT_SCHEMA =APPS
This facilitates users to run their queries as if they were connected to apps schema. However, their previliges will be restricted to those of apps_query

Note 5
It is assumed that ALTER SESSION privilege will exist for APPS_QUERY schema.

Oracle FNDLOAD Script Examples

In this article I wish to give real working examples of Oracle's FNDLOAD utility.
Besides that, I have included some useful notes on FNDLOAD utility

I have used FNDLOAD successfully in past for several different entities/data types within Oracle 11i for almost all my previous clients, ever since this utility became available.
Some of the examples in this FNDLOAD article include:-
FNDLOAD to transfer Request Groups
FNDLOAD for moving Concurrent Programs
FNDLOAD to download and upload Forms Personalizations ( or Personalisations depending on where you are located )

To FNDLOAD Web ADI, visit the link Web ADI FNDLOAD

Use FNDLOAD for transferring value set definitions.
-->Please note that when transferring Key Flex Fields and Descriptive flex fields the respective value sets against each segment will be extracted and loaded automatically.

Also, FNDLOAD can be used to migrate Key FlexFields, Descriptive Flexfields, Responsibilities and almost every other FND entity.

Please note that the text written down here could get wrapped in the browser.
Hence you may have to use \ to continue the single line command on Unix, in case you find the lines wrapping
In my case I am ensuring that $CLIENT_APPS_PWD has the apps password before running the scripts

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

##To FNDLOAD Request groups
FNDLOAD apps/$CLIENT_APPS_PWD O Y DOWNLOAD $FND_TOP/patch/115/import/afcpreqg.lct XX_MY_REPORT_GROUP_NAME.ldt REQUEST_GROUP REQUEST_GROUP_NAME="XX_MY_REPORT_GROUP_NAME" APPLICATION_SHORT_NAME="XXGMS"
##Note that
##---------
## <> will be your Application Shortname where request group is registered
## XX_MY_REPORT_GROUP_NAME
Will be the name of your request group
## ##To upload this Request Group in other environment after having transferred the ldt file

FNDLOAD apps/$CLIENT_APPS_PWD O Y UPLOAD $FND_TOP/patch/115/import/afcpreqg.lct

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

##To FNDLOAD Concurrent Programs
FNDLOAD apps/$CLIENT_APPS_PWD O Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct XX_CUSTOM_ORACLE_INTERFACE_PROG.ldt PROGRAM APPLICATION_SHORT_NAME="XXGMS" CONCURRENT_PROGRAM_NAME="XX_CUSTOM_ORACLE_INTERFACE_PROG"
##Note that
##---------
## XXGMS will be your custom GMS Application Shortname where concurrent program is registered
## XX_CUSTOM_ORACLE_INTERFACE_PROG
Will be the name of your request group
## XX_CUSTOM_ORACLE_INTERFACE_PROG.ldt is the file where concurrent program definition will be extracted
## ##To upload
FNDLOAD apps/$CLIENT_APPS_PWD O Y UPLOAD $FND_TOP/patch/115/import/afcpprog.lct XX_CUSTOM_ORACLE_INTERFACE_PROG.ldt

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

##To FNDLOAD Oracle Descriptive Flexfields
$FND_TOP/bin/FNDLOAD apps/$CLIENT_APPS_PWD 0 Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct XX_PO_REQ_HEADERS_DFF.ldt DESC_FLEX APPLICATION_SHORT_NAME=PO DESCRIPTIVE_FLEXFIELD_NAME='PO_REQUISITION_HEADERS'
##Note that
##---------
## PO is the Application Shortname against which descriptive flexfield against PO Headers is registered
## PO_REQUISITION_HEADERS
is the name of Descriptive Flexfield against PO Requisition Headers
## Use the SQL below to find the name of DFF, rather than logging into the screen (ooops via jinitiator)
########----->SELECT
########----->application_id, DESCRIPTIVE_FLEXFIELD_NAME, application_table_name
########----->FROM
########-----> fnd_descriptive_flexs_vl
########----->WHERE
########-----> APPLICATION_TABLE_NAME like '%' || upper('&tab_name') || '%'
########----->ORDER BY APPLICATION_TABLE_NAME
########----->/
## To upload into another environment
$FND_TOP/bin/FNDLOAD apps/$CLIENT_APPS_PWD 0 Y UPLOAD $FND_TOP/patch/115/import/afffload.lct XX_PO_REQ_HEADERS_DFF.ldt

## OK another example for DFF against FND_LOOKUPS
FNDLOAD apps/$CLIENT_APPS_PWD 0 Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct XX_FND_COMMON_LOOKUPS_DFF.ldt DESC_FLEX APPLICATION_SHORT_NAME=FND DESCRIPTIVE_FLEXFIELD_NAME='FND_COMMON_LOOKUPS'
## OK another example for DFF against Project Accounting Expenditure Types
FNDLOAD apps/$CLIENT_APPS_PWD 0 Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct XX_PA_EXPENDITURE_TYPES_DESC_FLEX_DFF.ldt DESC_FLEX APPLICATION_SHORT_NAME=PA DESCRIPTIVE_FLEXFIELD_NAME='PA_EXPENDITURE_TYPES_DESC_FLEX'

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

##To FNDLOAD Oracle Menus
$FND_TOP/bin/FNDLOAD apps/$CLIENT_APPS_PWD O Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct ICX_POR_SSP_HOME.ldt MENU MENU_NAME="ICX_POR_SSP_HOME"
##Note that
##---------
## Oracle Menus are not attached to applications. Hence no need to include application short name
## ICX_POR_SSP_HOME is the menu name. This can be validated via below SQL
## select user_menu_name from fnd_menus_vl where menu_name = 'ICX_POR_SSP_HOME' ;
## Also note that we do not pass in the User_menu_name in this example
## OK, now to upload this file
$FND_TOP/bin/FNDLOAD apps/$CLIENT_APPS_PWD O Y UPLOAD $FND_TOP/patch/115/import/afsload.lct ICX_POR_SSP_HOME.ldt

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

## Well, now for FND Messages to download a single message
FNDLOAD apps/$CLIENT_APPS_PWD 0 Y DOWNLOAD $FND_TOP/patch/115/import/afmdmsg.lct \
XX_ICX_POR_LIFECYCLE_PAY_TIP.ldt FND_NEW_MESSAGES APPLICATION_SHORT_NAME='ICX' MESSAGE_NAME=XX_ICX_POR_LIFECYCLE_PAY_TIP

## Or you may as well download all the messages within an application
FNDLOAD apps/$CLIENT_APPS_PWD 0 Y DOWNLOAD $FND_TOP/patch/115/import/afmdmsg.lct \
XX_ALL_GMS_MESSAGES_00.ldt FND_NEW_MESSAGES APPLICATION_SHORT_NAME='XXGMS'

## now to upload using FNDLOAD
FNDLOAD apps/$CLIENT_APPS_PWD 0 Y UPLOAD $FND_TOP/patch/115/import/afmdmsg.lct XX_ICX_POR_LIFECYCLE_PAY_TIP.ldt

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

## Now it's the turn of Lookup values. Again, its not a rocket science
FNDLOAD apps/$CLIENT_APPS_PWD 0 Y DOWNLOAD aflvmlu.lct XX_TRX_BATCH_STATUS.ldt FND_LOOKUP_TYPE APPLICATION_SHORT_NAME ='XXGMS' LOOKUP_TYPE="XX_TRX_BATCH_STATUS"
## Note that
## XX_TRX_BATCH_STATUS is the name of FND Lookup Type in this example
## This will download all the lookup codes within the defined lookup
## To upload
FNDLOAD apps/$CLIENT_APPS_PWD 0 Y UPLOAD aflvmlu.lct XX_TRX_BATCH_STATUS.ldt

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

## You can also move the User definitions from FND_USER
FNDLOAD apps/$CLIENT_APPS_PWD 0 Y DOWNLOAD $FND_TOP/patch/115/import/afscursp.lct ./XX_FND_USER_PASSI.ldt FND_USER USER_NAME='ANILPASSI'
#Do not worry about your password being extracted, it will be encrypted as below in ldt file
#BEGIN FND_USER "ANILPASSI"
# OWNER = "PASSIA"
# LAST_UPDATE_DATE = "2005/10/19"
# ENCRYPTED_USER_PASSWORD = "ZGE45A8A9BE5CF4339596C625B99CAEDF136C34FEA244DC7A"
# SESSION_NUMBER = "0"
To upload the FND_USER using FNDLOAD command use
FNDLOAD apps/$CLIENT_APPS_PWD 0 Y UPLOAD $FND_TOP/patch/115/import/afscursp.lct ./XX_FND_USER_PASSI.ldt
Notes for using FNDLOAD against FND_USER:-
1. After uploading using FNDLOAD, user will be promoted to change their password again during their next signon attempt.
2. All the responsibilities will be extracted by FNDLOAD alongwith User Definition in FND_USER
3. In the Target Environment , make sure that you have done FNDLOAD for new responsibilities prior to running FNDLOAD on users.

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

## Now lets have a look at the profile option using oracle's FNDLOAD
FNDLOAD apps/$CLIENT_APPS_PWD O Y DOWNLOAD $FND_TOP/patch/115/import/afscprof.lct POR_ENABLE_REQ_HEADER_CUST.ldt PROFILE PROFILE_NAME="POR_ENABLE_REQ_HEADER_CUST" APPLICATION_SHORT_NAME="ICX"
## Note that
## POR_ENABLE_REQ_HEADER_CUST is the short name of profile option
## We aren't passing the user profile option name in this case. Validate using ...
########----->select application_id, PROFILE_OPTION_NAME || '==>' || profile_option_id || '==>' ||
########----->USER_PROFILE_OPTION_NAME
########----->from FND_PROFILE_OPTIONS_VL
########----->where PROFILE_OPTION_NAME like '%' || upper('&profile_option_name') || '%'
########----->order by PROFILE_OPTION_NAME
########----->/
## Now to upload
FNDLOAD apps/$CLIENT_APPS_PWD O Y UPLOAD $FND_TOP/patch/115/import/afscprof.lct POR_ENABLE_REQ_HEADER_CUST.ldt

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

## Now for the request sets that contain the stages and links for underlying concurrent programs
## For this you will be firstly required to download the request set definition.
## Next you will be required to download the Sets Linkage definition
## Well, lets be clear here, the above sequence is more important while uploading
FNDLOAD apps/$CLIENT_APPS_PWD 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcprset.lct XX_GL_MY_INTERFACE_SET.ldt REQ_SET REQUEST_SET_NAME="FNDRSSUB4610101_Will_look_like_this"
FNDLOAD apps/$CLIENT_APPS_PWD 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcprset.lct XX_GL_MY_INTERFACE_SET_LINK.ldt REQ_SET_LINKS REQUEST_SET_NAME="FNDRSSUB4610101_Will_look_like_this"
## Note that FNDRSSUB4610101 can be found by doing an examine on the
########----->select request_set_name from fnd_request_sets_vl
########----->where user_request_set_name = 'User visible name for the request set here'
## Now for uploading the request set, execute the below commands
FNDLOAD apps/$CLIENT_APPS_PWD 0 Y UPLOAD $FND_TOP/patch/115/import/afcprset.lct XX_GL_MY_INTERFACE_SET.ldt
FNDLOAD apps/$CLIENT_APPS_PWD 0 Y UPLOAD $FND_TOP/patch/115/import/afcprset.lct XX_GL_MY_INTERFACE_SET_LINK.ldt

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

## Now for the responsibility
FNDLOAD apps/$CLIENT_APPS_PWD O Y DOWNLOAD $FND_TOP/patch/115/import/afscursp.lct XX_PERSON_RESPY.ldt FND_RESPONSIBILITY RESP_KEY="XX_PERSON_RESPY"
## note that XX_PERSON_RESPY is the responsibility key
## Now to upload
FNDLOAD apps/$CLIENT_APPS_PWD O Y UPLOAD $FND_TOP/patch/115/import/afscursp.lct XX_PERSON_RESPY.ldt

----------------------------------------------------------------------------------------------------------------------------
## OK, now for the forms personalizations
## For the forms personalizations, I have given three examples as below.
FNDLOAD apps/$CLIENT_APPS_PWD 0 Y DOWNLOAD $FND_TOP/patch/115/import/affrmcus.lct XX_PERWSHRG.ldt FND_FORM_CUSTOM_RULES function_name="PERWSHRG-404"
FNDLOAD apps/$CLIENT_APPS_PWD 0 Y DOWNLOAD $FND_TOP/patch/115/import/affrmcus.lct XX_HZ_ARXCUDCI_STD.ldt FND_FORM_CUSTOM_RULES function_name="HZ_ARXCUDCI_STD"
FNDLOAD apps/$CLIENT_APPS_PWD 0 Y DOWNLOAD $FND_TOP/patch/115/import/affrmcus.lct XX_AP_APXVDMVD.ldt FND_FORM_CUSTOM_RULES function_name="AP_APXVDMVD"
## Note that the function name above is the function short name as seen in the Function Definition Screen
## Now to upload the forms personalizations that are defined against these forms functions....
FNDLOAD apps/$CLIENT_APPS_PWD 0 Y UPLOAD $FND_TOP/patch/115/import/affrmcus.lct XX_PERWSHRG.ldt
FNDLOAD apps/$CLIENT_APPS_PWD 0 Y UPLOAD $FND_TOP/patch/115/import/affrmcus.lct XX_HZ_ARXCUDCI_STD.ldt
FNDLOAD apps/$CLIENT_APPS_PWD 0 Y UPLOAD $FND_TOP/patch/115/import/affrmcus.lct XX_AP_APXVDMVD.ldt

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

Notes :
1. Give special attention when downloading Menus or Responsibilities.
In case your client has several developers modifying Responsibilities and Menus, then be ultra carefull. Not being carefull will mean that untested Forms and Functions will become available in your clients Production environment besides your tested forms, functions and menus.

2. Be very careful when downloading flexfields that reference value sets with independent values for GL Segment Codes.
By doing so, you will download and extract all the test data in GL Codes that might not be applicable for production.

3. There are several variations possible for FNDLOAD, for example you can restrict the download and uploads to specific segments within Descriptive Flex Fields. Please amend the above examples as desired for applying appropriate filterations.

4. The list of examples by no mean cover all possible FNDLOAD entities.

5. FNDLOAD is very reliable and stable, if used properly. This happens to by one of my favourite Oracle utilities.

4. Last but not the least, please test your FNDLOAD properly, so as to ensure that you do not get any unexpected data. In past I have noticed undesired results when the Lookup gets modified manually directly on production, and then the FNDLOAD is run for similar changes. If possible, try to follow a good practice of modifying FNDLOADable data only by FNDLOAD on production environment.

5. As the name suggests, FNDLOAD is useful for FND Related objects. However in any implementation, you will be required to migrate the Setups in Financials and Oracle HRMS from one environment to another. For this you can use iSetup. "Oracle iSetup".
Some of the things that can be migrated using Oracle iSetup are
GL Set of Books, HR Organization Structures, HRMS Employees, Profile Options Setup, Suppliers, Customers, Tax Codes
& Tax Rates, Financials Setup, Accounting Calendars, Chart of Accounts, GL Currencies.

Playing with CUSTOM.pll

lease find some commands for CUSTOM.pll

To convert from CUSTOM.pll to CUSTOM.pld
f60gen module_type=LIBRARY module=CUSTOM script=YES userid=apps/apps

To convert back from CUSTOM.pld to CUSTOM.pll ( after having edited the text pld file )
f60gen module_type=LIBRARY module=CUSTOM parse=YES userid=apps/apps

To convert from CUSTOM.pll to CUSTOM.plx
f60gen module_type=LIBRARY module=CUSTOM userid=apps/apps

SSHR Rollout Create FND_USER and Allocate Responsibilities

Every organization that rolls out any Oracle Self Service Application requires a methodology to allocate responsibilities and to create Users.
The pseudo code example taken here has following requirements:-
1. Create a concurrent program, passing to parameter for one Cost Centre/Group or all Cost Centres.
This way, Self Service can be rolled out in Phased manner.
2. Besides a parameter for the cost centre, there will be another parameter to run the process in a Read-Only mode.
3. There are two possibilities for the Employees which are eligible(given their cost centre).
Possibility a. The employee already has an existing FND_USER record
Possibility b. The employee does not have an existing FND_USER record.

Solution for Possibility a.
Identify the FND_USER record, using the fnd_user.employee_id = per_all_people_f.person_id
Use fnd_user_resp_groups_api.insert_assignment to add new Self Service responsibility to that User.
Send the user an Email giving them details of the responsibility that has been added.

Solution for Possibility b.
Create an FND_USER record, using the fnd_user_pkg, attaching this to Person Id.
Use fnd_user_resp_groups_api.insert_assignment to add new Self Service responsibility to that User.
Send them an email to inform the User-Id and Password along with instructions to log on & use the application.

Questions & Answers
How do I create a 8 digit password for FND_USER record?
FUNCTION get_random_password RETURN VARCHAR2 IS
BEGIN
RETURN lower(dbms_random.STRING('X',8));
END get_random_password;

We want users to be enforced to alter their password every 6months, how to ?
Use parameter x_password_lifespan_days
fnd_user_pkg.createuser
(
x_user_name => p_user_name
,x_owner => ''
,x_unencrypted_password => v_password
,x_description => p_person_description
,x_password_lifespan_days => 180
,x_employee_id => p_person_id
,x_email_address => p_email_address
);

The random password that is generated might have repeating characters, which will error if Profile "Signon Password Hard To Guess" is Set to Yes?
You can temporarily set the profile to N in the session during which concurrent program runs.
fnd_profile.put(NAME => 'SIGNON_PASSWORD_HARD_TO_GUESS' ,val => 'N');

Should I use Workflow to inform users?
You might as well use SMTP. Be careful that on Dev environment business users do not receive Emails.
This can be ensured by checking return of select instance_name from v$instance to be PRD Database.
You can use procedure send_html_email in the Code Sample in link provided in the article.

Can I run the source code as is?
The source code provided is for allocating a responsibility named "XX HR Employee Self Service".I have removed all the client specific bits from the code. Hence you will need to alter to make this to be of any use. The idea is to merely provide you a guideline for API Usage.

Validate Email PL/SQL

Oracle delivers an API in EBS that can validate Email Address Format.
However it does not capture the invalidity if Email String were to be say xxx@host
You may use the below pl/sql function for Email Address Validation.

CREATE OR REPLACE FUNCTION xx_check_email(l_user_name IN VARCHAR2)
RETURN VARCHAR2 IS
l_dot_pos NUMBER;
l_at_pos NUMBER;
l_str_length NUMBER;
BEGIN
l_dot_pos := instr(l_user_name
,'.');
l_at_pos := instr(l_user_name
,'@');
l_str_length := length(l_user_name);
IF ((l_dot_pos = 0) OR (l_at_pos = 0) OR (l_dot_pos = l_at_pos + 1) OR
(l_at_pos = 1) OR (l_at_pos = l_str_length) OR
(l_dot_pos = l_str_length))
THEN
RETURN 'FAILURE';
END IF;
IF instr(substr(l_user_name
,l_at_pos)
,'.') = 0
THEN
RETURN 'FAILURE';
END IF;
RETURN 'SUCCESS';
END xx_check_email;

Which FND_USER is locking that table

This is a quick note to share a SQL that will tell you the FND_USER.USER_NAME of the person that has locked a given table in Oracle APPS.

The column named "module" will tell you the name of the Form Function or the Concurrent Program Short name which has aquired a lock onto that table.

SELECT c.owner
,c.object_name
,c.object_type
,fu.user_name locking_fnd_user_name
,fl.start_time locking_fnd_user_login_time
,vs.module
,vs.machine
,vs.osuser
,vlocked.oracle_username
,vs.sid
,vp.pid
,vp.spid AS os_process
,vs.serial#
,vs.status
,vs.saddr
,vs.audsid
,vs.process
FROM fnd_logins fl
,fnd_user fu
,v$locked_object vlocked
,v$process vp
,v$session vs
,dba_objects c
WHERE vs.sid = vlocked.session_id
AND vlocked.object_id = c.object_id
AND vs.paddr = vp.addr
AND vp.spid = fl.process_spid(+)
AND vp.pid = fl.pid(+)
AND fl.user_id = fu.user_id(+)
AND c.object_name LIKE '%' || upper('&tab_name_leaveblank4all') || '%'
AND nvl(vs.status
,'XX') != 'KILLED';