SaveToDB Framework 7 for Oracle Database

SaveToDB Framework 7 for Oracle Database

SaveToDB Framework for Oracle Database

SaveToDB Framework for Oracle Database contains ready-to-use solutions for application development using Microsoft Excel and Oracle Database.

The framework can be easily installed and uninstalled. The framework uses the SAVETODB_DEV and SAVETODB_XLS schemas.

The framework can be installed from the SaveToDB add-in in a couple of minutes. Also, you can generate an Excel workbook to customize SaveToDB features using Microsoft Excel.

See SaveToDB Framework Installer and Configuration Workbook Generator.

 

After installation you can customize required features:

Install and Uninstall

Installation Methods

SaveToDB Framework supports four methods to install or remove the framework:

  1. Installation using the SaveToDB Framework Installer integrated into the SaveToDB add-in.
  2. Installation using the standalone SaveToDB Framework Installer.
  3. Installation using source codes.
  4. Installation using command files.

The first two methods allow installing or removing the framework in databases on all supported platforms:
Microsoft SQL Server, Microsoft SQL Server Compact, Oracle Database, IBM DB2, MySQL, MariaDB, NuoDB, PostgreSQL, and SQLite.

See SaveToDB Framework Installer.

The source code is located in the "SaveToDB Framework for Oracle Database" folder:

  • framework-install-en.sql
  • framework-remove-en.sql

The installation using batch files is described below.

Installation Remarks

Database administrator permission is required to install or uninstall the framework.

Add Excel application end users to the SAVETODB_XLS_ROLE role and Excel application developers to the SAVETODB_DEV_ROLE role after the framework setup.

Only framework objects are removed while framework uninstall. However, this operation removes all configuration data. Be careful.

Install and Uninstall Using Batch Files

The source code and batch files are located in the "SaveToDB Framework for Oracle Database" folder.

Installing SaveToDB Framework

  1. Run 1-edit-config.cmd and edit the "setup" connection.
  2. Run 2-install-savetodb-framework.cmd.
  3. Run 3-clear-credentials.cmd and clear the "setup" connection password.

The "setup" connection user must have DBA permissions.

Instead of clearing the setup password on step 3, you may protect gsqlcmd.exe.config:

select the "User protected" option in gConnectionManager.

Uninstalling SaveToDB Framework

  1. Run 1-edit-config.cmd and edit the "setup" connection.
  2. Run framework-remove.cmd.
  3. Run 3-clear-credentials.cmd and clear the "setup" connection password.

Verifying Framework Objects

  1. Run 1-edit-config.cmd and edit the "setup" connection.
  2. Run framework-list.cmd.
  3. Run 3-clear-credentials.cmd and clear the "setup" connection password.

Users and Roles

SaveToDB Framework for Oracle Database has the following users (schemas):

UserDescription
SAVETODB_XLSThe schema contains the objects that should be available for Excel application end users.
SAVETODB_DEVThe schema contains the objects that hold data and should be available for Excel application developers.

SaveToDB Framework for Oracle Database creates the following roles:

RoleDescription
SAVETODB_XLS_ROLEThe role is for Excel application end users.
SAVETODB_DEV_ROLEThe role is for Excel application developers.

The SAVETODB_XLS_ROLE role permissions are enough to read the SaveToDB configurations.

The SAVETODB_DEV_ROLE role permissions are enough to read and update the SaveToDB configurations.

Query Lists

Preface

SaveToDB allows changing queries using the ribbon Query List.

It is a useful feature as application workbooks can contain fewer worksheets. Also, users can easily connect to new database objects after query list refresh.

The query lists can contain database objects, custom SQL codes, HTTP and text file queries.

The query list configuration views are also used to configure base tables, custom stored procedures, or custom SQL codes to save changes of data loaded from views, stored procedures, SQL codes, HTTP and text file queries.

SaveToDB Framework for Oracle Database creates database objects to configure these advanced features.

See Configuring Query List, Configuring Saving Changes.

Configuration Data Table

The SAVETODB_DEV.QUERY_LIST table is used as an additional data source for the SAVETODB_DEV.VIEW_QUERY_LIST configuration view.

CREATE TABLE SAVETODB_DEV.QUERY_LIST
(
  ID NUMBER,
  TABLE_SCHEMA VARCHAR2(30) NOT NULL,
  TABLE_NAME VARCHAR2(30) NOT NULL,
  TABLE_TYPE VARCHAR2(30) NOT NULL,
  TABLE_CODE NCLOB,
  INSERT_PROCEDURE NCLOB,
  UPDATE_PROCEDURE NCLOB,
  DELETE_PROCEDURE NCLOB,
  PROCEDURE_TYPE VARCHAR2(30),
  CONSTRAINT PK_QUERY_LIST
    PRIMARY KEY (ID)
)

The table is used to:

  • Define SQL codes for query lists.
    Specify an SQL code in the TABLE_CODE field and the CODE type in the TABLE_TYPE field.
  • Define HTTP queries for query lists.
    Specify an HTTP query in the TABLE_CODE field and the HTTP type in the TABLE_TYPE field.
  • Define text file queries for query lists.
    Specify a text file query in the TABLE_CODE field and the TEXT type in the TABLE_TYPE field
  • Define base tables to save changes of data loaded from views, stored procedures, or SQL codes, or to merge data loaded from any data source including HTTP and text file queries.
    To have the possibility to save changes, specify the table in all INSERT_PROCEDURE, UPDATE_PROCEDURE, and DELETE_PROCEDURE fields.
    To have the possibility to merge data, specify the table in the INSERT_PROCEDURE field only.
  • Define custom stored procedures or SQL codes to save changes of data loaded from views, stored procedures, or SQL codes, or to merge data loaded from any data source including HTTP and text file queries.
    All the INSERT_PROCEDURE, UPDATE_PROCEDURE, and DELETE_PROCEDURE fields must be specified for the save mode, and only the INSERT_PROCEDURE field is specified for the merge mode.

See Configuring Query List, Configuring Saving Changes.

Query List View for Developers

The SAVETODB_DEV.VIEW_QUERY_LIST view selects all objects available to the user, including SaveToDB Framework objects.

This view is suitable for developer query lists as developers can connect to any object.

Technical Details

The view uses the SYS.ALL_OBJECTS view to detect INSERT, UPDATE, and DELETE procedures using the naming convention.

You may modify the view to use the sys.obj$ view that works much faster.

Also, you may use the SAVETODB_DEV.QUERY_LIST table only to define the procedures if the used naming convention is not applicable.

The source code of the SAVETODB_DEV.VIEW_QUERY_LIST view:

CREATE OR REPLACE VIEW SAVETODB_DEV.VIEW_QUERY_LIST
(
    TABLE_SCHEMA,
    TABLE_NAME,
    TABLE_TYPE,
    TABLE_CODE,
    INSERT_PROCEDURE,
    UPDATE_PROCEDURE,
    DELETE_PROCEDURE,
    PROCEDURE_TYPE
)
AS
-- This code selects database objects
SELECT
    t.OWNER AS TABLE_SCHEMA
    , t.OBJECT_NAME AS TABLE_NAME
    , t.OBJECT_TYPE AS TABLE_TYPE
    , NULL AS TABLE_CODE

    -- Comment these lines if you select another way to get INSERT, UPDATE, and DELETE procedures
    , COALESCE(q.INSERT_PROCEDURE, CASE WHEN i.OBJECT_NAME IS NULL THEN NULL ELSE i.OWNER || '.' || i.OBJECT_NAME END) AS INSERT_PROCEDURE
    , COALESCE(q.UPDATE_PROCEDURE, CASE WHEN u.OBJECT_NAME IS NULL THEN NULL ELSE u.OWNER || '.' || u.OBJECT_NAME END) AS UPDATE_PROCEDURE
    , COALESCE(q.DELETE_PROCEDURE, CASE WHEN d.OBJECT_NAME IS NULL THEN NULL ELSE d.OWNER || '.' || d.OBJECT_NAME END) AS DELETE_PROCEDURE
    , NULL AS PROCEDURE_TYPE

    -- Use these lines if you use the sys.obj$ code
    --, COALESCE(q.INSERT_PROCEDURE, CASE WHEN i.NAME IS NULL THEN NULL ELSE t.OWNER || '.' || i.NAME END) AS INSERT_PROCEDURE
    --, COALESCE(q.UPDATE_PROCEDURE, CASE WHEN u.NAME IS NULL THEN NULL ELSE t.OWNER || '.' || u.NAME END) AS UPDATE_PROCEDURE
    --, COALESCE(q.DELETE_PROCEDURE, CASE WHEN d.NAME IS NULL THEN NULL ELSE t.OWNER || '.' || d.NAME END) AS DELETE_PROCEDURE
    --, NULL AS PROCEDURE_TYPE

    -- Use these lines if SAVETODB_DEV.QUERY_LIST is used only
    --, q.INSERT_PROCEDURE
    --, q.UPDATE_PROCEDURE
    --, q.DELETE_PROCEDURE
    --, q.PROCEDURE_TYPE
FROM
    SYS.ALL_OBJECTS t
    -- SAVETODB_DEV.QUERY_LIST contains manual data for INSERT_PROCEDURE, UPDATE_PROCEDURE, and DELETE_PROCEDURE
    LEFT OUTER JOIN SAVETODB_DEV.QUERY_LIST q ON q.TABLE_SCHEMA = t.OWNER AND q.TABLE_NAME = t.OBJECT_NAME

    -- You may comment these lines to use SAVETODB_DEV.QUERY_LIST only or sys.obj$. The view will run much faster
    LEFT OUTER JOIN SYS.ALL_OBJECTS i ON i.OWNER = t.OWNER AND i.OBJECT_NAME = REPLACE(t.OBJECT_NAME, '_SELECT', '') || '_INSERT'
    LEFT OUTER JOIN SYS.ALL_OBJECTS u ON u.OWNER = t.OWNER AND u.OBJECT_NAME = REPLACE(t.OBJECT_NAME, '_SELECT', '') || '_UPDATE'
    LEFT OUTER JOIN SYS.ALL_OBJECTS d ON d.OWNER = t.OWNER AND d.OBJECT_NAME = REPLACE(t.OBJECT_NAME, '_SELECT', '') || '_DELETE'

    -- You may use these lines instead of SYS.ALL_OBJECTS. The view will run faster
    -- INNER JOIN sys.user$ s ON s.NAME = t.OWNER
    -- LEFT OUTER JOIN sys.obj$ i ON i.TYPE# = 7 AND i.owner# = s.user# AND i.NAME = REPLACE(t.OBJECT_NAME, '_SELECT', '') || '_INSERT'
    -- LEFT OUTER JOIN sys.obj$ u ON u.TYPE# = 7 AND u.owner# = s.user# AND u.NAME = REPLACE(t.OBJECT_NAME, '_SELECT', '') || '_UPDATE'
    -- LEFT OUTER JOIN sys.obj$ d ON d.TYPE# = 7 AND d.owner# = s.user# AND d.NAME = REPLACE(t.OBJECT_NAME, '_SELECT', '') || '_DELETE'
WHERE
    t.OWNER NOT IN (
        'APEX_030200', 'APPQOSSYS', 'CTXSYS', 'DBSNMP', 'EXFSYS', 'FLOWS_FILES', 'IX', 'MDSYS', 'OLAPSYS',
        'ORDDATA', 'ORDSYS', 'ORDPLUGINS', 'OUTLN', 'OWBSYS', 'SYS', 'SYSTEM', 'SYSMAN', 'WMSYS', 'XDB'
        )
    AND t.OBJECT_TYPE IN ('TABLE', 'VIEW', 'PROCEDURE')
    AND t.OBJECT_NAME NOT LIKE '%_INSERT'
    AND t.OBJECT_NAME NOT LIKE '%_UPDATE'
    AND t.OBJECT_NAME NOT LIKE '%_DELETE'
    AND t.OBJECT_NAME NOT LIKE 'XL_%'
UNION ALL
-- This code selects non-database objects specified in the SAVETODB_DEV.QUERY_LIST table
SELECT
    q.TABLE_SCHEMA
    , q.TABLE_NAME
    , q.TABLE_TYPE
    , q.TABLE_CODE
    , q.INSERT_PROCEDURE
    , q.UPDATE_PROCEDURE
    , q.DELETE_PROCEDURE
    , q.PROCEDURE_TYPE
FROM
    SAVETODB_DEV.QUERY_LIST q
    LEFT OUTER JOIN SYS.ALL_OBJECTS t ON q.TABLE_SCHEMA = t.OWNER AND q.TABLE_NAME = t.OBJECT_NAME
WHERE
    q.TABLE_TYPE IN ('CODE', 'HTTP', 'TEXT')
    AND t.OBJECT_NAME IS NULL
ORDER BY
    TABLE_TYPE
    , TABLE_SCHEMA
    , TABLE_NAME;

Query List View for Users

The SAVETODB_XLS.VIEW_QUERY_LIST view selects all objects available to the user, excluding SaveToDB Framework and specially named objects.

This view can be used for creating derived views for different business areas.

For example:

CREATE OR REPLACE VIEW HR.VIEW_QUERY_LIST
(
    TABLE_SCHEMA,
    TABLE_NAME,
    TABLE_TYPE,
    TABLE_CODE,
    INSERT_PROCEDURE,
    UPDATE_PROCEDURE,
    DELETE_PROCEDURE,
    PROCEDURE_TYPE
)
AS
SELECT
    t.TABLE_SCHEMA
    , t.TABLE_NAME
    , t.TABLE_TYPE
    , t.TABLE_CODE
    , t.INSERT_PROCEDURE
    , t.UPDATE_PROCEDURE
    , t.DELETE_PROCEDURE
    , t.PROCEDURE_TYPE
FROM
    SAVETODB_XLS.VIEW_QUERY_LIST t
WHERE
    t.TABLE_SCHEMA IN ('HR', 'SAVETODB_XLS')
    AND NOT t.TABLE_NAME IN (
        'ADD_JOB_HISTORY'
        , 'SECURE_DML'
        )

The example view selects database objects in the certain schemas and hides service objects by name.

Event Handlers

Preface

SaveToDB allows using stored procedures, SQL codes, and HTTP queries for handling the following Microsoft Excel events:

  • Change
  • DoubleClick
  • SelectionChange

Using event handling developers can implement tasks like database data changes, drill-down queries, or master-detail interfaces.

Also, developers can customize the Microsoft Excel context menu and SaveToDB Actions menu to run:

  • Views
  • Stored procedures
  • SQL codes
  • HTTP queries
  • Text file queries
  • Macros
  • Windows Shell and CMD commands

See Configuring Event Handlers.

Configuration Data Table

The SAVETODB_DEV.EVENT_HANDLERS table contains configuration data selected via the SAVETODB_XLS.VIEW_EVENT_HANDLERS configuration view.

CREATE TABLE SAVETODB_DEV.EVENT_HANDLERS
(
  ID NUMBER,
  TABLE_SCHEMA VARCHAR2(30) NOT NULL,
  TABLE_NAME VARCHAR2(30) NOT NULL,
  COLUMN_NAME VARCHAR2(30),
  EVENT_NAME VARCHAR2(11) NOT NULL,
  HANDLER_SCHEMA VARCHAR2(30),
  HANDLER_NAME VARCHAR2(256) NOT NULL,
  HANDLER_TYPE VARCHAR2(30),
  HANDLER_CODE NCLOB,
  TARGET_WORKSHEET VARCHAR2(256),
  MENU_ORDER NUMBER(3,0),
  EDIT_PARAMETERS NUMBER(1,0),
  CONSTRAINT PK_EVENT_HANDLERS
    PRIMARY KEY (ID)
);

See field description in the Configuring Event Handlers topic.

The HANDLER_CODE field can contain SQL codes, HTTP and text queries, macro codes, or Windows Shell and CMD commands.

The recommended name format for event handling objects:

  • XL_<EVENT>_<SCHEMA>_<NAME>
  • XL_<EVENT>_<SCHEMA>_<NAME>_<COLUMN>

where <SCHEMA>_<NAME> is a base object name, <COLUMN> is a base object column, and <EVENT> has the following event name codes:

  • ACT - Actions
  • CHG - Change
  • DBL - DoubleClick
  • MNU - ContextMenu
  • SEL - SelectionChange

The objects named as XL_% are excluded from the Query List views.

Configuration View

The SAVETODB_XLS.VIEW_EVENT_HANDLERS view is used by the SaveToDB add-in to get the event handler configuration data.

The view selects the data from the SAVETODB_DEV.EVENT_HANDLERS table.

CREATE OR REPLACE VIEW SAVETODB_XLS.VIEW_EVENT_HANDLERS
(
    ID,
    TABLE_SCHEMA,
    TABLE_NAME,
    COLUMN_NAME,
    EVENT_NAME,
    HANDLER_SCHEMA,
    HANDLER_NAME,
    HANDLER_TYPE,
    HANDLER_CODE,
    TARGET_WORKSHEET,
    MENU_ORDER,
    EDIT_PARAMETERS
)
AS
SELECT
    t.ID,
    t.TABLE_SCHEMA,
    t.TABLE_NAME,
    t.COLUMN_NAME,
    t.EVENT_NAME,
    t.HANDLER_SCHEMA,
    t.HANDLER_NAME,
    t.HANDLER_TYPE,
    t.HANDLER_CODE,
    t.TARGET_WORKSHEET,
    t.MENU_ORDER,
    t.EDIT_PARAMETERS
FROM
    SAVETODB_DEV.EVENT_HANDLERS t;

The ID field is not required by the SaveToDB add-in. It supports the possibility to save data changes from the view and can be used in OData web services.

Data Translation

Preface

SaveToDB allows translating names of database objects, object fields, and stored procedure parameters within Microsoft Excel.

It is a useful feature as the names can be changed in configuration tables without changes of underlying database objects.

In addition, the translation data are used in ScreenTips and table header comments.

See Configuring Data Translation.

Object Translation Table

The SAVETODB_DEV.OBJECT_TRANSLATION table contains database object translation data selected via the SAVETODB_XLS.VIEW_OBJECT_TRANSLATION configuration view.

CREATE TABLE SAVETODB_DEV.OBJECT_TRANSLATION
(
  ID NUMBER,
  TABLE_SCHEMA VARCHAR2(30) NOT NULL,
  TABLE_NAME VARCHAR2(30) NOT NULL,
  LANGUAGE_NAME CHAR(2) NOT NULL,
  TRANSLATED_NAME NVARCHAR2(61),
  TRANSLATED_DESC NVARCHAR2(256),
  TRANSLATED_COMMENT NVARCHAR2(2000),
  CONSTRAINT PK_OBJECT_TRANSLATION
    PRIMARY KEY (ID)
);

CREATE UNIQUE INDEX SAVETODB_DEV.IX_OBJECT_TRANSLATION_S_N_L
  ON SAVETODB_DEV.OBJECT_TRANSLATION (
  TABLE_SCHEMA,
  TABLE_NAME,
  LANGUAGE_NAME
);

See field description in the Configuring Data Translation topic.

The size of the TRANSLATED_* fields can be changed.

Object Translation Configuration View

The SAVETODB_XLS.VIEW_OBJECT_TRANSLATION view is used by the SaveToDB add-in to get the object translation data.

The view selects the data from the SAVETODB_DEV.OBJECT_TRANSLATION table.

CREATE OR REPLACE VIEW SAVETODB_XLS.VIEW_OBJECT_TRANSLATION
(
  ID,
  TABLE_SCHEMA,
  TABLE_NAME,
  LANGUAGE_NAME,
  TRANSLATED_NAME,
  TRANSLATED_DESC,
  TRANSLATED_COMMENT
)
AS
  SELECT
    t.ID,
    t.TABLE_SCHEMA,
    t.TABLE_NAME,
    t.LANGUAGE_NAME,
    t.TRANSLATED_NAME,
    t.TRANSLATED_DESC,
    t.TRANSLATED_COMMENT
  FROM
    SAVETODB_DEV.OBJECT_TRANSLATION t
  ORDER BY
    t.LANGUAGE_NAME,
    t.TABLE_SCHEMA,
    t.TABLE_NAME;

The ID field is not required by the SaveToDB add-in. It supports the possibility to save data changes from the view and can be used in OData web services.

Column Translation Table

The SAVETODB_DEV.COLUMN_TRANSLATION table contains field and parameter translation data selected via the SAVETODB_XLS.VIEW_COLUMN_TRANSLATION configuration view.

CREATE TABLE SAVETODB_DEV.COLUMN_TRANSLATION
(
  ID NUMBER,
  TABLE_SCHEMA VARCHAR2(30),
  TABLE_NAME VARCHAR2(30),
  COLUMN_NAME VARCHAR2(30) NOT NULL,
  LANGUAGE_NAME CHAR(2) NOT NULL,
  TRANSLATED_NAME NVARCHAR2(61),
  TRANSLATED_DESC NVARCHAR2(1024),
  CONSTRAINT PK_COLUMN_TRANSLATION
    PRIMARY KEY (ID)
);

CREATE UNIQUE INDEX SAVETODB_DEV.IX_COLUMN_TRANSLATION_S_N_C_L
  ON SAVETODB_DEV.COLUMN_TRANSLATION (
  TABLE_SCHEMA,
  TABLE_NAME,
  COLUMN_NAME,
  LANGUAGE_NAME
);

See field description in the Configuring Data Translation topic.

Column Translation Configuration View

The SAVETODB_XLS.VIEW_COLUMN_TRANSLATION view is used by the SaveToDB add-in to get the column translation data.

The view selects the data from the SAVETODB_DEV.COLUMN_TRANSLATION table.

CREATE OR REPLACE VIEW SAVETODB_XLS.VIEW_COLUMN_TRANSLATION
(
  ID,
  TABLE_SCHEMA,
  TABLE_NAME,
  COLUMN_NAME,
  LANGUAGE_NAME,
  TRANSLATED_NAME,
  TRANSLATED_DESC
)
AS
  SELECT
    t.ID,
    t.TABLE_SCHEMA,
    t.TABLE_NAME,
    t.COLUMN_NAME,
    t.LANGUAGE_NAME,
    t.TRANSLATED_NAME,
    t.TRANSLATED_DESC
  FROM
    SAVETODB_DEV.COLUMN_TRANSLATION t
  ORDER BY
    t.TABLE_SCHEMA,
    t.TABLE_NAME,
    t.COLUMN_NAME;

The ID field is not required by the SaveToDB add-in. It supports the possibility to save data changes from the view and can be used in OData web services.

Object Description View

The SAVETODB_XLS.VIEW_OBJECT_DESCRIPTION view selects translation data from the SAVETODB_DEV.OBJECT_TRANSLATION table for objects available to users for connection.

This view can be used as an Excel application home page as a user can read object descriptions and connect to any object using the ribbon Query List.

Parameter Values

Preface

SaveToDB allows using views, stored procedures, or SQL codes to populate ribbon parameter values.

The ribbon parameters are used to change parameters of stored procedures, SQL codes, HTTP and text file queries, or WHERE fields for tables and views.

See Configuring Parameter Values.

Configuration Data Table

The SAVETODB_DEV.PARAMETER_VALUES table contains configuration data selected via the SAVETODB_XLS.VIEW_PARAMETER_VALUES configuration view.

CREATE TABLE SAVETODB_DEV.PARAMETER_VALUES
(
  ID NUMBER NOT NULL,
  TABLE_SCHEMA VARCHAR2(30) NOT NULL,
  TABLE_NAME VARCHAR2(30) NOT NULL,
  PARAMETER_NAME VARCHAR2(30) NOT NULL,
  SELECT_SCHEMA VARCHAR2(30),
  SELECT_NAME VARCHAR2(30) NOT NULL,
  SELECT_TYPE VARCHAR2(10),
  SELECT_CODE NCLOB,
  CONSTRAINT PK_PARAMETER_VALUES
    PRIMARY KEY (ID)
);

CREATE UNIQUE INDEX SAVETODB_DEV.IX_PARAMETER_VALUES_S_N_P
  ON SAVETODB_DEV.PARAMETER_VALUES (
    TABLE_SCHEMA,
    TABLE_NAME,
    PARAMETER_NAME
);

See field description in the Configuring Parameter Values topic.

The recommended name format for objects used in the SELECT_NAME field:

  • XL_VAL_<PARAMETER_NAME>

Such objects are excluded from the Query List views.

The SELECT_CODE field can contain SQL codes. In this case, the SELECT_TYPE field must contain the CODE type.

Configuration View

The SAVETODB_XLS.VIEW_PARAMETER_VALUES view is used by the SaveToDB add-in to get the configuration data.

The view selects the data from the SAVETODB_DEV.PARAMETER_VALUES table.

CREATE OR REPLACE VIEW SAVETODB_XLS.VIEW_PARAMETER_VALUES
(
    ID,
    TABLE_SCHEMA,
    TABLE_NAME,
    PARAMETER_NAME,
    SELECT_SCHEMA,
    SELECT_NAME,
    SELECT_TYPE,
    SELECT_CODE
)
AS
SELECT
    t.ID,
    t.TABLE_SCHEMA,
    t.TABLE_NAME,
    t.PARAMETER_NAME,
    t.SELECT_SCHEMA,
    t.SELECT_NAME,
    t.SELECT_TYPE,
    t.SELECT_CODE
FROM
    SAVETODB_DEV.PARAMETER_VALUES t;

The ID field is not required by the SaveToDB add-in. It supports the possibility to save data changes from the view and can be used in OData web services.

Table Formats

Preface

SaveToDB allows storing and distributing Microsoft Excel table formats of database objects using a database.

Database objects are formatted in Microsoft Excel when the objects are connected for the first time, or when required using the Table Format Wizard.

Table formats include the following elements:

  • Cell format.
  • Conditional formatting.
  • Formula columns.
  • Table totals.
  • Applied auto-filters.
  • Print page setup.
  • Window properties.
  • Column visibility.
  • Custom table views.

Use the Table Format Wizard to save, clear, or apply table formats.

See also Configuring Table Formats.

Configuration Data Table

The SAVETODB_DEV.TABLE_FORMATS table contains Microsoft Excel table formats of database objects.

CREATE TABLE SAVETODB_DEV.TABLE_FORMATS
(
  ID NUMBER,
  TABLE_SCHEMA VARCHAR2(30) NOT NULL,
  TABLE_NAME VARCHAR2(30) NOT NULL,
  TABLE_EXCEL_FORMAT_XML NCLOB,
  CONSTRAINT PK_TABLE_FORMATS
    PRIMARY KEY (ID)
);

CREATE UNIQUE INDEX SAVETODB_DEV.IX_TABLE_FORMATS_S_N
    ON SAVETODB_DEV.TABLE_FORMATS (
    TABLE_SCHEMA,
    TABLE_NAME
);

The SAVETODB_DEV.TABLE_FORMAT_UPDATE procedure is used to update the data.

Configuration View

The SAVETODB_XLS.VIEW_TABLE_FORMATS view is used by the SaveToDB add-in to get the table formats.

The view selects table formats from the SAVETODB_DEV.TABLE_FORMATS table.

CREATE OR REPLACE VIEW SAVETODB_XLS.VIEW_TABLE_FORMATS
(
  TABLE_SCHEMA,
  TABLE_NAME,
  TABLE_EXCEL_FORMAT_XML
)
AS
  SELECT
    TABLE_SCHEMA,
    TABLE_NAME,
    TABLE_EXCEL_FORMAT_XML
  FROM
    SAVETODB_DEV.TABLE_FORMATS;

The ID field is not required by the SaveToDB add-in. It supports the possibility to save data changes from the view and can be used in OData web services.

Configuration Procedure to Save Table Formats

The SAVETODB_DEV.TABLE_FORMAT_UPDATE procedure is used by the SaveToDB add-in to update the SAVETODB_DEV.TABLE_FORMATS table.

Procedure declaration:

CREATE PROCEDURE SAVETODB_DEV.TABLE_FORMAT_UPDATE (
    Schema IN NVARCHAR2,
    Name IN NVARCHAR2,
    ExcelFormatXML NCLOB
    )

Version History

Version 7.2, March 20, 2017

[+] New types of event handlers: ConvertFormulas and DoNotConvertFormulas.
[*] Added validation lists for the QUERY_LIST, EVENT_HANDLERS, and PARAMETER_VALUES tables and derived views.
[*] The EVENT_HANDLERS.HANDLER_NAME field may have NULL values.

Version 7.1, February 3, 2017

[*] New VALUES and RANGE types in the SELECT_TYPE field.
[*] New VALUES and RANGE types in the HANDLER_TYPE field.

Version 7.0, January 17, 2017

[*] Updated batch files and readme.txt.
SQL codes have no changes.

Version 6.9, June 21, 2016

[+] A new event handler type: ProtectRows.

Version 6.7, April 14, 2016

[+] The framework includes updated validation lists of the EVENT_NAME column of the event handler configuration tables.
Use savetodb-framework-62-upgrade-to-67.sql to update the existing frameworks.
Use Table Format Wizard to reload validation lists of existing Excel configuration tables.
Also, you may create new configuration workbooks using Configuration Workbook Generator.

Version 6.2, December 23, 2015

[+] New event handler types: DoNotSelect, DoNotSave, DoNotChange, Formula, FormulaValue, and ValidationList.
[*] SAVETODB_DEV.VIEW_QUERY_LIST excludes objects like '%_change'.
[*] You may use the code in the SaveToDB Framework Upgrade 5.0 to 6.2 folder to upgrade existing SaveToDB Frameworks 5.0.

Version 5.2, May 12, 2015

[*] Batch files have been updated.
[*] Procedures with the _MERGE suffixes are not shown in the SAVETODB_DEV.VIEW_QUERY_LIST and SAVETODB_XLS.VIEW_QUERY_LIST views.

Version 5.0, April 13, 2015

[*] The TABLE_CODE and PROCEDURE_TYPE fields have been added to the SAVETODB_DEV.QUERY_LIST table and VIEW_QUERY_LIST views.
[*] Types of the INSERT_PROCEDURE, UPDATE_PROCEDURE, and DELETE_PROCEDURE have been changed to NCLOB to hold large SQL codes.
[*] The HANDLER_CODE field has been added to the SAVETODB_DEV.EVENT_HANDLERS table and the SAVETODB_XLS.VIEW_EVENT_HANDLERS view.
[*] The SELECT_CODE field has been added to the SAVETODB_DEV.PARAMETER_VALUES table and the SAVETODB_XLS.VIEW_PARAMETER_VALUES view.
[*] The SPECIFIC_SCHEMA and SPECIFIC_NAME fields have been renamed to TABLE_SCHEMA and TABLE_NAME in the SAVETODB_DEV.PARAMETER_VALUES table and the SAVETODB_XLS.VIEW_PARAMETER_VALUES view.
[*] Types of the TABLE_SCHEMA and HANDLER_SCHEMA fields have been changed to VARCHAR2(30).

Version 4.7, July 15, 2014

[*] The batch files have been updated for using the gsqlcmd.exe command line utility instead of sqlplus.exe.
As a result, the framework operations can be executed on a machine without Oracle components installed.
[-] Issues with SAVETODB_DEV.VIEW_QUERY_LIST and SAVETODB_XLS.VIEW_QUERY_LIST have been fixed.

Version 4.0, December 13, 2013

[*]The lengths of the INSERT_PROCEDURE, UPDATE_PROCEDURE and DELETE_PROCEDURE fields of the SAVETODB_DEV.QUERY_LIST table have been increased to 2000 characters.
These fields can contain SQL codes to save data changes. You may increase the field length if needed.
[*]The SAVETODB_DEV.VIEW_QUERY_LIST view has been changed.
The view selects INSERT_PROCEDURE, UPDATE_PROCEDURE, and DELETE_PROCEDURE for database objects from the SAVETODB_DEV.QUERY_LIST table.
This change significantly improves performance but requires editing the SAVETODB_DEV.QUERY_LIST table to insert existing procedures.
The view allows selecting SQL codes, HTTP and text file queries (CODE, HTTP and TEXT types).
[*]The SAVETODB_XLS.VIEW_QUERY_LIST view has been changed.
The view selects data from the SAVETODB_DEV.VIEW_QUERY_LIST view.
[*]The size of the TARGET_WORKSHEET field of the SAVETODB_DEV.EVENT_HANDLERS table has been changed to 256 characters.
This field can contain a sheet list to refresh data if the REFRESH handler is specified.
[*]Unique indexes to SAVETODB_DEV.PARAMETER_VALUES, SAVETODB_DEV.OBJECT_TRANSLATION, SAVETODB_DEV.COLMN_TRANSLATION, and SAVETODB_DEV.TABLE_FORMATS have been added.
[*]Actions menu links to online help for framework tables and views have been added.

Version 3.0, June 10, 2013

[!]Framework object names have been changed to unify naming. See the name changes below.
[+]SaveToDB Framework can be installed or uninstalled using the SaveToDB Framework Installer wizard within Microsoft Excel or using the standalone SaveToDB Framework Installer.
[+]SaveToDB Framework tables can be edited using SaveToDB Framework configuration workbooks that are generated using the Configuration Workbook Generator wizard.
[*]The event handler configuration table and view have new fields to configure new SaveToDB 3.0 features.
[*]Some SAVETODB_DEV.OBJECT_TRANSLATION and SAVETODB_DEV.COLUMN_TRANSLATION field sizes have been increased.
[*]The SAVETODB_DEV.VIEW_QUERY_LIST view has been added. The view does not exclude framework objects.
The view is useful for Excel application developers.
[*]The SAVETODB_DEV.QUERY_LIST has been added to configure SQL codes and HTTP queries of Query Lists and to configure tables used to save data changes of views and stored procedures by built-in SaveToDB procedures.
The table is an additional data source for the SAVETODB_DEV.VIEW_QUERY_LIST and SAVETODB_XLS.VIEW_QUERY_LIST views.
[!]The SAVETODB_XLS.VIEW_PARAMETER_VALUES view is redesigned.
The view selects data from the new SAVETODB_DEV.PARAMETER_VALUES table.
So, all the framework configuration views use data from underlying configuration tables.
[*]Framework objects are documented in translation tables.
So, the objects are well documented in Microsoft Excel.
[*]The SAVETODB_XLS.VIEW_OBJECT_DESCRIPTION view has been added to select object descriptions for users.
The view is suitable for using as a home page of Excel applications.
[*]The documentation has been moved from the readme.htm and *.sql files to this help file.
[+]The auto-generated framework-setup-en.sql file has been added for manual install using visual developer tools.

SaveToDB Framework for Oracle Database object name changes:

SaveToDB Framework 2.0SaveToDB Framework 3.0
SAVETODB_DEV.TRANSLATION_OBJECTSSAVETODB_DEV.OBJECT_TRANSLATION
SAVETODB_DEV.TRANSLATION_COLUMNSSAVETODB_DEV.COLUMN_TRANSLATION
SAVETODB_DEV.EXCEL_EVENT_HANDLERSSAVETODB_DEV.EVENT_HANDLERS
SAVETODB_DEV.EXCEL_TABLE_FORMATSSAVETODB_DEV.TABLE_FORMATS
SAVETODB_XLS.VIEW_TRANSLATION_OBJECTSSAVETODB_XLS.VIEW_OBJECT_TRANSLATION
SAVETODB_XLS.VIEW_TRANSLATION_COLUMNSSAVETODB_XLS.VIEW_COLUMN_TRANSLATION
SAVETODB_XLS.VIEW_EXCEL_EVENT_HANDLERSSAVETODB_XLS.VIEW_EVENT_HANDLERS
SAVETODB_XLS.VIEW_EXCEL_TABLE_FORMATSSAVETODB_XLS.VIEW_TABLE_FORMATS
SAVETODB_XLS.VIEW_PARAMETER_VALUES_QUERIESSAVETODB_XLS.VIEW_PARAMETER_VALUES
SAVETODB_DEV.EXCEL_TABLE_FORMAT_UPDATESAVETODB_DEV.TABLE_FORMAT_SAVE

System Requirements

Supported Versions of SaveToDB:

  • SaveToDB 5.0 and higher

Supported Versions of Oracle Database:

  • Oracle Database 10g Release 1, Release 2
  • Oracle Database 11g Release 1, Release 2
  • Oracle Database 12c Release 1

SaveToDB Examples for Oracle Database

The SaveToDB SDK setup package contains applications and examples for Oracle Database.

You may deploy the applications to an Oracle Database using batch files or source codes.

Monthly Cash Flow Application

This application allows getting money transactions in one place and building cash flow statements.

The application is a good example of a feature-rich application built using Oracle Database as a back-end and Microsoft Excel as a front-end.

Oracle HR Example

The Oracle HR example allows developers to test SaveToDB features for working with Oracle Database.

Data connection features:

  • Connecting to Oracle Database tables, views, and stored procedures.

Saving data changes:

  • Saving data changes of database tables back to a database.
  • Saving data changes of database views and stored procedures to a base table.
  • Saving data changes of database views and stored procedures to a database using custom INSERT, UPDATE, and DELETE procedures.
  • Saving data changes using stored procedures as Excel cell change event handlers.

Query builder:

  • Changing queries using the ribbon Query List.
  • Saving table formats of closed queries and applying saved formats when the queries are reopened.
  • Customizing SELECT fields for tables and views.
  • Changing WHERE filters of the table and view queries using the ribbon parameters.
  • Changing stored procedure parameters using the ribbon parameters.
  • Changing parameters using the Query Parameters dialog box.
  • Updating named cells with parameter values.

Configuring user interface using a database:

  • Configuring the ribbon Actions menu to run context views, stored procedures, and HTTP queries.
  • Configuring the Microsoft Excel context menu to run context views, stored procedures, and HTTP queries.
  • Configuring drill-down queries on the Double-Click event.
  • Configuring query output targets like a sheet, the default web browser, a data window, or an internal browser.
  • Generating HTML documents within a database, including ready-to-print official documents.
  • Configuring the ribbon Query List using database views.
  • Configuring table and view fields to customize WHERE filters using the ribbon.
  • Populating parameter values using views and stored procedures.
  • Updating named cells with query data.
  • Distributing Excel table formats of database objects from a database.
  • Translating database object names and descriptions to any language within Microsoft Excel using database translation views.
  • Translating database object fields and parameters to any language within Microsoft Excel using database translation views.

Installing and Uninstalling Examples

Installing Application Examples for Oracle Database

Use the following steps to install the application examples for Oracle Database:

  1. Install example database objects.
  2. Change connection data in Microsoft Excel example workbooks.

Installing Examples

The source codes of examples for Oracle Database are located in the "source code" subfolders.

To install the example, open the subfolder and do the following:

  1. Run 1-edit-config.cmd and edit the "setup" connection.
  2. Run 2-edit-grants.cmd and edit example users if applicable.
  3. Run 3-install-savetodb-framework.cmd if SaveToDB Framework for Oracle Database is not installed yet.
  4. Run 4-install-application.cmd.
  5. Run 5-clear-credentials.cmd and clear the "setup" connection password.

You may select in the first step the target server and database, and the setup credentials.

The "setup" connection user must have database administrator permissions (root).

Instead of clearing the setup password on the last step, you may protect gsqlcmd.exe.config by selecting the "User protected" option in gConnectionManager.

Alternatively to batch files, you may use queries from the application-install.sql file to install the examples.

Changing Excel Workbook Connections

Examples contain preconfigured Excel workbooks.

You should change the connection server, database, username and the password in the workbooks if you have changed the default settings in the previous step.

You may use the Change Connection Wizard to modify all connections at once:

  1. Select the SaveToDB tab and run the Change Connection Wizard,
  2. Connect to the target database using the new target username and password.
  3. Select all the connections and click Finish.

Then you should refresh all the data tables. You may use the Reload, Reload All Workbook Tables menu item.

Microsoft Excel workbooks are configured to connect using the Orcl service. Instead of changing a server you may configure the Orcl service on the machine.

Default usernames and passwords:

ExampleDeveloper loginDeveloper passwordUser loginUser password
Oracle HRSAVETODB_DEMO_DEVDev_2011#_Xls4168SAVETODB_DEMO_USERUsr_2011#_Xls4168
Monthly Cash FlowPAYMENTS_DEVDev_2011#_Xls4168PAYMENTS_USERUsr_2011#_Xls4168

Uninstalling Application Examples for Oracle Database

To uninstall the application example, open the "source code" folder and do the following:

  1. Run 1-edit-config.cmd and edit the "setup" connection.
  2. Run application-remove.cmd.
  3. Run 5-clear-credentials.cmd and clear the "setup" connection password.

The setup connection user must have database administrator permissions.

Instead of clearing the setup password on the last step, you may protect gsqlcmd.exe.config by selecting the "User protected" option in gConnectionManager.

Alternatively to batch files, you may use queries from the application-remove.sql file.

Command Line Utility gsqlcmd

The batch files to install and remove the examples use the command line utility gsqlcmd.
gsqlcmd allows executing SQL scripts and exporting data against all SaveToDB supported database platforms including Oracle Database.

SaveToDB installs gsqlcmd and changes the PATH environment variable that allows using gsqlcmd anywhere.

SaveToDB SDK includes the gsqlcmd folder with the executables, and the utility is available from the SaveToDB SDK subfolders only.

Version History

Version 7.2, March 20, 2017

[*] Updated SaveToDB Framework 7.2.

Version 7.0, January 17, 2017

[*] Updated SaveToDB Framework 7.0, batch files, and readme.txt.
[*] Updated example dates.

System Requirements

Supported Versions of SaveToDB:

  • SaveToDB 5.0 and higher

Supported Versions of SaveToDB Framework for Oracle Database:

  • SaveToDB Framework 5.0 for Oracle Database and higher

Supported Versions of Oracle Database:

  • Oracle Database 10g Release 1, Release 2
  • Oracle Database 11g Release 1, Release 2
  • Oracle Database 12c Release 1

Prerequisites:

  • The test database from the Oracle Database setup package