...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
| Info | ||
|---|---|---|
| ||
This routine is available since starting with katl-commons 3.0.6. |
Syntax
...
sqlCallStoredProcedureWithOutParams(datasourceName, procedureName, [params])
Description
...
| Excerpt |
|---|
Executes the stored procedure over the defined datasource. |
Parameters
...
Parameter name | Type | Required | Description |
|---|---|---|---|
datasourceName | string | yes | The datasource name or JNDI name. For JIRA database, this is set to "jdbc/JiraDS" by default |
procedureName | string | yes | the stored procedure name |
The routine accepts multiple parameters, in this case the sql statement being pushed as prepared into the database (check the second example below for the correct syntax).
Returns
...
string []
Notes
...
| Note |
|---|
Please notice that this routine work only with Oracle stored procedures. The parameters with type "OUT" must have three properties (corresponding parameter name from the procedure, data type, type), while the other ones must have four (corresponding parameter name from the procedure, data type, type, value). |
Example
...
Example 1
...
| Code Block |
|---|
string [] results = sqlCallStoredProcedureWithOutParams("myDB", "showMessage");
|
Where showMessage() is a stored procedure existing in myDB database.
Example 2
...
| Code Block |
|---|
string [] results = sqlCallStoredProcedureWithOutParams("myDB", "INSERTTEST", {"p_userid", "FLOAT", "IN", "1.22"}, {"p_username", "VARCHAR2", "IN", "username"}, {"p_createdby", "VARCHAR2", "IN", "admin"}, {"p_date", "TIMESTAMP", "IN", "28-APR-2015"}); |
...
| Code Block |
|---|
create or replace PROCEDURE INSERTTEST (
p_userid IN TEST.USER_ID%TYPE,
p_username IN TEST.USERNAME%TYPE,
p_createdby IN TEST.CREATED_BY%TYPE,
p_date IN TEST.CREATED_DATE%TYPE )
IS
BEGIN
INSERT INTO TEST ("USER_ID", "USERNAME", "CREATED_BY", "CREATED_DATE")
VALUES (p_userid, p_username,p_createdby, p_date);
COMMIT;
END INSERTTEST; |
Example 3
...
| Code Block |
|---|
string [] results = sqlCallStoredProcedureWithOutParams("myDB", "TESTPROCEDURE", {"pObjName", "VARCHAR2", "IN", ""}, {"p_cursor", "REF CURSOR", "OUT"}, {"param", "VARCHAR2", "OUT"});
return results; |
...
This will return the third entry from "p_cursor".
Example 4
...
| Code Block |
|---|
string[] results = sqlCallStoredProcedureWithOutParams("myDB", "TESTPROCEDUREINOUTPARAMETER", {"param", "VARCHAR2", "IN OUT", "paramValue"});
return results; |
...
In the next table you can see the mapping between SQL Types and SIL Types:
| SQL Type | SIL Type |
|---|---|
User-defined collection | ARRAY |
CHAR | CHAR |
NUMBER | DECIMAL |
NUMBER | FLOAT |
NUMBER | INTEGER |
LONG | LONG |
REF CURSOR | REF CURSOR |
DATE | TIMESTAMP |
VARCHAR2 | VARCHAR2 |
Notes
...
| Note |
|---|
To see how you should configure the data source, check the corresponding configuration chapter: SQL Data Sources. |
See
...
also
| Filter by label (Content by label) | ||||||||
|---|---|---|---|---|---|---|---|---|
|