Re: Dynamic SQL using INSERT with a predefined query and an id.

Change your insert text to include a bind variable, viz:

    'insert into table select :b1, col1, ....'

then your pl/sql wrapper does:

m_var := seq.nextval
m_string := {the string you have selected}

execute immediate m_string using m_var;

--
Jonathan Lewis

Host to The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Author of:
Practical Oracle 8i: Building Efficient Databases
See http://www.jlcomp.demon.co.uk/book_rev.html

Seminars on getting the best out of Oracle
See http://www.jlcomp.demon.co.uk/seminar.html






Brian Tkatch wrote in message <3b3a3d0c.428070484_at_news.alt.net>...


>On 27 Jun 2001 12:33:29 -0700, crappygolucky_at_hotmail.com (crappy)

>wrote:

>

>>if i understand your problem, i have an awful hack for you.

>>

>>use the sequence, but subtract rownum from it (and add 1).  i.e., your

>>Query in the List table looks like

>>

>>"select myseq.nextval + 1 - rownum, ..."

>>

>>like i said, it's a hack, but for each "population" that first column

>>should be the same number.  another drawback is, the sequence will

>>still iterate of course, and you will have big gaps in the id's.

>>

>

>Scary, but strangely enticing. I would not want to use this. Both,

>because of the hack, and the outside reference to the sequence from a

>query.

>

>Brian