I'm working on setting up a 3rd party app to log to the database. The app only supports a very simple ODBC api. None of the Oracle specific features are available.
Given:
create table t1 (pk_t1 number, val number);
create table t2 (pk_t2 number, fk_t1 number);
create sequence t1_seq start with 1 increment by 1;
create sequence t2_seq start with 1 increment by 1;
I would normally insert into t1:
insert into t1 values (t1_seq.nextval, :val) returning pk_t1 into :my_pk1;
and then
insert into t2 values (t2_seq.nextval, :val2) returning pk_t2 into :my_pk2;
But the API doesn't support the
returning ... clause...
The options I see are:
1) "select t1_seq.nextval from dual", insert the record into both tables.
2) Write stored procedures that do the inserts for me, and return the PK values.
I don't like either one of these options very much, do you have any better solutions?
Thanks,
John
I love option #2, in fact I like it so much, I would be putting 100% of my sql statements that start with
insert
update
delete
merge
select
into them - and only calling stored procedures - especially if I were using a crippled ODBC driver
for then - then I could take advantage of everything and have the most performant code possible.
If the odbc api supports calling stored procedures with IN OUT parameters, it should also support you executing this plsql block too - wouldn't it?
"begin insert into ...values ( ?, ?... ) returning id into ?"
if it can do:
"begin proc( ?, ?, ... , ? ); end;"
it should be able to invoke the insert in the anonymous block.