Function with a string as a parameter and returns a string
Create a function:
SQL> edit
Wrote file afiedt.buf
1 CREATE OR REPLACE FUNCTION myfuncinout(x IN OUT VARCHAR) RETURN VARCHAR IS
2 BEGIN
3 x := x || ' - outvalue';
4 RETURN 'a returned string';
5* END;
SQL> /
Function created.
This function takes a string as a parameter and returns the string with ‘- outvalue’ appended to it.
SQL> set serveroutput on
SQL> edit
Wrote file afiedt.buf
1 DECLARE
2 v_employee VARCHAR2(30) := 'BillyBob';
3 v_returnVal VARCHAR2(30);
4 BEGIN
5 v_returnVal := myfuncinout(v_employee);
6 DBMS_OUTPUT.PUT_LINE('out value v_employee = ' || v_employee);
7 DBMS_OUTPUT.PUT_LINE('return value v_returnVal = ' || v_returnVal);
8* END;
SQL> /
The results of the above code are:
out value v_employee = BillyBob - outvalue
return value v_returnVal = a returned string
PL/SQL procedure successfully completed.
To do the same call in Smalltalk 10.0.0, execute the following script (NOTE: The return value must be the first value in the AbtCompoundType creation statement. This was done in a workspace where variables are automatically declared):
"Connect to the DB"
(AbtDatabaseConnectionSpec forDbmClass: AbtOracle10DatabaseManager databaseName: 'orcl') connect.
connection := AbtDbmSystem activeDatabaseConnection
flushCache;
autoCommit: true;
commitUnitOfWork;
yourself.
"Setup the function name, etc."
funcName := 'myfuncinout'.
returnValueName := 'return_value_str'.
paramRowName := 'in_out_str'.
"Setup the types"
inOutParams := AbtCompoundType new
addField: (AbtOracleVarCharField new
name: returnValueName;
count: 100);
addField: (AbtOracleVarCharField new
name: paramRowName;
count: 100).
"Dictionary to hold values"
aDict := Dictionary new.
aDict
at: returnValueName put: String new;
at: paramRowName put: 'BillyBob'.
"Setup the function call"
functionSpec := AbtFunctionSpec new
functionName: funcName;
useSqlCall: true;
parameters: inOutParams.
"Call the function"
resultSet := connection invokeFunctionSpec: functionSpec withInputDictionary: aDict.
resultSet
For the return String, I gave it a size or count of 100 bytes. Always make sure the size declared is large enough to hold any modifications to the string that is returned. Select the script above, right mouse click, and select ‘inspect’. If you are not logged in, a dialog box will pop up asking you for your username and password and database name. The result of inspecting the above is a Dictionary inspector which looks like the following:
Last modified date: 03/28/2020