Results 1 to 10 of 10
  1. #1
    Join Date
    Oct 2003
    Location
    Delhi
    Posts
    5

    Unanswered: Query on PLSQL Tables

    Hi All,

    I am having a query on PLSQL tables. Actually, I am using a PLSQL table to do a BULK Update in one of my tables. The PLSQL table would contain more than 400000 records of data.

    I was informed that there will be memory problem using this PLSQL tables. Is it so ? If yes, please let me know what is the solution for the same. Also I see a NEW CLAUSE called LIMIT BY <<fetch_size>>. What is this LIMIT BY clause. If we say, limit by 10000, does it mean that it will fetch only 10000 records at a time and later fetch the next 10000 records. If this is the case, it does by itself or do we need to give any explicit command? And wil this FETCH BY clause take care of this MEMORY problem? Or is there any other way of getting rid of this memory problem in the case of PLSQL tables?

    Please help.
    Thanks and Regards,
    Srini.

  2. #2
    Join Date
    Sep 2003
    Location
    Colombo, Sri Lanka
    Posts
    63

    Re: Query on PLSQL Tables

    Yes PL/SQL table take a lot of memory. It is simply an array or records. If you could explain your senario more it would help in giving an alternative way of getting the job done rather than using a PL/SQL table with 400000 records.

    Cheers,
    Suren.

  3. #3
    Join Date
    Oct 2003
    Location
    Delhi
    Posts
    5

    Query on PLSQL tables.

    Hi Suren,

    Thanks for your reply. Here is the exact scenario.
    I am working on Accounts Receivables product in 11i Oracle Applications. I have an Invoice table which has 1,000,000 records which are having 400,000 distinct Customer Ids.

    Now, there is a table which is containing the Customers data which is having 2 columns as old_customer_id and new_customer_id.

    Now, the customer_id column in the Invoices table map to the old_customer_id column in the Customers table. Now, montly I want to update the Invoices table(which will be around 1,000,000 records per month for those 400,000 customers) with the new_customer_id wherever there is the old_customer_id and for this I thought creating a PLSQL table for Customer_ids and then do a BULK UPDATE on the Invoice Table should resolve my issue.

    Please let me know what can be done for this? A PLSQL table will not work ? Any idea of what is the LIMIT BY clause?

    Thanks,
    Srini.

  4. #4
    Join Date
    Sep 2003
    Location
    Colombo, Sri Lanka
    Posts
    63

    Re: Query on PLSQL tables.

    Have you defined any table level foreign key constraints for the customer id in the invoice table?

    Originally posted by svasu
    Hi Suren,

    Thanks for your reply. Here is the exact scenario.
    I am working on Accounts Receivables product in 11i Oracle Applications. I have an Invoice table which has 1,000,000 records which are having 400,000 distinct Customer Ids.

    Now, there is a table which is containing the Customers data which is having 2 columns as old_customer_id and new_customer_id.

    Now, the customer_id column in the Invoices table map to the old_customer_id column in the Customers table. Now, montly I want to update the Invoices table(which will be around 1,000,000 records per month for those 400,000 customers) with the new_customer_id wherever there is the old_customer_id and for this I thought creating a PLSQL table for Customer_ids and then do a BULK UPDATE on the Invoice Table should resolve my issue.

    Please let me know what can be done for this? A PLSQL table will not work ? Any idea of what is the LIMIT BY clause?

    Thanks,
    Srini.

  5. #5
    Join Date
    Oct 2003
    Location
    Delhi
    Posts
    5
    Oh..ya Suren. That is available. The OLD_Customer_Id is the primary key in my Customers table and the Customer_id is a foreign key in the Invoices Table.

    Thanks.

  6. #6
    Join Date
    Sep 2003
    Location
    Colombo, Sri Lanka
    Posts
    63
    In that case I cannot figureout how you can update these customer IDs in the first place.

    Let me explain. If you go and put an update on the Invoice table to update the Customer IDs (old Cust Id values) with the new once, it will raise a foreign key vialation since the new Customer ID is not yet available in the Customer table.

    If you cinsider the other way round, and go and try to update the old customer ID with the new one, it will again fire a foreign key vialation, since details records are found in Invoice table for the given old customer ID.

  7. #7
    Join Date
    Oct 2003
    Location
    Delhi
    Posts
    5
    Great.
    But it so happens that this constraint needs to be disabled for all month ends , because this is how the process goes and it is important that the invoice table should be updated with the new Customer Id so that the Customers are referred correctly.

    And yes, later the Customers table are also refereshed.
    Please help on this BULK UPDATE process because opening and closing cursors are taking lot of time because there are similar foreigh key columns present in my invoices table which needs to be updated (the sales orders table and Addresses table).

    Thanks and Rgds,
    Srini.

  8. #8
    Join Date
    Jun 2003
    Posts
    34

    Thumbs up

    Originally posted by svasu
    Great.
    But it so happens that this constraint needs to be disabled for all month ends , because this is how the process goes and it is important that the invoice table should be updated with the new Customer Id so that the Customers are referred correctly.

    And yes, later the Customers table are also refereshed.
    Please help on this BULK UPDATE process because opening and closing cursors are taking lot of time because there are similar foreigh key columns present in my invoices table which needs to be updated (the sales orders table and Addresses table).

    Thanks and Rgds,
    Srini.

    Srini,
    Here is my 2 cents on this UPDATE. I don't know which database version you are using. IF you are using 9iR2, then I would suggest just go for MERGE statement in 9iR2. Its wonderfully fast. If you are not using 9iR2 then I guess a straight SQL will solve ur problem rather than writing a PL/SQL. For ex.
    UPDATE (select ar.old_customer_id old ,cust.new_customer_id new
    from AR_table_name ar, customer_table_name cust
    where ar.old_customer_id = cust.old_customer_id)
    set old = new)
    /

    commit;

    Remember what we can achieve through SQL SHOULD be done in SQL first. If we can't then go for PL/SQL.

    Cheers!!!

  9. #9
    Join Date
    Oct 2003
    Location
    Delhi
    Posts
    5
    Hi Arin,

    Thanks for the reply. Will try the same. Can you point out some examples for the MERGE statement?

    Thanks and Regards,
    Srini.

  10. #10
    Join Date
    Jun 2003
    Posts
    34
    Originally posted by svasu
    Hi Arin,

    Thanks for the reply. Will try the same. Can you point out some examples for the MERGE statement?

    Thanks and Regards,
    Srini.
    Well you see the Oracle Documentation they have given quite a bit on the subject. One thing though I forgot to mention and I apologise for that , which is you cannot use MERGE here as you'll be updating the same column by which you are going to search. this is the ONLY constraint you have when it comes to MERGE.

    Thanks
    Arindam

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •