This discussion is archived
1 2 Previous Next 18 Replies Latest reply: Jan 18, 2013 7:01 AM by 6363 Go to original post RSS
  • 15. Re: Inserting data into a table variable
    Paul Horth Expert
    Currently Being Moderated
    Oldman0215 wrote:
    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.
    Yes, because in most cases you can do it in pure SQL. For example:
    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;
    That is you can manipulate the data in the process of selecting it from the original table.
    (The last column example shows you can have quite complex manipulation in SQL).
    Taking the data out of a table into an array, manipulating it and then sticking
    it into another table is slower than doing it in pure SQL and wastes PGA memory.
  • 16. Re: Inserting data into a table variable
    719640 Newbie
    Currently Being Moderated
    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.
  • 17. Re: Inserting data into a table variable
    EdStevens Guru
    Currently Being Moderated
    Oldman0215 wrote:
    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.
    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.
  • 18. Re: Inserting data into a table variable
    6363 Guru
    Currently Being Moderated
    Oldman0215 wrote:

    Trust me is can't all be done in one statement
    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.

    To say it cannot be done is questionable and likely untrue.
    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.

    {message:id=4337747}
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points