Suppose you want to execute a function in an Oracle database called SQUARE to calculate a value of 5. The function takes one input parameter and returns the output as the function result (SQUARE(n) -> result). To execute this function using the Native ODBC commands in 4D 2004, you can try one of the following approaches:

1. Predefined the input parameter

$sqlstmt:="SELECT SQUARE(5) FROM DUAL"  `e.g. SELECT FROM DUAL
ODBC LOGIN("yvanorcl";"scott";"tiger")
ODBC EXECUTE($sqlstmt;vlresult)
ODBC LOAD RECORD `Load the function result into vlresult (25)
ODBC LOGOUT

2. Dynamically defined the value of the input parameter

vlinput:=5
$sqlstmt:="SELECT SQUARE(:vlinput) FROM DUAL"  `e.g. SELECT FROM DUAL
ODBC LOGIN("yvanorcl";"scott";"tiger")
ODBC SET PARAMETER(vlinput;ODBC Param In )
ODBC EXECUTE($sqlstmt;vlresult)
ODBC LOAD RECORD `Load the function result into vlresult (25)
ODBC LOGOUT

Using the Native ODBC commands, you can also execute a procedure in an Oracle database in the similar fashion as executing a function. The following is example of a method that calls a procedure in an Oracle database. The syntax of the procedure is as

  DOUBLE_VALUE(vinput in integer, voutput out integer)

In this example, the procedure has two parameters: vinput (input parameter) and voutput (output paramter). The execution of this procedure set value of "voutput" to "vinput*2".

To execute this procecdure using the Native ODBC comands in 4D 2004, here is an example:

vlinput:=5
vlresult:=0
$sqlstmt:="BEGIN DOUBLE_VALUE(:vlinput,:vlresult); END;" `e.g. BEGIN ; END;
ODBC LOGIN("yvanorcl";"scott";"tiger")
ODBC SET PARAMETER(vlinput;ODBC Param In )
ODBC SET PARAMETER(vlresult;ODBC Param Out )
ODBC EXECUTE($sqlstmt;vlresult) `Execute and load the output parameter into vlresult (10)
ODBC LOGOUT


Please note that all parameter in the function and procedure calls must be specified in the following format:

  FUNCTION_NAME(:PARAM1,:PARAM2,...,:PARAM[n])

  PROCEDURE_NAME(:PARAM1,:PARAM2,...,:PARAM[n])

Each PARAM can either be a 4D variable or field that you want to bind with.