Oldman0215 wrote:Yes, because in most cases you can do it in pure SQL. For example:
Does everyone really need to know why I want to do it? Does it really add value to explain the whole case. The fact that I need to use a CTE, the fact that I need to store it so that I can further manipulate it to get the data into a different table in another format. There really is no reason to cloud the straightforward question with the entire problem I'm trying to solve. When you have a type that holds a collection of records it only makes perfect functional sense that you can load data into it "like" a table, since it is holding a collection of "records". Thanks Kathick and aschefer for steering me in the correct direction. I knew there was something like that but I couldn't find it.
That is you can manipulate the data in the process of selecting it from the original table.
insert into different_table select col1 ,col2 * 3 ,do_something_with(col3) ,max(case when msg_id = 33 and state_id = 27 then substr(message ,15 ,12) when msg_id = 32 and state_id = 27 then substr(message ,54 ,12) end) keep(dense_rank last order by decode(state_id, 28, 0, 27, 1), decode(msg_id, 32, 1, 33, 2, 0), tx_date) venue_code from original_table;
Oldman0215 wrote:Trust me that no one wants to code your entire solution. But it is often the case that someone gets so wrapped up in some preconceived solution that they overlook the possibility that there is a far better solution just by taking a different route. It happens to us all. That's why the constant questions about business objectives. If we know where you want to go it's much easier to figure out the optimal route. We don't need every arcane detail of the business objective, but it is obvious that the business objective is NOT to store data from a table into a pl/sql collection.
Trust me is can't all be done in one statement and if it I could figure out a way it would be soooo conveluted no one would be able to support it. I have hierarchical data that needs to transform the data into a completely different format to get it into another table. I don't need the community to solve the whole problem for me. I appreciate the help but I don't think it is fair for me to take my whole issue and basically have everyone code it for me. I had a need that I know there is a capapbility to do and all I needed was some help to figure out how to do it.
Oldman0215 wrote:I would trust you if you said you don't know how to do it in a single statement and that would be fair enough.
Trust me is can't all be done in one statement
and if it I could figure out a way it would be soooo conveluted no one would be able to support it.In my experience something that runs over 20 times slower and creates 70 times more contention, uses 30 times more undo and 15 times more redo needs more support creates the real support challenges, every day on an ongoing basis.