returning into in Pro C embedded sql

From: Pat Paulson <miapjp_at_chartermi.net>
Date: Mon, 05 Apr 2004 19:30:39 -0500
Message-ID: <1073uhg6b33i62b@corp.supernews.com>

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 - 19:30:39 CDT