Showing posts from July, 2010

Upgrade Oracle Apex 4.0 in 11g Database on Linux

Before start the up gradation of Oracle apex 4.0 in oracle 11g you must take backup. My working directory is /opt/oracle therefore I copy the source of oracle apex 4.0 ( in my working directory. 

Open terminal and using unzip command extract the

After extraction apex directory is created in /opt/oracle

Login as SYS with SYSDBA privilege and create tablespace for apex as shown

After successful creation of tablespace, exit from SQL using quit command. Note: you can assign different directory for tablespace creation.
Now get into the directory on terminal /opt/oracle/apex and log in again as SYS with SYSDBA privilege.
apexins.sql script need to execute to start installation of apex 4.0. To execute apexins.sql script type the below shown command and mention in which TABLESPACE will be default for apex installation files.

It will take 15 to 20 minutes to complete the installation.
After completing the installation then you have to load the necessary images to the da…

Configure Oracle Apex in 11g on SUSE Linux Enterprise Server

Today I configured Oracle APEX in Oracle 11g Release 1 on SUSE Linux Enterprise Server 11. I did not realize that Oracle APEX is a part of the 11g standard database components.
So I just follow simple steps to configure Oracle APEX.  APEX application can access either the embedded PL/SQL gateway or Oracle HTTP server. For simplicity I chose the embedded PL/SQL gateway. By using the embedded PL/SQL gateway, it will run using the Oracle XML DB HTTP server which is already in Oracle database, so there is no need to install a separate HTTP server.
Step 1
To Configure the embedded PL/SQL gateway go to the $ORACLE_HOME/apex directory. In my case apex directory in the given below path
Step 2
Use SQL/Plus to connect as SYS to 11g database where APEX is installed. After connection type @apxconf and press enter as shown in the screenshot.

Create password for admin user

Step 3
Unlock the ANONYMOUS account.

Step 4
Enable Oracle XML DB HTTP server

Step 5
Oracle APEX is ready to ac…

Configure GRUB - SUSE Linux Enterprise Server 11

Configure GRUB by editing the file /boot/grub/menu.lst. The following is the general structure of the file:
default 0. The first entry (numbering from 0) is the default boot entry that starts automatically if no other entry is selected with the keyboard. timeout 8. The default boot entry is started automatically after 8 seconds. gfxmenu (hd0,0)/boot/message. This defines where the graphical menu is stored.
Following is an example of the configuration file /boot/grub/menu.lst

I have both SUSE Linux Enterprise Server 11 and Window 7 in my system. I want when grub load, Window 7 selected as default boot entry therefore i change the default entry from 0 to 3 in menu.lst file. 

Save the file and reboot your system now the default boot entry change to window 7.
Configure GRUB with YAST
Press Alt+F2 from the Desktop Run Window will open. Now type YAST2 and press enter.

The following appears

When the Section Management tab is selected, you see the current GRUB settings for your system. There is a Def (…

Oracle Function by examples

Functions are another type of stored code and are very similar to procedures. The significant difference is that a function is a PL/SQL block that returns a single value. Functions can accept one, many, or no parameters, but a function must have a return clause in the executable section of the function. The datatype of the return value must be declared in the header of the function.
Function Syntax
The syntax for creating a function is as follows:
CREATE [OR REPLACE] FUNCTION function_name (parameter list) RETURN datatype IS BEGIN RETURN (return_value); END;
The function does not necessarily have any parameters, but it must have a RETURN value whose datatype is declared in the header. 
Function Example

Invoking the Function

Passing Parameter to Function

Oracle Procedures by examples

A procedure is a module that performs one or more actions; it does not need to return any values.
The syntax for creating a procedure is as follows:
CREATE OR REPLACE PROCEDURE name [(parameter[, parameter, ...])] AS [local declarations] BEGIN executable statements [EXCEPTION exception handlers] END [name];
A procedure may have zero to many parameters.
Parameters are the means to pass values to and from the calling environment to the server. There are three types of modes for procedures:-
IN: data passed to the procedure. OUT: data returned by the procedure. INOUT: data passed to the procedure that is, during procedure execution, replaced by data to be returned from the procedure.
IN Mode Example
I simply created a procedure using IN mode that insert data into the table by using TOAD for Oracle.

Use F5 key to compile procedure. Now passing data from calling envirnment to the procedure.

Data insert successfully into the table by using procedure.
OUT Mode Example
This procedure is simply selecting data from…