Oracle Application Express Install

In this post I will discuss the steps needed to install Oracle Application Express 4.2.1.

First you need to make sure you have an Oracle DB installed on your computer. You can get a free version here.

Oracle 11G Express Edition

Here you need a login to the Oracle site. If you don’t have one don’t worry, it’s free to create one! Accept the license agreement then download the DB and install(I may write a blog post about how to install later).

Now to install Oracle Application Express.

Installing Oracle Application Express

Just like the Oracle DB, you need to download it from the Oracle site

Oracle Application Express 4.2

Once downloaded you will need to unzip the folder and move to your desired location. You will notice that all the files  inside the folder are  SQL files and no executable files of any kind. Open a DOS windows and navigate to the location of the apex files. Then run SQL*Plus from the cmd prompt. Login as the sysdba(password was set during DB installation)

Oracle Application Express
After connecting to the Oracle DB you need to run the apexins script.
An example of what parameters to use is shown below

@apexins.sql SYSAUX SYSAUX TEMP /i/

The first parameter is the tablesspace to use for the application user
The second parameter is the table space to use for the files user
The third parameter is the temp tablespace
The fourth parameter is the virtual directory for the application images

 

After that script gets everything setup the next step is to change the admin password. Again you run a script to accomplish this

@apxchpwd.sql

Here you will be prompted for a new password. Make sure the password has at least one capital letter, one number and one special character. I think a minimum of 6 characters are required. If you do not meet the password requirements you will not get any notification of it, the script will simply fail.

 

APEX2

After this step you are now ready to kick off Oracle Apex and begin making apps. I will cover this part in a separate blog post later.

Here is the Installation Guide made by Oracle for further reading.

Oracle Apex Installation Guide

 

Oracle Named Loops

When using nested loops in your PL/SQL code it is beneficial to use  Oracle named loops to make the code easier to read.

To name a loop you simply add <<LOOP_NAME>> before the LOOP keyword, then add LOOP_NAME at the end of the END LOOP keyword as shown below

<<MAIN_LOOP>>
LOOP
......
END LOOP MAIN_LOOP;

 

Example using Oracle Named Loops

The code below finds all prime numbers less than 50 while taking advantage of Oracle named loops.

DECLARE

   a number(3);
   b number(3);
BEGIN
   a := 2;
   <<MAIN_LOOP>>
   LOOP
      b:= 2;
      <<SUB_LOOP>>
      LOOP
         exit WHEN ((mod(a, b) = 0) or (b = a));
         b := b +1;
      END LOOP SUB_LOOP;
   IF (b = a ) THEN
      dbms_output.put_line(a || ' is prime');
   END IF;
   a := a + 1;
   exit WHEN a = 50;
   END LOOP MAIN_LOOP;
END;

As you can see the code is much easier to read when using Oracle named loops. I find it easier to keep track of the begin of end of loops when using nested loops. Other developers that read through your code will thank you too.

For more info on loops you can visit this link

Loops

 

Oracle Sequence Nextval

Using Oracle sequence nextval you can autonumber a column in a table, that column is usually the primary key. This is useful if you want to increment that column with each new record. This way you do not have to know the max of that column before issuing an insert statement. Assuming the sequence is called CUSTOMER_SEQ and you are inserting a record into the CUSTOMER table is would look like this.

INSERT INTO CUSTOMER VALUES(CUSTOMER_SEQ.NEXTVAL,'Bo','Smith');

The sequence keeps the following information

  • CREATED
  • LAST_DDL_TIME
  • SEQUENCE_OWNER
  • SEQUENCE_NAME
  • MIN_VALUE
  • MAX_VALUE
  • INCREMENT_BY
  • CYCLE_FLAG
  • ORDER_FLAG
  • CACHE_SIZE
  • LAST_NUMBER

The most relevant part of the sequence are the MIN_VALUE,MAX_VALUE,LAST_NUMBER and INCREMENT_BY .

If a table has a sequence and you do not use oracle sequence nextval when inserting new records then you will have to reset(increment) the sequence. The sequence does not know when you insert a row without using it. So if you where to do

SELECT MAX(CUSTOMER_NUMBER) FROM CUSTOMER;

and receive 123 back as the result of the query then you would know to use 124 as the next value for CUSTOMER_NUMBER when inserting a new record.

If this occurs, the next time oracle sequence nextval is used to insert a new record into that table is will try to use 124 as the next value which is already taken and will cause an error. You will then have to reset the sequence as said before. I have a script that will do this for you and here’s the code

Code to reset the Oracle Sequence Nextval

create or replace
procedure Reset_Sequence( p_seq_name in varchar2, 
p_val in number default 0) 
is 
l_current number := 0; 
l_difference number := 0; 
l_minvalue user_sequences.min_value%type := 0; 

--takes sequence name and new start number, 
--then sets that value for specified sequence
begin 
--get min value for sequence
select min_value 
into l_minvalue 
from user_sequences 
where sequence_name = UPPER(p_seq_name); 

--get current sequence value
execute immediate 
'select ' || UPPER(p_seq_name) || '.nextval from dual' 
INTO l_current; 

--can't set the value to something smaller than the min value
if p_Val < l_minvalue then 
l_difference := l_minvalue - l_current; 
else 
l_difference := p_Val - l_current; 
end if; 

if l_difference = 0 then 
return; 
end if; 

execute immediate 
--set increment to difference
'alter sequence ' || UPPER(p_seq_name) || ' increment by ' 
|| l_difference || 
' minvalue ' || l_minvalue; 

--run sequence to imcreament to value specified
execute immediate 
'select ' || UPPER(p_seq_name) || '.nextval from dual' 
INTO l_difference; 

--reset increment value back to 1
execute immediate 
'alter sequence ' || UPPER(p_seq_name) ||
 ' increment by 1 minvalue ' || l_minvalue; 
end Reset_Sequence;

This script takes the sequence name which can be in lower or upper case, and the new LAST_NUMBER value which you can get by querying for the max column that the sequence uses.

To create a sequence mentioned above you would use the code

CREATE SEQUENCE CUSTOMER_SEQ
 START WITH     1
 INCREMENT BY   1
 NOCACHE

NOCACHE means that the sequence does not preallocate numbers. You can specify a CACHE number for the sequence to preallocate for performance purposes.

Oracle 10G Purge Feature

With the new Oracle 10G purge feature a table is not truly dropped by using the drop table command. It is only renamed and considered inside Oracle’s recycle bin. This is important to know for 2 reasons.

1. If you drop a table it still counts against your tablespace until you remove it from the recycle bin.
2. If you change your mind about dropping a table you can simply flashback the table and the table structure with the data will be back.

Working with the new Oracle 10G Purge Feature

To retrieve a table you dropped consider the following.

flashback table t to before drop;

To remove a table from the recycle bin after it’s been dropped

delete from recyclebin where original_name = 't';

To drop a table and skip the recycle bin

drop table t purge;

Here is a link to an Ask Tom segment that talks about the new Oracle 10G Purge feature.

Ask Tom

Hello!

With this blog I plan to share my trials and tribulations with my continued learning of various programming languages, I will share my projects with everyone and also share any new findings I come across.

I also plan to post about PL/SQL, Oracle Apex, Java and .Net to help people learn about these technologies.  You will also find on this webpage my portfolio which I plan to fill with great examples of my work. I may discuss as much as I am allowed to, what I do at work.

I hope to connect with others that share the same passions I do!