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
