Outputting variable result from Oracle loop

Question

I'd like to output STATEMENT_NUMBER for each record where ID is between 10 and 40

I tried writing the below :-

DECLARE
    tempid       NUMBER := 10;
    tempresult   VARCHAR2(20);
BEGIN
    LOOP
        SELECT
            statement_number
        INTO
            tempresult
        FROM
            tblstatementsnew
        WHERE
            id = tempid;

        dbms_output.put_line(tempresult);
        tempid := tempid + 1;
        EXIT WHEN tempid = 40;
    END LOOP;
END;

..but I get the error :-

*Cause: No data was found from the objects. *Action: There was no data from the objects which may be due to end of fetch.

I appreciate there are more simple ways of getting these results, but I'm attempting to learn more about how loops work.

You should handle No data found error properly. See this answer - https://stackoverflow.com/a/1271985/1352127 — Shailesh Pratapwar, Apr 25 '18 at 14:16

score 2 · Accepted Answer · edited Apr 25 '18 at 14:46

2

I hope your code is more complicated than just a print statement. But, consider using an implicit cursor instead:

BEGIN
    FOR v_tempresult IN (SELECT statement_number 
                           FROM tblstatementsnew 
                           WHERE id BETWEEN 10 AND 40)
    LOOP
        dbms_output.put_line(v_tempresult.statement_number);
    END LOOP;
END;

edited Apr 25 '18 at 14:46

Nick Krasnov

26,886
6
61
78

answered Apr 25 '18 at 14:22

Gordon Linoff

1,242,037
58
646
786

I get a 'TEMPRESULT' must be declared error with this. Must that also be declared? – bd528 Apr 25 '18 at 14:27
+ dont forget to [enable the output](https://stackoverflow.com/a/7889380/9338645)! – Chrᴉz remembers Monica Apr 25 '18 at 14:30
hi, can you please take a look at my question? it is similar to trimming last characters. thank you https://stackoverflow.com/questions/69834411/update-one-column-of-each-row-in-a-table-pl-sql-unix-scripting – dcdum2018 Nov 04 '21 at 05:33

Outputting variable result from Oracle loop

1 Answers1