Friday, March 30, 2018

Oracle Application Express

Leave a Comment

This article will discuss about Oracle Application Express (Getting Started). Oracle Application Express or abbreviated with Oracle APEX is an application environment provided by Oracle Corporation for free.  Oracle APEX can be downloaded  through its website (apex.oracle.com). Oracle APEX is a web-based application whose development is done through a web browser (Mozilla Firefox, Microsoft Internet Explorer, etc.).  Oracle APEX is a separate application from the database, but Oracle APEX has been integrated in Oracle DB XE 11g, when we finished installing Oracle DB XE 11g then Oracle APEX will be installed as well. The Oracle APEX version integrated on Oracle DB XE 11g is Oracle APEX 4.0. The latest version now is Oracle APEX 5.1.

Oracle APEX able to create web-based applications components:  workspace management, user management, database object management (tables, view, procedure, function, etc.), application creation (form, report, chart, etc.) to security settings of applications made it.

The First Login




Before proceeding to interact with Oracle APEX, make sure you can login into Oracle APEX. Here are the steps to login to Oracle APEX:

1. From web browser (Mozilla Firefox, Google Chrome, etc), open  http://localhost: 8080/apex/apex_admin. An Oracle Application Express login form will appear.


2. Fill username field with "admin" and password with password field that you set while installation process, then click Login button.


3. You will be prompted to change the ADMIN password.

The new password must be met these terms:
a. Minimum 8 characters
b. There is at least a capital letter
c. There is at least 1 number
d. There's at least 1 special character (! "# $% () '* +, - / :; <=>? _)
e. Cannot contain element username

4. After successfully changing the password, re-login by using a new password


5. Welcome page will be displayed when the login is successful 


That’s all for the introduction of Oracle APEX. First things you have to do is changing the admin password for INTERNAL workspace (step number 3). Next thing you have to do is creating new workspace.

Thank You - Bobsis

Read More

Thursday, March 29, 2018

How To Set Default Home Page Of Different User Privileges On Oracle APEX 5

2 comments

In this article we are going to discuss about how to set default home page of different user privileges on Oracle APEX 5. Oracle APEX (Application Express) will create one login page as a default page on every application that we made. As a default it will use the Oracle APEX authentication schemes to check the username and password provided by user on login page. To manage that user validation credentials we have to go on workspace management and go to user management sections.

We can use another way to manage the login validation by using our own table. To do this process we must choose the Custom Authentication Schemes on application properties from the application that we create. With this custom authentication we may store any number of attributes to check the user login credentials or to change default behavior of Oracle APEX login flows.


This article will give you step by step explanations of how to create login page on Oracle APEX application that is not the default behavior of Oracle APEX login flows.

1.     Create table namely TUSER for holding the user data
CREATE TABLE tuser(
                Id NUMBER PRIMARY KEY,
                Uname VARCHAR2(24) UNIQUE,
                Pwd VARCHAR2(128),
                Status VARCHAR2(12)
);



2.       Create Desktop Application with 3 new Pages . These three pages will be used as default home page of three different users.



3.       Customize the AdminPage on page 2.



4.       Customize the ManagerPage on page 3.



5.       Customize the StaffPage on page 4.



6.       Create Application Item, namely GLOBAL_STATUS_ITEM with Unrestricted Session State Protection setting
a.       From your application on application builder go to Shared Components/Application Items
b.      Set the values



7.       Choose the Custom Authentication Schemes on current application
a.       Go to Shared Components/Authentication Schemes then Create
b.      Create New Custom Authentication Scheme



c.       Fill the PL/SQL Code area
FUNCTION my_user_auth
                (p_username IN VARCHAR2, p_password IN VARCHAR2) RETURN BOOLEAN
                AS
                  Result NUMBER :=0;
                  Sts VARCHAR2(12);
                BEGIN
                  SELECT 1, status INTO Result, Sts
                  FROM tuser
                  WHERE UPPER(uname)=UPPER(p_username)
                  AND pwd = p_password;

                  IF Result =1 THEN
                    APEX_UTIL.SET_SESSION_STATE('GLOBAL_STATUS_ITEM', Sts);
                    RETURN TRUE;
                  ELSE
                    RETURN FALSE;
                  END IF;
                EXCEPTION
                  WHEN NO_DATA_FOUND THEN
                                RETURN FALSE;
END my_user_auth;
d.      Make sure your custom authentication scheme is active, indicated with word of “Current”



8.       Edit Page 1 (Home)
a.       Create Branches
b.      Set Brach Point and Branch Type as follows



c.       Fill Branch Action with PL/SQL Script
DECLARE
  Psts VARCHAR2(64);
  Result VARCHAR2(8);
BEGIN
  Psts := APEX_UTIL.GET_SESSION_STATE('GLOBAL_STATUS_ITEM');
  IF Psts='ADMIN' THEN Result:= '2';
  ELSIF Psts='MANAGER' THEN Result:= '3';
  ELSE Result:= '4';
  END IF;
  RETURN Result;
END;
9.       Now it’s time to check the application and its behavior. Run the application, try to login with three username stated on step 1.
a.       Login as USER1, we will see the home page for Admin.



b.      Login as USER2, we will see the home page for Manager.



c.       Login as USER3, we will see the home page for Staff.



The conclusion is we can create one application on Oracle APEX with one login page to redirect different user on their own home page by using Custom Authentication Schemes.

Full article can be downloaded here

Thank You - Bobsis

Read More

Wednesday, March 28, 2018

Management of Objects in Oracle Database

Leave a Comment

The management of objects in Oracle databases is done by using the DDL (Data Definition Language) query command. The objects in Oracle database are user, table, view, package, procedure and function. SQL (Structured Query Language) is a standard DBMS (Database Management System) command in a database to interact with objects inside it. The management of the database object is performed by using one of the following SQL commands:
1. CREATE; used to create a new object.
2. ALTER; used to make changes or modifications to objects that have been created previously.
3. DROP; used to delete an existing object.

DDL on Table

The table is the object of a database consisting of rows and columns. The rows in the table are known as record / row while the columns in the table are known as field / column / attribute. Here is an example of using DDL to create a table with the name of a student that has 3 attributes (nim, name, notelp) in Oracle Database:
1. Login by using user belajar


2. Create a table with the name TMahasiswa that has 3 attributes (nim, name, notelp). The TMahasiswa table has 2 constraints ie NOT NULL and PRIMARY KEY. The NOT NULL constraint is given in the nim attribute, meaning the nim attribute must be loaded a value when the new row data is added. Constraint PRIMARY KEY is given at attribute nim, meaning attribute nim is a distinguishing attribute in table TMahasiswa where there should be no contents of the same data or repeated inputted on attribute nim in table TMahasiswa. Here is the DDL command to create a TMahasiswa table:

CREATE TABLE TMahasiswa (
nim NUMBER NOT NULL,
name of VARCHAR (64),
notelp VARCHAR (16),
CONSTRAINT pk_mhs PRIMARY KEY (nim)
);

if successful will appear Table table message.


3. Check the existence of the TMahasiswa table by typing the command
DESCRIBE TMahasiswa
if it appears table structure means TMahasiswa table has been successfully created. Describe is a command to display the structure of a table that is in the Oracle database (DESCRIBE command can be short with DESC)




4. If the contents of the NIM attribute is a combination of letters and numbers it is necessary to make changes to the NI deta type. Here's how to make changes to the NIM attribute to VARCHAR:
ALTER TABLE TMMODIFY student nim VARCHAR2 (16);
Re-show the table structure of the student:




DDL on Function

Function is an object of a database that serves to perform processing a data input to then generate a value and return it (return). A function can have one or more input parameters, but can also without having an input parameter. Many values ​​can be returned (return) by a function only one.

Here is the use of DDL to create a stored function in the Oracle database:
1. Login by using user learn (if not login)
2. Create a function with the name FHitung with 2 input parameters (p1, p2)
CREATE FUNCTION F Count (p1 IN NUMBER, p2 IN NUMBER) RETURN NUMBER
US
result NUMBER;
BEGIN
result: = p1 + p2;
RETURN results;
END F Count;
/
if successful will appear Message Function created.



3. Check the existence of a stored function by typing the following command:
SELECT object_name FROM user_objects WHERE object_type = 'FUNCTION';
Object_name with the name FHITUNG will be displayed, note the writing of capital hutuf FUNCTION on WHERE clause.



DDL on Procedure

Procedure is an object of a database that serves to perform processing a data input to then generate a value. Like function, procedure can have one or more input parameters, but can also without having input parameters. The difference between a function and a procedure is that the procedure does not return a value (return).
Here is the use of DDL to create a stored procedure in Oracle database:
1. Login by using user learn (if not login)
2. Create procedure with name of PPangkat with 1 input parameter (p1)

CREATE PROCEDURE PPangkat (p1 IN NUMBER)
US
result NUMBER: = 1;
BEGIN
result: = p1 * p1;
DBMS_OUTPUT.PUT_LINE (result);
END PPangkat;
/
if successful will appear Procedure created message.



3. Check the existence of a stored procedure by typing the following command:
SELECT object_name FROM user_objects WHERE object_type = 'PROCEDURE';
Object_name with the name of the DEVICE will be displayed, note the writing of capital hutuf PROCEDURE in the WHERE clause.


Thank You - Bobsis

Read More

Tuesday, March 27, 2018

User Management on Oracle Database

Leave a Comment

User management is a mechanism for managing user on database. In Oracle Database, user management is used to perform management of user objects in order to perform transactions on the database objects that exist within the Oracle Database. The management can be in the form of additional users, deletion of users or changes in user attributes. The user attributes in Oracle Database include user name, user password and user permissions. By default on Oracle Database XE 11g has a user name attribute that is not case sensitive while the password attribute is case sensitive. Case sensitive means the uppercase (capital letter) is distinguished by a lowercase letter on the same word, as USER1 is different from user1.

The user permissions on Oracle Database are known as user privileges. User privileges can be provided individually or by grouping first. The grouping of user privileges in Oracle Database is known as user roles. The user roles may consist of one or more privilege users, while 1 user privilege can be assigned to more than one user role. A user can have one or more user roles whose grants can be combined with user privileges. Here are some examples of user privileges available in Oracle Database:
DBA
CONNECT
RESOURCE
SELECT ANY TABLE
INSERT ANY TABLE
UPDATE ANY TABLE
DELETE ANY TABLE

Privileges are given to the user by using the GRANT command. The granting of privileges or roles can only be granted by users who have CREATE USER privileges. Users who already have CREATE USER privileges are SYS or SYSTEM users. The GRANT command is followed by the name of the privilege to be given and the user name to be assigned a privilege. GRANT format is GRANT privilege_name TO user _name;. Here's an example of a command to grant privileges connect to a user with my username

GRANT connect TO userku;

User privileges may be revoked or withdrawn by using the REVOKE command. The REVOKE writing format is REVOKE privilege_name FROM user_name ;. Here is an example of revocation of the connect privilege of the user with my username

REVOKE connect FROM userku;

A new user must be able to login as in the existing Oracle Database, the user can then perform transactions on the objects that exist in the Oracle database. At a minimum a user has 2 user privileges to perform operations on an Oracle database. The minimum user privileges assigned to a user are the privilege connect and privilege resource. Privilege connect is given so that user can login to Oracle Database while privilege resource is given so that user can do management of objects owned by that user.

Here are the steps that need to be done to perform user management in creating a user with a learning name that has connect and resource privileges:

1. Open a command prompt (C: \>), can be done by typing cmd in the Start - Run menu.


2. Login by using SYSTEM user by typing sqlplus command SYSTEM / <your password>



3. Create a user with learning name and password learning001 by typing the command
CREATE USER study IDENTIFIED BY belajar001;
if successful will appear User created message.




4. Give the privileges required for the user to learn, the minimum a user has 2 privileges CONNECT and RESOURCE. Privilege CONNECT is granted for the user to log into the Oracle Database. Privilege RESOURCE is provided for user to manage (DDL-DML) against tables owned by the user. Here's the command to grant the user access rights to learn:
GRANT CONNECT, RESOURCE TO exercise;
if successful will appear Grant message succeeded.



5. New user test created (user learn) by logging in using learning username and learning password001 on sql prompt (SQL):
CONN learning / learning001
if successful will appear Connected message.



6. Do a simple query to make sure that the learning user is ready to use, give the command:
SELECT COUNT (*) FROM tab;
if the information appears as in figure 1.27, it means user learning is ready to use.



Thank You - Bobsis


Read More

Saturday, March 24, 2018

Oracle Database 11gXE System Management On Microsoft Windows

Leave a Comment

On Microsoft Windows operating system, Oracle Database XE 11g can be set to run (operate) automatically or manually when the computer is turned on. If the given setting is automated then Oracle will be started automatically, but the computer will be booting longer than the manual setting.

Services Manager

The service manager is a feature of the Microsoft Windows operating system to perform the start-stop process of an existing app. In addition to setting the start-stop process, the services manager can also make settings in the application whether it runs automatically or not when the computer was first run. Setting Oracle Database XE can be done by utilizing services manager. Here are the steps to open the services manager page:
1. Click start (windows logo in the lower left corner), type services.msc in the search field of the program and press keypad Enter




2. On the Services manager page look for OracleServiceXE and OracleXETNSListener service names, make sure both are Started.




3. In Figure 1.11, OracleServiceXE service and OracleXETNSListener service are in Started status with Automatic startup type, which means that the service is automatically executed when the computer first turns on and is now running. If we want the startup type is not automatic, double click on the service name (eg service OracleServiceXE), change startup type to Manual then click OK.



The result is the startup type of service OracleServiceXE changed to Manual.



4. To start and stop the status of a service do right click on the service name (eg OracleServiceXE) and then click the Start option to run the service or click Stop to stop the service.



5. Make sure the OracleXETNSListener service is in Started state with startup type in both Automatic and Manual conditions.


  
Command Prompt Console

On Microsoft Windows operating system, apart from the services manager, the start and stop process of Oracle Database XE can also be done from the command prompt console (cmd) environment. Here are the steps to perform Oracle start-stop service settings from the command prompt (cmd):
1. Run a command prompt (cmd) by typing cmd after starting (logo window) then Run. Run the service listener by typing the net start OraclexETNSListener command terminated by pressing Enter. If the confirmation appears The OracleXETNSListener service was started successfully means that the service listener is in Started condition.




2. To run OracleServiceXE service type the net start command OracleServiceXE terminated by pressing Enter. If the confirmation appears The OracleServiceXE service was started successfully means the database is in Started state and ready to use.



3. Make a check by logging in using the System user then do a simple query, eg select count (*) from tab; such as illustration 1.18



In Figure 1.18 two commands are executed ie command to login and command to perform simple query. The result value 174 means there are 174 tables owned by the user System, if this figure appears meaning Oracle Database already in running condition.

Troubleshooting

The installation process does not always run smoothly, sometimes there is a problem with Oracle Database that we install after the installation process is complete. One of the problems that may be found is the service condition of OracleServiceXE and OracleXETNSListener in the services manager is already in Started state but the login is not successful. Here is one way of handling the problems that occur after the installation process is complete.
Eg found error message as in figure below



What can be done is to do a manual startup via SQL prompt by using a user who has SYSDBA privileges. The first step is to login to the database as SYSDBA by typing the sqlplus / as sysdba command line at the command prompt. After the SQL prompt appears type the shutdown immediate command, wait until the confirmation message appears ORACLE instance shut down.




Type the startup command to run the Oracle database again, wait until the Database confirmation message is opened. If a confirmation message appears Database opened means that the database is ready to be reused, but if it does not appear the database is not ready to be used and needs to be repaired in another way.



Thank You, Bobsis

Read More

Friday, March 23, 2018

Oracle Database 11g XE Installation Steps

Leave a Comment

This article will discuss the Oracle Database 11g XE installation process on Windows 32bit operating system. Oracle Database 11g XE can be installed on Windows or linux operating systems. The installer can be downloaded from the Oracle website (www.oracle.com). For Windows operating system available 32bit and 64bit installer while for Linux operating system available 64bit installer. Before performing the installation of Oracle Database XE 11g R2 make sure the computer has a minimum specification which are 1GHz processor clock speed, 512MB RAM, and 2GB free space on the hard disk.
Here are the steps to install Oracle Database XE 11g:
1.       Download the Oracle DB 11g Express Edition installer from http://www.oracle.com/technetwork/products/express-edition/downloads/index.html. Select the installer for Windows x32.
2.       Extract the zip compression file in windows explorer then run the installer file (setup.exe)

The Preparing to Install page will appear, wait until the loading process completed

3.       After the InstallShield Wizard form appears, click the Next button at the bottom of the form

4.       Next, look at the License Agreement page, read carefully the contents of the rules set by Oracle. If you agree select the radio button I accept the terms in the license agreement then click Next.

Specify the installation location of Oracle Database XE (e.g. c: \ oraclexe \) then click Next button. If you want to change the installation location press the Browse button.


On the Specify Database Passwords page, set the password for SYS and SYSTEM users. SYS and SYSTEM users on Oracle databases are the highest-level privileges to perform management operations on Oracle Database. When finished enter the password click the Next button.


On the Summary page will be shown the location of Oracle Home and Oracle Base from Oracle Database 11g XE which has been successfully installed along with the list of ports used. Oracle Home is the main location (root) of Oracle products being installed, while Oracle Base is a location consisting of one or more Oracle Home. By default the Database Listener port is 1521 and the HTTP Listener port is 8080, both ports are important to keep in mind. The Database Listener port serves as an intermediary of database interaction with the client when the database on the server computer is accessed from the client computer (remote access).The HTTP Listener port is the port that is invoked when we interact with the Oracle DB XE 11g via the web browser. Click the Install button to begin the installation process.


After the installation completed, click the Finish button at the bottom of the form.


The installation process is now completed, check whether Oracle Database XE 11g is working or not. To check that the Oracle DB 11g XE is working and operate, login to SQLPlus by entering the SYSTEM as username and your defined password (the password that inputted on installation process) at the command prompt (cmd). Type the command SQLPLUS system / admin1 (system is the username that will login while admin1 is the password that has been set at the time of installation). The following illustrations verify whether the  Oracle DB XE 11g is successfully installed, if figure 1.9 found that means your Oracle Database XE has been operating properly. Next we will call SQL> symbols as sql prompt.

Thank You, Bobsis

Read More