ONLINE HELP
 WINDEVWEBDEV AND WINDEV MOBILE

Help / Native Connectors/Native Accesses / Native Oracle Connector
  • 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
WINDEV
WindowsLinuxUniversal Windows 10 AppJavaReports and QueriesUser code (UMC)
WEBDEV
WindowsLinuxPHPWEBDEV - Browser code
WINDEV Mobile
AndroidAndroid Widget iPhone/iPadIOS WidgetApple WatchMac CatalystUniversal Windows 10 App
Others
Stored procedures
Native Oracle Connector: Retrieving the output value of a stored procedure
Native Connectors (Native Accesses)Available only with this kind of connection
Overview
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).
How to?

Steps

To specify and retrieve values when running a query, you must:
  1. Declare a data source. This data source will contain the result of the SQL query.
  2. 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:
    • initialize the value of the variable (simple types: integer, string, float)
    • specify the expected type with the Type property (not supported in this version)
  3. 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. Caution: the ":" character must be followed by at least one letter (the syntax:1 is not allowed).
    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.
Example
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.
MyProc is Data Source

2. Declaring the variables used by the procedure

The variables handled by the procedure are declared from the data source.
MyProc.n = 3 // Automatically declares an integer initialized to 3
MyProc.str = "Example"
Remark: There is no need to declare all the 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

To execute the query, use HExecuteSQLQuery:
HExecuteSQLQuery(MyProc, Connection, hQueryWithoutCorrection, "begin:Res:=sp_cut(:n,:str);end;")
Info(MyProc.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_name      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 display_user
    RETURN SYS_REFCURSOR AS
    vRefCursor SYS_REFCURSOR;
    begin
    OPEN vRefCursor FOR
    SELECT user_name, upper(user_name) FROM frm_user;
    RETURN vRefCursor;
    end;
  • WLanguage code to execute the function and read the result:
    myProcedure is Data Source
    IF NOT HExecuteSQLQuery(myProcedure, connectionOracle, hQueryWithoutCorrection, ...
    "begin:return_value%CURSOR:= display_user(); end;") THEN
    Error("Error on HExecuteSQLQuery", "", HErrorInfo(hErrFullDetails))
    RETURN
    ELSE
    FOR EACH myProcedure
    Trace(HRecordToString(myProcedure))
    END
    END

    The trace contains:
    toto
    titi
    tutu

Example: Procedure with a cursor parameter

  • Creating the source table of data:
    CREATE TABLE frm_user(user_name 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_display_user(O_RESULT_SET OUT SYS_REFCURSOR)
     AS
     BEGIN
       OPEN O_RESULT_SET FOR
       SELECT user_name, upper(user_name) FROM frm_user;
     END;
  • WLanguage code to execute the procedure and read the result:
    myProcedure is Data Source
    IF NOT HExecuteSQLQuery(myProcedure, connectionOracle, hQueryWithoutCorrection, ...
    "begin Proc_display_user(:return_value%CURSOR); end;") THEN
    Error("Error on HExecuteSQLQuery", "", HErrorInfo(hErrFullDetails))
    RETURN
    ELSE
    FOR EACH myProcedure
    Trace(HRecordToString(myProcedure))
    END
    END

    The trace contains:
    toto
    titi
    tutu
Minimum version required
  • Version 9
This page is also available for…
Comments
Click [Add] to post a comment

Last update: 10/27/2022

Send a report | Local help