Workspace Management on Oracle APEX

Before we create some applications on Oracle APEX, we must create a workspace by doing Workspace Management on Oracle APEX. Workspace management on Oracle APEX can be done by using INTERNAL workspace with ADMIN user. In brief workspace is an area that needs to be prepared before building applications on Oracle APEX. Inside workspace we can create any of database objects (table, function, procedure, etc.) and application pages to manage the database objects. Without a workspace, Oracle APEX cannot create any of applications.

Before continuing Workspace management, first create a database user named USERPOS with password pos123.

SLQPLUS system / <your_password>

After successfully login, type the following command:

CREATE USER userpos IDENTIFIED BY pos123;

Give the DBA grant to the USERPOS user. DBA is a privilege for database administrators. We use this privilege to focus more on Oracle APEX.

GRANT dba TO userpos;

After the user is successfully created, connect as USERPOS and execute the post.sql file.

Here is the post.sql code:

--Sequence
CREATE SEQUENCE sqbrg;
--TBarang
CREATE TABLE tbarang(
                kdbarang VARCHAR2(24) NOT NULL,
                namab VARCHAR2(128),
                jmlstok NUMBER,
                harga NUMBER,
                hrgmemb NUMBER,
                CONSTRAINT pk_tbarang PRIMARY KEY (kdbarang)
);
--triggers
create trigger trg_kd_tbarang
before insert
on tbarang
for each row
begin
                select 'BRG'||sqbrg.nextval
                into :new.kdbarang
                from dual;
end trg_kd_tbarang;
/
--form detil penjualan
DECLARE
 vtid VARCHAR2(24);
 vkdbrg VARCHAR2(24);
 vjml NUMBER;
BEGIN
 vtid := :P4_ID_1_2;
 htp.p('<form>');
 htp.p('<table>');
 htp.p('<tr><td>Trans. ID </td><td>'||vtid||'</td><td></td><td>TOTAL</td><td>Bayar</td><td>Kembali</td></tr>');
 htp.p('<tr><td>Item Barang </td><td>');
                htp.p('<select name="txtItem" id="txtItem">');
                                htp.p('<option value="1">Satu</option>');
                                htp.p('<option value="2">Dua</option>');
                htp.p('</select>');
 htp.p('</td><td></td><td>');
                htp.p('<label name="lblBayar" id="lblBayar" value="xxx">');
 htp.p('</td><td>');
                htp.p('<input type="TEXT" name="txtBayar" id="txtBayar">');
 htp.p('</td><td>');
                htp.p('<script>document.write("otomatis");</script>');
 htp.p('</td></tr>');
 htp.p('');
 htp.p('');
 htp.p(vtid);
 htp.p('</table>');
 htp.p('</form>');
END;
--proc detil penjualan
CREATE OR REPLACE PROCEDURE pos_proc_detjual (
ptrans IN VARCHAR2, pbarang IN VARCHAR2, pjml IN NUMBER)
AS
BEGIN
                INSERT INTO tdetiltrans (idtrans, kdbarang, jumlah) VALUES (ptrans, pbarang, pjml);
                COMMIT;
END pos_proc_detjual;
/

Here's an illustration of how to execute a post.sql file (copy the post.sql file attached to drive c: \):

Now the user preparation steps are completed, now let's create a workspace on Oracle APEX. Here are the steps to create a workspace on Oracle APEX:

1. Log into APEX (http: // http: // localhost: 8080 / apex) using ADMIN user

2. On the welcome page click the Manage Workspaces tab so that the following page appears:

3. In the Workspace Actions area, click the Create Workspace link and complete the field in the next stage

4. Fill in the name of the workspace and the workspace ID. Suppose the workspace name is POS_WS and ID 100002 (ID must be greater than 100000).

5. Specify the database schema to be used. If we have previously created a database schema then select Yes on select Re-use existing schema and select an existing schema name (e.g. USERPOS). If No option selected, fill in the desired scheme name along with the password and select the quota of the scheme (min 10MB).

6. Next specify the username and password that will become the administrator of the scheme. For example username is ADMIN_POS and password is admin1 (no need special treatment, but if will be used need to change password with special treatment). Fill in your email address in the email text field.

7. A confirmation page will be displayed. Make sure all the information is displayed in accordance with the one you planned and click the Create Workspace button.

8. The workspace creation process has been completed, here is the final look of the workspace creation process named POS_WS and the ADMIN_POS user as the administrator of the workspace.

Click Done button and you're Done. Now your can try to login into Oracle APEX by using your new workspace with username on step number 6.

Thank You - Bobsis