The following walkthrough describes how to create a sample project, add folders and files into it, deploy the project, compile it and export it to a single script. A sample project will contain scripts, that create database objects, that were have created manually in previous walkthroughs.
To create a new project select New, Project from the File menu, select Devart Database Projects in the Project types box, select Oracle Project in the Templates box and click OK. A project will appear in Solution Explorer.
Folders help to organize related scripts and queries into groups, so, first of all, let’s create a folder structure for the new project. There will be three folders in the root folder: Security, Database Objects, Data. Folders for every database object type that is created in the project will be in the Database Objects folder.
To create such folder structure perform the following steps.
Right-click the project node in Solution Explorer and choose Add Folder from the shortcut menu
-or-
Select the project node in Solution Explorer and choose Add New Folder from the Project menu.
Enter the new folder name (‘Security’).
Repeat steps 1-3 for creating Database Objects and Data folders.
Right-click the Database Objects folder node in Solution Explorer.
Select Add Folder from the shortcut menu.
Enter the new folder name (‘Tables’).
Repeat steps 5-7 for creating Views, Procedures, Sequences and Triggers folders.
Now you have a folder structure for our project created. Note that project folders are not real Windows folders, information about these folders is stored in a project file. When you create or delete folders, corresponding Windows folders are not created or deleted.
Now let’s create required SQL script files and add them to the project. Let’s learn how to add existing files to the project first. Existing files can be added to a project in two ways. Perform following actions to create files and add them to a project.
Click the New SQL button on the Devart - Main toolbar.
Type or paste following code into the SQL document:
CREATE TABLE DEMOBASE.DEPT (
DEPTNO NUMBER(38),
DNAME VARCHAR2(14),
LOC VARCHAR2(13),
CONSTRAINT PK_DEPT PRIMARY KEY (DEPTNO))
Click Save button on the Standard toolbar.
Choose file location and enter file name (for example, ‘Table Dept’) in the appeared Save File As dialog.
Save the file and close the document.
Right-click the Tables folder in Solution Explorer and choose Add Existing File from the shortcut menu
-or-
Click the Tables folder in Solution Explorer and choose Add Existing Item from the Project menu.
Choose the created file in the Open File dialog.
Now the file is added to the project. Perform following actions, that demonstrate it.
Click the New SQL button on the Devart - Main toolbar.
Type or paste following code into the SQL document:
CREATE TABLE DEMOBASE.EMP (
EMPNO NUMBER(38) NOT NULL,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(38),
HIREDATE DATE,
SAL FLOAT(126),
COMM FLOAT(126),
DEPTNO NUMBER(38))
Click Save on the Standard toolbar.
Choose file location and enter file name (for example, ‘Table Emp’) in the appeared Save File As dialog.
Save the file.
Right-click the Tables folder in Solution Explorer and choose Add Existing File from the shortcut menu
-or-
Click the Tables folder in Solution Explorer and choose Add Existing Item from the Project menu.
Choose the created file in the Open File dialog.
You can add not only existing files to the project, but to create new files, that will belong to the project too. You can use file templates when adding a new file to project. Perform following actions to create an SQL file for creating the view STAFF.
Right-click the Views folder node in Solution Explorer and choose Add New File from the shortcut menu
-or-
Click the Views folder node in Solution Explorer and choose Add New File from the shortcut menu.
Select View Script item in the Templates box and click OK.
Replace the view name ‘VIEW1’ with ‘STAFF’ in the appeared SQL document
Type or paste following query text after AS keyword.
SELECT
emp.ename AS name,
emp.job,
emp.sal AS salary,
dept.dname AS department
FROM emp, dept
WHERE emp.deptno = dept.deptno
(+)
Save the document with the name ‘View Staff’.
To complete the project you should add some more scripts to it. Create and add to the project the following scripts in the same way as previous ones.
INSERT INTO demobase.emp VALUES (7839, 'KING', 'PRESIDENT', NULL, '17/11/1981', 5000, 0, 10);
INSERT INTO demobase.emp VALUES (7566, 'JONES', 'MANAGER', 7839, '15/04/1981', 2975, 0, 20);
INSERT INTO demobase.emp VALUES (7698, 'BLAKE', 'MANAGER', 7839, '01/05/1981', 2850, 0, 30);
INSERT INTO demobase.emp VALUES (7782, 'CLARK', 'MANAGER', 7839, '09/06/1981', 2450, 0, 10);
INSERT INTO demobase.emp VALUES (7788, 'SCOTT', 'ANALYST', 7566, '19/08/1987', 3000, 0, 20);
INSERT INTO demobase.emp VALUES (7844, 'TURNER', 'SALESMAN', 7698, '08/09/1981', 1500, 0, 30);
INSERT INTO demobase.emp VALUES (7654, 'MARTIN', 'SALESMAN', 7698, '28/09/1981', 1250, 1400, 30);
INSERT INTO demobase.emp VALUES (7876, 'ADAMS', 'CLERK', 7788, '13/07/1987', 1100, 0, 20);
INSERT INTO demobase.emp VALUES (7900, 'JAMES', 'CLERK', 7698, '03/12/1981', 950, 0, 30);
INSERT INTO demobase.emp VALUES (7902, 'FORD', 'ANALYST', 7566, '03/12/1981', 3000, 0, 20);
INSERT INTO demobase.emp VALUES (7369, 'SMITH', 'CLERK', 7902, '17/12/1980', 800, 0, 20);
INSERT INTO demobase.emp VALUES (7499, 'ALLEN', 'SALESMAN', 7698, '20/02/1981', 1600, 300, 30);
INSERT INTO demobase.emp VALUES (7521, 'WARD', 'SALESMAN', 7698, '22/02/1981', 1250, 500, 30);
INSERT INTO demobase.emp VALUES (7934, 'MILLER', 'CLERK', 7782, '23/01/1982', 1300, 0, 10);
INSERT INTO demobase.dept(deptno, dname, loc) VALUES (10, 'ACCOUNTING', 'NEW YORK');
INSERT INTO demobase.dept(deptno, dname, loc) VALUES (20, 'RESEARCH', 'DALLAS');
INSERT INTO demobase.dept(deptno, dname, loc) VALUES (30, 'SALES', 'CHICAGO');
CREATE SEQUENCE DEMOBASE.SEQ_EMPNO START WITH 8000
CREATE OR REPLACE TRIGGER DEMOBASE.TRIG_EMPINSERT
BEFORE INSERT ON DEMOBASE.EMP
REFERENCING NEW AS NEWVALUES OLD AS OLD FOR EACH ROW
BEGIN
SELECT SEQ_EMPNO.NEXTVAL INTO :NewValues.EMPNO FROM DUAL;
SELECT EmpNo INTO :NewValues.MGR FROM Demobase.Emp WHERE EName='Jones';
SELECT EmpNo INTO :NewValues.MGR FROM Demobase.Emp WHERE EName='Jones';
END;
GRANT CONNECT, RESOURCE, UNLIMITED TABLESPACE TO DEMOBASE IDENTIFIED BY TEST;
CREATE FUNCTION DEMOBASE.RAISESALARY (
Department IN VARCHAR, Rate IN NUMBER
)
RETURN NUMBER
AS
d_No NUMBER;
e_Affected NUMBER;
BEGIN
SELECT DeptNo INTO d_No FROM Dept WHERE Dname=Department;
UPDATE Emp SET Sal=Sal*Rate WHERE DeptNo=d_No;
SELECT Count(*) INTO e_Affected FROM Emp WHERE DeptNo=d_No;
RETURN e_Affected;
END;
After you added these scripts to project, save the project and all unsaved files for future use. To do it perform the following actions.
Right-click the project node in Solution Explorer and choose ‘Save NewProject1’ from the shortcut menu
-or-
Select the project node in Solution Explorer and choose ‘Save NewProject1’ or ‘Save NewProject1 As’ from the File menu.
Type the project name ‘Demobase’ and choose the project location.
Click OK to save the project.
Project deployment is a very convenient feature for executing project scripts in specific order called the deployment order. With the sample project, that was created in this walkthrough you can create entire Demobase database using project deployment. At first you should set the project deployment order. User Demobase should be created first. Then you should execute scripts, that create tables and fill them with data. Then you can create a sequence and a trigger. After this other scripts can be performed in any order or may be not performed. So, the deployment order must be following.
Perform following actions to set such order.
Select Build Order from the Project menu or right-click the project node in Solution Explorer and select Build Order from the Project menu to invoke the Project Deployment Order dialog box.
Drag User Demobase.sql to the top of the list.
Choose Table Dept.sql and click the Move Up button several times till Table Dept.sql takes the second place in the list.
Use Drag-n-drop and Move Up and Move Down buttons to set the deployment order.
Clear list item check boxes for scripts, that you don’t want to be executed. For example, if you don’t want to execute View Staff.sql when performing project deployment, you should clear its check box in the deployment order list.
Click OK.
You can save the project, to permanently save its deployment order.
All the project scripts will be executed using the project connection. If the project does not have a connection, it can not be deployed. So, you should assign a connection to the project. It must be a connection to account of a user that have administrative rights, because project contains script, that creates a user. So, create such a connection or use the existing one. To assign a connection to a project perform following steps.
Right-click the project in Solution Explorer and select Assign Connection from the shortcut menu.
Select required connection from the list and click OK.
-or-
Right-click the connection in Database Explorer and select Add to Project from the shortcut menu.
Before deploying the project you have to drop old demobase schema, that was created in the previous walkthroughs. You can do it in two ways. The first is using Security Manager and the second is executing the DROP USER statement.
To delete user using Security Manager perform the following steps.
Select the project connection in Database Explorer.
Select Security Manager from the