PC SOFT

ONLINE HELP
FOR WINDEV, WEBDEV AND WINDEV MOBILE

Home | Sign in | English US
  • Overview
  • How to proceed?
  • The steps
  • Notes
  • 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/iPadApple WatchUniversal Windows 10 AppWindows Mobile
Others
Stored procedures
Native Oracle Connector: Retrieving the output value of a stored procedure
Native AccessesAvailable 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.
Notes:
  • You also have the ability to use variables in any type of query.
  • Versions 18 and later
    The functions and procedures returning a cursor can also be run.
    New in version 18
    The functions and procedures returning a cursor can also be run.
    The functions and procedures returning a cursor can also be run.
If the query must be run several times, you can prepare the query to run (HPrepareSQLQuery) as well as the different parameters to modify, then run the query as many times as necessary with HExecuteSQLQuery. This solution optimizes the time taken to browse the query result (for a SELECT query).
How to proceed?

The 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 variables of the query (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 ..Type (not supported in this version)
  3. Run the query with HExecuteSQLQuery.

Notes

  • HExecuteSQLQuery must be used with:
    • the name of the connection,
    • 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 re-used.
  • To run a query without enabling the bind, 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 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"
Note: 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 run the query, all you have to do is 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.
Versions 18 and later
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.
Notes:
  • 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:
    return sys_refcursor
    as

    vRefCursor SYS_REFCURSOR;
    begin
    OPEN vRefCursor FOR
    SELECT user_name
    FROM frm_user;

    RETURN vRefCursor;
    end;
  • WLanguage code used to run the function and to browse 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(HRetrieveRecord(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:
    (O_RESULT_SET OUT SYS_REFCURSOR)
     AS
     BEGIN
       OPEN O_RESULT_SET FOR
       SELECT user_name
       FROM frm_user;
     END;
  • WLanguage code used to run the procedure and to browse 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(HRetrieveRecord(myProcedure))
    END
    END

    The trace contains:
    toto
    titi
    tutu
New in version 18
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.
Notes:
  • 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:
    return sys_refcursor
    as

    vRefCursor SYS_REFCURSOR;
    begin
    OPEN vRefCursor FOR
    SELECT user_name
    FROM frm_user;

    RETURN vRefCursor;
    end;
  • WLanguage code used to run the function and to browse 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(HRetrieveRecord(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:
    (O_RESULT_SET OUT SYS_REFCURSOR)
     AS
     BEGIN
       OPEN O_RESULT_SET FOR
       SELECT user_name
       FROM frm_user;
     END;
  • WLanguage code used to run the procedure and to browse 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(HRetrieveRecord(myProcedure))
    END
    END

    The trace contains:
    toto
    titi
    tutu
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.
Notes:
  • 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:
    return sys_refcursor
    as

    vRefCursor SYS_REFCURSOR;
    begin
    OPEN vRefCursor FOR
    SELECT user_name
    FROM frm_user;

    RETURN vRefCursor;
    end;
  • WLanguage code used to run the function and to browse 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(HRetrieveRecord(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:
    (O_RESULT_SET OUT SYS_REFCURSOR)
     AS
     BEGIN
       OPEN O_RESULT_SET FOR
       SELECT user_name
       FROM frm_user;
     END;
  • WLanguage code used to run the procedure and to browse 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(HRetrieveRecord(myProcedure))
    END
    END

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