Retrieving data from a pl/sql block using a select statement
First, create a type and a table using SQL:
SQL> edit
Wrote file afiedt.buf
1 CREATE OR REPLACE TYPE phoneType AS OBJECT (
2 areaCode VARCHAR(3),
3* phoneNumber VARCHAR(8))
SQL> /
Type created.
SQL> edit
Wrote file afiedt.buf
1 CREATE TABLE phone (
2 personId INTEGER,
3* personPhone phoneType)
SQL> /
Table created.
Use an anonymous PL/SQL block to insert rows into the above table:
SQL> edit
Wrote file afiedt.buf
1 BEGIN
2 FOR i IN 1 .. 9
3 LOOP
4 INSERT INTO phone VALUES (
5 i,
6 phoneType(TO_CHAR(i + 200), '555-121' || TO_CHAR(i))
7 );
8 END LOOP;
9 COMMIT;
10* END;
SQL> /
PL/SQL procedure successfully completed.
Select the rows:
SQL> select personid id, rpad(p.personphone.areacode,10) area_code, rpad(p.personphone.phonenumber,15) phone_number from phone p;
ID AREA_CODE PHONE_NUMBER
---------- ------------------- --------------------------
1 201 555-1211
2 202 555-1212
3 203 555-1213
4 204 555-1214
5 205 555-1215
6 206 555-1216
7 207 555-1217
8 208 555-1218
9 209 555-1219
9 rows selected.
The job now is to select those rows and get the data into ST. The way to do that is execute the following PL/SQL block from ST:
BEGIN
SELECT
personId,
p.personPhone.areaCode,
p.personPhone.phoneNumber
BULK COLLECT INTO
:outIDs,
:outAreaCodes,
:outPhoneNumbers
FROM phone p;
END;
Three parameter markers (variable names that start with a colon) are used above which are used by ST to actually get the data. The ST script is:
"Log on and get the connection"
(AbtDatabaseConnectionSpec forDbmClass: AbtOracle10DatabaseManager databaseName: 'orcl') connect.
connection := AbtDbmSystem activeDatabaseConnection
flushCache;
autoCommit: true;
commitUnitOfWork;
yourself.
"Find out how many rows there are"
querySpec := 'SELECT COUNT(*) FROM phone' abrAsQuerySpec.
result := connection resultTableFromQuerySpec: querySpec ifError: [:ex | ex inspect]. numberOfRows := (result first asDictionary at: 'COUNT(*)') asInteger.
"PL/SQL Block"
plSqlStr := 'BEGIN
SELECT
personId,
p.personPhone.areaCode,
p.personPhone.phoneNumber
BULK COLLECT INTO
:outIDs,
:outAreaCodes,
:outPhoneNumbers
FROM phone p;
END;'.
"Remove cr/lf, leaving lf only. Some Oracles do not like cr’s"
plSqlStr := plSqlStr collect: [ :ch |
ch = Character cr
ifTrue: [ Character space ]
ifFalse: [ ch ] ].
"Param Marker names in the PL/SQL block."
perIDArrayName := 'outIDs'.
aCodeArrayName := 'outAreaCodes'.
pNumberArrayName := 'outPhoneNumbers'.
"Setup the param marker types"
perIDInt := AbtOracleIntegerField new
name: perIDArrayName;
yourself.
aCodeVarChar := AbtOracleVarCharField new
name: aCodeArrayName;
count: 3;
yourself.
pNumberVarChar := AbtOracleVarCharField new
name: pNumberArrayName;
count: 8;
yourself.
"Setup the data fields. NOTE: Data fields must be in the same
Order as the PL/SQL block"
inOutParamRow := AbtCompoundType new
name: inputRowName;
addField: (AbtArrayField new
arrayType: perIDInt;
count: numberOfRows;
name: perIDArrayName;
yourself);
addField: (AbtArrayField new
arrayType: aCodeVarChar;
count: numberOfRows;
name: aCodeArrayName;
yourself);
addField: (AbtArrayField new
arrayType: pNumberVarChar;
count: numberOfRows;
name: pNumberArrayName;
yourself).
"Add the arrays to hold the data returned"
aDict := Dictionary new.
aDict
at: perIDArrayName put: ((1 to: numberOfRows) collect: [ :i | i ]);
at: aCodeArrayName put: ((1 to: numberOfRows) collect: [ :i | i asString ]);
at: pNumberArrayName put: ((1 to: numberOfRows) collect: [ :i | i asString ]);
yourself.
"Create the anonymous block spec"
anonymousBlockSpec := AbtAnonymousBlockSpec new
plSqlString: plSqlStr;
useSqlCall: true;
parameters: inOutParamRow.
"Execute the block"
resultSet := connection invokeAnonymousBlockSpec: anonymousBlockSpec withInputDictionary: aDict.
resultSet
The result will be stored in ‘aDict’:
Last modified date: 06/01/2018