Home » Infrastructure » Linux » Bash & PL/SQL Procedure (Redhat / Oracle )
Bash & PL/SQL Procedure [message #680088] Tue, 21 April 2020 10:05 Go to next message
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,
Re: Bash & PL/SQL Procedure [message #680091 is a reply to message #680088] Tue, 21 April 2020 10:15 Go to previous messageGo to next message
BlackSwan
Messages: 26752
Registered: January 2009
Location: SoCal
Senior Member
Welcome to this forum

Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read
Re: Bash & PL/SQL Procedure [message #680098 is a reply to message #680088] Tue, 21 April 2020 11:12 Go to previous message
Michel Cadot
Messages: 67487
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

This question bears no real connection with Oracle database.
Please find a more appropriate forum.
The topic is locked. /forum/fa/448/0/
If I'm wrong, please, PM me (or report this message to a moderator, explain why you think it should be unlocked and it might be done).
Previous Topic: yum update for OL database and apps servers
Next Topic: I can't login on linux with oracle user
Goto Forum:
  


Current Time: Fri Oct 30 08:46:52 CDT 2020