SQL Operations (Stored Procedures, Functions and Triggers)

SQL Operations are defined by multiple SQL statements that are pre-compiled and stored on the server for reuse, and can have parameters. SQL Operations often contain multiple DML statements (SELECT, INSERT, UPDATE, DELETE, etc.), and use control statements (IF, THEN, ELSE, CASE, FOR, etc.).
‎Support for SQL Operations is limited to functions and stored procedures with the following support and limitations:

• The general support for Stored Procedures is defined as follows:
  • Stored procedures reading from and writing to tables (catalog objects): supported
  • Stored procedures lineage support for IN, OUT, INOUT parameters: supported
  • Stored procedures with cursors: supported
  • Stored procedures with temporary/Local variables: supported
  • Stored procedures using functions: limited support
  • Stored procedures using public/private synonyms: supported
  • Stored procedures using other stored procedures: limited support
  • Stored procedures using temporary tables: supported
  • Stored procedures with error management: not relevant for data flow linage
• Parsing of independent SQL Stored Procedures and Functions is currently limited to the following database technologies:
  ‎(although some statements specific to database proprietary SQL syntax may not be supported)
  • Microsoft SQL Server Database
  • Oracle Database
  • PostgreSQL Database
  • Pivotal GreenPlum Database
  • Teradata Database
• Most dynamic and chaining aspects of the operations are not supported such as:
  • operations calling other operations
  • operations that execute dynamic SQL (e.g. SQL text passed as parameter)
  • external operations (e.g. includes)
• Some usage aspects of such operations by DI and BI tools might be limited:
  • using SQL operations to read data is supported in most DI and BI tool import bridges
  • using SQL operations to write data is supported only in DI tool import bridges such as Informatica PowerCenter, Microsoft SSIS and Talend