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

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,

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.

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.