oracle bulk collect and reading the data

Question

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.

First, your procedure will only return the data for the arbitrarily last row that you fetched. It seems unlikely that you want to go to all the trouble of generating the result set and fetching the data only to discard all but one row and that you don't care which row is retained. Second, you can't pass a `%rowtype` variable to `dbms_output.put_line`. You could pass particular scalar attributes, i.e. `dbms_output.put_line( v_batchstatus.col1 || ' ' || v_batchstatus.col2 || ... );` but you'd have to enumerate the attributes you want to see. — Justin Cave, Aug 18 '16 at 04:57

Hawk · Accepted Answer · 2016-08-18T05:21:58.767

0

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

Wrong number of arguments for EVMPDADM.GETALLBATCHES_ARTICLE_57: It has one OUT parameter. So you need to pass that parameter.
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

edited Aug 18 '16 at 05:21

answered Aug 18 '16 at 05:03

Hawk

5,060
12
49
74

I have corrected the pl-sql block. It was my mistake that I forget to handle the out parameter correctly and was not using the attribute names while printing. – Jig232 Aug 18 '16 at 05:10
I have updated the question. can you please tell me how to print all the records from the table. – Jig232 Aug 18 '16 at 05:11
Thanks Hawk:) Actually the problem is i want all the data as an out parameter because we want to show this data in a grid view in a .NET form I don't know .NET and i am not sure how my team mate is going to show this data as a grid their. He just asked me to write the proc to read the data and send it as an out parameter. – Jig232 Aug 18 '16 at 05:30
1

Use a sysrefcursor to fetch records to your .NET form. Just google down you will find many solutions using sysrefcursor. – XING Aug 18 '16 at 06:56

oracle bulk collect and reading the data

1 Answers1