416.467.9100 | Dundas Data Visualization | Login
Welcome Guest
This is the support page for the legacy Dundas Dashboard application. For assistance with the current Dundas BI application please click here.
Dashboard v5.0

This site makes extensive use of JavaScript.

Please enable JavaScript in your web browser and reload the page before proceeding.

Data Provider - Oracle

Hide navigation
RSS
Modified on Wed, 30 Apr 2014 10:11 AM Categorized as Data Connectors
Click to return to: Documentation | Connecting to Data | Creating a Data Connector


Overview

Dundas Dashboard can connect to Oracle 10g or 11g.

Connecting to Oracle

From the General tab of the Data Connector Designer screen, configure the data provider for Oracle as follows:

  1. Click inside the Server name auto-complete box. A discovery of available servers on your network is initiated. A dropdown menu will appear if one or more servers are found.
  2. Choose a server name from the resulting dropdown menu (if applicable) or type the name of the Oracle server instance in the Server name box.
  3. Enter a username and password in the boxes provided.

Oracle provider option.

Oracle provider option.


Oracle stored procedures

In order for an Oracle stored procedure to be discoverable in Dundas Dashboard, it must have a single OUT SYS_RECURSOR parameter.

For example:

create or replace PROCEDURE "TERRITORIES_BY_REGION" ( RegionId NUMBER, p_recordset OUT SYS_REFCURSOR ) AS BEGIN OPEN p_recordset FOR SELECT * FROM "MARKETING"."TERRITORIES" WHERE REGIONID = TERRITORIES_BY_REGION.RegionId ORDER BY 1; END TERRITORIES_BY_REGION;

Oracle functions

In order for an Oracle function to be discoverable in Dundas Dashboard, the function must be a pipelined function. A pipelined function can be used as if it were a database table and will generally have better performance than stored procedures because there is no need to create temporary tables.

Here is an example of creating a pipelined function in Oracle:

  1. Use this table as a base.

    CREATE TABLE "MARKETING"."TERRITORIES"
    (
    "TERRITORYID" VARCHAR2(20 BYTE),
    "TERRITORYDESCRIPTION" VARCHAR2(50 BYTE),
    "REGIONID" NUMBER
    )
  2. Create a type that describes a table row.

    type MARKETING.TERRITORIESROW as object (
    "TERRITORYID" VARCHAR2(20 BYTE),
    "TERRITORYDESCRIPTION" VARCHAR2(50 BYTE),
    "REGIONID" NUMBER);
  3. Create a type that describes a table.

    create or replace
    type TERRITORIESTABLE as table of MARKETING.TERRITORIESROW;
  4. Create a pipelined function.

    CREATE OR REPLACE FUNCTION TERRITORIES_BY_REGION_FUNC
    (
    RegionId IN NUMBER
    ) RETURN TERRITORIESTABLE PIPELINED
    AS
    CURSOR Territories IS
    SELECT *
    FROM "MARKETING"."TERRITORIES"
    WHERE REGIONID = TERRITORIES_BY_REGION_FUNC.RegionId
    ORDER BY 1;

    TerritoriesRecord "MARKETING"."TERRITORIES"%rowtype;
    BEGIN
    OPEN TERRITORIES_BY_REGION_FUNC.Territories;

    LOOP
    fetch Territories into TerritoriesRecord;
    exit when (Territories%notfound);
    pipe row (TERRITORIESROW(
    TerritoriesRecord.TERRITORYID,
    TerritoriesRecord.TERRITORYDESCRIPTION,
    TerritoriesRecord.REGIONID)
    );
    END LOOP;
    END TERRITORIES_BY_REGION_FUNC;


Notes

  • Dundas Dashboard 3.0 or later supports discovery of functions and stored procedures within Oracle packages.

Related topics


Click to return to: Documentation | Connecting to Data | Creating a Data Connector

About Dundas | Contact Us Follow us on Twitter! | Privacy Statement | Report Site Issues

Copyright © 2009-2014 Dundas Data Visualization, Inc.