Tableau is a leading data visualization and business intelligence software that helps you connect, visualize, and share data in an engaging and interactive way. Whether you are an analyst, a data scientist, a business professional, or an organization, you can use Tableau to gain insights from your data and make data-driven decisions.
Exploring Tableau’s Data Visualization Power
Connect, Visualize, and Share Data with Tableau
Understanding Oracle’s Stored Procedures and PL/SQL
Encapsulating Logic and Enhancing Database Performance with Stored Procedures
The Challenge: Oracle Stored Procedure Limitation in Tableau Data Source
A Workaround Solution: Converting Oracle Stored Procedures into Functions
Introduction to Oracle Functions and Their Benefits
Steps to Convert Oracle Stored Procedure into a Function
Creating Reports with Oracle Functions in Tableau
Using Oracle Functions for Data Retrieval in Tableau
Crafting Reports Using Oracle Function Results in Tableau
Oracle is a powerful and widely used relational database management system that supports PL/SQL, a procedural extension of SQL. PL/SQL allows you to create stored procedures, which are named blocks of code that are stored in the database and can be executed on-demand by applications or users. Stored procedures are useful for encapsulating business logic and repetitive tasks, providing a modular approach to application development, and improving the performance and security of the database.
However, if you want to use Tableau to visualize data from an Oracle stored procedure, you might encounter a peculiar problem. Tableau does not show Oracle stored procedures in the data source pane when connected to an Oracle database. This means that you cannot build reports that fetch data from stored procedures.
But don’t worry, there is an easy workaround for this. You can convert your stored procedure into a function with the same logic and use it in Tableau instead. A function is similar to a stored procedure, but it returns a value or a result set. Tableau can recognize and use Oracle functions to create the report.
In this blog post, we will show you how you can convert an Oracle stored procedure into a function and use it in Tableau to create a report. Follow these steps to get started.
Step 1: Creating a Custom Object in Oracle
Create an object.
CREATE TYPE my_object AS OBJECT
(COL1 VARCHAR2(50),
COL2 VARCHAR2(50),
COL3 VARCHAR2(50));Step 2: Creating Table Using the Custom Object
Create Table using object.
CREATE TYPE MY_OBJ_TABLE AS TABLE OF my_object;Step 3: Creating an Oracle Function for Reporting
Create a function.
create or replace function myfunction2 (p_pxobjclass in varchar2)
return my_obj_table pipelined
is
begin
for r_curl in (select cust_cd,cust_nm,tax_cd from BIWMARTS.CUSTOMER_DIM where rownum<100)
loop
pipe row (my_object(r_curl.cust_cd,r_curl.cust_nm,r_curl.tax_cd));
end loop;
return;
end;
Step 4: Calling the Oracle Function in Tableau Using Custom SQL
Call the function in Tableau using custom SQL. Below is an example for reference.
select COL1, COL2, COL3 from TABLE (myfunction2('SomeSampletask'))‘SomeSampletask’ is a dummy parametric value
Unlocking the Potential of Tableau with Oracle Function Integration
Seamless Integration: Oracle Function in Tableau for Enhanced Reports
Data Insights and Business Intelligence with Oracle Function-Driven Reports
Conclusion: Enhancing Oracle-Tableau Integration for Data-Driven Insights
Seamlessly Bridging the Gap Between Oracle and Tableau
Effortless Report Creation and Business Decision-Making
Explore More Possibilities with Tableau and Oracle Function Integration
And there you have it! Thank us later.
