Environment Details
Not able to access Oracle stored procedure list from the Pre and Post SQL Statement feature in the Input Tool.
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)