Results 1 to 13 of 13
  1. #1
    Join Date
    Feb 2004
    Posts
    41

    Unanswered: Bulk Collect Into

    Hi,
    I have tried to loda data from database to PL/SQL Table.
    I have written code

    Declare

    type r1 is record
    (emp_id varchar2(100),
    emp_name varchar2(10));

    type t1 is table of r1
    index by binary_integer;


    t_empdata t1;

    cursor c1 is select emp_id, emp_name from emp;

    begin

    open c1;

    fetch c1 bulk collect into t_empdata.emp_id, t_empdata.emp_name;

    close c1;

    for i in 1..t_empdata.count loop
    dbms_output.put_line(t_empdata(i).emp_id||t_empdat a(i).emp_name);
    end loop;

    end;

    but i am getting the following error.

    fetch c1 bulk collect into t_empdata.emp_id, t_empdata.emp_name;
    *
    ERROR at line 19:
    ORA-06550: line 19, column 37:
    PLS-00302: component 'EMP_ID' must be declared
    ORA-06550: line 19, column 1:
    PL/SQL: SQL Statement ignored

    Pls help me to resolve the problem.

    Thakns & Regrads
    Sowmya
    'A candle will loose nothing by lighting an another candle'

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Bulk Collect Into

    This is a restriction on bulk collects. You can't bulk collect into a table of records, but you can bulk collect into a "record of tables":
    PHP Code:
      1  declare
      
    2    type empno_t is table of integer index by binary_integer;
      
    3    type ename_t is table of varchar2(20index by binary_integer;
      
    4    type t1 is recordempno empno_tename ename_t );
      
    5    t_empdata t1;
      
    6    cursor c1 is select empnoename from emp;
      
    7  begin
      8    open c1
    ;
      
    9    fetch c1 bulk collect into t_empdata.empnot_empdata.ename;
     
    10    close c1;
     
    11    for i in 1..t_empdata.empno.count loop
     12      dbms_output
    .put_line(t_empdata.empno(i)||t_empdata.ename(i));
     
    13    end loop;
     
    14end;
    SQL> /
    7369SMITH
    7499ALLEN
    7521WARD
    7566JONES
    7654MARTIN
    7698BLAKE
    7782CLARK
    7788SCOTT
    7839KING
    7844TURNER
    7876ADAMS
    7900JAMES
    7902FORD
    7934MILLER

    PL
    /SQL procedure successfully completed

  3. #3
    Join Date
    Feb 2004
    Posts
    41

    Re: Bulk Collect Into

    Ok. Thank U very much.
    'A candle will loose nothing by lighting an another candle'

  4. #4
    Join Date
    Feb 2004
    Posts
    41

    Re: Bulk Collect Into

    hey but i can bulk collect into a pl/sql table, if it has only one field.
    say if pl/sql table is like this

    Declare
    type t1 is table of emp.emp_no%TYPE
    index by binary_integer;

    empdata t1;

    cursor c1 is select Emp_no from emp;

    Begin

    open c1;

    fetch c1 bulk collect into empdata;

    close c1;

    End;
    'A candle will loose nothing by lighting an another candle'

  5. #5
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Bulk Collect Into

    Sure you can! You can bulk collect into a table, or a record of tables. But you cannot bulk collect into a table of records. It is just the way Oracle works.

  6. #6
    Join Date
    Feb 2004
    Posts
    41

    Re: Bulk Collect Into

    Ok. Thank U very Much Tony.

    I have one doubt regarding Cursor Attributes.
    we have FOUND and NOTFOUND cursor attributes.

    see when we want to check data found or not we can do it with Only
    FOUND or NOTFOUND, but still we have both.
    Is there any specific reason for having these attributes?.

    Thanks In Advance.
    'A candle will loose nothing by lighting an another candle'

  7. #7
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Bulk Collect Into

    I don't know. I agree it seems redundant to have c%NOTFOUND when you can get the same result using "NOT c%FOUND".

  8. #8
    Join Date
    Feb 2004
    Posts
    41

    Re: Bulk Collect Into

    Ok, Actually what i thought is, it is a performance issue, because NOT operator reduces the performane, but still iw as not convienced so i asked.
    any way thanku.
    'A candle will loose nothing by lighting an another candle'

  9. #9
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Bulk Collect Into

    Don't assume I know! For all I know, maybe %NOTFOUND is faster than NOT %FOUND, though I doubt it.

  10. #10
    Join Date
    Feb 2004
    Posts
    41

    Re: Bulk Collect Into

    Ok. Pls let me know when u get teh answer.
    'A candle will loose nothing by lighting an another candle'

  11. #11
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Bulk Collect Into

    Simple test:

    PHP Code:
      1  declare
      
    2    start1 integer;
      
    3    end1 integer;
      
    4    start2 integer;
      
    5    end2 integer;
      
    6    cursor c is select object_id from all_objects;
      
    7    r c%rowtype;
      
    8  begin
      9    
    for i in 1..3 loop
     10      open c
    ;
     
    11      start1 := dbms_utility.get_time;
     
    12      loop
     13        fetch c into r
    ;
     
    14        exit when c%notfound;
     
    15      end loop;
     
    16      end1 := dbms_utility.get_time;
     
    17      close c;
     
    18      open c;
     
    19      start2 := dbms_utility.get_time;
     
    20      loop
     21        fetch c into r
    ;
     
    22        exit when not c%found;
     
    23      end loop;
     
    24      end2 := dbms_utility.get_time;
     
    25      close c;
     
    26      dbms_output.put_line'%notfound time: ' || (end1-start1) );
     
    27      dbms_output.put_line'%found time:    ' || (end2-start2) );
     
    28    end loop;
     
    29end;
    SQL> /
    %
    notfound time1973
    %found time:    1960
    %notfound time1952
    %found time:    2014
    %notfound time1951
    %found time:    1954

    PL
    /SQL procedure successfully completed
    Pretty inconclusive really. If there is a difference, it's so small as to be insignificant, swamped by other factors. So use whichever you like best!

  12. #12
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    Hi,

    I would suggest it comes from general practice, it could be equated to why have the = and <> operators when <> could be written a if not (x=1) :-)

    A readability issue I would suppose?

    Cheers
    Bill
    Please don't email me directly with questions. I've probably just got home from the pub and cannot guarantee the sanity of my answers. In fact, I can't believe I actually made it home.

  13. #13
    Join Date
    Feb 2004
    Posts
    41
    I agree with u readability issue, but still i wanted to know if any specific reason.
    'A candle will loose nothing by lighting an another candle'

Posting Permissions

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