| Bash & PL/SQL Procedure [message #680088] |
Tue, 21 April 2020 10:05  |
 |
Remy24804
Messages: 1
Registered: April 2020
|
Junior Member |
|
|
Hello,
It's the first time I need help on a "dev" topic, I've been trying to make it work for hours.. But I guess it's beyond my knowledge this time.
Here is my issue : I am trying to run a shell script, which first does an SQL request to gather some informations, then run a PL/SQL Procedure using the SQL output.
The PL/SQL procedure must be called X time, where X is the number of row from the SQL request, best would be to have a single connection for every row from the SQL output.
So here is what I tried :
1 - SQL REQUEST : Store WANTED_DATA into $tmp var in order to reuse it later into the PL SQL Procedure.
For the example, lets say "WANTED_DATA" = "123, 321; 789, 987;"
function SQL_REQ {
tmp=$(`$APP_SQL -S $USR/$PWD@$DB <<-eof
select WANTED_DATA
from TABLE
where CONDITION
exit
eof`)
PLSQL_Proc
}
2 - PL SQL Procedure : And here is the tricky part. From the shell, I need to start a function that will run a PL SQL Procedure, using the "$WANTED_DATA" into the procedure. Another issue I have, is that even if this work, it will connect and disconnect from the database for each row of $tmp..
function PLSQL_Proc {
while IFS=; read -r WANTED_DATA
do
$APP_SQL -S $USR/$PWD@$DB<<-eof
DECLARE
result varchar2(100);
BEGIN
result := scripts.remove_data($WANTED_DATA);
END;
/
eof
done < $tmp
}
SQL_REQ
Current error(s) :
$tmp ambigous redirect
123, 321; : Command not found (But at least, we know that the SQL request works since we do catch WANTED_DATA)
To note :
-I cannot create anything on the database side, but I can do anything on the shell side.
-Using Redhat (The before-last version - didnt took time to upgrade yet haha), and Oracle (Lastest)
If any of you have an idea, please let me know.
Thanks,
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
