Using for loop in shell script over OracleDB sqlplus

Question

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.

Boneist · Accepted Answer · 2019-11-19 12:29:54Z

2

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;
/

answered Nov 19, 2019 at 12:29

Boneist

23.6k1 gold badge28 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Evghen Tester Over a year ago

Thank you, but it does not fully answer my requirements. Though it helped me realize how I need to approach it.

Collectives™ on Stack Overflow

Using for loop in shell script over OracleDB sqlplus

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related