Passing data to a pl/sql block and inserting it into a table
Create a user defined type and a table in SQL:
SQL> edit
Wrote file afiedt.buf
1 CREATE OR REPLACE TYPE employee_info_type AS OBJECT (
2 id INTEGER,
3* department VARCHAR(50))
SQL> /
Type created.
SQL> edit
Wrote file afiedt.buf
1 CREATE TABLE employees (
2 name VARCHAR2(20),
3* employee_info employee_info_type)
SQL> /
Table created.
Insert one row:
SQL> insert into employees values ('Bob', employee_info_type(1, 'Accounting'));
1 row created.
Select the row:
SQL> select * from employees;
NAME EMPLOYEE_INFO(ID, DEPARTMENT)
--------- ----------------------------------------------------------
Bob EMPLOYEE_INFO_TYPE(1, 'Accounting')
To insert multiple rows into the above table, execute the following PL/SQL block from ST:
'BEGIN
FORALL i IN 1 .. :inEmpCount
INSERT INTO employees
VALUES (:inEmpNames(i), employee_info_type(:inEmpIds(i), :inEmpDepts(i)));
END; '
The ST code, to insert four lines using the above block, would be as follows (this was done in a workspace where variables are automatically declared):
"Data to insert."
empNames := #( 'joe' 'jim' 'mary' 'sue').
empIds := #( 21 22 23 24).
empDepts := #('tractors' 'planting' 'tilling' 'harvesting').
"varchar sizes"
empNameVarCharSize := 20.
empDeptVarCharSize := 50.
"Log on and get the connection"
(AbtDatabaseConnectionSpec forDbmClass: AbtOracle10DatabaseManager databaseName: 'orcl') connect.
connection := AbtDbmSystem activeDatabaseConnection
flushCache;
autoCommit: true;
commitUnitOfWork;
yourself.
"PL/SQL Block"
plSqlStr := 'BEGIN
FORALL i IN 1 .. :inEmpCount
INSERT INTO employees
VALUES (:inEmpNames(i), employee_info_type(:inEmpIds(i), :inEmpDepts(i)));
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."
empCount := 'inEmpCount'.
empArrayName := 'inEmpNames'.
empArrayId := 'inEmpIds'.
empArrayDept := 'inEmpDepts'.
"data field name and size, used below"
inputRowName := 'inputRow'.
numberOfRows := empNames size.
"Setup the param marker types"
empCountInt := AbtOracleIntegerField new
name: empCount;
yourself.
empNameVarChar := AbtOracleVarCharField new
name: empArrayName;
count: empNameVarCharSize;
yourself.
empIdInt := AbtOracleIntegerField new
name: empArrayId;
yourself.
empDeptVarChar := AbtOracleVarCharField new
name: empArrayDept;
count: empDeptVarCharSize;
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: empCountInt;
addField: (AbtArrayField new
arrayType: empNameVarChar;
count: numberOfRows;
name: empArrayName;
yourself);
addField: (AbtArrayField new
arrayType: empIdInt;
count: numberOfRows;
name: empArrayId;
yourself);
addField: (AbtArrayField new
arrayType: empDeptVarChar;
count: numberOfRows;
name: empArrayDept;
yourself).
"Add the Data"
aDict := Dictionary new
at: empCount put: numberOfRows;
at: empArrayName put: empNames;
at: empArrayId put: empIds;
at: empArrayDept put: empDepts;
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
Select the rows again using SQL:
SQL> select substr(name,1,5), e.employee_info.id, substr(e.employee_info.department,1,40) from employees e;
SUBST EMPLOYEE_INFO.ID SUBSTR(E.EMPLOYEE_INFO.DEPARTMENT,1,40)
---------- ----------------------------- -----------------------------------------------------------------------
Bob 1 Accounting
joe 21 tractors
jim 22 planting
mary 23 tilling
sue 24 harvesting