Quick Guide for Overcoming Oracle Stored Procedure Limitation in Tableau

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.

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:

Create an object.

CREATE TYPE my_object AS OBJECT

     (COL1       VARCHAR2(50),

      COL2       VARCHAR2(50),

      COL3       VARCHAR2(50));

Step 2:

Create Table using object.

CREATE TYPE MY_OBJ_TABLE AS TABLE OF my_object;

Step 3:

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:

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

And there you have it! Thank us later.