Stored procedure using an array of strings
To create a test stored procedure using an array of strings in Oracle, enter the following in a SQL Plus window:
SQL> create or replace package arrtest as type buf_arr is
table of varchar2(256) index by binary_integer;
procedure str_arr (p1 in out buf_arr);
end;
SQL> create or replace package body arrtest as
procedure str_arr (p1 in out buf_arr) is
begin
for i in 1 .. p1.count
loop
p1(i) := p1(i) || ' - modified at index ' || i;
end loop;
end;
end;
SQL> /
Package body created.
To test the stored procedure from SQL Plus, enter the following in a SQL Plus window:
SQL> declare
l_data arrtest.buf_arr;
begin
l_data(1) := 'Hello1';
l_data(2) := 'Hello2';
l_data(3) := 'Hello3';
dbms_output.put_line( 'before....' );
for i in 1 .. l_data.count
loop
dbms_output.put_line( l_data(i) );
end loop;
arrtest.str_arr( 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 last forward slash executes the above SQL. The results of executing the above code are:
before....
Hello1
Hello2
Hello3
after....
Hello1 - modified
Hello2 - modified
Hello3 - modified
PL/SQL procedure successfully completed.
To execute the above PL SQL Stored procedure from Smalltalk, highlight and inspect the following script:
| connection storedProcSpec resultSet tDict |
(AbtDatabaseConnectionSpec forDbmClass: AbtOracle10DatabaseManager databaseName: 'orcl') connect.
connection := AbtDbmSystem activeDatabaseConnection.
storedProcSpec := AbtStoredProcSpec new
name: 'testSPSpec';
description: 'test simple stored procedure from workspace';
procName: 'arrtest.str_arr';
useSqlCall: true;
inputStringLabel: nil;
description: 'test Store Procedure spec';
inputRow: (AbtCompoundType new
name: 'inputRow';
addField: (((((AbtOracleVarCharField new name: 'varChar'; nullsOk: true; procBindType: 1; length: 100) new) arrayOf) count: 3; name: 'varcharArray'; yourself))).
tDict := Dictionary new.
tDict at: 'varcharArray' put: #('hello1' 'hello2' 'hello3').
resultSet := connection invokeStoredProcSpec: storedProcSpec withInputDictionary: tDict.
resultSet
Note in the above code, the length for the AbtOracleVarCharField is 100 bytes. Always make sure enough bytes are declared to hold any modifications done to the strings. Also note that the ‘procName’ is specified by using the package name and the proc name.
The result of inspecting the above script is:
The result of inspecting the above script is:
The above is a dictionary with key, ‘varcharArray’ and value, an array of strings. Each string in the array was changed by having ‘ - modified’ appended to it.
Last modified date: 06/01/2018