Working with Project

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, Blank Project on the File menu. A project will appear in the Project Explorer.

Creating Project

Creating Folder Structure

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, and 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.

  1. Right-click the project node in Project Explorer and choose Add Folder on the shortcut menu

    or

    Select the project node in Project Explorer and choose Add Folder on the Project menu.

  2. Enter the new folder name (Security).
  3. Repeat steps 1 - 3 for creating Database Objects and Data folders.
  4. Right-click the Database Objects folder node in Project Explorer.
  5. Select Add Folder on the shortcut menu.
  6. Enter the new folder name (Tables).
  7. 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.

Adding Scripts to the Project

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.

  1. Click the New SQL button on the Standard toolbar.

  2. 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))
  3. Click the Save button on the Standard toolbar.
  4. Choose file location and enter file name (for example, Table Dept) in the appeared Save File As dialog box.
  5. Save the file and close the document.

  6. Right-click on the Tables folder in Project Explorer and choose Add Existing File on the shortcut menu

    or

    Click on the Tables folder in Project Explorer and choose Add Existing File on the Project menu.

  7. Choose the created file in the Open File dialog box.

Now, the file is added to the project. Perform following actions, that demonstrate it.

  1. Click the New SQL button on the Standard toolbar.

  2. 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))
  3. Click the Save button on the Standard toolbar.
  4. Choose file location and enter file name (for example, Table Emp) in the appeared Save File As dialog box.
  5. Save the file.

  6. Right-click on the Tables folder in Project Explorer and choose Add Existing File on the shortcut menu

    or

    Click on the Tables folder in Project Explorer and choose Add Existing File on the Project menu.

  7. Choose the created file in the Open File dialog box.

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.

  1. Right-click the Views folder node in Project Explorer and choose Add New File on the shortcut menu

    or

    Click the Views folder node in Project Explorer and choose Add New File on the shortcut menu.

  2. Select the View Script item in the Templates box and click OK.
  3. Replace the view name VIEW1 with STAFF in the appeared SQL document

  4. Type or paste following query text after the 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
 (+)

  5. 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.

  • Emp data to the Data folder

      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);

  • Dept data to the Data folder

      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');

  • Sequence Seq_Empno to the Sequences folder

      CREATE SEQUENCE DEMOBASE.SEQ_EMPNO START WITH 8000

  • Trigger Trig_Empinsert to the Triggers folder

      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';

  END;

  • User Demobase to the Security folder

      GRANT CONNECT, RESOURCE, UNLIMITED TABLESPACE TO DEMOBASE IDENTIFIED BY TEST;

  • Function RaiseSalary to the Procedures folder

      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.

  1. Right-click the project node in Project Explorer and choose Save NewProject1 on the shortcut menu

    or

    Select the project node in Project Explorer and choose Save NewProject1 or Save NewProject1 As on the File menu.

  2. Type the project name Demobase and choose the project location.
  3. Click OK to save the project.

Working with Project

Deploying 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.

  • User Demobase.sql
  • Table Dept.sql
  • Table Emp.sql
  • Dept data.sql
  • Emp data.sql
  • Sequence Seq_Empno.sql
  • Trigger Trig_Empinsert.sql
  • Function RaiseSalary.sql
  • View Staff.sql

Perform following actions to set such order.

  1. Select Build Order on the Project menu or right-click the project node in Project Explorer and select Build Order on the Project menu to invoke the Project Deployment Order dialog box.
  2. Drag User Demobase.sql to the top of the list.
  3. Choose Table Dept.sql and click the Move Up button several times till Table Dept.sql takes the second place in the list.
  4. Use Drag-n-drop and Move Up and Move Down buttons to set the deployment order.
  5. 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.
  6. Click OK.
  7. 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.

  1. Right-click on the project in Project Explorer and select Assign Connection on the shortcut menu.

  2. Select required connection from the list and click OK.

    or

    Right-click the connection in Database Explorer and select Assign to Project on 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 a user using Security Manager, perform the following steps:

  1. Select the project connection in Database Explorer.
  2. Select Security Manager on the Database menu.
  3. Expand the Users node in the Security Manager

  4. Select the user DEMOBASE and click the Delete button on the Security Manager toolbar

    or

    Right-click the user DEMOBASE and select Delete on the shortcut menu.

  5. Click Yes in the appeared dialog box.

To delete user by executing the DROP USER statement, perform the following actions:

  1. Open an SQL editor by clicking the Create New SQL button on the Standard toolbar.

  2. In the SQL editor, type the following:

     DROP USER DEMOBASE CASCADE
  3. Execute the document by clicking on appropriate button on the SQL toolbar.
  4. After performing all these steps you can deploy project. To deploy the project select Deploy Demobase on the Project menu and click Yes in the appeared dialog. You will see deployment log in the General tab of the Output window. After deployment is finished, you can access Demobase database and its objects.

After deploying the project you can add database object shortcuts to it. To add a database object shortcut drag the database object from the project connection in Database Explorer to the project explorer or select Add to Project and then select Demobase on the shortcut menu of the object node in Database Explorer. Database object shortcuts can be used for fast opening of object editors by double-clicking the shortcuts. You can also easily find the object in Database Explorer by right-clicking its shortcut in Project Explorer and selecting Find in Database Explorer on the shortcut menu.

Compiling Project

After deploying project, you can compile it. Compiling project means compiling all database source objects and views, that correspond to the project DDL scripts. So, all deployed source objects and views will be compiled during project compilation. Project will be compiled through the project connection. In case of the Demobase project following objects will be compiled if they were deployed: view STAFF, trigger TRIG_EMPINSERT, function RAISESALARY.

To compile project, on the Project menu, select Compile. You can also compile project with debug information for debugging project source objects.

Want to Find out More?

Overview

Overview

Take a quick tour to learn all about the key benefits delivered by dbForge Studio for Oracle.
All Features

All features

Get acquainted with the rich features and capabilities of the Studio in less than 5 minutes.
Request a demo

Request a demo

If you consider employing the Studio for your business, request a demo to see it in action.
Ready to start using dbForge Studio for Oracle?
Download a free 30-day trial