| Info | ||
|---|---|---|
| ||
This routine is available starting with SIL Engine™ 3.0.6. |
Syntax
...
| Table plus | ||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| ||||||||||||
|
Description
| Excerpt | ||
|---|---|---|
| ||
| Executes the stored procedure over the defined datasource. |
Executes the stored procedure over the defined datasource.
Parameters
| Table plus | ||||||||||
|---|---|---|---|---|---|---|---|---|---|---|
| ||||||||||
|
...
|
...
|
...
|
...
|
The routine accepts multiple parameters, in this case the sql statement being pushed as prepared into the database. Check Example 2 below for the correct syntax.
Returns
...
Return Type
String []
Notes
| Note |
|---|
This routine works 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
...
Examples
Where showMessage() is a stored procedure existing in myDB database.
| Code Block |
|---|
string [] results = sqlCallStoredProcedureWithOutParams("myDB", "showMessage");
|
Where showMessage() is a INSERTTEST is a stored procedure existing in myDB database.
Example 2
, you can see it below:
| 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"}); |
Where INSERTTEST is a stored procedure existing in myDB database, you can see it below:
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; |
Where TESTPROCEDURE(pObjName IN varchar2, p_cursor OUT SYS_REFCURSOR, param OUT VARCHAR2) is a stored procedure existing in myDB database.
You can also return the result this way:
| Code Block |
|---|
string [] results = sqlCallStoredProcedureWithOutParams("myDB", "TESTPROCEDURE", {"pObjName", "VARCHAR2", "IN", ""}, {"p_cursor", "REF CURSOR", "OUT"}, {"param", "VARCHAR2", "OUT"});
return results["p_cursor"]; |
This will return the entry for "p_cursor"; same result will be returned for: return results[0]; | Code Block |
|---|
string [] results = sqlCallStoredProcedureWithOutParams("myDB", "TESTPROCEDURE", {"pObjName", "VARCHAR2", "IN", ""}, {"p_cursor", "REF CURSOR", "OUT"}, {"param", "VARCHAR2", "OUT"});
string[] t = results["p_cursor"];
return t[3]; |
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; |
Where TESTPROCEDUREINOUTPARAMETER (param IN OUT VARCHAR2) is a stored procedure existing in myDB database.
In the next table you can see the mapping between SQL Types and SIL™ Types:
...
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 configure the data source, check the SQL |
...
data sources configuration chapter. |
See also
| Filter by label (Content by label) | ||||||||||
|---|---|---|---|---|---|---|---|---|---|---|
|