Alteryx Server Knowledge Base

Definitive answers from Server experts.

Oracle connection in the Input Tool is not returning stored procedures

JLinAltX
Alteryx
Alteryx
Created

Environment Details


  Not able to access Oracle stored procedure list from the Pre and Post SQL Statement feature in the Input Tool.  

  • Alteryx Designer, Server
    • 2020.2.3.27789


Cause


To get the Oracle stored procedure list in the Pre and Post SQL Statement feature, the Designer runs the following query:

SELECT OBJECT_NAME FROM USER_PROCEDURES WHERE OBJECT_TYPE = 'PROCEDURE'
 


Resolution


Resolution A

Because the Designer queries USER_PROCEDURES, you will need to use the Oracle User that owns the stored procedure in the Input Tool Oracle connection in order to see the list or be granted rights to read from USER_PROCEDURES.  Please consult with your DBA for grants..   

Resolution B

The stored procedure list is just for convenience.  You can type in the stored procedure directly into the SQL Editor tab of the Pre and Post SQL Statement window.  We use "CALL" to execute stored procedures with no semi-colon.

e.g. CALL Stored_Proc_Name(Parameter)

Comments
jmorning
5 - Atom

Thank you for this solution worked. Just one more question do I need to call the procedure in both Pre and Post SQL Statement. Also what does Pre and Post SQL Statement do?

lepome
Alteryx Alumni (Retired)

@jmorning 

Even though you've probably figured this out long ago, it's a good question and I'd like to help other users understand. 

 

Pre-SQL and Post-SQL statements, as the names imply, run either before the main query or after the main query.  You do not need to use both.  In many cases, such statements would be counter-productive if run both before and after the main query.  For example, if you create a table as part of the main query, you might want to drop it as part of the workflow after it is used (Post-SQL).  If you are calling a Stored Procedure, you would probably only want to call it once per tool.  If the Stored Procedure returns data, then you can use it as a Pre-SQL statement without any "main query".  The Input Data tool requires that some data be returned, even if that data is never used downstream in the workflow, so if your Stored Procedure does not return data, you will need to query your database to return something, anything, really.

Example "main query" after Pre-SQL call of Stored Procedure:

SELECT 1+1

 

Oracle databases use "from DUAL" to complete Select statements without table references.  Example:

SELECT 1+1 FROM DUAL