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).