Saturday, April 28, 2018

Advanced Form (Nested Form) development with Oracle APEX

Leave a Comment

This article will discuss about advanced form (nested form) development with Oracle APEX. This article gives an example about creating nested form on student registration process. We will use 3 tables based on following Entity Relationship Diagram (ERD):

Here is the mockup design of the input form that we’re going to create:
Form Pilih Mata Kuliah


<Display Only (sesuai login)>
Semester Berjalan
<Select List (1-8)>

<Display Only (sesuai login)>

Tgl Lahir
<Display Only (sesuai login)>
<Proses (button)>

<Display Only (sesuai login)>

Daftar Mata Kuliah Semester <select list (1-6)>
Mata Kuliah

Dasar PABD
<check box>

PABD Lanjut
<check box>

Proyek PABD
<check box>

Here are step by steps for creating the form:
1.       Create 3 tables based on the stated ER Diagram (XMAHASISWA, XMATAKULIAH, XMEMILIH)
a.       XMAHASISWA {#nim,nama,tgllahir,jk}
b.       XMATAKULIAH {#kdmatkul,matkul,semester,sks}
c.       XMEMILIH {tgl_perwalian,semester_berjalan,ket,@nim,@kdmatkul}

2.       Create new database application with one blank page.

3.       Add one HTML region namely atas
a.       Add some items as follows:

b.       On P8_NIM, fill this code on Default :
c.       On P8_NAMA, fill these codes on Default:

d.       On P8_TGLLAHIR, fill these codes on default:

e.       On P8_JK, fill these codes on default:

f.        On P8_SMTBERJALAN, create a static list of values

g.       Run the Application it should be looks like this (login if needed):

4.       Edit the page, add HTML region namely bawah
a.       At User Interface areas, choose atas(10) as Parent Region with No Template

b.       Add a Select List item (P8_DAFTARMATKUL) to bawah’s region

c.       Run the Page, it should be looks like this:

5.       Create a stored procedure (function) namely cek_status. Type following codes:
create or replace function cek_status(p1 in varchar2, p2 in number) return varchar2
                hasil varchar2(128);
                select ket into hasil from xmemilih where kdmatkul=p1 and nim=p2;
                return hasil;

6.       Add one Report Region (Classic)
a.       Type following Source SQL:
SELECT a.kdmatkul,matkul,sks,
                NVL(cek_status(a.kdmatkul,:APP_USER),APEX_ITEM.CHECKBOX2(1,a.kdmatkul,'CHECKED')) as status,DECODE(cek_status(a.kdmatkul,:APP_USER),'BARU',tgl_perwalian,null) as tgl_perwalian
FROM xmatakuliah a
                LEFT OUTER JOIN xmemilih b ON a.kdmatkul=b.kdmatkul
b.       Complete the report creation process
c.       Edit that report region, click on Report Attributes tab then edit on column STATUS

Make sure the Display as on Column attributes is Standard Report Column, click on  Apply Changes

7.       Create one PL/SQL Process namely simpan_matkul
a.       Type the following source code:
  FOR i IN 1..apex_application.g_f01.count loop
                INSERT INTO xmemilih(tgl_perwalian,semester_berjalan,ket,nim,kdmatkul)
  END LOOP;         
b.       Isikan pada Success Messages ‘Berhasil’

8.       Create a Dynamic Action namely prosesmatkul
a.       When

b.       True Action = Submit Page
c.       Uncheck Fire On Load

9.       Create another Dynamic Action namely reset_daftarmatkul
a.       When

b.       True Action = Refresh
c.       Affected Elements

10.   Create another Dynamic Action namely reset_totalsks
a.       When

b.       True Action = Submit Page

11.   Edit P8_TOTALSKS Item, follow these rules
a.       Source Type = SQL Query (return single value)
b.       Source Expression :
                FROM xmatakuliah a
                                JOIN xmemilih b ON a.kdmatkul=b.kdmatkul
c.       Apply Changes

12.   Run the application
a.       Check the XMAHASISWA table data

b.       Check the XMATAKULIAH table data

c.       Run the pade, if we choose 5 on Daftar Mata Kuliah, the grid data will be changed and showing only Matkul available on 5 semester. If there’s found checked Status means the Matkul hasn’t selected yet, otherwise it already taken. Click on the Proses button

d.       The ‘Berhasil’ message should be shown and the data should be inserted and the views will be changed automatically.

e.       Check the XMEMILIH table on Object Browser

There will be found new records based on the inserted data on the form.
That’s it the step by step processes for creating nested nor on Oracle APEX. Hopefully I will be able to create the video in a close time.
Thank You - Bobsis

Read More

Friday, April 20, 2018

Custom Authentication on Oracle APEX with User Roles Selection

Leave a Comment
In this article we are going to discuss about custom authentication on Oracle APEX with user roles selection. On every application built with Oracle APEX there will be found one login page. The login process will done by comparing username & password typed by user against data stored in authentication table. There will be 2 text input required which are username and password. In this article we are going to add 1 additional text input that will check the user roles that stored in the authentication table. Checking in addition to comparing the validity of whether the username and password also compare the role or permissions owned by the user. Suppose that the existing roles in the application is ADMIN, DOSEN and MAHASISWA (the roles names is freely defined).

Here are the steps of making custom authentication by implementing the user roles checking process:

1. Create one table as the authentication table that will be used for storing the authorized user informations.
a. The table name is CustomLogin {#uname, pwd, status}

b. Insert three records as the user roles. You might add more than three records but the roles must be ADMIN or DOSEN or MAHASISWA.

2. On Application Builder, create new Desktop Application

3. Edit page 101 (Login), add one Select List item namely P101_STATUS
a. Choose Static on Select List Type

The List of items should be like this:

b. Make an arrangement (Grid Layout) so the result should be like this:

4. Back to main Application design page, Go to Shared Components
a. Create new authentication schemes

b. Choose Based on a pre-configured scheme from the gallery
c. Give it a name of cek_login and the Schema Type is Custom 
d. On Authentication Function Name fill cek_login

e. On the Source enter these codes:

FUNCTION cek_login
(p_username IN VARCHAR2, p_password IN VARCHAR2)
hasil NUMBER := 0;
SELECT 1 INTO hasil FROM customlogin
WHERE UPPER(uname) = UPPER(p_username)
AND pwd = p_password
AND status = :P101_STATUS;
END cek_login;

f. Complete the process of authentication schemes creation, make sure the active scheme is cek_login authentication (Current)

5. Run the application and login as admin (use username and password stored on authentication table)
a. Enter the username administrator, password admin, status ADMIN then click on login button

Home page should be shown, on the right top of the page will be found user information about current user

b. Logout then login with the username administrator, password admin, status DOSEN

Error message should be found because the login information is not valid. Try to empty the password and try to login, the error message should be appear.

6. Try to login as dosen roles
a. Enter the username dosennya, password dosen, status DOSEN

Home page will be shown, look at the right top of the page, it should be found the information about current user which is DOSENNYA.

7. Try to login as mahasiswa
a. Enter the username mahasiswanya, password mahasiswa, status MAHASISWA

Home page will be shown with MAHASISWANYA as the current user logged in.

Okay that’s the discussion and an example about creating custom authentication with user roles selection on Oracle APEX. You are freely to modify the source code based on your needed.

Thank You - Bobsis
Read More