Friday, July 27, 2018

Oracle APEX 18.1 Tutorial For Beginners

Leave a Comment

Current version of Oracle APEX is 18.1, here we are going to discuss about Oracle APEX 18.1 tutorial for beginners. So let's get started.

Oracle Application Express is a free development tool provided by Oracle. Why it called express because it allows developers to create web-based applications quickly only by using a web browser and stored data directly into Oracle database.
New Features Available
Here are new features available on Oracle APEX 18.1:
  • Access control
  • Email Reporting
  • Feedback
  • Activity reporting
  • Dynamic user interface selection
  • Cards report interface
  • Timeline Report
  • A Dashboard
  • REST Enabled SQL to include data from another remote database without database link
  • Oracle Cloud Application REST services
  • Able to generate Swagger documentation against REST definition on a button click
  • Wizard streamlined with fewer steps
  • Advanced color palette and graphics on Page Designer
  • Native authentication scheme and social sign-in
  • New chart (Box-Plot, Gantt, Pyramid)
  • ListView, Reflow report, Column Toggle on Mobile applications
  • Universal theme enhancement for mobile
  • a Set of 32x32 high-resolution icons
That's all for the new features available on Oracle APEX 18.1.

Thank You - bobsis

Read More

Wednesday, June 13, 2018

SQL Plus For INSERTING Data

Leave a Comment
 SQL Plus used for managing database Object on Oracle Database. For accessing SQL Plus you can use command prompt on Windows Operating System. Open CMD from command prompt then type:

SQLPLUS / AS SYSDBA;

You can login using your own defined username by following instructions on http://www.developapex.com/2016/10/managingbasic-user-privileges-on-oracle.html . Form example if you create user with name mi123 with user mi123 you can type the following syntax:

SQLPLUS mi123/mi123;

The INSERT command has a rule value (must be the value) must have the same number, data type and sequence with the attribute (column name) of the table to which it belongs. If the structure of the destination table is known for certain amount, data type and sequence then the column name / attribute can be excluded.

Format:
INSERT INTO <tabel_name> (<attribut(es)>)
VALUES (<value(s)>);

Example:
INSERT INTO tstudent (sid, name, phone) VALUES (101, ’One’, 111111);

Explanations :
INSERT INTO tstudent -> Perform the insert command into the tstudent table.

(sidm name, phone) -> Target attributes to do the addition of data, the order of attributes really need to be considered.

VALUES (101, ‘One’, 111111) -> The value 101 is inputted into the SID attribute, the value 'One' is inputted into the name attribute, the value '111111' is inputted into the phone attribute. These three values generate a new row of data in the table.

; (titik koma) -> Denotes the end of a SQL command.

Now please fill in the following data into the TSTUDENT table by using the INSERT command like the example above:

SID
NAME
PHONE
101
One
111111
102
Two
222222
103
Three
333333

Thus one uses the INSERT command on DML. Explanation of other INSERT commands and other DML commands will be discussed in the next article.

Thank You, Bobsis.
Read More

Thursday, May 10, 2018

Advanced Form Development (Nested Form with Tabs) on Oracle APEX

Leave a Comment

This article will discuss about advanced form development (nested form with tabs) on Oracle APEX. Here we’re going to create a process of submission that involves three forms where each form in on different tab. The goal is we will fill some data into form1 on tab1, after completed then we will fill some data into form2 on tab2 and then we will fill some data into form3 on tab3. Data will be correlated among all of those three  forms.
The development phases will be divided into three, that are ER Diagram creation, Mockup Diagram creation and then following with coding phase.
1.       ER Diagram Creation
This article will use following ER Diagram that has 3 Entity Sets (Mahasiswa, DetilMahasiswa and UKM).



2.       Mockup Diagram Creation
Here are the three mockup diagrams that we will create (Form1, Form2, Form3):



3.       Coding Phase
After we design an ER Diagram, now we are ready for entering the coding phase. Here are the steps:
1.       Create 3 tables based on ER Diagram
a.       Tabel TMAHASISWA {#nim,nama,jk}
b.       Tabel TDETILMAHASISWA {alamat1,alamat2,telp,email,@nim}
c.       Tabel TMHS_UKM {tahun,jabatan,@idukm,@nim}

2.       Create new Database Application with a blank page

3.       Edit Page 1, Add HTML region namely form1
a.       Add following items (P1_JK static select list)


b.       Add a Next button, action when clicked is Submit.
c.       Fill this code on PL/SQL Process name it as simpan_form1:
BEGIN
                INSERT INTO tmahasiswa(nim,nama,jk) VALUES(:P1_NIM,:P1_NAMA,:P1_JK);
                COMMIT;
                htp.init;
                owa_util.redirect_url('f?p=&APP_ID.:2:&APP_SESSION.::::P2_NIM:'||:P1_NIM);
                apex_application.stop_apex_engine;
                apex_application.g_print_success_message := 'Berhasil';
END;

d.       Run the page, it should be look like this:


4.       Add new Blank Page
a.       Name it as Form2, Breadcrumb is enabled


b.       Choose Tab option enabled


c.       Finish the creation process the edit it. Add a HTML Region.
d.       Add following items on Form 2:


e.       Set P2_NAMA item with following descriptions:


f.        Add a button name it P2_PREV. Set the properties as follows:


g.       Add a button name it P2_NEXT, action when button clicked = Submit. Set the grid layout properties as follows:


h.       Create a PL/SQL Process name it as simpan_form2, assign it to P2_PREV button. Fill this code into source:
BEGIN
                INSERT INTO tdetilmahasiswa(alamat1,alamat2,telp,email,nim) VALUES(:P2_ALAMAT1,:P2_ALAMAT2,:P2_TELP,:P2_EMAIL,:P2_NIM);
                COMMIT;
                htp.init;
                owa_util.redirect_url('f?p=&APP_ID.:3:&APP_SESSION.::::P3_NIM:'||:P2_NIM);
                apex_application.stop_apex_engine;
END;
i.         Run the Page, it should be look like this:


5.       On SQL Command, create stored function name it as cek_ukm
create or replace function cek_ukm(p1 in varchar2, p2 in number, p3 in number) return varchar2
as
                vukm VARCHAR2(8);
                hasil varchar2(128);
begin
                select idukm into vukm from tmhs_ukm where idukm=p1 and nim=p2 and tahun=p3;
                select nama_ukm into hasil from tukm where id=vukm;
                return hasil;
end;

6.       Create stored function name it as cek_jabatan
create or replace function cek_jabatan(p1 in varchar2, p2 in number, p3 in number) return varchar2
as
                hasil varchar2(128);
begin
                select jabatan into hasil from tmhs_ukm where idukm=p1 and nim=p2 and tahun=p3;
                return hasil;
end;

7.       Create new Blank Page
a.       Name it as Form3, Breadcrumb is activated


b.       Activate the Tab set


c.        Finish the page creation the edit it. Add new HTML region.
d.       Add following items:


e.       Set P3_NAMA item properties as follows:


f.         Add a report region, fill this SQL into the source:
select NVL(cek_ukm(id,nim,tahun),APEX_ITEM.SELECT_LIST_FROM_QUERY(1,NULL,'SELECT NAMA_UKM,ID FROM TUKM')) as ORG, NVL(cek_jabatan(id,nim,tahun),APEX_ITEM.TEXT(2)) as JBT
from tukm a
left outer join (select * from tmhs_ukm where nim=:P3_NIM and tahun= :P3_TAHUN) b on a.id=b.idukm

Edit that region, go to Report Attributes


Edit column ORG and JBT, make sure the Column Attributes set to Display As Standard Report Column. Apply Changes.
g.       Add new button (P3_SIMPAN), action when button clicked=submit
h.       Create new PL/SQL Process name it as simpan_form3, assign to P3_SIMPAN button. Fill this code into the Source area:
begin
 for i in 1..apex_application.g_f01.count loop
  IF apex_application.g_f01(i) <> '%null%' THEN
  insert into TMHS_UKM (tahun,jabatan,idukm,nim)
                values (:P3_TAHUN,apex_application.g_f02(i),apex_application.g_f01(i),:P3_NIM);
  END IF;
 end loop;
 COMMIT;
end;
i.         Run the Page, it should be look like this:


The creation process has completed, now we’re going to try it.
1.       Check the TUKM table data


2.       Run the application, fill data on Form1 then click Next button


3.       Form2 will automatically shows along with Nama (Boby Siswanto). Fill data on Form2 then click on Next button


a.       Form3 will automatically shows, fill the text fields. Choose select list options on bellow area (Organisasi) and fill the Jabatan’s values. Click Simpan button.


b.       When the process is successful, success message will be shown:


c.       Check the TMAHASISWA table, new record with Boby Siswanto values will be inserted.
d.       Check the TMHS_UKM table, it should be contains some values:


That’s all for advanced form development (nested form with tabs) on Oracle APEX tutorial. You should be able to create one process divided into several forms.

Thank You - Bobsis

Read More

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


atas
 


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

NAMA
<Display Only (sesuai login)>

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

JK
<Display Only (sesuai login)>



Daftar Mata Kuliah Semester <select list (1-6)>
bawah
 
NO
Kode
Mata Kuliah
Status

1
MI3001
Dasar PABD
<check box>

2
MI3002
PABD Lanjut
<check box>

3
MI3003
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 :
&APP_USER.
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
as
                hasil varchar2(128);
begin
                select ket into hasil from xmemilih where kdmatkul=p1 and nim=p2;
                return hasil;
end;

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
WHERE semester = :P8_DAFTARMATKUL;
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:
BEGIN
  FOR i IN 1..apex_application.g_f01.count loop
                INSERT INTO xmemilih(tgl_perwalian,semester_berjalan,ket,nim,kdmatkul)
                VALUES(sysdate,:P8_SMTBERJALAN,'BARU',:APP_USER,apex_application.g_f01(i));
  END LOOP;         
END;
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 :
SELECT SUM(sks)
                FROM xmatakuliah a
                                JOIN xmemilih b ON a.kdmatkul=b.kdmatkul
WHERE NIM= :P8_NIM  AND SEMESTER_BERJALAN= :P8_SMTBERJALAN;
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