Current Position:Home > Bulk binding issues in 9i/8i

Bulk binding issues in 9i/8i

Update:11-30Source: network consolidation
Advertisement
Hi all,
I would like to know, if there is any change ,in the bulk binding feature of PL/SQL in Oracle 9i .
Please guide me on following issues ,
1) We use it since it was introduced in 8i and now we are migrating to 9i. I would be happy to exploit any new feature, if there .
2) when I am inserting or updating with buld binding, the collection I use must be dense (not sparse).Is there any way to oversome this limitation ?
3) Suppose there is a table of 1 million rows. I have to transfer the data from this table to another using bulk binding. What I want to do is, want to transfer the rows in batches of 10000 rows. As collecting 1 million records data once in a collection does not look like a good idea.
Please advice.
Regards.

The Best Answer

Advertisement
There are almost always new features with each successive release. There are a few sections in the PL/SQL User's Guide http://download-west.oracle.com/docs/cd/B10501_01/appdev.920/a96624/05_colls.htm#23723 that discuss bulk operations in 9.2-- you probably want to take a look at that. There is also a book from Oracle Press called "Oracle 9i New Features" which is excellent.
The LIMIT clause may have been introduced in 9i, i.e.
FETCH <<some cursor>>
  BULK COLLECT INTO <<some collection>>
  LIMIT 1000There is a section in the PL/SQL User's Guide pertaining to the LIMIT clause http://download-west.oracle.com/docs/cd/B10501_01/appdev.920/a96624/05_colls.htm#29027
Justin
Distributed Database Consulting, Inc.
http://www.ddbcinc.com/askDDBC
  • Bulk binding issues in 9i/8i Update:11-30

    Hi all, I would like to know, if there is any change ,in the bulk binding feature of PL/SQL in Oracle 9i . Please guide me on following issues , 1) We use it since it was introduced in 8i and now we are migrating to 9i. I would be happy to exploit an

  • Accessing the index in bulk binds Update:10-11

    Hi I wonder if it is possible to access the index itself in a bulk bind. For instance: SQL> desc draft_responces; Name Type Nullable Default Comments S_NUMBER VARCHAR2(10) CLUSTER_ID NUMBER STATEMENT_ID NUMBER RESPONCE NUMBER Y RESP_NUM NUMBER Y and

  • Bulk Binding Update:10-11

    The below example is based on an example from Chapter 5 in Release 2 (9.2) documentation (Available in Oracle site). But it is failing and throwing the following error: "PLS-00435 DML statement without BULK In-BIND cannot be used inside FORALL".

  • Bulk Binds-How to avoid naming all columns? Update:11-30

    When using Bulk Binds for inserts/updates,One has to explicitly use all column names in declaration.This is maint intensive as addition of any colum will require changes in code at multiple locations. Is there any way to reference the columns though

  • Bulk Binding-How to avoid naming all columns Update:11-30

    When using Bulk Binds for inserts/updates,One has to explicitly use all column names in declaration.This is maint intensive as addition of any colum will require changes in code at multiple locations. Is there any way to reference the columns though

  • Bulk binding in 11g Update:11-30

    Hello, My below block works perfectly fine. However the processing takes longer time because of the volume of records in the table. I would like to change this below code to use FORALL. How can I convert the code to use bulk binding FORALL instead of

  • Bulk Binding Drawback Update:11-30

    Hi All, Seems that Bulk binding of pl/sql do enhance performance, but have no information about its drawback. Does anyone know what drawback that bulk binding have? Thank you. CH.The only drawback I am aware of is that in some circumstances, using bi

  • Jdbc thin driver bulk binding slow insertion performance problem Update:11-30

    Hello All, We have a third party application reporting slow insertion performance, while I traced the session and found out most of elapsed time for one insert execution is sql*net more data from client, it appears bulk binding is being used here bec

  • Jdbc thin driver and bulk binding slow insertion performance Update:11-30

    Hello All, We have a third party application reporting slow insertion performance, while I traced the session and found out most of elapsed time for one insert execution is sql*net more data from client, it appears bulk binding is being used here bec

  • Dynamic SQL and Bulk Bind... Interesting Problem !!! Update:10-11

    Hi Forum !! I've got a very interesting problem involving Dynamic SQL and Bulk Bind. I really Hope you guys have some suggestions for me... Table A contains a column named TX_FORMULA. There are many strings holding expressions like '.3 * 2 + 1.5' or