Returning Into

Posted on 2004-04-13
Medium Priority
Last Modified: 2013-12-26
PB Version 8 using 8 oracle drivers
Database is Oracle 9i

I have a table I want to add a new row into.  The table has a primary key which is being created using a sequence.  The sequence is generated via a trigger.

I need to be able to get the new primary key from the new row to use in children rows.

I have heard of an oracle feature called RETURNING INTO.  It works something like this:

Insert Into schema.table
(col2, col3)
Values (:bind1, :bind2)
Returning col1 Into :variable
using sqlca;

if sqlca.sqlcode = -1 then
messagebox("Database Error",sqlca.sqlerrtext,Exclamation!)
end if

This above statement executes fine but I can't seem to capture the col1 (aka primary key column) into a variable for later use.
Question by:Lordain
  • 3
  • 2
LVL 15

Expert Comment

ID: 10819114
Is col defined as sequence column. If so see link below:



Author Comment

ID: 10819929
Namasi -

The column I want to capture is the primary key (a sequence value set up in oracle).  The database has been set up to use a before insert trigger to get the nextval of the sequence to insert into the row.  Therefore, I can not get the sequence this way because I will not be able to insert a value into the row as the trigger will fire inserting a different number into the row.

I need to be able to determine what the trigger inserted for the primary key (sequence) at the time the insert call is made.  I understand returning into should do this.  The above insert SQL statement does work but not the returning into part (Oracle does not complain about the SQL statement).  The row is inserted with the sequence but I unable to determine what number was used for the primary key insertion because the trigger handles that insert for me.

What I believe is happening is Oracle does execute the Returning into part of the statment just fine but I can't seem to figure out how to capture the returning value in powerbuilder.
LVL 18

Accepted Solution

diasroshan earned 750 total points
ID: 10820319
hi lordain,
i tried the following,,,,what u need is a workaround....
..instead of writing the insert statement in powerbuilder why don't u use a procedure to
insert into the table with the returning clause and return that value from the procedure....
...now u will get ur desired pk value as the procedures return value in powerbuilder script....

create table auto_increment (
  id                number not null constraint pk_ai primary key,
  something         number

create sequence seq_auto_increment start with 1 increment by 1;

create or replace trigger ai
  before insert on auto_increment
  for each row
    if :new.id is null then
      select seq_auto_increment.nextval into :new.id from dual;
    end if;

...use a procedure here to insert into ur table..... i'm sure u'll get the procedures return value in powerbuilder....

  i number;
  insert into auto_increment (something) values (9) returning id into i;
  dbms_output.put_line('id was: '||i);

  insert into auto_increment (something) values (8) returning id into i;
  dbms_output.put_line('id was: '||i);

  insert into auto_increment (something) values (4) returning id into i;
  dbms_output.put_line('id was: '||i);

  insert into auto_increment (something) values (5) returning id into i;
  dbms_output.put_line('id was: '||i);

...hope it works....

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

LVL 18

Expert Comment

ID: 10866232
hi lordain,

did any of the comments help u...
...in case u have found some other solution do let us know ...it can help us too....


Author Comment

ID: 10871412
Hi Rosh -

Our team came up with a generic function residing on the oracle side.    It accepts 4 parameters.  The schema owner, the view, a string array of column names and a string array of values.  It returns the primary key as a string.  If an error occurs it returns the error message as a string.

On the powerbuilder side of things I created a local external function in a standard class object inherited from transaction which I called u_rpcfunc.  I had to change the sqlca object from type transaction to u_rpcfunc.

In the global variables area I typed the following:
u_rpcfunc sqlca

To call this function I just type string_var = sqlca.function_name(parameters)

This solution allows for easy to maintain remote procedure function calls and is generic enough to use with any powerbuilder application which ultimately was what we were looking for.

Our IT team came up with this solution prior to getting your response.  I did forward your solution to them but we decided to go ahead and keep the solution we have in place now.

I appreciate your answer though although we went with another solution your answer seems plausible but I am unable to confirm.

LVL 18

Expert Comment

ID: 10875187
hi lordain,

its nice to know that u have got a generic solution to ur problem...
...well as far as ur question goes... i sent u the solution to ur question regarding 'RETURNING INTO' and i sent it on the same day u posted ur question....
...well i feel my comment catered to ur question and now its up to u to award the points to the right answer to the question posted by u...

hope u do the needful...


Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Programmer's Notepad is, one of the best free text editing tools available, simply because the developers appear to have second-guessed every weird problem or issue a programmer is likely to run into. One of these problems is selecting and deleti…
How to install Selenium IDE and loops for quick automated testing. Get Selenium IDE from http://seleniumhq.org Go to that link and select download selenium in the right hand column That will then direct you to their download page. From that p…
This tutorial covers a step-by-step guide to install VisualVM launcher in eclipse.
The viewer will learn how to use and create keystrokes in Netbeans IDE 8.0 for Windows.
Suggested Courses

621 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question