Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> x-post (in c.d.o.misc) "returning into" in Pro C

x-post (in c.d.o.misc) "returning into" in Pro C

From: Pat Paulson <miapjp_at_chartermi.net>
Date: Mon, 05 Apr 2004 21:04:50 -0500
Message-ID: <1074422h3pcll80@corp.supernews.com>


Cross-posted on comp.databases.oracle.misc.

Hi,

This has been driving me nuts and in all my Googling I haven't yet been able to find if I just can't do it or my syntax/variables are wrong.

I've got a Pro C query and I want to do a "returning into" to retrieve a newly inserted Oracle sequence number (so I can have it in a variable and use that sequence number to associate detail rows for the inserted header row). The Pro C guide gives the syntax but also mentions that it's available in dynamic SQL - but doesn't say if it's **only** available that way. Also, the guide mentions "host" variables, and I'm assuming that's the same as the bind vars I'm using - am I wrong?

Here's an edited version of the code:

EXEC SQL BEGIN DECLARE SECTION; char n_seq_number [21] = {NULL};

EXEC SQL VAR n_seq_number IS STRING

EXEC SQL END DECLARE SECTION; EXEC SQL
    INSERT INTO orders

                (wh,
         ord_no,
         customer,
         ord_date
         line_number)
          VALUES(:WH_ID,
             :ord_no,
         :customer,
         sysdate,
         my_seq.nextval)

    RETURNING to_char(line_number) INTO :n_seq_number;

the number is NUMBER(20), so I can't use integers. Although hte sequence is only up to 7 long, so I tried an int bind variable and still have problems. Oh, sorry, the problem is the first time it executes this (on UNIX (AIX)), I get a segmentation fault and an undecipherable (to me) core dump.

I'd appreciate any help!

Thanks in advance,
Pat Received on Mon Apr 05 2004 - 21:04:50 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US