5.4.8 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.

5.4.8.1 Configuring Settings Declaratively

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

To configure settings declaratively:

1. Navigate to the Quick SQL page:
   1. On the Workspace home page, click the SQL Workshop icon.
   2. Click Utilities.
   3. Click Quick SQL.
2. Click Settings on the Oracle SQL Output pane.

   The Settings page appears. Configure the appropriate attributes.

3. Under Table:
   • Object Prefix - Database object prefix. Prefixes all object names with the provided value .
   • Schema - Prefix object names with a schema name. The default is no schema prefix for object names.
   • On Delete - Defines how foreign keys behavebehave on delete of the parent row. Options include:
     • Cascade - A foreigh key with "on delete cascade" automatically deletes the child row when the correponding parent is deleted.
     • Restrict - A foreign key with "on delete restrict" prevents deletion of the parent row if a child row exists.
     • Set Null - A foreign key with "set null on delete" means that if a record in the parent table is deleted, then the corresponding records in the child table will have the foreign key fields set to null.
   • Compression - Determines if you want your tables to be compressed or not.

     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 - 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.
4. Under Column:
   • Primary Keys - Defines how table primary keys are to be populated. 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.

   • Date Data Type - Defines what type of date to use to store columns identified as dates. Options include: Date, Timestamp, and Timestamp with local time zone.
   • Semantics- Options include:
     • Default - Default to the database setting. If you are unsure what this is, select Default.
     • BYTE - Indicates that the column will have byte length semantics.
     • CHAR - Indicates that the column will have character semantics.
5. Under Additional Columns:
   • Include - Additional columns to automatically add to your table. Options include: Audit Columns, Row Key, Row Version Number, and Security Group ID.
6. Under Options:
   • Data Language - Controls the language used for generating the data using the /INSERT flag.
   • PL/SQL API - Select On to generate a PL/SQL APIs with one package per table on all tables. 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;

   • Auto Primary Key - Select On to have primary key columns automatically generated for tables. The primary key column name will be ID and it will be of type number.

     Tip:

     To see an example, see field-level Help.
   • Longer Varchars - Available in Oracle Database 12.1 or later, Longer Varchars allow for columns to be up to 32K bytes. Ensure your database is configured to support longer varchars if you wish to use this feature.
   • Editionable - When generating PL/SQL objects including triggers and packages make these editionable.
   • APEX Enabled - Controls the syntax generated to support audit columns.
   • Tags Framework - Choose On or Off.
   • Compatibility - Select an Oracle Database release. Ensures generated SQL is compatible with the database release selected.
7. Under Preferences:
   • Prefix primary keys with table name - Prefix primary key database table columns with name of table. Choose On or Off.
   • Created Column Name - When generating audit columns, controls the name of this audit column.
   • Created By Column Name - When generating audit columns, controls the name of this audit column.
   • Updated Column Name - When generating audit columns, controls the name of this audit column.
   • Updated By Column Name - When generating audit columns, controls the name of this audit column.
8. To save your configuration, click Save Changes
9. To reset to the default settings, click Reset

5.4.8.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

Entering settings directly into the Quick SQL Shorthand 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 clicking Help and then Settings.