Shell script to automatically run PL/SQL

Question

I have PL/SQL scripts and I was asked to make them run automatically using shell scripts. The problem is that I have no idea on how to do that, so please if there is any way to help me do that I will be thankful.

I tried to take the basics but it's far away from what I need:

output="$(sqlplus -S user/pw@//ip:1521/db <<ENDOFSQL
set serveroutput on;
DECLARE
    v_return PLS_INTEGER;
BEGIN

    DBMS_OUTPUT.PUT_LINE(v_return);
END;
exit;
ENDOFSQL)"

echo $output

You need a `/` after the `end;` (on a new line). But apart from that I have **no** idea what you are asking — , Feb 05 '15 at 14:07

score 3 · Answer 1 · edited Feb 05 '15 at 15:15

3

You can do it like this:

sqlplus -S user/pw@//ip:1521/db <<ENDOFSQL
set serveroutput on;
DECLARE
    v_return PLS_INTEGER;
BEGIN

    DBMS_OUTPUT.PUT_LINE('Hello World');
END;
.
/

ENDOFSQL

There is no need to capture the output in the variable if all you're going to do is echo the output afterwards.

edited Feb 05 '15 at 15:15

Jonathan Leffler

730,956
141
904
1,278

answered Feb 05 '15 at 15:00

Wernfried Domscheit

54,457
9
76
110

1

The question was: how to run a PL/SQL block directly from shell. He does not mention anything about scheduling - at least I don't see anything in his code. – Wernfried Domscheit Feb 05 '15 at 16:22
Exactly what I want. – EsmaeelE Nov 23 '22 at 07:21

score 2 · Accepted Answer · edited Feb 05 '15 at 15:12

2

I was asked to make them run automatically using shell scripts

At first place, why do you worry about automation of the script at database level. You could simply schedule it as a cron job at the OS level.

You could also schedule it at database level using DBMS_SCHEDULER or DBMS_JOB, whichever is applicable to your database version.

edited Feb 05 '15 at 15:12

Jonathan Leffler

730,956
141
904
1,278

answered Feb 05 '15 at 14:21

Lalit Kumar B

47,486
13
97
124

i'm forced to do it with shell script but i didn't know how to start – Achref Khrissi Feb 05 '15 at 15:19
@Achref, just follow my answer. Is there anything else concerning you? – Lalit Kumar B Feb 05 '15 at 15:22
@LalitKumarB is there any tutorial that i can have – Achref Khrissi Feb 05 '15 at 15:30
Tim Hall has some good stuff here http://oracle-base.com/articles/linux/cron-on-linux.php – Lalit Kumar B Feb 05 '15 at 15:50

Shell script to automatically run PL/SQL

2 Answers2