Configuring Quick SQL Settings

5.4.9 Configuring Quick SQL Settings

Configure settings inline to explicitly set SQL syntax generation options, or click the Settings button at the top of the right pane to declaratively set the generation options.

Configuring Settings Declaratively
Configure settings declaratively by clicking the Settings button at the top of the right pane.
Configuring Settings Inline
Configure settings inline to explicitly set SQL syntax generation options.

Parent topic: Using Quick SQL

5.4.9.1 Configuring Settings Declaratively

Configure settings declaratively by clicking the Settings button at the top of the right pane.

To configure settings declaratively:

On the Quick SQL page, click Settings above the right pane.

The Settings page appears. Configure the appropriate attributes.
Table:
- Object Prefix - Identifies common prefix for all objects, automatically separated by an underscore.
- Schema - Prefix object names with a schema name. The default is no schema prefix for object names.
- Compression - Enable to compress each table's data to optimize space.
  Table compression saves disk space and reduces memory use in the buffer cache. Table compression can also speed up query execution during reads. There is, however, a cost in CPU overhead for data loading and DML. Table compression is completely transparent to applications. It is especially useful in online analytical processing (OLAP) systems, where there are lengthy read-only operations, but can also be used in online transaction processing (OLTP) systems.
- Generate Inserts - If disabled, disables all sample data generation.
  Generating inserts can be time consuming and impact usability. Consider turning off the generation of inserts as you refine your data model, then turning on when you are ready to generate data.
Primary Key:
- Add Primary Key - Generates a ID primary key column for each table.
- Population Method - Choose a primary key strategy of identity. Options include:
  - SYS_GUID - Populates the primary key with a globally unique identifier. It generates a numeric random number that is non sequential and non uniform in length. This method is useful in that it can be more secure, it can also allow data to be merged from different databases with out fear of primary key conflict.
  - Identity Column - Sets the primary key by default to an ever increasing number.
  - Sequence - Increments the primary key using number sequence. This primary key population is implemented via a database trigger.
  - None - This method will not populate the primary key with any value.
- Prefix primary keys with table name - Prefixes primary key column with table name (for example, EMPLOYEE_ID).
Column:
- Date Data Type - Defines what type of date to use to store columns identified as dates. Options include:
  - Date
  - Timestamp
  - Timestamp with time zone
  - Timestamp with local time zone
- Semantics- Set VARCHAR2 column length semantics to one of the following:
  - Default - Default to the database setting. If you are unsure what this is, select Default.
  - CHAR - Indicates that the column will have character semantics.
  - BYTE - Indicates that the column will have byte length semantics.
Additional Columns:
- Include - Additional columns to automatically add to your table. Options include:
  - Audit Columns
  - Row Version Number
  - Row Key
Options:
- PL/SQL API - Select On to generate a PL/SQL package API for each table to query, insert, update, and delete its data. Select Off to only generate PL/SQL APIs for tables that include an /api at the end of the name. For example:
```
employees /api
   name
   email
   phone num
```
- Include Drops - Include drop object commands in the output. When set to On, a drop command is generated for each database object created. For example:
```
drop table X including contents;
drop table Y including contents;
```
- Editionable - When generating PL/SQL objects including triggers and packages make these editionable.
- APEX Enabled - Controls whether audit column triggers use the APEX-aware expression to set the <code>created_by</code> and <code>updated_by</code> column values:
```
'coalesce(sys_context(''APEX$SESSION'',''APP_USER''),user)'
```
  When not enabled the following function is used:
```
'user'
```
- Compatibility - Select an Oracle Database release. Ensures generated SQL is compatible with the database release selected.
Audit Columns:
- Created Column Name - Overrides default audit column name for when row was created.
- Created By Column Name - Overrides default audit column name for user who created a row.
- Updated Column Name - Overrides default audit column name for when a row was last updated.
- Updated By Column Name - Overrides default audit column name for user who last updated a row.
To save your configuration, click Save Changes
To reset to the default settings, click Reset

Parent topic: Configuring Quick SQL Settings

5.4.9.2 Configuring Settings Inline

Configure settings inline to explicitly set SQL syntax generation options.

You can configure settings inline to explicitly set SQL syntax generation options. When configuring setting inline:

Settings must start on a new line. To enter multiple settings, begin with # settings =. To enter a single setting per line, use #.
All values are case insensitive.
Brackets, spaces, and commas can be added for clarity but are ignored.
To have all settings generated use # verbose: true

To configure settings inline:

On the Quick SQL page, enter Quick SQL shorthand syntax in the left pane.

The generated SQL displays in the right pane.

Scroll through generated SQL in right pane.

At the bottom, the line starting with -- Generated by Quick SQL is the SQL shorthand syntax. Consider the following example:

-- Generated by Quick SQL undefined 12/7/2023, 12:20:47 PM

/*
departments /insert 4
   name /nn
   location
   country
   employees /insert 14
      name /nn vc50
      email /lower
      cost center num
      date hired
      job vc255

view emp_v departments employees

--- Non-default options:
# settings = {"apex":"Y","db":"19c"}

*/

Specify settings after the line starting with # settings = .

Entering settings directly into the right pane ensures the same SQL generation options are utilized even if you download the script and later paste it back. For example, enter the following to prefix all table names with TEST and generate for schema OBE:
```
# settings = { prefix: "test", schema: "OBE" }.
Alternatively, enter each setting on a separate line for the same result:
# prefix: "test" 
# schema: "OBE"
```

Tip:

To view a listing of settings, by click Help and then the Settings tab.

Parent topic: Configuring Quick SQL Settings