Results 1 to 7 of 7

Thread: PL/SQL Table

  1. #1
    Join Date
    Dec 2003
    Posts
    76

    Unanswered: PL/SQL Table

    Hi ...

    I have a question ...
    If I have a table with the following columns:
    COD_ARTICLE || NAME_ARTICLE || COD_PUBLICATION
    and if, at a PL/SQL Package, I have the need to bring the results of a query (wich returns 3 or more rows), in a PL/SQL Table, how can I make this ??

    The following code, gives me an error, while on compiling:
    TYPE table_aux IS TABLE OF tabela%rowtype INDEX BY BINARY_INTEGER;
    tab_aux table_aux;

    CURSOR my_cursor IS SELECT COD_ARTICLE || NAME_ARTICLE || COD_PUBLICATION FROM TABELA WHERE COD_PUBLICATION = I_COD_PUBLICATION;

    BEGIN
    OPEN my_cursor;
    LOOP
    FETCH my_cursor INTO tab_aux;

    Error:
    PLS-00597: expression 'TAB_AUX' in the INTO list is of wrong type

    Thanx,

  2. #2
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    The || in your select means you concatenate all three text fields into a single one, and then you try to say it is a 3 columns row... wonder why it doesn't work ? I think that if you just replace || with , in your select, it should work a little better :
    Code:
    CURSOR my_cursor IS SELECT COD_ARTICLE, NAME_ARTICLE, COD_PUBLICATION FROM TABELA WHERE COD_PUBLICATION = I_COD_PUBLICATION;
    Regards,

    RBARAER

  3. #3
    Join Date
    Dec 2003
    Posts
    76
    No ...
    The || in the select, were an mistake ...
    I've made copy-paste from the table description ...

    Even though I've tried a different way to solve my problem, and I think I've found it ... may be not the best one, but it seems working:

    TYPE table_aux IS TABLE OF tabela%rowtype INDEX BY BINARY_INTEGER;
    tab_aux table_aux;

    CURSOR my_cursor IS SELECT COD_ARTICLE, NAME_ARTICLE, COD_PUBLICATION FROM TABELA WHERE COD_PUBLICATION = I_COD_PUBLICATION;

    BEGIN
    OPEN my_cursor;
    LOOP
    FETCH my_cursor INTO tab_aux(i).COD_ARTICLE, tab_aux(i).NAME_ARTICLE, tab_aux(i).COD_PUBLICATION;

    It seems to work ... do anyone know a better solution ....

    Thankx,

  4. #4
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    You surely want BULK COLLECT :
    Code:
    OPEN my_cursor;
    LOOP
    FETCH my_cursor BULK COLLECT INTO tab_aux;
    This loads all the result of your query into your PL/SQL table without fetching row by row. It's much more efficient, but can use a lot of RAM on the server if there are many rows. To limit the number of rows you fetch at a time (here 100) :
    Code:
    OPEN my_cursor;
    LOOP
        FETCH my_cursor BULK COLLECT INTO tab_aux LIMIT 100;
        FOR i IN 1..tab_aux.count LOOP
             -- Process tab_aux(i)
        END LOOP;
    END LOOP;
    HTH & Regards,

    RBARAER

  5. #5
    Join Date
    Dec 2003
    Posts
    76
    It gives me the following error:
    PLS-00597: expression 'TAB_AUX' in the INTO list is of wrong type

  6. #6
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    Try to define your table non-indexed, but I'm not sure :
    Code:
    TYPE table_aux IS TABLE OF tabela%rowtype;
    For more info about BULK COLLECT, have a look at the PL/SQL reference documentation.
    Nice examples too, like this one :
    Code:
    DECLARE
    TYPE DeptRecTab IS TABLE OF dept%ROWTYPE;
    dept_recs DeptRecTab;
    CURSOR c1 IS
    SELECT deptno, dname, loc FROM dept WHERE deptno > 10;
    BEGIN
    OPEN c1;
    FETCH c1 BULK COLLECT INTO dept_recs;
    END;
    PS : I think this only works since 9iR2, not before (before, you had to do as you said it worked : one array per column).

    HTH & Regards,

    RBARAER

  7. #7
    Join Date
    Dec 2003
    Posts
    76
    Ok ...
    Since I have Oracle8i Release 8.1.7.4.1, I guess I must stay with my solution ...

    Thankx,

Posting Permissions

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