The contents of my plsql loop are not executed

I have this code :

set serveroutput on size 500000
DECLARE
  CURSOR crs_equipment IS
      select cd_box from equipment;
BEGIN
   DBMS_OUTPUT.PUT_LINE ( 'Before the Loop' );
   FOR i IN crs_equipment
   LOOP
      DBMS_OUTPUT.PUT_LINE ( 'In the Loop' );
   END LOOP;
   DBMS_OUTPUT.PUT_LINE ( 'After the Loop' );
END;
/

When I run it in SQL developer it works fine.

But when I run it through this ksh script in a terminal it works but doesn’t give me the display that is in the loop. It just displays what is before the loop and what is after the loop. But it does not fit in the loop.

Here is the calling ksh script

sqlplus -s user/password@K04FCNT <<! >path/script.LOG
start path/script.sql
exit
!

Help me please…

Answer

1. Check you are connecting to the correct server and/or database instance.
2. Check you are connecting with the correct user.
3. Check that the data has been committed. If the data has been INSERTed in one session but a COMMIT statement has not been issued then that data will only be visible for that single user session. If you log on with another session (even as the same user) then those rows will not appear in the table.

If all that has been checked and found to be okay then debug the script.

4. Start by running it in a new session in SQL developer (don’t re-use the old one, disconnect and then reconnect) and see if it shows the rows? Then try running it from the terminal again.

5. Change the script to:

   set serveroutput on size 500000
   DECLARE
     CURSOR crs_equipment IS
       SELECT DUMMY FROM DUAL;
   BEGIN
     DBMS_OUTPUT.PUT_LINE ( 'Before the Loop' );
     FOR i IN crs_equipment
     LOOP
       DBMS_OUTPUT.PUT_LINE ( 'In the Loop' );
     END LOOP;
     DBMS_OUTPUT.PUT_LINE ( 'After the Loop' );
   END;
   /
   

   So that you know there is 1 guaranteed row. See if it runs as expected in SQL developer and then if it also runs in the terminal.