Stored procedure using Date, String, and Number Parameters
Using SQL Plus, here is a simple stored procedure:
SQL> create or replace procedure simple_param_test (num in OUT number, str in
out varchar2, myDate in out date)
as
begin
num := num * 2;
str := str || ' - modified';
myDate := add_months(myDate,3);
end;
Procedure created.
This stored procedure takes three parameters, a number, a string, and a date and multiplies the number by 2, adds the string ‘- modified’ to the string passed in, and advanced the data by three months. Notice that each parameter is listed as an ‘in out’ parameter meaning the new value assigned to the parameter is passed back to the caller. The above stored procedure is tested in SQL Plus by doing the following:
SQL> set serveroutput on format wrapped
SQL> declare
2 num number default 10;
3 str varchar2(256);
4 myDate date default sysdate;
5 begin
6 str := 'Hello';
7 dbms_output.put_line('Before:');
8 dbms_output.put_line('num = ' || num);
9 dbms_output.put_line('str = ' || str);
10 dbms_output.put_line('myDate = ' || myDate);
11 simple_param_test(num, str, mydate);
12 dbms_output.put_line('After:');
13 dbms_output.put_line('num = ' || num);
14 dbms_output.put_line('str = ' || str);
15 dbms_output.put_line('myDate = ' || myDate);
16* end;
SQL> /
The forward slash at the end runs the SQL just entered. The results of the above code are:
Before:
num = 10
str = Hello
myDate = 17-JUL-06
After:
num = 20
str = Hello - modified
myDate = 17-OCT-06
PL/SQL procedure successfully completed.
As stated above, the number 10 is multiplied by 2, the string ‘Hello’ had ‘- modified’ appended to it, and the date ’17-JUL-06’ was advanced 3 months to 17-OCT-06’.
To do the same call from Smalltalk, one must first create the stored procedure as shown above and then try the following script:
| connection storedProcSpec resultSet querySpec tDict |
(AbtDatabaseConnectionSpec forDbmClass: AbtOracle10DatabaseManager databaseName: 'orcl') connect.
connection := AbtDbmSystem activeDatabaseConnection.
connection emptyParameterListForProcedureNamed: 'simple_param_test'.
storedProcSpec := AbtStoredProcSpec new
name: 'testSPSpec';
description: 'test simple stored procedure from workspace';
procName: 'simple_param_test';
useSqlCall: true;
inputStringLabel: nil;
description: 'test Store Procedure spec';
inputRow: (AbtCompoundType new
name: 'inputRow';
addField: (AbtDatabaseLongIntegerField new
name: 'num';
nullsOk: true; procBindType: 1);
addField: (AbtDatabaseVarCharField new
name: 'str';
nullsOk: true; procBindType: 1; count: 256);
addField: (AbtOracleDateField new
name: 'myDate';
nullsOk: true; procBindType: 1)
).
tDict := AbtOrderedIdentityDictionary new.
tDict
at: 'num' put: 10;
at: 'str' put: 'Hello';
at: 'myDate' put: AbtTimestamp now.
resultSet := connection invokeStoredProcSpec: storedProcSpec withInputDictionary: tDict.
resultSet
Notice an AbtLongIntegerField was used, this is because even though the number ‘10’ is not a long (32 bits), the virtual machine changes these numbers to a long when making a call. For the String, I gave it a size or count of 256 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:
I ran the following script inside the Dictionary inspector:
self keysAndValuesDo: [:key :val | Transcript show: key, ': ', val asString; cr]
. . . and got the following result in the transcript:
myDate: 2006-10-17-12.58.17.000000
num: 20
str: Hello – modified
Last modified date: 06/01/2018