Order of Operation for SQL

595448

Im not sure if I am in the right form.

I have an apex application that sends over a item_id into table a
I have an insert statement that works where it puts the clob and blob into a table b.
However I need a next_val for item_id to insert another file name aswell in Table a. But after the clob procedure for table b it does not allow me to insert the next
statement but it does keep the value.
Example
File Name.PDF
File2 Name.TIFF

Now they are entered the same time with the same information. How do I get the next value to accep the next file name without hurting the table b clob proceedure.
I hope I made some sort of sense.

Dimitri Gielis

Hello,

I'm not totally clear on your problem. Do you want to save two file browse items in different tables?

Regards,
Dimitri
----------------------------------
http://dgielis.blogspot.com/
http://www.apex-evangelists.com/
http://www.apexblogs.info/

David Pulliam

It sounds like item_id is a key value you are incrementing by hand. If this is the case, this should be managed by a before insert trigger and sequence. If done that way, then you shouldn't have an issue. Have you tried the "returning item_id into variable" clause of the insert statement? That will give you the current id back. However, if you are manually maintaining a PK you should stop and look into before insert triggers and sequences to handle the item id value as I said before because these will always give you a unique value. If this does not provide you with enough information then please describe what your trying to do with item_id alittle better as it is difficult to tell if it is a link between tables a and b or if it is just a PK on one of the tables.











From what I get from your post, it sounds like you have (please correct me if I am wrong):



A Detail Table of some kind (Table A) about the file or maybe a index of some kind.



A Storage table (Table .







Item_ID which is an identification number of some kind on table b that you need in an associated record in table a.







Here is what I would do:







<ol>


<li>Have Item_ID be generated automatically by a trigger.</li>

<li>Insert into table B first using a returning clause to get the item_id and store it into a tmp variable.</li>

<li>Insert into table A using the item_id returned from the insert into table b.</li>
</ol>
Just a note: you will need to repete steps 2 and 3 for each file or put them all as part of the same record and use one insert statement.

Edited by: David Pulliam on Nov 4, 2008 1:57 PM