oracle-databaseplsqlbulk-collect

oracle bulk collect and reading the data


I have created below proc to read all the data from one table and populate it in a grid in .net form.

CREATE OR REPLACE PROCEDURE EVMPDADM.GETALLBATCHES_ARTICLE_57(p_batchstatus OUT XEVMPD_SUBMITTEDBATCH%ROWTYPE )
IS

 TYPE batch_status IS TABLE OF XEVMPD_SUBMITTEDBATCH%ROWTYPE  INDEX BY PLS_INTEGER;
 l_batchstatus batch_status;

BEGIN

SELECT *  BULK COLLECT INTO l_batchstatus FROM XEVMPD_SUBMITTEDBATCH ;

   FOR i IN 1..l_batchstatus.count LOOP 

    p_batchstatus:= l_batchstatus(i);

   END LOOP;

END GETALLBATCHES_ARTICLE_57;

To test if the proc is running fine I tried to print the data by using below Pl-sql block:

DECLARE
    v_batchstatus XEVMPD_SUBMITTEDBATCH%ROWTYPE;
    BEGIN

    EVMPDADM.GETALLBATCHES_ARTICLE_57(v_batchstatus);
    DBMS_OUTPUT.PUT_LINE( v_batchstatus.Batch_id || ' ' || v_batchstatus.BATCH_DESCRIPTION || ' ' || v_batchstatus.STATUS || ' ' ||v_batchstatus.RECORD_STATUS || ' ' ||v_batchstatus.NUMBER_OF_RECORDS);

   END;
 /

But from this process I am getting the last row only. I want to print all the records present in the table. can any one please help me to figure out what is wrong in the above code.


Solution

  • The error messages are very obvious. You are calling your procedures with:

    1. Wrong number of arguments for EVMPDADM.GETALLBATCHES_ARTICLE_57: It has one OUT parameter. So you need to pass that parameter.
    2. Wrong type of argument for DBMS_OUTPUT.PUT_LINE: It has one IN VARCHAR2 parameter, and not XEVMPD_SUBMITTEDBATCH%ROWTYPE. Read here

    So, it should be this way:

    DECLARE
        v_batchstatus XEVMPD_SUBMITTEDBATCH%ROWTYPE;
        BEGIN
    
        v_batchstatus:= EVMPDADM.GETALLBATCHES_ARTICLE_57(v_batchstatus);
        --use DBMS_OUTPUT.PUT_LINE for every column of XEVMPD_SUBMITTEDBATCH separately after you convert them to varchar2 if they are not.
    
    END;
    /
    

    Besides, the procedure this way will return only the last row. So you might want to change that.

    If you want to print all the records from the table, you need to add DBMS_OUTPUT.PUT_LINE inside the loop, it will become like this:

    FOR i IN 1..l_batchstatus.count LOOP  
    
    p_batchstatus:= l_batchstatus(i);
    
    dbms_output.put_line( p_batchstatus.col1 || ' ' || p_batchstatus.col2 || ... );
    
    END LOOP;
    

    Where col1, col2, ... are the columns names of XEVMPD_SUBMITTEDBATCH given they are of the type VARCHAR2. Or you will need extra processing