8 Replies Latest reply: Nov 8, 2013 12:35 AM by user346369 RSS

    How to fetch block into array

    Naamas

      Hi all ,

      I  was wonder if there is any feature in forms 10g to fetch block and to insert it into array or associative array , as if i was doing it as FORALL.

      Thanks In Advanced

      Naama

        • 1. Re: How to fetch block into array
          Naamas

          please your help .

          Thanks In Advanced ,

          Naama

          • 2. Re: How to fetch block into array
            Christian Erlinger

            And why do you want to do that?

             

            cheers

            • 3. Re: How to fetch block into array
              Naamas

              Hi ,

              I have massive amount of records.

              basically i want to do this steps :

              1. move the records into array.

              2. send this array into stored procedure .

              3. insert the array into temporary table .

              4. join this temporary into some table in the d.b

              5. update the array via the results , ant then display  a relevant message into the user.

              I thought to do this in this way in order to reduce the insert data into the d.b tables thru the forms. i wanrt everything to perform " behind the scene " , means in stored procedure.

              Thanks In Advanced

              Naama

              • 4. Re: How to fetch block into array
                Christian Erlinger
                I have massive amount of records.

                Then fetching this massive amount into a PL/SQL collection would be a bad idea IMHO.

                 

                1. move the records into array.

                2. send this array into stored procedure .

                3. insert the array into temporary table .

                4. join this temporary into some table in the d.b

                5. update the array via the results , ant then display  a relevant message into the user.

                why don't you simply

                 

                1. do it in SQL via a MERGE statement?

                 

                MERGE

                 

                If your block is based on a from-clause query then create a view upon your from-clause query and use this view in your *using* clause in the merge statement.

                 

                cheers

                • 5. Re: How to fetch block into array
                  Naamas

                  Hi,

                  Thanks for the respons.

                  nevertheless I would like to know if there is any option for what i want in forms.

                  merge won't be a good idea for me . i need to take the temporary table and join it with 3 different table and according to the results , returning message to the user.

                  Thanks In Advanced

                  Naama

                  • 6. Re: How to fetch block into array
                    Christian Erlinger

                    merge won't be a good idea for me . i need to take the temporary table and join it with 3 different table and according to the results , returning message to the user.

                    And that would be because...? The amount of data? Because it's slower (trust me: it isn't)? Do you have a scenario where it is considerably faster, easier or better doing what you propose? Why isn't it a good idea to just use SQL to process your data (I might be missing the point, so maybe your requirement satisfies such a method - but I don't know that since you didn't tell us).

                     

                    However; in essence you'd rather

                    - loop over your block and populate an array manually (where the data got pulled from the database in the first place as I trust)

                    - copy this large array over SQL*Net to the database where you populate a temporary table where it will consume a considerable amount of Process Private Memory

                    - Do some SQLish stuff with the temporary result data

                    - return a message to the user

                     

                    instead of

                    - Process your data in SQL which most certainly will be the fastest, robustest and as far as writing code is concerned easiest way

                    - requery your block and some message

                     

                    There is no such thing as bulk collect block into PL/SQL array, so you'd need to do that on your own by looping through your block and populate your array manually. That's one reason why I wouldn't do such a thing in the first place. If you want a PL/SQL API where data gets processed "behind the scene" then doing a merge in a stored procedure is as "behind the scene" as it gets.

                     

                    cheers

                    • 7. Re: How to fetch block into array
                      Naamas

                      Thanks for the respons.

                      i want to describe the scenario.

                       


                      --=================================

                      create table item

                      (item_id number ,

                      item_status number,

                      customer_no number ,

                       


                      constraint item_pk primary key (item_id) ).

                      --

                      the table item contain massive of data . and manipulated by DML actions Iteratively , by customers.

                      --===============================

                      FORMS

                      ==========

                      BLOCK requests  - no base table

                      ITEMS  :requests.item_id

                                :requests.customer_no

                      ( the block contains more items which are not critical for this operation.

                      list of items
                      123456
                      8874545
                      6955855

                       

                      NEED TO :

                      select item_id

                      from item

                      where customer_no = :requests.customer_no

                      minus

                      ( need to take all the reocrds in  the block requests

                      join them with item table  on :requests.item_id =item.item_id

                      if no data found return message to the user that he can continue the process.

                      few limitations :

                      1. there is a procedure in  the form that loops over twice on block requests   - for it's all need

                      2. i've been asked not to change the process that already exists , but to add my changing to the data .

                          the process that already exists is too complicated .

                      Those too things i'm not about to change them right now to the result that i'm asked.

                      Please try to understand that i need a result due to those limitaions.

                      --I thought about  Table Functions.

                      I've created package

                      --=================================

                      CREATE  OR REPLACE PACKAGE item_TEST

                      IS

                      TYPE

                      item_id_type_tab is table of item.item_id%TYPE

                      index by pls_integer;

                      END item_TEST;

                      --=================================

                      in the  forms's procedure i declared :

                      g_itemes_id  item_TEST.item_id_type_tab;

                      .

                      .

                      .

                      loop

                      g_itemes_id  := :requests.item_id

                      end loop;

                      now i want to send this array  thru strored procedure and insert it into table function,

                      and  to join  the table function with table item .

                      stuck here do not know how to continue.

                      Hope the scenarion is clear .

                      Please you Help

                      Naama

                      • 8. Re: How to fetch block into array
                        user346369

                        I don't quite understand the explanation.  Where are the two complex processes run that already exist?   In the form?

                         

                        Along the line of what Christian is saying, you really don't want to bring massive amounts of data into the form to process.   What has worked for me in complex situations is to do all the processing possible in a database-side package, and interact with the data and the package by calling procedures in the package from various operations in the form.

                         

                        If you can process massive data in a form, you can do it faster by processing it on the database (within the package), and then return results to the form where it displays the message or simple data to the user.

                         

                        In the complicated process that I developed, the database package specification defined several tables of data that were passed back and forth between the form and the package.  Then in the form, I created a package specification that contained the package tables storage area:

                        example:   T1_tbl   SIPK0100_FormPkg100.T1_tbl_typ;

                         

                        Then, the form could call procedures in the package, where T1_tbl was passed as a parameter.   The table was used to pass multiple records with several 8000-character columns of data.  The process has been running successfully for years.