Windows Oracle Apex upgrade on XE from 2.2 to 3.2

I was tempted to upgrade my Apex installation to 3.2 from default (2.2) that comes along with Oracle XE database.
I followed the instructions on
http://www.oracle.com/technology/products/database/application_express/html/3.2_and_xe.html
and
http://blogs.oracle.com/SanthoshK/2008/09/oracle_xe_apex_3x_installation.html

But finally got struck during the step of loading images. I had kept the APEX install on D:

SQL> @apxldimg.sql D:\Downloads\Oracle_Tools\apex_3.2

PL/SQL procedure successfully completed.

old   1: create directory APEX_IMAGES as '&1/apex/images'
new   1: create directory APEX_IMAGES as 'D:\Downloads\Oracle_Tools\apex_3.2/apex/images'

Directory created.

declare
*
ERROR at line 1:
ORA-22288: file or LOB operation FILEOPEN failed
Access is denied.
ORA-06512: at "SYS.DBMS_LOB", line 523
ORA-06512: at "SYS.XMLTYPE", line 287
ORA-06512: at line 15

I tried several alternatives like changing the apxldimg.sql script and googled atleast 2 hours before trying a simple solution.

I copied the apex installation folder from D: drive to
C:\apex_32 and reran the step

SQL> @apxldimg.sql c:\apex_32

PL/SQL procedure successfully completed.

old   1: create directory APEX_IMAGES as '&1/apex/images'
new   1: create directory APEX_IMAGES as 'c:\apex_32/apex/images'

Directory created.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
Commit complete.

timing for: Load Images
Elapsed: 00:01:01.59

Directory dropped.

Now I can see all the images on http://localhost:8080/apex
Apparently, there are quirks involving any other drive than C: that need to be resolved by Oracle. But for the time this is the quickest way to resolve and enjoy the functionalities of Apex 3.2

Step by Step guide to Install Oracle 11g on Ubuntu VM

After downloading the Ubuntu VMWare image, there were few more quirks to settle.
I had to install NFS and SSH on the machine to make it accessible by ssh from Windows.

Following are the commands:
sudo apt-get install openssh-server openssh-client

sudo apt-get install nfs-kernel-server nfs-common portmap

One of the forums that helped were:
http://ubuntuforums.org/archive/index.php/t-492772.html

Now I setup the samba share on this machine

sudo aptitude install samba

https://help.ubuntu.com/community/SettingUpSamba

After installing the samba, create a directory on the FS which you want to share with Windows or other systems.
Then edit /etc/samba/smb.conf and add these lines to the end

[share_name]
        comment = Share to Linux
        path = /home/user/winshare
        guest ok = yes
        writeable = yes

You can use the following commands to restart the services if needed:
/etc/init.d/nfs-common restart
/etc/init.d/samba restart

Following is a suggested helpful link:
http://www.howtogeek.com/howto/ubuntu/create-a-samba-user-on-ubuntu/

Process to install the Oracle on Ubuntu starts

This page is particularly helpful and is worth reading every bit:
http://www.pythian.com/blogs/654/installing-oracle-11g-on-ubuntu-linux-710-gutsy-gibbon

It mentions the packages that need to be updated before Oracle Installation.

apt-get update
apt-get upgrade (This would update the packages from the internet and will take sometime)

Anyways follow step by step the instructions in the above link to complete the installation.

At some point of time, you will encounter insufficient disk space in Linux due to which you cannot install Oracle.
To add more disk space to the Linux VM, follow the link below
http://www.matttopper.com/?p=25

I allocated a 20GB SCSI HD to Linux VM and split it into 3 partitions. The FSTAB entries are:
/dev/sdb1 /u01 ext3 defaults 1 1
/dev/sdb2 /oradata ext3 defaults 1 1
/dev/sdb3 /data ext3 defaults 1 1

Also, the Oracle installer needs a GUI. So, you need an X-Server (like eXceed) to complete the installation.
Simply on command prompt, set DISPLAY

For bash- > export DISPLAY=ExceedHostIP:0.0

And then start Exceed on Windows and connect to the IP address of the VM linux. Try 3-4 times to connect as it may error out.

If you get a timeout error, ignore the message and press nothing. Continue with Installation and complete the post-installation steps in
http://www.pythian.com/blogs/654/installing-oracle-11g-on-ubuntu-linux-710-gutsy-gibbon

Good Luck

Installing APEX on Oracle 11g from Database Troubleshooting

I installed Oracle 11g on Oracle Linux from a VM Image. So I am good with Oracle Installation.

Now my interest is to use APEX on this database.

I followed the instructions on the following page:
http://download.oracle.com/docs/cd/E10513_01/doc/install.310/e10496/db_install.htm#CBHCDBAB

After the installation is complete and I tried to access
http://localhost:8080/apex/apex_admin, I kept getting username/password for xdb account.
I tried unlocking the account xdb and anonymous using
alter user xdb account unlock;
alter user anonymous account unlock;

It didnt work.

If it works for you then, you will be able to access the site. That site requires admin as username.
You will need to change the password for this user in the database by executing the following command:
sql> @apxxepwd.sql <admin123>
This command is found in $ORACLE_HOME/apex

Then I tried executing apex_epg_config script found in
/ora/db/11.1.0/apex

It asks for a parameter. This parameter should be /ora/db/11.1.0
eg.
sql> @apex_epg_config $ORACLE_HOME

It basically loads the apex images to the database. Instructions for executing this script is not well-documented.

But still the problem remains the same. I am not able to access the website.

Now I stop the listener.
$ lsnrctl stop

and then
$lsnrctl start

Copyright (c) 1991, 2007, Oracle. All rights reserved.

Starting /ora/db/11.1.0/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 11.1.0.6.0 - Production
System parameter file is /ora/db/11.1.0/network/admin/listener.ora
Log messages written to /ora/diag/tnslsnr/oracle2go/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oracle2go.us.oracle.com)
(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oracle2go.us.oracle.com)
(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.1.0.6.0 - Production
Start Date 03-FEB-2009 08:26:18
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /ora/db/11.1.0/network/admin/listener.ora
Listener Log File /ora/diag/tnslsnr/oracle2go/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oracle2go.us.oracle.com)(PORT=1521))
)
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
The listener supports no services
The command completed successfully

Then waited for 5 minutes. Somehow it didnt work for me the next instant. I think it takes some time to refresh.

Now I can access the
http://localhost:8080/apex website.

The key is to restart the listener after unlocking the xdb account.

There is another page that I hit while searching for the solutions to APEX installation:
http://www.uaex.edu/srea/Application_Express_Installation.htm

Oracle Subquery bug - Test each subquery individually

Here arises the importance of unit testing each subquery individually. Consider the scenario below:

I have this code:

 DELETE FROM cfgview
        WHERE cfgviewid NOT IN (SELECT cfgviewid
                                FROM vw_ext_acc_configview);

The inner subquery if executed alone, returns an error because the columnname cfgviewid doesn't exist.

But if you execute the full query, it returns successfully and deletes 0 rows.

So, this implies that you need to unit test each query before adding it as a subquery.

Oracle Enterprise Manager 10g Job Scheduler Configuration Problem Resolution for Solaris

Oracle Enterprise Manager 10g is a powerful tool that comes with many administrative features which make database management much more simpler.
It has an inbuilt job scheduler that lets you schedule jobs. Its not a very feature rich scheduler but still does quite a lot considering the fact that it is free with Oracle's License.

I am outlining a configuration problem that may save some time to diagnose:
If a shell script or a Java program is executed using OEM under the credentials of a user who doesnt belong to the Oracle user group on Solaris, it fails with the following error:

Shell script :

/bin/sh: cannot determine current directory
or

shell-init: could not get current directory: getcwd: cannot access parent directories: Permission denied

Java Proram :

Error occurred during initialization of VM
java.lang.Error: Properties init: Could not determine current working directory

This is due to permission lacking on the directory where the OEM agent is installed
/u01/app/oracle/product/agent10g/sysman/emd

Check the permissions on this directory.
drwxr-x--- 6 oracle dba 512 May 12 13:40 emd

Change the permissions to
drwxr-xr-x 6 oracle dba 512 May 12 13:40 emd

I believe that OEM's working directory is the above location and since the underlying user doesnt have access to this directory, hence all the programs error out.

Oracle Transparent Gateway SQL Server Encoding issues resolution

This article for all those who are involved in migration of data from Oracle to SQL server or vice versa.
It highlights a number of encoding issues that you might be facing.


You cannot represent the left and right quotation marks in Latin 8859-1.
They are represented as ‘(â\200\230) and ’ (\200\231).
Only when you set the format to UTF-8 (Unicode Transport Format), you can see them as ( ‘ ) and ( ’ ). These symbols are not found on the Windows keyboards but they are generated by tools like Microsoft Word.

When inserted inside the database, they may get messed up.

There is another problem while inserting these characters in SQL Server database using TG:
SS uses Collation Latin1_General_CI_AS by default. And UCS-2 is the UTF8 equivalent of Oracle in SS. But by no means, they are compatible.

So, a character needs to be converted to ANSI before moving over to SS.

Another problem with UTF8 is with the German characters involving Umlauts. If that is the last character in an insert statement, then 'ORA-01756: quoted string not properly terminated' is encountered.
eg: insert into tmp_enc@dblinkToSS values ('Umlauts ä ö ü ß Ä Ö Ü ');
would result in an error.

Adding a space before the terminating quote character will result in Success.
insert into tmp_enc@dblinkToSS values ('Umlauts ä ö ü ß Ä Ö Ü ');


This is being discussed at the following thread too:

http://www.orafaq.com/forum/t/89484/0/

SQL Server create table as Syntax Oracle

SQL server syntax for creating a table from a query

select * into new_table from
(select col1, col2 from old_tab1,old_tab2 where 1=1) a


Keep in mind that the alias needs to be specified. SQL Server is not that forgiving.


I find the Oracle syntax much intuitive:
create table new_table
as
select * from ....