3.3 Managing Tables

A table is a unit of data storage in an Oracle database, containing rows and columns. Use Object Browser to create, view, edit, or drop tables.

• Creating a Table
  Create a table using Object Browser.
• Viewing a Table
  Select a table from the Object Tree and access tabs in the Object Detail View.
• Editing Table Columns
  Select a table from the Object Tree and click the Columns tab.
• Editing Table Data
  Select a table from the Object Tree and click the Data tab.
• Dropping a Table
  Select a table from the Object Tree, click the Columns tab, and select Drop Table from the More menu.
• Creating an Application from a Table
  Select a table from the Object Tree, click the Columns tab, and select Create App from the More menu.
• Analyzing Statistics of a Table
  Select a table from the Object Tree and click the Statistics tab to examine various metrics of a table.
• Viewing Sample Queries for a Table
  Select a table from the Object Tree and click the Sample Queries tab to view sample queries.

See Also:

• Tables and Table Clusters in Oracle Database Concepts for an introduction to schema objects and discusses tables
• Managing Tables in Oracle APEX Administration Guide

3.3.1 Creating a Table

Create a table using Object Browser.

To create a table:

1. In Object Browser, click the Create Database Objects menu and select Table.

   Tip:

   To create new objects from the Object Tree, right-click the object and select the Create option.

   The Create Table Wizard appears.

2. In Create Table:
   1. Table Name - Enter the name of the table. The name must conform to Oracle naming conventions and cannot contain spaces, or start with a number or underscore.
   2. Semantics - Select the semantics option for the text columns. Options include:
      • Default - Defaults to the database setting. If you are unsure about this setting, then choose Default.
      • BYTE - Storage for the text columns is determined by the number of bytes.
      • CHAR - Storage is determined by the number of characters.
   3. Comments - Enter comments for the table. These comments are added to the table object.
3. Columns - Use the Columns to add, edit, and delete a column. Available controls include:
   • Edit - Toggles Editing mode. When enabled, you can single-click a cell to edit. When disabled, you must double-click to edit.
   • Move Down - Moves a column down in the table.
   • Move Up - Moves a column up in the table.
   • Add Column - Adds a new row (or column) to the table.
   • Row Actions menu - Displays in the first cell on a row in the Columns region. Available Actions include:
     • Add Column
     • Delete Column
     • Move Up (Alt + Up)
     • Move Down (Alt + Down)
4. To add a column, click Add Column.

   A new row appears. Edit the column attributes by selecting a cell and typing. Column attributes include:

   1. Column Name - Enter the column name.
   2. Data Type - Select the column type.
   3. Precision - Enter the number of digits before the decimal point.
      Not all column types have this setting. Precision must be a positive integer. Only NUMBER, FLOAT, INTERVAL YEAR TO MONTH, and INTERVAL DAY TO SECOND have this setting. For example, if a column of type NUMBER has precision of 4, the column value can be 0 through 9999.
   4. Scale - Specifies scale or length depending on the selected data type:
      • For NUMBER types, scale is the number of digits after the decimal point.
      • For VARCHAR2 and CHAR types, scale is the number of stored characters.
      • For TIMESTAMP types, scale is the fractional seconds precision and must be a number between 0 and 9. Scale must be a positive number. For example, if a column type TIMESTAMP has a scale of 3, seconds are between .000 seconds and .999 seconds.
   5. Not Null - Enable to specify a column must always have a value, select the Not Null check box.
   6. Primary Key - Enable if the column is part of the primary key. A primary key uniquely identifies a row in a table.

      If only one column is selected as primary key column and if the column data type is NUMBER or FLOAT, then the primary key column is automatically created as identity column.

   7. Default Expression - If applicable, enter a default expression (must be a valid SQL expression). Custom functions are not allowed.
   8. Comment - Enter comments for the column. These comments are added to the table column.
5. (Optional) Click Preview SQL to review the SQL script used to generate the table.
6. Click Create Table.

See Also:

• Using the Table Finder in Oracle APEX App Builder User’s Guide
• Overview of Tables in Oracle Database Concepts for information about tables

3.3.2 Viewing a Table

Select a table from the Object Tree and access tabs in the Object Detail View.

To browse a table:

1. In Object Browser, Object Tree, expand Tables and select a table.
2. The Object Detail View appears and displays the following tabs:
   • Columns - Displays the column name, data type, nullable status, default value, primary key, comments, and identify. Most commonly used actions display as buttons. Available actions include:
     • Add Column
     • Modify Column
     • Rename Column
     • Drop Column
     • Refresh
     • More - Less commonly used actions are accessible from the More menu. Available actions include:
       • Rename Table
       • Copy Table
       • Drop Table
       • Truncate Table
       • Table Comment
       • Create Lookup Table
       • Create App
       • UI Defaults
   • Data - Displays data in the current table. Available actions include:
     • Insert Row - Insert a row in the table.
     • Columns - Configure the columns to display.
     • Filter - Create a filter by selecting a column, operation, and value.
     • Count Rows - Displays the number of rows in the current table.
     • Load Data - Use the Load Data Wizard to upload a file or copy and paste CSV data.
     • Download - Download an XLSX file.
     • Refresh - Refresh the current view.
   • Indexes - Displays indexes associated with this table. Available actions include:
     • Create
     • Drop
     • Refresh
   • Constraints - Displays a list of constraints for the current table. Available actions include:
     • Create
     • Drop
     • Enable
     • Disable
     • Refresh
   • Grants - Details of grants for the current table, including privilege, grantee, grantable, grantor, and object name. Available actions include:
     • Grant
     • Revoke
     • Refresh
   • Statistics - Displays collected statistics about the current table and columns. Table statistics include the number of rows and blocks, the average row length, and the data was last analyzed. Available actions include:
     • Gather Statistics
     • Refresh
   • Triggers - Displays a list of triggers associated with the current table. To view trigger details, click the trigger name. Available actions include:
     • Create
     • Drop
     • Enable
     • Disable
     • Refresh
   • Dependencies - Displays objects that use (or depend) on the current table and objects the table depends on. To link to an object, click the object name. Available actions include:
     • Refresh
   • DDL - Displays the DDL necessary to re-create this table, including keys, indexes, triggers, table and column comments, and table definition. Edit the code using a fully functional Code Editor. Available actions include:
     • Download
     • Refresh

     Tip:

     To learn more about the Code Editor, see Code Editor in Object Browser.
   • Sample Queries - Displays sample queries for using the table. Includes Copy and Run capability. Available actions include:
     • Refresh

3.3.3 Editing Table Columns

Select a table from the Object Tree and click the Columns tab.

To edit table columns:

1. In Object Browser, Object Tree, expand Tables and select a table.
2. On the Columns tab, click Add Column:
   1. Configure the attributes. At a minimum, enter a column name and select a type. Depending upon the column type, specify whether the column requires precision, scale, a default value, or is nullable. For more details on an attribute, see field-level Help.
   2. SQL - Displays the generated SQL used to add the column. To copy the displayed SQL, click the Copy icon.
   3. Click Apply.
3. On the Columns tab, click Modify Column:
   1. Configure the attributes.
      Data Redaction region may be shown if the current schema selected is granted execute on DBMS_REDACT.
   2. SQL - Displays the generated SQL used to modify the column. To copy the displayed SQL, click the Copy icon.
   3. Click Apply.
4. On the Columns tab, click Rename Column:
   1. Configure the attributes.
   2. SQL - Displays the generated SQL used to rename the column. To copy the displayed SQL, click the Copy icon.
   3. Click Apply.
5. On the Columns tab, click Drop Column:
   1. Drop Column - Review the details.
   2. SQL - Displays the generated SQL used to drop the column. To copy the displayed SQL, click the Copy icon.
   3. Click Apply.
6. More menu - Less commonly used actions are accessible from the More menu. Available actions include:
   • Rename Table - Rename the selected table. In New Table Name, enter the new name.
   • Copy Table - Copy the selected table. In New Table Name, enter the new name.
   • Drop Table - Delete the selected table and all table rows. See Dropping a Table.
   • Truncate Table - Empty the selected table completely.
   • Table Comment - Add or edit comments for both a table or table column.
   • Create Lookup Table - A table lookup finds a value corresponding to another known value, which is called the lookup basis.
   • Create App - Create an application on the currently selected table. See Creating an Application from a Table.
   • UI Defaults - Create Table Dictionnary defaults from the table or column definition in the database.

See Also:

Using the Recycle Bin

3.3.4 Editing Table Data

Select a table from the Object Tree and click the Data tab.

To edit table data:

1. In Object Browser, Object Tree, expand Tables and select a table.
2. In Object Detail view, click the Data tab.
   More commonly used actions display as tabs.
3. Insert Row - In the Insert Row dialog, enter the data and click Create.
4. Modify existing row data:
   1. Find the row to edit and click the Edit Row icon.
   2. In the Edit Row dialog, edit the data and click Save.

      Tip:

      To delete the row, click Delete.
5. Columns - Choose which columns to display.
6. Filters - Create column filters. In the Filters dialog:
   1. Select a Column, Operation, and Value and click Add.
   2. Click Apply.
7. Count Rows - Displays the number of rows in the current table.

   Count Rows function always provides the count of the total number of rows in a table. Applying filters does not affect count rows function.

8. Load Data - Use the Load Data Wizard to upload a file, copy and paste CSV data, or load a sample data set.
9. Download - Download a native XLSX file.
10. Refresh - Refresh the current view.

3.3.5 Dropping a Table

Select a table from the Object Tree, click the Columns tab, and select Drop Table from the More menu.

Dropping a table deletes the selected table and all table rows.

To drop a table:

1. In Object Browser, Object Tree, expand Tables and select a table.

   The Object Detail View appears.

2. On the Columns tab, select More, and then Drop Table.
   1. Drop - Review the details.
   2. (Optional) Enable Cascade Constraints to drop all referential integrity constraints that refer to primary and unique keys in the dropped table. If disabled when referential integrity constraints exist, the database returns an error and cancels the drop procedure.
   3. SQL - Displays the SQL generated to drop the table.
   4. (Optional) Click the Copy icon to copy the displayed SQL.
   5. To confirm your selection, click Drop again.

See Also:

Using the Recycle Bin

3.3.6 Creating an Application from a Table

Select a table from the Object Tree, click the Columns tab, and select Create App from the More menu.

APEX Data Dictionary analyzes the table data with DBMS_STATS and adds the following pages:

• Chart page, if chart recommendation was computed
• Calendar page, if any date or timestamp column type exists
• Interactive report and form page
• Dashboard with charts with columns for charting (these dashboard charts automatically include links to the interactive report page)
• Faceted search page (if valid facets are found)

To create an application from a table in Object Browser:

1. In Object Browser, Object Tree, expand Tables and select a table.

   The Object Detail View appears.

2. On the Columns tab, select the More menu, and then Create App.
   The Create Application Wizard appears.
3. On the confirmation page, click Create App.
   The Create Application Wizard appears.
4. Follow the instructions.

Note:

If table Data Dictionary Cache is out of sync with the database table information due to making changes to the table (such as modifications to the table structure or column names), Create Application Wizard may raise an error. To avoid the error, refresh the Data Dictionary Cache.

See Also:

• Refreshing the Data Dictionary Cache in Oracle APEX Administration Guide
• Creating Applications in Oracle APEX App Builder User’s Guide

3.3.7 Analyzing Statistics of a Table

Select a table from the Object Tree and click the Statistics tab to examine various metrics of a table.

To view the statistics of a table:

1. In Object Browser, Object Tree, expand Tables and select a table.

   The Object Detail View appears.

2. Click the Statistics tab.
   The Statistics tab displays a number of metrics separated into Table Statistics for row analysis and Column Statistics for column analysis. The information presented depends on the table.
3. To refresh the displayed statistics, click one of the following:
   • Gather Statistics - Gathers table statistics using DBMS_STATS. "Gather statistics in progress..." displays as Last Analyzed column value while this process runs in the database background. Click Refresh to get the table and column statistics information. If the background process finishes, the statistics information should display with Last Analyzed column value updated.
   • Refresh - Refresh queries statistics information gathered already from USER_TABLES and USER_TAB_COLUMNS views.
   The Statistics tab refreshes.

3.3.8 Viewing Sample Queries for a Table

Select a table from the Object Tree and click the Sample Queries tab to view sample queries.

To view sample queries:

1. In Object Browser, Object Tree, expand Tables and select a table.

   The Object Detail View appears.

2. On the Sample Queries tab, choose from available actions:
   • Refresh - Refreshes the page.
   • Copy - Copies the query.
   • Run - Runs the query.