1 2 Previous Next 16 Replies Latest reply: Feb 18, 2013 2:57 AM by BluShadow RSS

    GTT to pass data from one sp to another sp.

    947771
      Hi,

      Is it possible to get data from one sp to other sp useing GTT ON COMMIT DELETE ROWS.

      pls give me one example.

      yours sincerelly
        • 1. Re: GTT to pass data from one sp to another sp.
          jeneesh
          Yes, it is possible..

          1. Create a procedure (p1) which will insert data in the GTT.
          2. Call the other procedure(p2) before committing (As per your post, you will be calling P2 from P1 itself). Now, P2 will be able to see the data in the GTT

          Edited by: jeneesh on Feb 11, 2013 5:22 PM
          • 2. Re: GTT to pass data from one sp to another sp.
            BluShadow
            Slight addition required...
            jeneesh wrote:
            Yes, it is possible..

            1. Create a procedure (p1) which will insert data in the GTT.
            2. Call the other procedure(p2) from the same session before committing. Now, P2 will be able to see the data in the GTT
            • 3. Re: GTT to pass data from one sp to another sp.
              Karthick_Arp
              944768 wrote:
              Hi,

              Is it possible to get data from one sp to other sp useing GTT ON COMMIT DELETE ROWS.

              pls give me one example.

              yours sincerelly
              GTT data is preserved maximum at Session level. So when you get into Session the GTT is empty. You populate the GTT with data and when you leave the session the data gets removed.

              So you are saying you want to call a stored procedure SP1 which will insert data into GTT and in the same session another stored procedure SP2 will be using it?

              What is the source of data for GTT, a normal Table? In that case it would smell like a bad design for me. I am not being judgmental, but storing data midway for processing has never been best of the approach in Oracle.

              So i would suggest you discuss your overall objective first rather before picking up a tool to use.
              • 4. Re: GTT to pass data from one sp to another sp.
                user13325846
                Yes...I agree with Karthick.

                Think about ref cursor for any kind of data transfer between procedures.
                • 5. Re: GTT to pass data from one sp to another sp.
                  947771
                  what if i call both procs sepratlly with transaction?
                  that means one sp will not call other sp, instead , they will be called sepratlly in a transation.

                  yours sincerelly
                  • 6. Re: GTT to pass data from one sp to another sp.
                    jeneesh
                    944768 wrote:
                    what if i call both procs sepratlly with transaction?
                    that means one sp will not call other sp, instead , they will be called sepratlly in a transation.

                    yours sincerelly
                    Could you explain, what is your actual business requirement..?

                    Each object is designed for different purposes - thing is that you need to choose the best method for your scenario..

                    As already told many times - don't use hammer to tighten a screw, even if you can do that. Svrew driver is designed for that..

                    There are scenarios, for which it will be better to use GTT. There are situations where REF CURSORS will be better. Some time collections..

                    Explain, your scenario - people will be able to help you..

                    I repeat, Don't try to tighten the screw with hammer..
                    • 7. Re: GTT to pass data from one sp to another sp.
                      BluShadow
                      944768 wrote:
                      what if i call both procs sepratlly with transaction?
                      that means one sp will not call other sp, instead , they will be called sepratlly in a transation.

                      yours sincerelly
                      As long as they are both called in the same session the data can be in the GTT, regardless of where they're called or if they call each other or not.
                      • 8. Re: GTT to pass data from one sp to another sp.
                        947771
                        well i am a new bie

                        requirement of the sp is

                        in a SP i want to loop throug certain data.

                        step1 i am creating GTT or truncating if exist.

                        step2 inserting some data in it

                        step3 looping throught the rows of GTT and based on each row values i am inserting deleting update in diffrent tables.

                        Finally commiting in last of sp after selecting some data to show .

                        1)is it required to commit in sp? if yes then what should be the correct place?

                        2)i think one should commit from prestantion layer or BAL or DAL?

                        3) what if one has to use this sp and other such sps, only in database in one transation and there is no presetaion layer call?


                        please consider all cases.

                        yours sincerely

                        Edited by: 944768 on Feb 14, 2013 1:54 AM

                        Edited by: 944768 on Feb 14, 2013 1:55 AM
                        • 9. Re: GTT to pass data from one sp to another sp.
                          947771
                          i have explained the requiremnt.
                          • 10. Re: GTT to pass data from one sp to another sp.
                            Solomon Yakobson
                            944768 wrote:
                            i have explained the requiremnt.
                            And based on it there is a good chance you don't need PL/SQL at all. It might be implemented using one INSERT ALL. Other than that, yes you can commit in SP, but in most cases it is not the best idea. Since SP can be called under different circumstances normally you let caller to decide what to do - commit or rollback.

                            SY.
                            • 11. Re: GTT to pass data from one sp to another sp.
                              rp0428
                              >
                              well i am a new bie

                              requirement of the sp is

                              in a SP i want to loop throug certain data.

                              step1 i am creating GTT or truncating if exist.

                              step2 inserting some data in it

                              step3 looping throught the rows of GTT and based on each row values i am inserting deleting update in diffrent tables.
                              >
                              You still haven't explained why you need to use a GTT instead of just a SQL query. Let the SP 'loop' thru a cursor result set for the 'inserting deleting update' in different tables.
                              >
                              Finally commiting in last of sp after selecting some data to show .

                              1)is it required to commit in sp? if yes then what should be the correct place?

                              2)i think one should commit from prestantion layer or BAL or DAL?
                              >
                              The COMMIT should be done after the unit of work has been completed. If the client initiates the unit of work the client should do the commit. If the unit of work belongs to the SP then the sp chould do the commit.

                              What do you mean commiting 'after selecting some data to show'? Does the user need to see committed data? Or do they need to see the data BEFORE it is committed?

                              Why would you show data to the user that might not be committed afterward?
                              >
                              3) what if one has to use this sp and other such sps, only in database in one transation and there is no presetaion layer call?
                              >
                              How do you select 'some data to show' if there is no client to show it to?

                              Nothing in what you posted shows any need to create or use a GTT.
                              • 12. Re: GTT to pass data from one sp to another sp.
                                Sg049
                                If you can able to do it in SQL... Merge statement would be the good option, if not you can use cursor or collections with Bulkcollect and Forall.

                                How much amount of data you process i.e insert/update/delete for each call/session?


                                It all depends.... As mentioned above "Don't use the hammer to tighten the screw" ;)
                                • 13. Re: GTT to pass data from one sp to another sp.
                                  947771
                                  as Mr jeneesh said there are scenarios to use GTT,cursors and collection

                                  1)please tel me senarios in general.

                                  2)to loop cursor, i show there are many loops avialable.

                                  please tel me which loop is used in which condition.( i mean good practice).

                                  like some body told me for small data use collections and for big use cursors, after that question came to me which looping is good in which condition?

                                  yours sincerly.

                                  Edited by: 944768 on Feb 17, 2013 10:56 PM

                                  Edited by: 944768 on Feb 17, 2013 10:57 PM
                                  • 14. Re: GTT to pass data from one sp to another sp.
                                    Sg049
                                    Oracle has only 2 kind of loops
                                    1)While loop 2) For loop.

                                    Even if you use collections for a large amount of data please make sure to use "LIMIT" key word so that you can re use the same amount of memory for each iteration and another advantage is using the collections is you could use collection methods to perform certain validations on data.

                                    for passing data from one procedure to other procedure you can create ref cursor and you can make use of it.

                                    Here are the links for basic understanding of ref cursors

                                    http://www.devshed.com/c/a/Oracle/Working-with-REF-CURSOR-in-PL-SQL/

                                    http://www.oracle-base.com/articles/misc/using-ref-cursors-to-return-recordsets.php

                                    Thanks,
                                    sg
                                    1 2 Previous Next