CODEWITHSUNDEEP
bashoracle-databaseshellsqlplus
Evghen Tester
Evghen Tester

Reputation: 47

Using for loop in shell script over OracleDB sqlplus

I am using this script:

#!/bin/sh
for i in $(seq 1 20);
do
 echo "CREATE TABLE eugene_$i (id NUMBER NOT NULL);
 ! sleep 10
 DECLARE j NUMBER;
 BEGIN
  FOR j IN 1..20 LOOP
   select * from eugene_$i;
   ! sleep 10
  END LOOP;
 END;
 DROP TABLE eugene_$i;" | sqlplus system/password &
done
wait

My intent is to keep the connection alive, and run repetitive queries for testing.

The goal is wait 10 seconds after each select and call the next one.

I am not getting selects at all. I assume the inner for loop has some syntax problem.

Upvotes: 1

Views: 1451

Answers (1)

Boneist
Boneist

Reputation: 23578

When you issue a select statement (either as an implicit or explicit cursor) in PL/SQL, you need to fetch the values into a variable. You can return one row or bulk collect many rows into one or more variables (e.g. one scalar variable per column returned, or a record per row).

The PL/SQL inside your script should therefore look something like:

DECLARE
  -- no need to declare the j variable; that's implicit to the `FOR ... loop`
  id number; 
BEGIN
 FOR j IN 1..20 LOOP
  select *
  into   id
  from   eugene_$i;
  ! sleep 10 -- I'm not sure this will work in your script; it's not Oracle syntax, but may get translated by your script?
 END LOOP;
END;
/

Upvotes: 2

Related Questions