• Home
• How To
• AskOTG
• Tutorials
• Certifications
• Contact Us

PHP with Oracle AQ

• Overview
• Create Table
• Create AQ
• Enqueue Procedure
• Dequeue Procedure
• PHP AQ Producer
• PHP AQ Consumer

Setup the Employees Table

Before you could start creating the PHP pages the first step is to create the Oracle database table, Advanced Queues and PL/SQL procedures. For the use case explained in this tutorial you only need one table, which is the Employees table. You can either create this table manually or you can use the sample HR schema that can be installed automatically with the seed data when you create a new database.

Option 1: Using Sample HR Schema

You have the option to install sample schemas when creating a new database with Database Configuration Assistant (DBCA). If you havent selected that option during database creation, you may also install the sample schemas manually by following the steps described here

Note: When using the Sample HR schema, you may have to unlock the hr user account and reset its password using the statement below if you haven't done it previously.

ALTER USER hr ACCOUNT UNLOCK IDENTIFIED BY Password;

After you have done this login to the HR schema and verify the employee table exists with some seed data in it.

Option 2: Manual Setup

Create User and Grant privileges

CREATE USER hr IDENTIFIED BY Password;
GRANT CONNECT, RESOURCE TO hr;

Create Table

Login to your Oracle database and run the following SQL statements

CREATE TABLE EMPLOYEES 
 (  EMPLOYEE_ID NUMBER(6,0) NOT NULL ENABLE, 
	FIRST_NAME VARCHAR2(20 BYTE) NOT NULL ENABLE, 
	LAST_NAME VARCHAR2(25 BYTE) NOT NULL ENABLE, 
	JOB_ID VARCHAR2(10 BYTE) NOT NULL ENABLE, 
	SALARY NUMBER(8,2) NOT NULL ENABLE,
  CONSTRAINT "EMPLOYEE_PK" PRIMARY KEY ("EMPLOYEE_ID")
 );

Insert seed data

REM INSERTING into EMPLOYEES
SET DEFINE OFF;
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,JOB_ID,SALARY) values (100,'Steven','King','AD_PRES',24000);
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,JOB_ID,SALARY) values (101,'Neena','Kochhar','AD_VP',17000);
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,JOB_ID,SALARY) values (102,'Lex','De Haan','AD_VP',17000);
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,JOB_ID,SALARY) values (103,'Alexander','Hunold','IT_PROG',9000);
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,JOB_ID,SALARY) values (104,'Bruce','Ernst','IT_PROG',7000);
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,JOB_ID,SALARY) values (105,'David','Austin','IT_PROG',4800);
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,JOB_ID,SALARY) values (106,'Valli','Pataballa','IT_PROG',4800);
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,JOB_ID,SALARY) values (107,'Diana','Lorentz','IT_PROG',4200);
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,JOB_ID,SALARY) values (108,'Nancy','Greenberg','FI_MGR',12000);
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,JOB_ID,SALARY) values (109,'Daniel','Faviet','FI_ACCOUNT',9000);
COMMIT;

Post a comment

Comments