Call Procedure
The "Call procedure" module executes a stored procedure (or, on Exasol, a script) in a database. The call text is taken from the transformation action. The module can run the call once or repeat it for every row of an optional loop query.
| Name | Meaning |
|---|---|
| Module | Call procedure |
| Module Class | DsModExecProcedure |
| Type | Java |
| Purpose | Execute a database procedure |
| Transformation Code | Procedure call (without a leading CALL / EXECUTE SCRIPT) |
| Sources | Optional tables in one database (used to choose the connection) |
| Targets | Optional tables in the same database |
Description
The action body contains the procedure invocation, including arguments, for example my_schema.refresh_stats() or load_partition('${target_table}'). The module wraps this text in the dialect of the target database:
- most databases:
CALL <action>or JDBC escape{call <action>} - Exasol:
EXECUTE SCRIPT <action>
Do not write CALL or EXECUTE SCRIPT in the action body yourself. Freemarker placeholders in the action body are replaced once before execution, using the usual datasqill variables (batch, action, sources, targets, module timestamps, and so on).
The database connection is chosen in this order:
- all source tables (object type table) that share one connection
- otherwise all target tables that share one connection
- otherwise the attribute Source Database
If sources or targets refer to more than one database, the module falls back to Source Database.
During Validate, the module checks that the procedure call can be prepared, applies object rights for sources and targets, and records an unknown dependency on the chosen database. During Run, it executes the call and commits.
Loop Query
If Loop Query is set, the procedure is executed once per result row. Each iteration commits separately. The loop query is validated as SQL on the same connection. Schema names in the loop query are rewritten with the connection’s schema mapping.
The action body is not re-templated per loop row, and loop-query columns are not added as Freemarker variables. The loop only repeats the same call. Use it when the procedure itself selects the next work item, or when you simply need N identical executions with a commit after each one.
If the loop query returns no rows and Error if Loop empty is enabled, the transformation fails with error code -119.
Data Sources
Sources are optional. If they are tables, they should all belong to the database in which the procedure runs. The datasqill runtime user needs execute rights on the procedure and the rights that the procedure itself requires.
Data Targets
Targets are optional. They are used like sources to pick the connection and to apply object rights during validate. The procedure may write to these tables, but the module does not generate DML itself.
Attributes
| Name | Type | Meaning |
|---|---|---|
| Source Database | Connection | Connection used when sources and targets do not uniquely identify one database (default: connection 1). |
| Loop Query | SQL | Optional query. The procedure is called once per result row. |
| Error if Loop empty | Boolean | If enabled and the loop query returns no rows, the transformation fails (default: N). |
Triggers
The module offers the following connection-level triggers:
- After Database Open
- Before Validate / After Validate
- Before Loop / After Loop
- Before Iteration / After Iteration
- Before Commit / After Commit
With a loop query, Before Loop / After Loop wrap the whole cursor, and Before Iteration / After Iteration plus Before Commit / After Commit run for every row. Without a loop query, the call runs once (Before/After Iteration, then commit).
Statistics
The module reports among others:
| Name | Meaning |
|---|---|
| write_duration | Time spent executing the procedure call(s), in nanoseconds |
| convert_duration | Time outside the call (templating, open, close), in nanoseconds |
| module_duration | Overall module runtime, in nanoseconds |
| iterations | Number of procedure executions |
rows_processed is taken from the JDBC update count of the last call (0 if the driver does not report one).
Examples
Single procedure call
Action body:
staging.refresh_aggregates()
Connect the tables the procedure reads or writes so that datasqill can pick the connection and show dependencies.
Call with Freemarker
Action body:
staging.load_partition('${target_table}')
${target_table} is filled from the single table target of the transformation.
Repeat the same call
Loop Query:
SELECT 1 FROM staging.work_queue WHERE status = 'READY'
Action body:
staging.process_next_item()
The procedure runs once per queue row. Each run is committed. Enable Error if Loop empty if an empty queue should fail the transformation.