Skip to end of banner
Go to start of banner

sqlCallStoredProcedureWithOutParams

Skip to end of metadata
Go to start of metadata

You are viewing an old version of this page. View the current version.

Compare with Current View Page History

« Previous Version 4 Next »

Availability

This routine is available starting with katl-commons 3.0.6.

Syntax

sqlCallStoredProcedureWithOutParams(datasourceName, procedureName, [params])

Description

Executes the stored procedure over the defined datasource.

Parameters

Parameter name

Type

Required

Description

datasourceName

string

yes

Datasource name or JNDI name. For Jira database, this is set to "jdbc/JiraDS" by default.

procedureName

string

yes

Stored procedure name.

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

string []

Notes

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

string [] results = sqlCallStoredProcedureWithOutParams("myDB", "showMessage");

Where showMessage() is a stored procedure existing in myDB database.

Example 2

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

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:

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]; 

 

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

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:

SQL TypeSIL Type

User-defined collection

ARRAY

CHAR

CHAR

NUMBER

DECIMAL

NUMBER

FLOAT

NUMBER

INTEGER

LONG

LONG

REF CURSOR

REF CURSOR

DATE

TIMESTAMP

VARCHAR2

VARCHAR2

Notes

To configure the data source check the SQL data sources configuration chapter.

See also

 

  • No labels