Stored Procedure using an array of numbers
The test stored procedure for an array of numbers was created in Oracle using SQL plus and entering the following:
SQL> create or replace package numArrTest is type numArray is
table of number index by binary_integer;
procedure num_array (p_array in out numArray);
end;
Type created.
SQL> create or replace package body numArrTest as
procedure num_array( p_array in out numArray )
as
begin
for i in 1 .. p_array.count
loop
p_array(i) := p_array(i) * 2;
end loop;
end;
end;
/
Procedure created.
One important thing to note is an array of numbers must be declared inside a package. Only from within a package is the following allowed:
table of number index by binary_integer
If the declaration is not done from within a package then one can declare the following:
Beforetable of number
A declaration of this form is not callable from Smalltalk 7.5.
The stored procedure takes the array of numbers, multiplies each one by 2 and returns the array back to the caller. To test this stored procedure from SQL Plus, enter the following in a SQL Plus window:
SQL> set serveroutput on format wrapped
SQL> declare
l_data numArrTest.numArray;
begin
l_data(1) := 1;
l_data(2) := 2;
l_data(3) := 3;
for i in 1 .. l_data.count
loop
dbms_output.put_line( l_data(i) );
end loop;
numArrTest.num_array( l_data );
dbms_output.put_line( 'after....' );
for i in 1 .. l_data.count
loop
dbms_output.put_line( l_data(i) );
end loop;
end;
/
SQL> /
The very last forward slash runs the sql code just entered. The result of running the above code is the following:
1
2
3
after....
2
4
6
PL/SQL procedure successfully completed.
The following ST script was used to run the above stored procedure:
| connection storedProcSpec resultSet querySpec tDict cursor |
(AbtDatabaseConnectionSpec forDbmClass: AbtOracle10DatabaseManager databaseName: 'orcl') connect.
connection := AbtDbmSystem activeDatabaseConnection.
storedProcSpec := AbtStoredProcSpec new
name: 'testSPSpec';
description: 'test simple stored procedure from workspace';
procName: 'numArrTest.num_array';
useSqlCall: true;
inputStringLabel: nil;
description: 'test Store Procedure spec';
inputRow: (AbtCompoundType new
name: 'inputRow';
addField: (((((AbtDatabaseLongIntegerField new name: 'longInt'; nullsOk: true; procBindType: 1) new) arrayOf) count: 3; name: 'numArray'; yourself))).
tDict := AbtOrderedIdentityDictionary new.
tDict at: 'numArray' put: #(1 2 3).
resultSet := connection invokeStoredProcSpec: storedProcSpec withInputDictionary: tDict.
resultSet
The stored procedure in the above script is written using the package name, 'numArrTest.num_array'. Without the package name, the stored procedure would not be callable. Selecting the above, right mouse clicking, and selecting ‘inspect’ from the menu results in the following (Note: If you are not logged in, a dialog box will pop up asking you for your username and password and database name):
The array of numbers passed in was #(1 2 3), each number was multiplied by ‘2’ and an array of numbers was passed out, #(2 4 6).
Last modified date: 06/01/2018