Oracle by Example brandingManage Tables Using SQL Developer

section 0Before You Begin

Select the Oracle Database release:

This 15-minute tutorial shows you how to view table definitions and table data. You also learn to create a new table and modify it.

Background

A schema is a collection of database objects. A schema is owned by a database user and shares the same name as the user. Schema objects are logical structures created by users. Some objects, such as tables or indexes, hold data. Other objects, such as views or synonyms, consist of a definition only.

What Do You Need?

  • Oracle Database 18c19c
  • SQL Developer 19.1
  • Installed the sample schemas in the pluggable database
  • Download the load_po.csv file.

section 1View Tables

  1. Expand the system node in Oracle SQL Developer.
    system connection
    Description of the illustration a1
  2. Expand the Other Users node and then expand the HR node.
    other users
    Description of the illustration a2
  3. Expand the Tables (Filtered) node and select the EMPLOYEES table. Detailed information about the table is displayed in the object pane. The Columns tab displays the column names and definitions.
    Employees table
    Description of the illustration a3

section 2View Table Data

  1. Ensure that the EMPLOYEES table is selected in the Connections pane. Click the Data tab to view the data stored in the table. The Data tab shows the rows stored in the EMPLOYEES table.
    2. Employees Data tab
    Description of the illustration b1
  2. To sort the rows by last name, right-click the LAST_NAME column name and select Sort in the menu.
    3. sort option is selected
    Description of the illustration b2
  3. To Select the LAST_NAME column and click the right-arrow to move it to the Selected Columns list. Click OK.
    Sort Columns window
    Description of the illustration b3
  4. The data is now displayed in sorted order.
    employees table
    Description of the illustration b4

    Dismiss the EMPLOYEES and SYSTEM window.

section 2Create a Table

In this topic you create a new table in the APPUSER schema. You created the APPUSER schema in the Administering User Accounts and Security tutorial.

  1. Expand the APPUSER node in Oracle SQL Developer. Right-click the Tables node and select New Table.
    2. new table
    Description of the illustration c1
  2. Enter PURCHASE_ORDERS in the Name field. Select Primary Key. Enter PO_NUMBER in the Column Name field. Select NUMBER as the Data Type. Select Not Null. Click Add Column.
    create table wizard
    Description of the illustration c2
  3. Enter PO_DESCRIPTION in the Column Name field. Select VARCHAR2 as the type. Enter 200 in the Size field. Click Add Column.
    create table
    Description of the illustration c3
  4. Enter PO_DATE in the Column Name field. Select DATE for the type and select the Not Null column. Click Add Column.
  5. Enter PO_VENDOR in the Column Name field. Select NUMBER as the type and select the Not Null column. Click OK.
    create table wizard
    Description of the illustration c5
  6. Expand the Tables node to view the PURCHASE_ORDERS table in the Tables list for the APPUSER user.
  7. Click the Columns tab to view the column definitions of the PURCHASE_ORDERS table.
    columns tab
    Description of the illustration c7

section 2Add a Column

  1. Right-click the PURCHASE_ORDERS table and select Edit.
    edit table option
    Description of the illustration d1
  2. The Edit Table dialog box appears. Click the plus sign to add a column or press Alt+1.
  3. Enter PO_DATE_RECIEVED in the Name field. Select DATE in the Type menu. Click the plus sign again or press ALT+1.
  4. Enter PO_REQUESTOR_NAME in the Name field. Select VARCHAR2 in the Type menu. Enter 40 in the Size field. Click OK.
    edit table wizard
    Description of the illustration d4
  5. The Columns tab shows the new columns.
    purchase orders table
    Description of the illustration d5

section 2Load Data into a Table

  1. Download the load_po.csv file.
  2. Right-click the APPUSER user and select Edit User.
    edit user
    Description of the illustration e2
  3. On the Edit User window, click Quotas. Select Unlimited for the APPTS tablespace and click Apply. You will receive a prompt stating that the SQL command processed successfully, click OK. The Edit User window closes automatically.
    edit user window
    Description of the illustration e3
  4. Expand Tables. Right-click the PURCHASE_ORDERS table and select Import Data.
    Import Data
    Description of the illustration e4
  5. Click Browse and select the load_po.csv file. Click Open.
  6. Deselect Header. Ensure that Format is set to csv and UTF8 is selected for Encoding. Select none for Left Enclosure. Click Next.
    data import wizard
    Description of the illustration e6
  7. Ensure that Import Method is set to Insert. Click Next. Click Next.
    data import wizard
    Description of the illustration e7
  8. Verify each column in the Source Data Columns list and its value in the Name field BEFORE clicking Next. After verifying all five columns, click Next. As shown in the screenshot, you may need to edit the Format of COLUMN5 to DD-MON-YYYY.
    data import wizard
    Description of the illustration e8
  9. Click Finish.You get a success message that the data is successfully imported. Click OK.
  10. Select the PURCHASE_ORDERS table and click the Data tab to see the new rows.
    Purchase_orders
    Description of the illustration e10