Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions

Returning Into

Posted on 2004-04-13
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 250 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....

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone 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

Update (December 2011): Since this article was published, the things have changed for good for Android native developers. The Sequoyah Project (http://www.eclipse.org/sequoyah/) automates most of the tasks discussed in this article. You can even fin…
Here is a helpful source code for C++ Builder programmers that allows you to manage and manipulate HTML content from C++ code, while also handling HTML events like onclick, onmouseover, ... Some objects defined and used in this source include: …
The viewer will learn how to use and create keystrokes in Netbeans IDE 8.0 for Windows.
The viewer will learn how to use and create new code templates in NetBeans IDE 8.0 for Windows.

792 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