Obtaining an inserted recordid on Oracle db

Question

I'm using Oracle on database server, from an XP client, using VB6 and ADO. In one transaction, I'm inserting one record into a parent table, which has a trigger and sequence to create a unique recordid, then that recordid is used for the relationship to a child table for a variable number of inserts to the child table. For performance, this is being sent in one execute command from my client app. For instance (simplified example):

declare Recordid int;
begin
insert into ParentTable (field list) Values (data list);
Select ParentTableSequence.currVal into Recordid from dual;
insert into ChildTable (RecordID, field list) Values (Recordid, data list);
insert into ChildTable (RecordID, field list) Values (Recordid, data list);
... multiple, variable number of additional ChildTable inserts
commit;
end;

This is working fine. My question is: I also need to return to the client the Recordid that was created for the inserts. On SQL Server, I can add something like a select to Scope_Identity() after the commit to return a recordset to the client with the unique id.

But how can I do something similar for Oracle (doesn't have to be a recordset, I just need that long integer value)? I've tried a number of things based on results from searching the 'net, but have failed in finding a solution.

Thanks!
MarkL

Answer 1

These two lines can be compressed into a single statement:

--  insert into ParentTable (field list) Values (data list);
--  Select ParentTableSequence.currVal into Recordid from dual;
insert into ParentTable (field list) Values (data list)
  returning ParentTable.ID into Recordid;

If you want to pass the ID back to the calling program you will need to define your program as a stored procedure or function, returning Recordid as an OUT parameter or a RETURN value respectively.

Edit

MarkL commented:

This is more of an Oracle PL/SQL question than anything else, I believe.

I confess that I no nothing about ADO, so I don't know whether the following example will work in your case. It involves building some infrastructure which allows us to pass an array of values into a procedure. The following example creates a new department, promotes an existing employee to manage it and assigns two new hires.

SQL> create or replace type new_emp_t as object
  2      (ename varchar2(10)
  3       , sal number (7,2)
  4       , job varchar2(10));
  5  /

Type created.

SQL>
SQL> create or replace type new_emp_nt as table of new_emp_t;
  2  /

Type created.

SQL>
SQL> create or replace procedure pop_new_dept
  2      (p_dname in dept.dname%type
  3       , p_loc in dept.loc%type
  4       , p_mgr in emp.empno%type
  5       , p_staff in new_emp_nt
  6       , p_deptno out dept.deptno%type)
  7  is
  8      l_deptno  dept.deptno%type;
  9  begin
 10      insert into dept
 11          (dname, loc)
 12      values
 13          (p_dname, p_loc)
 14      returning deptno into l_deptno;
 15      update emp
 16          set deptno = l_deptno
 17              , job = 'MANAGER'
 18              , mgr = 7839
 19          where empno = p_mgr;
 20      forall i in p_staff.first()..p_staff.last()
 21          insert into emp
 22              (ename
 23                  , sal
 24                  , job
 25                  , hiredate
 26                  , mgr
 27                  , deptno)
 28          values
 29              (p_staff(i).ename
 30                , p_staff(i).sal
 31                , p_staff(i).job
 32                , sysdate
 33                , p_mgr
 34                , l_deptno);
 35      p_deptno := l_deptno;
 36  end pop_new_dept;
 37  /

Procedure created.

SQL>
SQL> set serveroutput on
SQL>
SQL> declare
  2      dept_staff new_emp_nt;
  3      new_dept dept.deptno%type;
  4  begin
  5      dept_staff := new_emp_nt(new_emp_t('MARKL', 4200, 'DEVELOPER')
  6                               , new_emp_t('APC', 2300, 'DEVELOPER'));
  7      pop_new_dept('IT', 'BRNO', 7844, dept_staff, new_dept);
  8      dbms_output.put_line('New DEPTNO = '||new_dept);
  9  end;
 10  /
New DEPTNO = 70

PL/SQL procedure successfully completed.

SQL>

The primary keys for both DEPT and EMP are assigned through triggers. The FORALL syntax is a very efficient way of inserting records (it also works for UPDATE and DELETE). This could be written as a FUNCTION to return the new DEPTNO instead, but it is generally considered better practice to use a PROCEDURE when inserting, updating or deleting.

That would be my preferred approach but I admit it's not to everybody's taste.

Edit 2

With regards to performance, bulk operations using FORALL will definitely perform better than a handful of individual inserts. In SQL, set operations are always preferable to record-by-record. However, if we are dealing with only a handful of records each time it can be hard to notice the difference.

Building a PL/SQL collection (what you think of as a temporary table in SQL Server) can be expensive in terms of memory. This is especially true if there are many users running the code, because it comes out of the session level allocation of memory, not the Shared Global Area. When we're dealing with a large number of records it is better to populate an array in chunks, perhaps using the BULK COLLECT syntax with a LIMIT clause.

The Oracle online documentation set is pretty good. The PL/SQL Developer's Guide has a whole chapter on Collections. Find out more.