Discussions
Categories
- 197.1K All Categories
- 2.5K Data
- 546 Big Data Appliance
- 1.9K Data Science
- 450.7K Databases
- 221.9K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 552 MySQL Community Space
- 479 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3.1K ORDS, SODA & JSON in the Database
- 555 SQLcl
- 4K SQL Developer Data Modeler
- 187.2K SQL & PL/SQL
- 21.3K SQL Developer
- 296.3K Development
- 17 Developer Projects
- 139 Programming Languages
- 293K Development Tools
- 110 DevOps
- 3.1K QA/Testing
- 646.1K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 158 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.2K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 19 Java Essentials
- 162 Java 8 Questions
- 86K Java Programming
- 81 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 204 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 466 LiveLabs
- 39 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 175 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 233 Portuguese
Order of Operation for SQL

595448
Member Posts: 20
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.
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.
Answers
-
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/ -
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
This discussion has been closed.