Saturday, May 18, 2019

Custom Authentication with MD5 Encryption on Oracle APEX 19

Leave a Comment

This article will guide you to create custom authentication with MD5 Encryption on Oracle APEX 19.


On every computer application there will be a login process to make sure only authenticated user is allowed. As a default, every application built on Oracle Application Express has predefined authentication scheme using Application Express Accounts. This predefined authentication scheme will allow user to make a login into built application using same credential as their workspace login.

There is another way to make another authentication scheme, which is will be using different credential not the workspace credential. We can create Custom authentication scheme that will use custom table to store the login credential. For example, we can create TLOGIN table with username and password attributes to hold the authenticated user credentials. This TLOGIN table will be read by the application login page when the application is running for the first time.

By default, all information stored inside the table the hold password information is a string. For example, the password for admin user is “thePassword”, this word will appear every time we open the data of table. Unfortunately, if someone else able to open the table, they will know the password and they will be able to enter the application.

There is a way to secure the credential even if someone else able to view the data inside the table. We can encrypt the string of text that hold the password credential by using MD5 hash encryption. Fortunately, Oracle has built in function to implement the MD5 hash function. This MD5 function will encrypt any words into 32 randomized characters. For example, “thePassword” will be encrypted into “OED4768C11461AB762DA1F5719AD560B”.

This tutorial will guide you to implement MD5 hash function into custom authentication scheme. Here are the steps:

1.       Open Oracle APEX website using your web browser. Login into your Oracle Application Express Workspace


2.       Create a Table namely TLOGIN with some attributes (e.g. ID, USERNAME, PASSWORD, STATUS)


3.       Go to Application Builder and click on Create to create new application


4.       Next choose New Application


5.       Set the project name with anything you wish (e.g. ProjectWithMD5), leave other options as default and the click on Create Application button


6.       The application is created, you will found 3 pages (Global Page, Home, Login Page)


7.       Click on Shared Component


8.       On Security area, click on Authentication Schemes


9.       Choose “Based on a pre-configured scheme from gallery” the click on Next button


10.   Fill some information on required text field.
Name: myOten, Scheme Type: Custom, Authentication Function Name: cek_otentikasi


11.   Still on Create Authentication Scheme page, scroll down and fill in the PL/SQL code with a function and then click on Create Authentication Scheme Button


12.   Create a function “GetMD5(pstring IN VARCHAR2) RETURN VARCHAR2” using SQL Workshop


13.   Execute the function to get encrypted password


14.   Open TLOGIN table, insert one record contains the encrypted password


15.   Run the application, try to login using any username and password. Invalid Login Credentials will be appeared.


16.   Try to make a login using admin as the username and thePassword as the password, it should be worked.


17.   The homepage will be appeared as the login process has been successful


Thank You - Bobsis

Read More

Wednesday, March 27, 2019

How To Create First Web Application in Oracle APEX

Leave a Comment

This article will describe about How To Create First Web Application in Oracle APEX (Application Express). After successfully install Oracle APEX, the next process which is the main process is creating the web application. This article will give you an illustration about how to create simple web application. The processes will give you global knowledge about how to create a web application in Oracle APEX for all of your future applications. When you understand the processes in this article, you will be able to create other application easily.

Here are the processes of creating web application on Oracle APEX (Oracle Application Express 19.1.0.00.13):
1.       Login into your Oracle Application Express Workspace.



2.       After successfully login into the workspace, click on App Builder to start the creation.


3.       The next process is choosing about how we are going to create the application. We can Import existing application by choosing Import icon instead of creating from scratch. Here in this article we are going to create the application from scratch, so we choose Create icon.


4.       Next, choose New Application to continue the process


5.       In the next page we need to fill and choose some options. For an example you might to fill the information as follows:
a.       Name                    : myProject
b.       Appearance       : Vita – Red, Side Menu
c.       Features              : About Page, Feedback
Scroll down the page and click on Create Application button on the bottom.


6.       The creation progress bar will appear, wait until the progress is completed.



7.       That’s it, we already complete the process, some pages will be generate automatically. It’s time to test our new application by clicking the Run Application icon.


8.       The first page will be shown is your application login page. Fill your Oracle APEX credentials and click the Sign In button.


9.       Your new application home page will appear, you may click anything visible there. If you want to sign out from your application just click on login username located on the right top of the page.



That’s all for the process of HowTo Create First Web Application in Oracle APEX. Hope it’s clear enough for you to create your first web application in Oracle APEX.

Thank You - Bobsis

Read More

Monday, December 24, 2018

How To Create Workspace User in Oracle APEX

Leave a Comment

This article will describe about How To Create Workspace User in Oracle APEX. You will get one workspace when you are successfully register on Oracle APEX website (https://apex.oracle.com). On Oracle Application Express, all of your applications and data will be combined on 1 application workspace. This workspace will have a correlation with your database schema. One workspace will be mapped into one schema, but one schema might be used by several workspace.

Workspace can be analogically as a garage. On a garage there will be stored some tools, a vehicle or some furniture (chair, desk, etc.) or anything you want to place in it. Garage will be secured with a special door with a key. To enter the garage you must open the door with a correct key, without a correct key you will not be able to enter the garage. You may allow another person to enter the garage by giving the duplicate of the key.


Okay let we start to create a workspace user on Oracle APEX, here are the steps:
1.       Sign In into your existing workspace with your username and password


2.       Click on Manage Users and Groups option, it located on the right top of your Oracle APEX dashboard. Click on the head with dropdown menus (3rd from the right) and you will get the menu.



3.       Now you will be at “Manage Users and Groups” page. You will find existing users there. Click on Create User button (blue button) on the top right to start the process.


4.       Next on User Identification section you need to fill some information. Fill the desired username and your valid email address, these fields are mandatory. Next Click on Account Privileges tab.


5.       On Account Privileges section, you must choose some options. Choose the default Database Schema, decide whether the user is workspace administrator or not. The one thing for sure is you must choose the user as a developer to give the user access to be able to create some applications. Next click on the Password tab.


6.       On the Password tab, set your desired password. It must be unique password, consist of 8 character with combination of uppercase, lowercase, number and special character (e.g. *). Click on Create User blue button to complete the process.



7.       Now you will find new user you just created. To create another user, you can repeat the process by clicking the Create User Button.



8.       It’s time to check your new user. Sign out from existing user by clicking on Sign Out button under currently connected user (top right corner). Sign In with your new user on the Oracle Application Express Sign In form.


9.       If you are entering the correct username and password combinations you should be able to enter the Oracle Application Express dashboard as a developer.


That’s all for the process of HowTo Create Workspace User in Oracle APEX. Hope it’s clear enough for you to manage your workspace users.
Thank You - Bobsis

Read More

Monday, December 3, 2018

First Step to Learn Oracle APEX (Create a Workspace)

Leave a Comment

This article will describe about first step to learn Oracle APEX (Create a Workspace). Before you can develop any application with Oracle Application Express (APEX), you must have a workspace to deploy your application. As we know that Oracle APEX is integrated with Oracle Database, every application develop with Oracle APEX, automatically will be stored in Oracle Database along with the application data.



Oracle APEX is a free web-based development environment. As long we have authentication credential to the Oracle APEX server, we can develop our application anywhere in the world using a web browser. To obtain the workspace, we need to request the free request by accessing apex.oracle.com website.

Now let’s start to request our free workspace. Here are the steps:

1.       Open the website - https://apex.oracle.com/ the click on Get Started for Free blue button


2.       Next click on the Request a Free Workspace button



3.    We will find the Request a Workspace form. In the Identification section, fill your identity and your desire Workspace name. Click on Next button when you are done.


4.       In the next form we will be asked to fill some survey questions. Answer the survey then click on Next button.


5.       Next you must write something about why you are requesting the workspace. You may answer it by type “I need it for learning purposes” or something like that. Click on Next button when you’re done.


6.       After your information recorded, next step is agreement confirmation. You have to click on I accept the terms (please scroll the terms and read it) then click on Next Button.


7.       The final step of workspace request is reading the Confirmation page. Make sure the information shown is the same as your desire. Click in Submit Request Button to finalize the request.


8.       Notification will be shown, next you have to check you mailbox to activate the workspace.


9.       Open an email sent from oracle-application-express_ww@oracle.com, it will stated that you workspace request approved. You have to click on the given link, it will be redirected to oracle apex website.


10.   Congratulation! Your free Oracle APEX Workspace has successfully created. Click on Continue to Sign In Screen button to enter your workspace.



11.   Because it is your first time to access the workspace, you need to change your initial password. Please enter your new password then click on Apply changes button. In the future this process will not be needed, you can change your password only when you need to change it.


12.   Here is your Oracle APEX Workspace desktop, now you can start to create your Oracle APEX application.


Okay that’s all the processes to create free Oracle APEX Workspace. This is the first step to learn Oracle APEX.

Thank You - Bobsis


Read More

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