MacLochlainns Weblog

The following walks you through how you sign on to a STUDENT Workspace with Oracle’s APEX product. It shows you how to create a new table with the Object Browser tool.

You can find instructions on how to create your own STUDENT Workspace in this blog post. Overall, Oracle APEX is a valuable tool to learn and master.

1. You start the process by accessing the Oracle Database 11g APEX, which you can access at http://localhost:8080/apex by default on the server. If you’ve got a static IP address for your instance, you can replace localhost with the IP address or hostname for the IP address.
   • Workspace: STUDENT
   • Username:  ADMIN
   • Password:  STUDENT

2. After you login to the STUDENT workspace, you have four options. They are the: Application Builder, SQL Workshop, Team Development, and Administration. You start the process by accessing the Oracle Database 11g APEX, which you can access at http://localhost:8080/apex by default on the server. If you’ve got a static IP address for your instance, you can replace localhost with the IP address or hostname for the IP address. Click on the Object Browser icon to proceed.

3. Clicking the SQL Workshop icon takes you to the second level menu. You click the Object Browser icon to create a database object.

4. After clicking the Object Browser icon, you see the screen at the left. Click the Create button to create a table.

5. After clicking the Create button, you see the screen at the left. Click the type of database object that you want to create. In our case, we click the Table hypertext to start the create table workflow.

6. After clicking the Table hyperlink, you see the Create Table screen at the left. Enter the column names, choose their data types and set the scale and precision. You should also check the Not Null checkbox when you want a column to be mandatory. Click the Next button to continue the create table workflow.

7. After entering the column names, you should choose the data types, enter the scale and precision, and check the NOT NULL checkbox to make appropriate columns mandatory by applying NOT NULL database constraints. If you run out of entry rows, you can click the Add Column button to add new rows. Click the Next button to continue the create table workflow when you’ve defined the columns.

8. After defining the column names, you should choose whether the primary key will use a new sequence or an existing sequence. You also have the ability to not assign a primary key value or simply leave it unpopulated when inserting new rows. The example creates an IMAGE_PK primary key constraint on the IMAGE_ID column, and declares an IMAGE_SEQ sequence value. Click the Next button to continue the create table workflow when you’ve defined the primary key constraint and any new sequence value for the primary key column.

9. After defining the primary key constraint, you can define foreign key column constraints. You enter a foreign key constraint name, choose between a Disallow Delete, Cascade Delete, or Set Null on Delete rule, select the foreign key column, the foreign key’s referenced table and column. Click the Add button to continue the create table workflow.

10. After defining a foreign key constraint, you can see the constraint that you created. Then, you can define another foreign key column constraints. You repeat the steps from the prior steps to add another foreign key constraint. Click the Add button to create a second foreign key constraint and complete the create table workflow.

11. After defining a second foreign key constraint, you see the following two foreign key constraints. Click the Next button to complete the create table workflow.

12. After defining all the foreign key constraints, you can create check and unique constraints. You check a radio button for a check or unique constraint, and then you select the columns for the constraint’s key. Click the /Add button to create any check or unique constraints as part of the create table workflow.

13. After defining all check and unique key constraints, you can see them in the Constraints box. Click the Next button to complete the create table workflow.

14. After defining all items about the table, you can see the SQL to create the IMAGE table and its constraints. You can copy the SQL into a file for later use when writing a re-runnable script. Click the Create button to complete the create table workflow and create the table.

     

    The following are the contents of the script for the actions you’ve defined:

    CREATE table "IMAGE" (
        "IMAGE_ID"         NUMBER NOT NULL,
        "FILE_NAME"        VARCHAR2(60) NOT NULL,
        "MIME_TYPE"        NUMBER NOT NULL,
        "ITEM_IMAGE"       BLOB,
        "CREATED_BY"       NUMBER NOT NULL,
        "CREATION_DATE"    DATE NOT NULL,
        "LAST_UPDATED_BY"  NUMBER NOT NULL,
        "LAST_UPDATE_DATE" DATE NOT NULL,
        constraint  "IMAGE_PK" primary key ("IMAGE_ID")
    )
    /
    
    CREATE sequence "IMAGE_SEQ" 
    /
    
    CREATE trigger "BI_IMAGE"  
      before insert on "IMAGE"              
      for each row 
    begin  
      if :NEW."IMAGE_ID" is null then
        select "IMAGE_SEQ".nextval into :NEW."IMAGE_ID" from dual;
      end if;
    end;
    /   
    
    ALTER TABLE "IMAGE" ADD CONSTRAINT "IMAGE_FK1" 
    FOREIGN KEY ("CREATED_BY")
    REFERENCES "SYSTEM_USER" ("SYSTEM_USER_ID")
    
    /
    ALTER TABLE "IMAGE" ADD CONSTRAINT "IMAGE_FK2" 
    FOREIGN KEY ("LAST_UPDATED_BY")
    REFERENCES "SYSTEM_USER" ("SYSTEM_USER_ID")
    
    /
    alter table "IMAGE" add
    constraint "IMAGE_UK1" 
    unique ("FILE_NAME","MIME_TYPE")
    /   
    

15. After creating the table, trigger, sequence, and constraints, you can see the table definition. You also have the ability to modify the table. At this point, you can create another structure or you can click the Home or SQL Workshop menu choice.

As always, I hope this helps those looking to learn new things and approaches.