|
|
|
|
|
|
|
|
|
|
- Overview
- How to?
- Steps
- Remarks
- Example
- 1. Declaring the data source
- 2. Declaring the variables used by the procedure
- 3. Running the query and retrieving the result
- Functions and procedures returning a cursor
- Principle
- Example: Function returning a cursor
- Example: Procedure with a cursor parameter
Oracle Native Connector: Retrieve the output value of a stored procedure
 Available only with this kind of connection
The Native Oracle Connector (also called Native Oracle Access) is used to: - specify the input values during the call to a stored procedure.
- retrieve the output values after a call to a stored procedure.
Remarks: - You also have the ability to use variables in any type of query.
- The functions and procedures returning a cursor can also be run.
If the query must be executed multiple times, prepare the query to be executed (with HPrepareSQLQuery) as well as the different parameters to be changed. Then, execute the query as many times as necessary with HExecuteSQLQuery. This is a quick solution and reduces the time it takes to loop through the result of a query (SELECT query). Steps To specify and retrieve values when running a query, you must: - Declare a data source. This data source will contain the result of the SQL query.
- Declare the different query variables (input and output variables).
The variables are retrieved in the type specified in WLanguage. The variables are text variables by default.
Therefore, conversions may occur between the native type of the database and the WLanguage type, causing unexpected rounding or formatting errors (Date transformed into string for example).
Therefore, we advise you to specify the WLanguage types of the variables with great care before running the query. To do so:- by initializing the value of the variable (simple types: integer, string, float)
- specify the expected type with the Type property (not supported in this version)
- Run the query with HExecuteSQLQuery.
Remarks - HExecuteSQLQuery must be used with:
- the connection name,
- the hQueryWithoutCorrection constant.
- The declared variables must be identical to the ones used. Otherwise, a WLanguage error occurs.
- In the call to the stored procedure, you must use the syntax specific to the database used, including for the syntax of parameters. Therefore, for Oracle, the parameters are specified with the :ParamName syntax. Warning: the ":" character must be followed by at least one letter (the:1 syntax is forbidden)..
The same parameter can be used several times. In this case, the corresponding variable will be reused. - To execute a query without enabling the bind option, use the hNoBind constant in HExecuteSQLQuery.
The stored procedure 'sp_cut(n,str)' truncates the 'str' string after the first 'n' characters and returns the former length of the string. - n is an input variable.
- str is an input / output variable.
1. Declaring the data source The data source is used to handle the variables of the procedure that must be run. 2. Declaring the variables used by the procedure The variables handled by the procedure are declared from the data source. MaProc.n = 3
MaProc.str = "Exemple"
Note: It is not mandatory to declare all variables used. In this example, the variable used to retrieve the result of the procedure is not declared. 3. Running the query and retrieving the result HExecuteSQLQuery(MaProc, connection, hQueryWithoutCorrection, "begin:Res:=sp_cut(:n,:str);end;")
Info(MaProc.Res)
After the execution of the query, the MyProc.str variable contains "exa" and the MyProc.res variable contains 7. Functions and procedures returning a cursor Principle If the query returns no 'resultset' but if one of its parameters is a cursor (the return value is processed like an OUT parameter), the cursor is browsed like if it was the query result. Remarks: - HPrepareSQLQuery cannot be used to initialize the browse of the cursor. You must call HExecuteSQLQuery directly.
- The name of the cursor parameter or the name of the cursor return value must be postfixed by "%CURSOR" in order for the Native Connector to perform a bind of a cursor parameter.
Example: Function returning a cursor - Creating the source table of data:
CREATE TABLE frm_user(user_nom VARCHAR2(20));
INSERT INTO frm_user VALUES ('toto');
INSERT INTO frm_user VALUES ('titi');
INSERT INTO frm_user VALUES ('tutu');
COMMIT;
- Function returning a cursor:
CREATE OR REPLACE FUNCTION affiche_user
RETURN SYS_REFCURSOR AS
vRefCursor SYS_REFCURSOR;
begin
OPEN vRefCursor FOR
SELECT user_nom, upper(user_nom) FROM frm_user;
RETURN vRefCursor;
end;
- WLanguage code to execute the function and read the result:
maProcedure is Data Source
IF NOT HExecuteSQLQuery(maProcedure, connexionOracle, hQueryWithoutCorrection, ...
"begin:return_value%CURSOR:= affiche_user(); end;") THEN
Error("Erreur sur HExécuteRequêteSQL", "", HErrorInfo(hErrFullDetails))
RETURN
ELSE
FOR EACH maProcedure
Trace(HRecordToString(maProcedure))
END
END
The trace contains:
Example: Procedure with a cursor parameter - Creating the source table of data:
CREATE TABLE frm_user(user_nom VARCHAR2(20));
INSERT INTO frm_user VALUES ('toto');
INSERT INTO frm_user VALUES ('titi');
INSERT INTO frm_user VALUES ('tutu');
COMMIT;
- Procedure with a cursor parameter:
CREATE OR REPLACE PROCEDURE Proc_affiche_user(O_RESULT_SET OUT SYS_REFCURSOR)
AS
BEGIN
OPEN O_RESULT_SET FOR
SELECT user_nom, upper(user_nom) FROM frm_user;
END;
- WLanguage code to execute the procedure and read the result:
maProcedure is Data Source
IF NOT HExecuteSQLQuery(maProcedure, connexionOracle, hQueryWithoutCorrection, ...
"begin Proc_affiche_user(:return_value%CURSOR); end;") THEN
Error("Erreur sur HExécuteRequêteSQL", "", HErrorInfo(hErrFullDetails))
RETURN
ELSE
FOR EACH maProcedure
Trace(HRecordToString(maProcedure))
END
END
The trace contains:
This page is also available for…
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|