Function that has an integer as an in parameter and returns a float
Create a function:
SQL> edit
Wrote file afiedt.buf
1 CREATE OR REPLACE FUNCTION myNumberIn(n IN NUMBER) RETURN NUMBER IS
2 BEGIN
3 RETURN n * 1.2;
4* END;
SQL> /
Function created.
The Function takes a number, multiplies it by 1.2 and returns a float back to the caller. Call the function:
SQL> set serveroutput on
SQL> edit
Wrote file afiedt.buf
1 DECLARE
2 num NUMBER := 3;
3 BEGIN
4 num := myNumberIn(num);
5 DBMS_OUTPUT.PUT_LINE('num = ' || TO_CHAR(num));
6* END;
SQL> /
The forward slash runs the sql code just entered. The result of running the above code is the following:
num = 3.6
PL/SQL procedure successfully completed.
The following ST script was used to call the above Function:
"Setup the connection"
(AbtDatabaseConnectionSpec forDbmClass: AbtOracle10DatabaseManager databaseName: 'orcl') connect.
connection := AbtDbmSystem activeDatabaseConnection
flushCache;
autoCommit: true;
commitUnitOfWork;
yourself.
"values and variable names"
integerIn := 3.
funcName := 'myNumberIn'.
returnValueName := 'returnValue'.
paramRowName := 'inOutParamRow'.
inputRowName := 'inputRow'.
"Variable types"
inOutParamRow := AbtCompoundType new
name: inputRowName;
addField: (AbtOracleLongField new
name: returnValueName;
yourself);
addField: (AbtOracleIntegerField new
name: paramRowName;
yourself).
"Values"
aDict := Dictionary new
at: returnValueName put: String new;
at: paramRowName put: integerIn;
yourself.
"Function spec"
functionSpec := AbtFunctionSpec new
functionName: funcName;
useSqlCall: true;
parameters: inOutParamRow.
"invoke the function"
resultSet := connection invokeFunctionSpec: functionSpec withInputDictionary: aDict.
(resultSet at: returnValueName) asScaledDecimal asFloat
Selecting the above, right mouse clicking, and selecting ‘inspect’ from the menu results in the following:
Last modified date: 03/28/2020