Support of Oracle PL/SQL REF CURSORs and Nested Tables
From Cincom® VisualWorks® 7.8 and Cincom® ObjectStudio® 8.3, Cincom starts to support Oracle PL/SQL REF CURSORs and Nested Tables.
Nested Table is an Oracle data type used to support columns containing sub-tables. PL/SQL REF CURSORs is a data structure pointing to the result set of the sub-tables.
Support of Oracle PL/SQL REF CURSORs and Nested Tables in VisualWorks
In VisualWorks, Oracle Nested Tables will be returned as prepared OracleSessions, and users can use them in the same way as OracleSessions.
When using PL/SQL REF CURSORs in functions and procedures, users should use instances of OracleSession to bind to them in bindInput or through binding variables, but not both at the same time.
In the following Workspace script, we’ll demonstrate different ways of using Oracle PL/SQL REF CURSORs and Nested Tables in VisualWorks:
“Connect to an Oracle database.”
conn := OracleConnection new.
conn username: ‘username’;
password: ‘password’;
environment: ‘OracleEnv’.
conn connect.
“Example 1: retrieving values and a nested table and then return the results as Arrays.”
“Select the departments’ number, name, and a list of the names of all employees working for that specific department returned as a prepared OracleSession. The results will be printed in the Transcript Window.”
sess := conn getSession.
sess prepare: ‘
SELECT d.deptno,
d.dname,
CURSOR(SELECT e.ename
FROM scott.emp e
WHERE e.deptno = d.deptno
) emps
FROM scott.dept d’.
sess execute.
ansStrm := sess answer.
[ansStrm == #noMoreAnswers] whileFalse:
[(ansStrm == #noAnswerStream) ifFalse: [
[ansStrm atEnd] whileFalse:
[| row cur ansStrm1 recs |
row := ansStrm next.
Transcript show: ‘Department:’; cr.
Transcript show: row printString; cr.
“Get the ref cursor, returned as an OracleSession.”
cur := row at: 3.
ansStrm1 := cur answer.
recs := ansStrm1 upToEnd.
Transcript show: (‘ Employees: ‘, recs printString); cr; cr
].
ansStrm := sess answer]].
sess disconnect.
” Example 2: retrieving values and a nested table and then return the results as instances of a user-defined class. In order for this example to work, you have to define a class called Department with instance variables deptid and emps. You also need to define the setter and getter methods for the instance variable using their names. ”
“Select the departments’ number, and a list of the names of all employees working for that specific department is returned as a prepared OracleSession.”
sess := conn getSession.
sess prepare: ‘
SELECT d.deptno deptid,
CURSOR(SELECT e.ename
FROM scott.emp e
WHERE e.deptno = d.deptno
) emps
FROM scott.dept d’.
sess bindOutput: Department new.
sess execute.
ansStrm := sess answer.
[ansStrm == #noMoreAnswers] whileFalse:
[(ansStrm == #noAnswerStream) ifFalse: [
[ansStrm atEnd] whileFalse:
[| dept cur ansStrm1 recs |
dept := ansStrm next.
Transcript show: ‘Department: ‘.
Transcript show: dept deptid printString; cr.
“Get the ref cursor, returned as an OracleSession.”
cur := dept emps.
ansStrm1 := cur answer.
recs := ansStrm1 upToEnd.
Transcript show: (‘ Employees: ‘, recs printString); cr; cr
].
ansStrm := sess answer]].
sess disconnect.
“Positional and name binding for REF CURSORs used in Oracle functions and procedures.”
“Create an Oracle package.”
sess := conn getSession.
sess prepare: ‘create or replace package types
as
type cursorType is ref cursor;
end;
‘.
sess execute.
ansStrm := sess answer.
ansStrm := sess answer.
“Create a test function returning all employee names and numbers from the scott.emp table.”
sess prepare: ‘create or replace function sp_ListEmp return types.cursortype
as
l_cursor types.cursorType;
begin
open l_cursor for select ename, empno from scott.emp order by ename;
return l_cursor;
end;
‘.
sess execute.
ansStrm := sess answer.
ansStrm := sess answer.
“Create a test procedure returning all employee names and numbers from the scott.emp table.”
sess prepare: ‘create or replace procedure getemps( p_cursor in out types.cursorType )
as
begin
open p_cursor for select ename, empno from scott.emp order by ename;
end;
‘.
sess execute.
ansStrm := sess answer.
ansStrm := sess answer.
“Create another test procedure returning the employee names and numbers in a department.”
sess prepare: ‘create or replace procedure getemps_1( deptid int, emps in out types.cursorType )
as
begin
open emps for select ename, empno from scott.emp where deptno = deptid;
end;
‘.
sess execute.
ansStrm := sess answer.
ansStrm := sess answer.
sess disconnect.
“Invoke the test function. Binding by name.”
sess := conn getSession.
cur := conn getSession.
sess preparePLSQL: ‘BEGIN :success := sp_ListEmp; END;’.
“Binding the session for the cursor reference.”
sess bindVariable: #success value: cur.
sess execute.
cur1 := sess bindVariable: #success.
“Retrieve the contents of the cursor.”
ans := cur1 answer.
ans upToEnd inspect.
cur1 disconnect.
sess disconnect.
“Invoke the test function. Binding by position.”
sess := conn getSession.
cur := conn getSession.
sess preparePLSQL: ‘BEGIN :1 := sp_ListEmp; END;’.
sess bindInput: (Array with: cur).
sess execute.
“Retrieve the contents of the cursor.”
ans := cur answer.
ans upToEnd inspect.
cur disconnect.
sess disconnect.
“Invoke the first test procedure, binding an Array.”
sess := conn getSession.
cur := conn getSession.
sess preparePLSQL: ‘BEGIN getemps(?); END;’.
sess bindInput: (Array with: cur).
sess execute.
“Retrieve the contents of the cursor.”
ans := cur answer.
res := ans upToEnd inspect.
cur disconnect.
sess disconnect.
“Invoke the second test procedure, binding an Array.”
sess := conn getSession.
cur := conn getSession.
sess preparePLSQL: ‘BEGIN getemps_1(?, ?); END;’.
sess bindInput: (Array with: 20 with: cur).
sess execute.
“Retrieve the contents of the cursor.”
ans := cur answer.
res := ans upToEnd inspect.
cur disconnect.
sess disconnect.
“Create an instance of user-defined class for binding.”
dept := (Department new)
deptid: 10;
emps: conn getSession.
“Invoke the second test procedure, binding an instance of user-defined class.”
sess := conn getSession.
sess preparePLSQL: ‘BEGIN getemps_1(:deptid, :emps); END;’.
sess bindInput: dept.
sess execute.
“Retrieve the contents of the cursor.”
cursor := dept emps.
ans := cursor answer.
res := ans upToEnd inspect.
cursor disconnect.
sess disconnect.
conn disconnect.
Support of Oracle PL/SQL REF CURSORs and Nested Tables in ObjectStudio
In ObjectStudio, you can retrieve information stored in Oracle Nested Tables using either of two alternative approaches:
- Getting information all at once from each referenced Oracle Nested Table. This approach will return the information from each referenced Oracle Nested Table in an ObjectStudio table. To make use of this approach, use the method execSql: to execute an SQL statement that references one or more Oracle Nested Tables.
- Getting information row-by-row from each referenced Oracle Nested Table. This approach will return a cursor for each referenced Oracle Nested Table. To make use of this approach, use the forward-only cursor to execute an SQL statement that references one or more Oracle Nested Tables, then process the records row-by-row.
Code samples for retrieving Oracle Nested Tables and for using Oracle REF Cursors in Oracle functions and procedures
This Workspace script contains the following examples:
¨ Example 1: Retrieve Oracle Nested Table information in two alternative ways
¨ Example 2: Use PL/SQL REF Cursors in Oracle functions and procedures
See below:
“Log on to the Oracle server.”
ret := OracleDatabase logOnServer: #OracleDB user: #username password: #pwd alias: #OracleDB.
“Access the database.”
db := OracleDatabase accessName: #oracleDB.
“Example 1: Retrieve Oracle Nested Table information in two alternative ways.”
“This SQL statement will SELECT the department’s number and name, as well as the names of all employees working for that specific department.”
sqlString := ‘
SELECT d.deptno,
d.dname,
CURSOR(SELECT e.ename
FROM scott.emp e
WHERE e.deptno = d.deptno
) emps
FROM scott.dept d’.
“Get the Oracle Nested Table’s information all at once. To do this, use the execSql: method to return the information, taken from the referenced Oracle Nested Table, in an ObjectStudio table.”
tab := db execSql: sqlString.
“Get the Oracle Nested Table’s information row-by-row. To do this, use a forward-only cursor to return a cursor for the referenced Oracle Nested Table. You can then process the records row-by-row.”
cur := db privateOpenCursorSql: sqlString.
[ (row := cur next) isNilOrEmpty ] whileFalse: [
(‘Dept Number : ‘ + (row at: #DEPTNO)) out.
(‘Dept Name : ‘ + (row at: #DNAME)) out.
cur1 := row at: #EMPS.
cur1 out.
[ (row1 := cur1 next) isNilOrEmpty ] whileFalse: [
(‘ Employee Name : ‘ + (row1 at: #ENAME)) out.
].
].
“Close the REF Cursor.”
cur1 close.
“Close the Cursor.”
cur close.
“Example 2: Use PL/SQL REF Cursors in Oracle functions and procedures.”
“Create a package.”
res := db execSql: ‘create or replace package types
as
type cursorType is ref cursor;
end;
‘.
“Create a test function that will get employee names and employee numbers from table scott.emp.”
res := db execSql: ‘create or replace function fn_ListEmp return types.cursortype
as
l_cursor types.cursorType;
begin
open l_cursor for select ename, empno from scott.emp order by ename;
return l_cursor;
end;
‘.
“Create test procedure 1. This procedure will get employee names and employee numbers from table scott.emp.”
res := db execSql: ‘create or replace procedure getemps( p_cursor in out types.cursorType )
as
begin
open p_cursor for select ename, empno from scott.emp order by ename;
end;
‘.
“Create test procedure 2. This procedure will get employee names and employee numbers, for a specific department, from table scott.emp.”
res := db execSql: ‘create or replace procedure getemps_1( deptid int, emps in out types.cursorType )
as
begin
open emps for select ename, empno from scott.emp where deptno = deptid;
end;
‘.
“Invoke the test function.”
res := db execFunc: ‘fn_ListEmp’ returnType: #REFCURSOR paramTypes: {}.
“Invoke test procedure 1.”
res := db execProc: ‘getemps(:1)’ paramTypes: { #REFCURSOR }.
“Invoke test procedure 2.”
res := db execProc: ‘getemps_1(30, :1)’ paramTypes: { #REFCURSOR