[SOLVED] loops with SQL (oracle)

dc.901 · 01-10-2023, 01:15 PM

Hey folks,
I need some guidance on loops with SQL (in Oracle more specifically).
In bash, this works:

Code:

while read a
do
echo $a
done < filename

So, trying to do something similar with SQL:

Code:

while read a
do
sqlplus user/password@"$a" 
"show pdbs;"
done < filename

But it does not seem to work. No errors either.

For troubleshooting, I tried "echo" as follows and it looks correct.

So, trying to do something similar with SQL:

Code:

while read a
do
echo sqlplus user/password@"$a" 
done < filename

And, above loop with echo shows sqlplus entry correctly, so am bit puzzled with this.
What am I missing?

dc.901 · 01-10-2023, 02:22 PM

Nevermind, figured things out; here's what worked for me if someone else come along looking for similar solution (or can point me to a better solution)

Code:

while read a
do
echo $a
sqlplus user/password@"$a" << EOF
show pdbs;
EOF
done < filename

grail · 01-11-2023, 04:37 AM

To answer your original question, the "show pdbs;" is being read by bash as it is not being passed to sqlplus.

If you are doing a simple short one liner, you could also use a herestring instead of a heredoc:

Code:

sqlplus user@"$a" <<<"show pdbs;"

dc.901 · 01-13-2023, 08:27 AM

Quote:

Originally Posted by grail

To answer your original question, the "show pdbs;" is being read by bash as it is not being passed to sqlplus.

If you are doing a simple short one liner, you could also use a herestring instead of a heredoc:

Code:

sqlplus user@"$a" <<<"show pdbs;"

Thank you!