Hi Experts,
Below is a procedure where i declared a cursor. I tried to display the value stored in the procedure to the output window. I need to see the records stored in the cursor.
I googled and got a code but its not displaying records. Its just running and showing successfully completed.
Note: I am running this in oracle Toad. I checked for options in Toad to display cursor records but nothing works.
Please check the code and let me know what should i do to see the values in cursor.
CREATE OR REPLACE PROCEDURE STUB_DW_ETL.test1 AS
CURSOR C_FLD IS
SELECT SELLER_ID, DOM.DOMAIN_ID, MIN(S.DATE_ADDED) FIRST_LIST_DATE
FROM table1 S, table 2 E, table 3 XREF, table 4 DOM, table 5 U
WHERE S.EVENT_ID = E.EVENT_ID
AND E.EVENT_DW_ID = XREF.EVENT_DW_ID
AND DOM.DOMAIN_DW_ID = XREF.DOMAIN_DW_ID
AND u.FIRST_LISTed_DATE IS NULL AND DATE_ADDED>'5-JAN-2015' AND DATE_ADDED <= '10-JAN-2015'
and exists ( SELECT 1 FROM table 1 SS,table 2 UU
WHERE SS.SELLER_ID = UU.USER_ID AND UU.FIRST_LISTED_DATE IS NULL
DATE_ADDED>'5-JAN-2015' AND DATE_ADDED <= '10-JAN-2015')
GROUP BY SELLER_ID,DOM.DOMAIN_ID;
seller_id number;
DOMAIN_ID number;
FIRST_LIST_DATE date;
BEGIN
OPEN C_FLD;
fetch C_FLD into seller_id,DOMAIN_ID,FIRST_LIST_DATE;
dbms_output.put_line(chr(13) || chr(13));
dbms_output.put_line('output');
dbms_output.put_line('------------------------');
while C_FLD%FOUND loop
dbms_output.put_line(seller_id ||DOMAIN_ID|| FIRST_LIST_DATE);
fetch C_FLD into seller_id, DOMAIN_ID,FIRST_LIST_DATE;
end loop;
close C_FLD ;
end;
/
Kindly advise.
Thanks
Bhaskar.S