Re: more about DBMS_OUTPUT.PUT_LINE not working

<" srcnckpc"_at_hotmail.com> wrote in message news:3AC30A4C.F10FEFD3_at_hotmail.com...
> Hi:
> As you can see, set serveroutput is turned on. Also the room_id numbers
> in the database start from 99999 to 99991. If I make it ascending, I get
> an error msg.
> THe 'course is' shows up but not v_course in SQL.
>
> Thanks again,
> SRC
>
>
>
> SET SERVEROUTPUT ON SIZE 100000
>
>
>
>
> DECLARE
>
> TYPE rooms_table_type IS TABLE OF rooms%ROWTYPE
> INDEX BY BINARY_INTEGER;
>
> rooms_table rooms_table_type;
>
> v_room_id classes.room_id%TYPE;
> v_dept classes.department%TYPE;
> v_course classes.course%TYPE;
>
>
> BEGIN
>
>
> FOR i IN 99999 .. 99991 LOOP
>
> SELECT *
> INTO rooms_table(i)
> FROM rooms
> WHERE room_id = i;
>
> end loop;
>
> FOR i IN 99999 .. 99991 LOOP
>
> SELECT classes.room_id, classes.department, classes.course
> INTO v_room_id, v_dept, v_course
> FROM classes, rooms
> WHERE classes.room_id = rooms.room_id
> and classes.room_id = i;
>
>
> END LOOP;
>
>
> DBMS_OUTPUT.PUT_LINE('course is'||v_course);
>
>
> END;
> /
>
>
>
>
>
>

Hopefully I'm not offending you, but your call to dbms_output is *OUTSIDE ANY* loop.
So, it is quite obvious you don't get anything back. Also if the records you're trying to retrieve don't exist your code will simply crash.

Regards,

Sybrand Bakker, Oracle DBA Received on Thu Mar 29 2001 - 20:24:54 CEST