Hi,
I would like to have the output from an Oracle procedure be captured into a bash variable, then emailed to me when it runs on the cron daily as such:
```text
#!/bin/bash
shellvar=`sqlplus -s <<EOF
execute test();
commit;
exit;
EOF`
echo $shellvar
mail -s "email title" myemail@test.com <<< "$shellvar rows inserted"
```
```text
create or replace procedure test (oRes OUT number) IS
begin
oRes:=1;
EXCEPTION
WHEN OTHERS THEN
raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);
end;
```
This doesn't return anything.
The only thing that works is using a function instead of a procedure and then using select as in below.
```text
#!/bin/bash
shellvar=`sqlplus -s <<EOF
select test() from dual;
commit;
exit;
EOF`
echo $shellvar
mail -s "email title" myemail@test.com <<< "$shellvar rows inserted"
```
```text
create or replace function test return number IS
oRes number;
begin
select 1 into oRes from dual;
return oRes;
EXCEPTION
WHEN OTHERS THEN
raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);
end;
```