CODEWITHSUNDEEP
bashoracle-databaseloopsplsqlsqlplus
Ahmed Zein
Ahmed Zein

Reputation: 27

Running Loops in SQLPlus

I made a bash script that connects to a database by SQLPlus and runs a SQL Script which contains a For loop as below. But once running it, it stuck in the BEGIN of the loop as below. I tried to run it directly through SQLPlus and it is the same. So can anyone advise what is done wrong here.

BEGIN
  FOR l_counter IN 1..5
  LOOP
    DBMS_OUTPUT.PUT_LINE( l_counter );
  END LOOP;
END

Result:

   BEGIN
  2  FOR l_counter IN 1..5
  3  LOOP
  4  DBMS_OUTPUT.PUT_LINE( l_counter );
  5  END LOOP;
  6  END;
  7
  8

Bash:

SPOOL $FILE

@/home/genesys/scripts/Counter.sql
SPOOL OFF
EXIT
EOF

Edit: This is the result I am getting after adding a slash at the end.

SQL> set serveroutput on;
SQL> BEGIN
  FOR l_counter IN 1..10
  LOOP
    DBMS_OUTPUT.PUT_LINE( l_counter || ' finished ');
  END LOOP;
END;/  2    3    4    5    6
  7
  8
  9
 10

Upvotes: 2

Views: 994

Answers (1)

Barbaros Özhan
Barbaros Özhan

Reputation: 65105

You can prefer using one of the following two methods in order to put the results of the DBMS_OUTPUT.PUT_LINE command into a file

  1. By using .sql file
$ cd /home/genesys/scripts
$ sqlplus /nolog
SQL> conn un/pwd
SQL> @Counter.sql

where Counter.sql file has the following content :

SET FEEDBACK OFF
SET SERVEROUTPUT ON
SPOOL output2.txt
BEGIN
FOR l_counter IN 1..10
 LOOP
  DBMS_OUTPUT.PUT_LINE(l_counter || ' finished ');
 END LOOP; 
END;
/
SPOOL OFF
  1. By Creating a .sh(Counter.sh) file :
record=`sqlplus -S /nolog << EOF
conn hr/hr
SET FEEDBACK OFF
SET SERVEROUTPUT ON
SPOOL output.txt
BEGIN
 FOR l_counter IN 1..10
 LOOP
   DBMS_OUTPUT.PUT_LINE(l_counter || ' finished ');
 END LOOP; 
END;
/
SPOOL OFF
EOF`

call from the command prompt where you created the file

$ cd /home/genesys/scripts
$ . Counter.sh

Upvotes: 1

Related Questions