Tutorial 1 part 27. Creating new database table with SQL Developer

Creating new database tables with SQL Developer

New database

Start SQL Developer and log on.

Create a new table by pressing the rightmost mouse button when the cursor is on top of the tables. This opens up the list of available options. Select ‘New Table…’ from the list. This opens the wizard.

Click on the Advanced check box to go to a more detailed view.

I called my table GEO_ACTIONS.The first column is preset on named COLUMN1. You can change its name (e.g. to ID), datatype and other parameters with the fields in the screen.

If you want to make some column the primary key, you need to select the ‘Primary Key’ selection from the right side of the form.

You can switch between simple and advanced modes of editing the fields by checking or unchecking the checkbox labeled ‘Advanced’

You can add new columns by pressing the green + button on the screen.

I added the following fields:

-          Id, primary key coming from sequence

-          LocationId representing the location from where the event is originating (this is the site from where the car was driven away). VARCHAR2(32)

-          Script – script to run as response. VARCHAR2 (64)

-          Priority NUMBER(2,0) – order when to run the action

-          PARAM1 VARCHAR2(20) – optional parameter

-          PARAM2 VARCHAR2 (20) – optional parameter to script

Optionally you might want to consider adding fields like:

-          Username – If there is need to log on to some external resource. VARCHAR2 (32)

-          Password VARCHAR2 (32)

-          ScriptDir VARCHAR2(128)

I defined all fields to be ‘NOT NULL’ except the priority, Param1 and param2.

I also added an index based on the locationid as the SELECT statements will use this as the query key.

If you later want to change the table contents, you can select the button that looks like a pen over a sheet of paper to go back to the same form where you can add, change and delete fields. If there is existing data in the table and contents for a field are not null, you cannot delete that type of columns.

Creating a sequence to automatically populate the ID field with numbers

A very common approach is to create a sequence and have it auto-generate values for the ID field. Use the following commands:

create sequence act_seq start with 1

/

create trigger act_ins

before insert on geo_action

for each row

begin

    select act_seq.nextval into :new.id from dual;

end;

You can also add and edit data directly to the table from the same form. Select the ‘Data’ tab and a new form will open up that allows you to edit data.

You can add data by directly editing to the form. You need to commit the changes by selecting the button that looks like ok symbol over a symbol of a disk pack. The tooltip for this button is ‘commit changes’