Defining an EXECUTE Statement

You use this statement to execute a stored procedure.

Format of EXECUTE Statement

<StatementName>

<storedProcedureName action=" EXECUTE">

<table>realStoredProcedureeName</table>

<param1 [isInput="true"] [isOutput=true] type=SQLDatatype>val1</param1>

</storedProcedureName >

</StatementName>

1. Enter the name of the stored procedure in the database before the action.
2. If you use the optional <table> element, the value specified here is used as the stored procedure name.

   This enables you, for example, to define stored procedure names that contain non-XML-compatible characters or characters that stop them from being used in interface definitions in the Integration Builder.

   • Enter <table> as the first element of the block within <dbTableName>.
3. Specify the parameters for the stored procedure.
   • You have the option of specifying the attribute isInput="1"(input parameter) or isOutput="1" (output parameter) for the parameters.

     If both attributes are missing, the element is interpreted as an input parameter.

     The parameter names must be identical to those of the stored procedure definition.

   • You must specify the attribute type=<SQLDatatype> for all parameter types (IN, OUT).

     It describes the valid SQL data type.

     The following SQL data types are supported:

     INTEGER, BIT, TINYINT, SMALLINT, BIGINT, FLOAT, REAL, DOUBLE, NUMERIC, DECIMAL, CHAR, VARCHAR, STRING, LONGVARCHAR, DATE, TIME, TIMESTAMP, BINARY, VARBINARY, LONGVARBINARY, BLOB (input and output), CLOB (input and output), CURSOR (output; only in connection with the Oracle JDBC driver)

     Tip

     The binary data for BLOB is hexadecimal encoded.

All return values are returned in an XML structure. The results within the stored procedure are returned either as a table or as the element <update_count>. This depends on the SQL statements executed within the stored procedure. The return parameters of a stored procedure are appended in a separate structure.