Information
  • Author: Appian Corporation
  • Type: Plug-in (Function & Smart Service)
  • Support: This plug-in or utility is not supported by the author. The content is free to use with a licensed product, but it is made available as-is and Community supported.
  • Industry: All Industries
  • Compatible Version(s): 18.4+
  • First Released:
  • Last Updated:
  • Cost: Free
Resources
Share
Home » AppMarket » Execute Stored Procedure

Execute Stored Procedure

**NOTE: This plug-in is deprecated and no longer recommended for use for sites running on Appian 22.1 or later. The functionality of this component is available in Appian without installing the plug-in. You can use a!executeStoredProcedureForQuery(), a!executeStoredProcedureOnSave(), or the Execute Stored Procedure Smart Service in Appian 22.1 or later.**

Overview

This plug-in provides a Smart Service and Function for executing stored procedures. The Smart Service can be used to execute stored procedures that modify data. Result sets are returned as CDTs. As custom functions cannot have side-effects, it must not be used to execute stored procedures that modify data. It is only safe to use the Smart Service with stored procedures that modify data. Following SQL Statements should not be used by the stored procedures executed with the function:

  • INSERT
  • UPDATE
  • DELETE
  • TRUNCATE
  • CREATE TEMPORARY TABLE
  • SELECT... INTO (when the input target is not an input or output variable)

Key Features & Functionality

  • Works with both complex and simple data types
  • Tested with: MySQL, SQL Server, and Oracle
  • See the instructions document provided with the download for an example and how to configure
  • (New) Total row count column added to trace log
Anonymous
Parents

  • Hi James,

    We are using this stored procedure smart service for a while and we did not see any issues, recently we have migrated from Oracle 11G to 12C, and while doing the testing we found that the meta data query is taking longer time and holding more resources. Is this issue reported anytime / any fix available for it. We are currently using V1.2.0. below is the query which is been executed. 

    SQL Text : SELECT -- Standalone procedures and functions NULL AS procedure_cat, owner AS procedure_schem, object_name AS procedure_name, NULL, NULL, NULL, 'Standalone procedure or function' AS remarks, DECODE(object_type, 'PROCEDURE', 1, 'FUNCTION', 2, 0) AS procedure_type , NULL AS specific_name FROM all_objects WHERE (object_type = 'PROCEDURE' OR object_type = 'FUNCTION') AND owner LIKE :1 ESCAPE '/' AND object_name LIKE :2 ESCAPE '/' UNION ALL SELECT -- Packaged procedures -- object_name AS procedure_cat, owner AS procedure_schem, procedure_name AS procedure_name, NULL, NULL, NULL, 'Packaged procedure' AS remarks, 1 AS procedure_type , NULL AS specific_name FROM all_procedures WHERE procedure_name IS NOT NULL AND object_name IS NOT NULL AND owner LIKE :6 ESCAPE '/' AND procedure_name LIKE :7 ESCAPE '/' AND NOT EXISTS (SELECT NULL FROM all_arguments WHERE argument_name IS NULL AND in_out = 'OUT' AND data_level = 0 AND package_name IS NOT NULL AND owner LIKE :6 ESCAPE '/' AND object_name LIKE :7 ESCAPE '/' AND all_procedures.object_id = all_arguments.object_id AND all_procedures.subprogram_id = all_arguments.subprogram_id)UNION ALL SELECT -- Packaged functions package_name AS procedure_cat, owner AS procedure_schem, object_name AS procedure_name, NULL, NULL, NULL, 'Packaged function' AS remarks, 2 AS procedure_type , NULL AS specific_name FROM all_arguments WHERE argument_name IS NULL AND in_out = 'OUT' AND data_level = 0 AND package_name IS NOT NULL AND owner LIKE :6 ESCAPE '/' AND object_name LIKE :7 ESCAPE '/' ORDER BY procedure_schem, procedure_name

    Thanks,

    Narasimha 

Comment

  • Hi James,

    We are using this stored procedure smart service for a while and we did not see any issues, recently we have migrated from Oracle 11G to 12C, and while doing the testing we found that the meta data query is taking longer time and holding more resources. Is this issue reported anytime / any fix available for it. We are currently using V1.2.0. below is the query which is been executed. 

    SQL Text : SELECT -- Standalone procedures and functions NULL AS procedure_cat, owner AS procedure_schem, object_name AS procedure_name, NULL, NULL, NULL, 'Standalone procedure or function' AS remarks, DECODE(object_type, 'PROCEDURE', 1, 'FUNCTION', 2, 0) AS procedure_type , NULL AS specific_name FROM all_objects WHERE (object_type = 'PROCEDURE' OR object_type = 'FUNCTION') AND owner LIKE :1 ESCAPE '/' AND object_name LIKE :2 ESCAPE '/' UNION ALL SELECT -- Packaged procedures -- object_name AS procedure_cat, owner AS procedure_schem, procedure_name AS procedure_name, NULL, NULL, NULL, 'Packaged procedure' AS remarks, 1 AS procedure_type , NULL AS specific_name FROM all_procedures WHERE procedure_name IS NOT NULL AND object_name IS NOT NULL AND owner LIKE :6 ESCAPE '/' AND procedure_name LIKE :7 ESCAPE '/' AND NOT EXISTS (SELECT NULL FROM all_arguments WHERE argument_name IS NULL AND in_out = 'OUT' AND data_level = 0 AND package_name IS NOT NULL AND owner LIKE :6 ESCAPE '/' AND object_name LIKE :7 ESCAPE '/' AND all_procedures.object_id = all_arguments.object_id AND all_procedures.subprogram_id = all_arguments.subprogram_id)UNION ALL SELECT -- Packaged functions package_name AS procedure_cat, owner AS procedure_schem, object_name AS procedure_name, NULL, NULL, NULL, 'Packaged function' AS remarks, 2 AS procedure_type , NULL AS specific_name FROM all_arguments WHERE argument_name IS NULL AND in_out = 'OUT' AND data_level = 0 AND package_name IS NOT NULL AND owner LIKE :6 ESCAPE '/' AND object_name LIKE :7 ESCAPE '/' ORDER BY procedure_schem, procedure_name

    Thanks,

    Narasimha 

Children
© 2024 Appian. All rights reserved.
We want to hear from you! Submit a review on Gartner or G2.