Page Comparison

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

Datasource name or JNDI name. For

JIRA

Jira database, this is set to "jdbc/JiraDS" by default.
procedureName	string	yes

the stored

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 . Check Example 2 below for the correct syntax). 

Returns

...

string []

Notes

...

Note
Please notice that this routine work 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

...

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;

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:

...

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 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 SQL data sources configuration chapter: SQL Data Sources.

See

...

also