1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

PLS-00436: implementation restriction: cannot reference fields of BULK In-BIND table

Discussion in 'SQL PL/SQL' started by baluaggala, Oct 24, 2013.

  1. baluaggala

    baluaggala Active Member

    Messages:
    12
    Likes Received:
    0
    Trophy Points:
    80
    1) Using pipe lined table function we process above 50 lakhs records will fetch almost 2 lakhs records with 20 columns

    2) we need to load 9 values(3 static data like sysdata,'ced','0' + 6 out of 20 columns fetched) into another table, parallely load all 20 columns into flat file using util_file.

    As current process is using normal cursor and for loop it is taking more than 9 hours to complete.

    we can reduce to half time using bulk collect and bulk bind but as we should refer individual columns to populate table and load into flat files........."for all" is not working in oracle 10g.

    i am getting the following error:

    PLS-00436: implementation restriction: cannot reference fields of BULK In-BIND table

    is there any alternate way to tune my code for better perfomance. please help me out. thanks
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Re: PLS-00436: implementation restriction: cannot reference fields of BULK In-BIND ta

    Why isn't forall 'working in oracle 10g'? Please post output proving that.
     
  3. DTSIGuy

    DTSIGuy Forum Advisor

    Messages:
    402
    Likes Received:
    58
    Trophy Points:
    410
    Location:
    Texas
    Re: PLS-00436: implementation restriction: cannot reference fields of BULK In-BIND ta

    This post mentions the issue (if there is one) is fixed in 11g :

    https://forums.oracle.com/message/1664829

    Plz note the above post is rather old and updated after 3 yrs...but it does seem to fit your problem area.

    HTH

    CJ
     
  4. baluaggala

    baluaggala Active Member

    Messages:
    12
    Likes Received:
    0
    Trophy Points:
    80
    Re: PLS-00436: implementation restriction: cannot reference fields of BULK In-BIND ta

    David,

    i cannot post the code and output here as we are working in secured environment, but i am posting my problem in detail below, please help me out.thanks

    Declare

    cursor c1 is select cols fro table(function) ---its pipelined table function it will process 25 to 50 lakhs records in result it fetches 2 lakhs records


    daily we fetch 2 laks records with 20 columns (which are processed from multiple tables using table function )

    TYPE tab1 is table of c1%rowtype

    BEGIN


    OPEN c1;

    LOOP
    FETCH c1 bulkcollect into tab1 limit limit_value;


    if tab1.count>0
    then

    FOR ALL i in 1..tab1.COUNT

    insert into shared_table(col1,col2,col3,col4,col5,col6)values(tab1(i).col1,tab1(i).col2,tab1(i).col3,tab1(i).col4,sysdate,0);

    //bulk insert parallelly loading all column values into flat file using util file
    util_file(fp, col1||delimiter||col2...........col20);

    end if;
    END loop;
    close c1;


    this is the sample code of my problem please let me know the possible solution , if using collections its possible can you explain with the code.


    As our production is using 10g we should tune this job in 10g only.currently its taking 9 hours to complete the process
     
  5. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Re: PLS-00436: implementation restriction: cannot reference fields of BULK In-BIND ta

    Here is an example where I created a destination table with 20 columns so I wouldn 't need to specify column names for the bulk insert:

    Code (SQL):
    SQL> SELECT COUNT(*) FROM sourcetab;
     
      COUNT(*)
    ----------
           100
     
    SQL> SELECT COUNT(*) FROM desttab;
     
      COUNT(*)
    ----------
             0
     
    SQL>
    SQL> DECLARE
      2
      3  cursor c1 IS SELECT * FROM sourcetab;
      4
      5  TYPE tab_1 IS TABLE OF c1%rowtype;
      6
      7  tab1 tab_1;
      8
      9  BEGIN
     10
     11
     12  OPEN c1;
     13
     14  LOOP
     15  FETCH c1 bulk collect INTO tab1 LIMIT 10;
     16  exit WHEN tab1.COUNT = 0;
     17
     18  FORALL i IN 1..tab1.COUNT
     19  INSERT INTO desttab VALUES tab1(i);
     20
     21  END loop;
     22  close c1;
     23
     24  END;
     25  /
     
    PL/SQL PROCEDURE successfully completed.
     
    SQL>
    SQL> commit;
     
    Commit complete.
     
    SQL>
    SQL> SELECT COUNT(*) FROM sourcetab;
     
      COUNT(*)
    ----------
           100
     
    SQL> SELECT col1,col2,col3,col4,col5,col6 FROM desttab;
     
          COL1 COL2                 COL3      COL4                 COL5                 COL6
    ---------- -------------------- --------- -------------------- -------------------- --------------------
             1 1                    26-OCT-13 1                    1                    1
             2 2                    27-OCT-13 2                    2                    2
             3 3                    28-OCT-13 3                    3                    3
             4 4                    29-OCT-13 4                    4                    4
             5 5                    30-OCT-13 5                    5                    5
             6 6                    31-OCT-13 6                    6                    6
             7 7                    01-NOV-13 7                    7                    7
             8 8                    02-NOV-13 8                    8                    8
             9 9                    03-NOV-13 9                    9                    9
            10 10                   04-NOV-13 10                   10                   10
            11 11                   05-NOV-13 11                   11                   11
            12 12                   06-NOV-13 12                   12                   12
            13 13                   07-NOV-13 13                   13                   13
            14 14                   08-NOV-13 14                   14                   14
            15 15                   09-NOV-13 15                   15                   15
            16 16                   10-NOV-13 16                   16                   16
            17 17                   11-NOV-13 17                   17                   17
            18 18                   12-NOV-13 18                   18                   18
            19 19                   13-NOV-13 19                   19                   19
            20 20                   14-NOV-13 20                   20                   20
            21 21                   15-NOV-13 21                   21                   21
            22 22                   16-NOV-13 22                   22                   22
            23 23                   17-NOV-13 23                   23                   23
            24 24                   18-NOV-13 24                   24                   24
            25 25                   19-NOV-13 25                   25                   25
            26 26                   20-NOV-13 26                   26                   26
            27 27                   21-NOV-13 27                   27                   27
            28 28                   22-NOV-13 28                   28                   28
            29 29                   23-NOV-13 29                   29                   29
            30 30                   24-NOV-13 30                   30                   30
            31 31                   25-NOV-13 31                   31                   31
            32 32                   26-NOV-13 32                   32                   32
            33 33                   27-NOV-13 33                   33                   33
            34 34                   28-NOV-13 34                   34                   34
            35 35                   29-NOV-13 35                   35                   35
            36 36                   30-NOV-13 36                   36                   36
            37 37                   01-DEC-13 37                   37                   37
            38 38                   02-DEC-13 38                   38                   38
            39 39                   03-DEC-13 39                   39                   39
            40 40                   04-DEC-13 40                   40                   40
            41 41                   05-DEC-13 41                   41                   41
            42 42                   06-DEC-13 42                   42                   42
            43 43                   07-DEC-13 43                   43                   43
            44 44                   08-DEC-13 44                   44                   44
            45 45                   09-DEC-13 45                   45                   45
            46 46                   10-DEC-13 46                   46                   46
            47 47                   11-DEC-13 47                   47                   47
            48 48                   12-DEC-13 48                   48                   48
            49 49                   13-DEC-13 49                   49                   49
            50 50                   14-DEC-13 50                   50                   50
            51 51                   15-DEC-13 51                   51                   51
            52 52                   16-DEC-13 52                   52                   52
            53 53                   17-DEC-13 53                   53                   53
            54 54                   18-DEC-13 54                   54                   54
            55 55                   19-DEC-13 55                   55                   55
            56 56                   20-DEC-13 56                   56                   56
            57 57                   21-DEC-13 57                   57                   57
            58 58                   22-DEC-13 58                   58                   58
            59 59                   23-DEC-13 59                   59                   59
            60 60                   24-DEC-13 60                   60                   60
            61 61                   25-DEC-13 61                   61                   61
            62 62                   26-DEC-13 62                   62                   62
            63 63                   27-DEC-13 63                   63                   63
            64 64                   28-DEC-13 64                   64                   64
            65 65                   29-DEC-13 65                   65                   65
            66 66                   30-DEC-13 66                   66                   66
            67 67                   31-DEC-13 67                   67                   67
            68 68                   01-JAN-14 68                   68                   68
            69 69                   02-JAN-14 69                   69                   69
            70 70                   03-JAN-14 70                   70                   70
            71 71                   04-JAN-14 71                   71                   71
            72 72                   05-JAN-14 72                   72                   72
            73 73                   06-JAN-14 73                   73                   73
            74 74                   07-JAN-14 74                   74                   74
            75 75                   08-JAN-14 75                   75                   75
            76 76                   09-JAN-14 76                   76                   76
            77 77                   10-JAN-14 77                   77                   77
            78 78                   11-JAN-14 78                   78                   78
            79 79                   12-JAN-14 79                   79                   79
            80 80                   13-JAN-14 80                   80                   80
            81 81                   14-JAN-14 81                   81                   81
            82 82                   15-JAN-14 82                   82                   82
            83 83                   16-JAN-14 83                   83                   83
            84 84                   17-JAN-14 84                   84                   84
            85 85                   18-JAN-14 85                   85                   85
            86 86                   19-JAN-14 86                   86                   86
            87 87                   20-JAN-14 87                   87                   87
            88 88                   21-JAN-14 88                   88                   88
            89 89                   22-JAN-14 89                   89                   89
            90 90                   23-JAN-14 90                   90                   90
            91 91                   24-JAN-14 91                   91                   91
            92 92                   25-JAN-14 92                   92                   92
            93 93                   26-JAN-14 93                   93                   93
            94 94                   27-JAN-14 94                   94                   94
            95 95                   28-JAN-14 95                   95                   95
            96 96                   29-JAN-14 96                   96                   96
            97 97                   30-JAN-14 97                   97                   97
     
          COL1 COL2                 COL3      COL4                 COL5                 COL6
    ---------- -------------------- --------- -------------------- -------------------- --------------------
            98 98                   31-JAN-14 98                   98                   98
            99 99                   01-FEB-14 99                   99                   99
           100 100                  02-FEB-14 100                  100                  100
     
    100 ROWS selected.
     
    SQL>
     
  6. baluaggala

    baluaggala Active Member

    Messages:
    12
    Likes Received:
    0
    Trophy Points:
    80
    Re: PLS-00436: implementation restriction: cannot reference fields of BULK In-BIND ta


    David,

    i believe you didnt get my problem completely.....


    1) i am getting 2 lakhs records with 20 columns............this is the daily process i cannot create and drop dest table every time and one more thing, the process we are doing is we are fetching from 10 to 15 source tables processing using pipelined table functions

    2) and my process should populate 6 cols and extra static values like sysdate and '0' into dest table ****Parallelly*** i need to load all 20 columns data into *flatfiles* using utilfile .hope you got the problem please repl me if u have any solution to tune
     
  7. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Re: PLS-00436: implementation restriction: cannot reference fields of BULK In-BIND ta

    You don't create and drop the test table so you didn't understand my response.

    The pipelined table function is basically the same as my static table; you create a table that will accept all of the column values returned from the pipelined function then use that to insert into using the bulk collect and forall functionality. You then insert into your destination table from this temporary holding table, then truncate the holding table for the next run. Since this is a pipelined function it should return the same 'columns' regardless of when you access it, thus the holding table should not change definition.

    You CANNOT use the method available in 11.2 because you are not using 11.2; you therefore need to find a workaround if you want to use bulk collect and forall since you CANNOT access the individual fields in the bulk bind table. For speed using bulk collect/forall is the way to go; you will need to insert ALL of the returned 'column' values somewhere then pare down that list to the desired columns plus the additional values you require.

    I did understand the problem, and I still do.
     
  8. baluaggala

    baluaggala Active Member

    Messages:
    12
    Likes Received:
    0
    Trophy Points:
    80
    Re: PLS-00436: implementation restriction: cannot reference fields of BULK In-BIND ta

    David,

    Thanks for the solution given.my understanding is

    1) create a destination table with all 20 columns which are fetched from function result

    2) populate only 6 columns from the destination table using select and insert(hope this is the fastest)

    insert into shared_tab
    select col1,col2,col3,sysdata,'ced' from dest_tab;

    3) using cursor for loop populate all 20 columns from dest table into flatfile using util-file package

    my doubts
    -------------

    1) as it is 3 step process it takes long time than usual process??

    2) as we are using pipelined table function processed data will come each time in small portions when ever row is returned .

    in that case

    every time my dest table is populating?? shared table populating?? flat file populating?? duplicates may occur??

    please correct me if i understood wrongly about the pipelined function and provide clear solution if you can .thanks
     
  9. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Re: PLS-00436: implementation restriction: cannot reference fields of BULK In-BIND ta

    It shouldn't. Here is the time it took to load 100000 records in the source table:

    Code (SQL):
    SQL> BEGIN
      2        FOR i IN 1..100000 loop
      3         INSERT INTO sourcetab
      4         VALUES(i,i,sysdate+i,i,i,i,i,i,i,i,i,i,i,i,i,i,i,i,i,i);
      5        END loop;
      6  
      7        commit;
      8  
      9  END;
     10  /
     
    PL/SQL PROCEDURE successfully completed.
     
    Elapsed: 00:00:05.61
    SQL>
    This is the timing for loading the second table from the first using FORALL:

    Code (SQL):
    SQL> DECLARE
      2  
      3  cursor c1 IS SELECT * FROM sourcetab;
      4  
      5  TYPE tab_1 IS TABLE OF c1%rowtype;
      6  
      7  tab1 tab_1;
      8  
      9  BEGIN
     10  
     11  
     12  OPEN c1;
     13  
     14  LOOP
     15  FETCH c1 bulk collect INTO tab1 LIMIT 10;
     16  exit WHEN tab1.COUNT = 0;
     17  
     18  FORALL i IN 1..tab1.COUNT
     19  INSERT INTO desttab VALUES tab1(i);
     20  
     21  END loop;
     22  close c1;
     23  
     24  END;
     25  /
     
    PL/SQL PROCEDURE successfully completed.
     
    Elapsed: 00:00:02.13
    SQL>
    And loading the third table from the second with a selective insert:

    Code (SQL):
    SQL> INSERT INTO finaltab(col1,col2,col3,col4,col5,col6)
      2  SELECT col1,col2,col3,col4,col5,col6 FROM desttab;
     
    100000 ROWS created.
     
    Elapsed: 00:00:01.27
    SQL>
    Notice that none of these actions took a long time to complete, mostly due to the FORALL processing.
     
  10. baluaggala

    baluaggala Active Member

    Messages:
    12
    Likes Received:
    0
    Trophy Points:
    80
    Re: PLS-00436: implementation restriction: cannot reference fields of BULK In-BIND ta

    hello David,

    i have implemented the above problem with above suggestions and tested for 1 lakh records processing

    it fetched 16000 records after processing using pipelined table functions


    process followed in testing
    -------------------------

    1) processing 1 lakh records using pipeline functions (16 k result records)
    2) insert into temp table 16k records
    3) insert 6 columns from temp table into dest table
    4) using for loop cursor populate all 16k records with all 20 columns into flat file using uril file

    time taken with normal process: 1900 seconds

    time taken with current process: 1750 seconds

    i dont find much difference in performance..............

    1)please explain me about how pipelined table functions will work ?? will it process data parallelly?? why its taking much time??

    2) one more doubt is if we load data into flatfile using flat_file will it contain context switching?? how much time will take with flat file loading process???



    what i believe is remaining insert and selects will work faster as it wont use context switching at all.............

    but in case of "Pipelined table function Processing" and "util file loading" perfomance is effecting i guess....

    can you please explain me more in detail regarding my problem so that i can tune it effectively.thanks
     
  11. baluaggala

    baluaggala Active Member

    Messages:
    12
    Likes Received:
    0
    Trophy Points:
    80
    Re: PLS-00436: implementation restriction: cannot reference fields of BULK In-BIND ta

    hello David,

    i have implemented the above problem with above suggestions and tested for 1 lakh records processing

    it fetched 16000 records after processing using pipelined table functions


    process followed in testing
    -------------------------

    1) processing 1 lakh records using pipeline functions (16 k result records)
    2) insert into temp table 16k records
    3) insert 6 columns from temp table into dest table
    4) using for loop cursor populate all 16k records with all 20 columns into flat file using uril file

    time taken with normal process: 1900 seconds

    time taken with current process: 1750 seconds

    i dont find much difference in performance..............

    1)please explain me about how pipelined table functions will work ?? will it process data parallelly?? why its taking much time??

    2) one more doubt is if we load data into flatfile using flat_file will it contain context switching?? how much time will take with flat file loading process???



    what i believe is remaining insert and selects will work faster as it wont use context switching at all.............

    but in case of "Pipelined table function Processing" and "util file loading" perfomance is effecting i guess....

    can you please explain me more in detail regarding my problem so that i can tune it effectively.thanks
     
  12. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Re: PLS-00436: implementation restriction: cannot reference fields of BULK In-BIND ta

    Pipelined functions won't return results using parallel processing, it's a limitation of pipelined functions -- you retrieve a row, pipe it to the output stream, retrieve a row, pipe it to the output stream ...

    If you want parallelized processing then using a pipelined function won't work, nor will using the table() function as that introduces a pickler fetch to format the data stream into a table-like format; read here:

    http://dfitzjarrell.wordpress.com/2009/12/10/in-a-pickle/

    The time saved is a function of the in-memory processing you can do with the FORALL and BULK COLLECT processing. If you have plenty of memory you can load the entire array into memory without swapping.