Home » Categories » CHARTrunner » CR Data Source

CR: Using Oracle Stored Procedures

Revision Date: 13-April-2006

Q. Can CHARTrunner use an Oracle stored procedure as the data source for a chart?

CHARTrunner version 1.6.87 (and higher) has been configured to work with Oracle stored procedures.  Prior versions of CHARTrunner may experience various problems when dealing with Oracle stored procedures, particularly with respect to stored procedures having input parameters.

Q. What data providers are available for fetching data from Oracle?  Which are recommended?

PQ knows of the following four data providers that can be used with Oracle:

  1. Oracle Provider for OLE DB (recommended)
  2. Oracle ODBC Driver
  3. Microsoft OLE DB Provider for Oracle
  4. Microsoft ODBC for Oracle

PQ recommends using the Oracle Provider for OLE DB (download) because we have had the best results using that data provider with our test Oracle8i database.  We have had some success using the Microsoft OLE DB Provider for Oracle which is installed as part of the MDAC (Microsoft Data Access Components) distribution.  Our results with Oracle stored procedures using either the Oracle ODBC Driver or the Microsoft ODBC for Oracle driver have been poor.

Q. How should an Oracle stored procedure be defined in order to work with CHARTrunner?

The Sql*Plus script file CR_SP_TEST.SQL is available in the attachment to this article.  This script creates a sample data table, a user function and some stored procedures that can be used to fetch data for your chart.

The general scheme is that a variable of type REF CURSOR (i.e. reference cursor) is used as an OUT parameter of the stored procedure to return the data for the chart. Optionally, the stored procedure can have one or more IN parameters that can be used to define what data the stored procedure is to fetch from the database.  REF CURSORs are supported by Oracle 8.0.5 (or later) server.

First, define a REF CURSOR type in an Oracle package, as shown below.  In this example, the package name is PKG_CR, but any valid package name may be used.  In this example a type called CURSOR_TYPE is defined, but there is nothing special about the name CURSOR_TYPE, and it could just as easily have been called C_RefCur.

CREATE OR REPLACE PACKAGE PKG_CR AS
   TYPE CURSOR_TYPE IS REF CURSOR;
END PKG_CR;

Next, create your stored procedure having an output reference cursor of type PKG_CR.CURSOR_TYPE.

Stored procedure SP_CR_TEST1 (show below) has a single reference cursor output parameter O_RESULT_SET that passes the selected chart data back to CHARTrunner.  This stored procedure has no input parameters.

CREATE OR REPLACE 
   PROCEDURE SP_CR_TEST1 (O_RESULT_SET OUT PKG_CR.CURSOR_TYPE)
   AS
   BEGIN
     OPEN O_RESULT_SET FOR
        SELECT * FROM CR_TEST_TABLE1 ORDER BY ID;
   END SP_CR_TEST1;

Stored procedure SP_CR_TEST1C (show below) is similar to the previous example but has two date input parameters that are used to fetch chart data that falls within the date range between I_DATE1 and I_DATE2.

CREATE OR REPLACE 
   PROCEDURE SP_CR_TEST1C (I_DATE1 IN DATE, I_DATE2 IN DATE, O_RESULT_SET OUT PKG_CR.CURSOR_TYPE)
   AS
   BEGIN
     OPEN O_RESULT_SET FOR
        SELECT * FROM CR_TEST_TABLE1
           WHERE DATETIME BETWEEN I_DATE1 AND I_DATE2
           ORDER BY ID;
   END SP_CR_TEST1C;

You can also define a user function that returns a reference cursor.  The following example creates FN_CR_TEST1C which is similar to the previous example.

CREATE OR REPLACE
   FUNCTION FN_CR_TEST1C (I_DATE1 IN DATE, I_DATE2 IN DATE) RETURN PKG_CR.CURSOR_TYPE
   AS 
      O_RESULT_SET  PKG_CR.CURSOR_TYPE; 
   BEGIN 
      OPEN O_RESULT_SET FOR
         SELECT * FROM CR_TEST_TABLE1 
            WHERE DATETIME BETWEEN I_DATE1 AND I_DATE2 
            ORDER BY ID;
   RETURN O_RESULT_SET; 
   END FN_CR_TEST1C;

Q. Does CHARTrunner support an Oracle stored procedure that returns multiple reference cursors?

It is possible to create a stored procedure that returns multiple reference cursors, such as in the following example where two reference cursors are returned:

CREATE OR REPLACE 
   PROCEDURE SP_CR_MULTI_REF_CURSORS (I_DATE1 IN DATE, O_RESULT_SET1 OUT PKG_CR.CURSOR_TYPE,
      O_RESULT_SET2 OUT PKG_CR.CURSOR_TYPE)
   AS
   BEGIN
     OPEN O_RESULT_SET1 FOR
        SELECT * FROM CR_TEST_TABLE1
           WHERE DATETIME < I_DATE1
           ORDER BY ID;
     OPEN O_RESULT_SET2 FOR
        SELECT * FROM CR_TEST_TABLE1
           WHERE DATETIME >= I_DATE1
           ORDER BY ID;
   END SP_CR_MULTI_REF_CURSORS;

In the example shown above, using the Oracle Provider for OLE DB, CHARTrunner uses the reference cursor returned via O_RESULT_SET1 and ignores the reference cursor returned via O_RESULT_SET2.  It is unclear at this time whether different data providers behave differently.  It is probably best to only return one reference cursor to avoid any chance of confusion.

Q. How should the CHARTrunner chart definition be setup to use query parameters with an Oracle stored procedure?

Several sample CHARTrunner 1.6 chart definition files are available in the attachment to this article to illustrate how to fetch data from the stored procedures and user function created by the CR_SP_TEST.SQL script.  It will be necessary to modify the connect string and User/Password on the "Data source" tab appropriately for your site in order to connect to your Oracle server using these sample chart definitions.  You will also need to run the attached Sql*Plus script CR_SP_TEST.SQL in order to create the sample data table and stored procedures used by the charts.

Setting Up the CHARTrunner "Data source" Tab

See the knowledgebase article Oracle Data Source for instructions on how to setup the "Data source" tab of the CHARTrunner chart definition.

If the Oracle Provider for OLE DB is used (which we recommend) then you must include PLSQLRSet=1 in the connection string (as shown in the example below) so that the driver binds automatically to the REF CURSOR variable that returns the chart data from the stored procedure. 

Provider=oraoledb.oracle;Data Source=oracle8.empire;PLSQLRSet=1;User ID={{USER}};Password={{PASSWORD}};

Setting Up the CHARTrunner "Data definition" Tab

You might expect that you could simply specify "Stored procedure" as the "Source for data records", select the desired stored procedure, and proceed as normal.  However, due to limitations in the Oracle driver software, CHARTrunner is unable to determine needed information about each input parameter, and this lack of knowledge results in errors.

In order to use a stored procedure or user function that has input parameters it is necessary to specify a CHARTrunner "Custom query" that looks like this:

{ CALL SP_CR_TEST1B(10) }

This example calls the stored procedure SP_CR_TEST1B and passes the value 10 for the lower bound of the ID column.

The following is an example of calling the stored procedure SP_CR_TEST1C and passing in lower and upper date bounds for the chart's data:

{ CALL SP_CR_TEST1C( TO_DATE('01-01-2004', 'MM-DD-YYYY'), 
                     TO_DATE('01-11-2004 23:59:59', 'MM-DD-YYYY HH24:MI:SS')) }
Using Custom Parameters

At this point you may be asking, can CHARTrunner prompt me for the values of the input parameters when using this { CALL ... } scheme?  The answer is yes when you use Custom Parameters.

The following example is identical to the previous one, but this time we specify two Custom Parameters in the query:

{ CALL SP_CR_TEST1C( TO_DATE('{{Starting Date}}', 'MM-DD-YYYY'), 
                     TO_DATE('{{Ending Date}} 23:59:59', 'MM-DD-YYYY HH24:MI:SS')) }

When CHARTrunner examines the custom query shown above, it treats anything enclosed by "{{" and "}}" as the name of a Custom Parameter that can be dealt with via the Parameters button on the "Data definition" tab.  As CHARTrunner fetches the data for the chart, it substitutes the appropriate parameter value in place of {{Starting Date}} and {{Ending Date}}.  For example, if the value of the Starting Date parameter is 03-15-2003 and the value of the Ending Date parameter is 04-15-2003, then the query that is actually passed to the Oracle database engine is:

{ CALL SP_CR_TEST1C( TO_DATE('03-15-2003', 'MM-DD-YYYY'), 
                     TO_DATE('04-15-2003 23:59:59', 'MM-DD-YYYY HH24:MI:SS')) }

Consider what happens while you are in the process of defining the chart definition that uses the query shown above.  When you click the OK button on the Custom query editor window CHARTrunner adds the Starting Date and Ending Date parameters to its list of parameters used by the chart.  When CHARTrunner adds a new parameter it defaults the parameter's value to be the NULL value.  Then CHARTrunner attempts to verify that the custom query you have entered is valid by passing the query to the database engine to see if it reports any errors.  But, when NULL is substituted in place of {{Starting Date}} and {{Ending Date}}the resulting query (shown below) is not a valid query because the TO_DATE function doesn't understand NULL as a valid date:

{ CALL SP_CR_TEST1C( TO_DATE('NULL', 'MM-DD-YYYY'), 
                     TO_DATE('NULL 23:59:59', 'MM-DD-YYYY HH24:MI:SS')) }

When CHARTrunner detects an error in a custom query that has one or more Custom Parameters it will automatically display the "Query parameters" window that allows you to assign valid values to your query parameters.

Other Reading

You may find additional helpful information in the following articles.

http://osi.oracle.com/~tkyte/ResultSets/index.html (this article may no longer be available, but you can view it in the Google cache)

PQ KnowledgeBase article CR: Using Custom Parameters in Your Custom Queries

Attachments (1) Attachments
Related Articles RSS Feed
GN: MDAC Information
Viewed 22099 times since Mon, Nov 29, 2010
CR: Charting from IndustrialSQL Server
Viewed 2500 times since Fri, Nov 9, 2007
CR: Using Custom Parameters in Your Custom Queries
Viewed 2218 times since Fri, Nov 9, 2007
CR: Charting from a Paradox Data Source
Viewed 1911 times since Fri, Nov 9, 2007
CR: Chart refresh is not refreshing when data is added
Viewed 2804 times since Mon, Mar 3, 2008
CR: Using a Text File as a Data Source
Viewed 5038 times since Tue, Feb 5, 2008
CR: Installable ISAM Error
Viewed 2141 times since Thu, Feb 23, 2012
CR: Missing the First Row of Data from an Excel Data Source
Viewed 1870 times since Fri, Nov 9, 2007
CR: Charting from a FoxPro Data Source
Viewed 2062 times since Fri, Nov 9, 2007
CR: Fetching Specification Limits from the Data Source
Viewed 1702 times since Fri, May 30, 2008