Calling a stored procedure in SQLExplorer is no different to executing a piece of standard SQL, provided that all parameters are input parameters - and such criteria excludes stored functions because they output a return value. This is particularly a problem on RDBMS's where the result sets are not returned via the standard getResultSet() mechanism but instead returned as an output parameter (IE Oracle).
In order to use parameters you must first declare them (where you must give each parameter a unique name) using the structured comment "parameter" command, and then you can refer to the parameter anywhere further on in the query by a colon (":") and the name.
${parameter name [("output"|"inout")] [datatype] [arguments]} [value]
The name is mandatory and must uniquely identify the parameter - if you subsequently define a new parameter with the same name as an existing one, the existing one is overwritten from that point onwards. After the name, you can optionally specify whether a parameter is input, output, or both ("inout"), and if you do not specify a direction then the default is "input". The datatype is generally only useful if a cursor or date/time parameter is being specified, and an optional has a series of arguments depending on what data type is selected; if no data type is given then the default is String. Finally, after the command (ie after the closing "}") is the value for input parameters; this is interpretted according to the data type selected - for example, the arguments to a date/time field specify the format that the value is in.
The datatypes available are: cursor, string, integer, decimal, date, time, and datetime. The date, time, and datetime datatypes support arguments which indicate how to interpret the value (if provided) and can be either "short", "medium", "long", or a format pattern.
When declaring a cursor, the direction is always assumed to be "output" and the value is ignored.
For example, to declare a cursor and execute a stored procedure which returns a result set in that cursor:
--${ parameter c output cursor }
--${ parameter my_date input date 'dd/MM/yyyy' } 21/11/2007
--${ parameter my_out output string }
begin
pkg_test.my_test_proc(:c, :my_date, :my_out);
end;
This example also declares a date, specifies the format of that date, and sets its input value to 21st November 2007; executing the above code will produce results tabs for the cursor result set and a result tab listing all the output parameters with their new values.