Pl/sql Procedure
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Pl/sql Procedure

  1. #1
    Join Date
    Jun 2001
    Posts
    150

    Pl/sql Procedure

    Hi Guys
    I am bit new to pl/sql programming. I am trying to write a procedure who will select data from onw table and insert into another. The other thing i want to do is commit after 50000 rows as table has 4 million rows in it.
    The table is invoice
    Name Null? Type
    ----------------------------------------- -------- ---------------
    INVOICENUMBER NOT NULL VARCHAR2(15)
    INVOICEDATE NOT NULL DATE
    DUEDATE DATE
    INVOICEVALUEEXCLVAT NUMBER(10,2)
    INVOICEVALUEINCLVAT NUMBER(10,2)
    INVOICEVATDESC VARCHAR2(20)
    INVOICEVATVALUE NUMBER(10,2)
    ACCOUNTNUMBER NOT NULL VARCHAR2(15)
    TARIFFDESC VARCHAR2(20)
    MOBILENUMBER NOT NULL VARCHAR2(15)
    BUNDLEUSED VARCHAR2(8)
    BUNDLEAVAILABLE VARCHAR2(8)

    Thanks
    UKDBA

  2. #2
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    instead of PL/SQL have you considered insert /*+ APPEND */ ?

    generates small redo and small rollback if you disable the indexes, drawback is it starts to insert from segment HWM

  3. #3
    Join Date
    Sep 2001
    Location
    Düsseldorf, Germany.
    Posts
    588
    refer to http://www.oracle-base.com/Articles/8i/BulkBinds8i.asp

    First try to write your own code, in case of any problem, post the error message for further assistance..

    HTH

    Sameer

  4. #4
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    By far the best solution would be to increase the size of your rollback segments to allow the insert of all the rows at once. Commiting part way through is asking for "snapshot too old" trouble.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

Posting Permissions

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


Click Here to Expand Forum to Full Width