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.
The error messages are very obvious. You are calling your procedures with:
EVMPDADM.GETALLBATCHES_ARTICLE_57
: It has one OUT parameter. So you need to pass that parameter.DBMS_OUTPUT.PUT_LINE
: It has one IN VARCHAR2
parameter, and not XEVMPD_SUBMITTEDBATCH%ROWTYPE
. Read hereSo, 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