09-10-2020 10:49 AM - edited 07-22-2021 01:28 PM
Not able to access Oracle stored procedure list from the Pre and Post SQL Statement feature in the Input Tool.
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)
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?
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