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 — user330315
– user330315, Commented Feb 5, 2015 at 14:07

Jonathan Leffler · Accepted Answer · 2015-02-05 15:15:47Z

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 5, 2015 at 15:15

Jonathan Leffler

760k145 gold badges961 silver badges1.3k bronze badges

answered Feb 5, 2015 at 15:00

Wernfried Domscheit

60.4k10 gold badges93 silver badges132 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Wernfried Domscheit Over a year ago

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.

EsmaeelE Over a year ago

Exactly what I want.

Jonathan Leffler · Accepted Answer · 2015-02-05 15:12:04Z

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 5, 2015 at 15:12

Jonathan Leffler

760k145 gold badges961 silver badges1.3k bronze badges

answered Feb 5, 2015 at 14:21

Lalit Kumar B

49.5k13 gold badges104 silver badges131 bronze badges

4 Comments

Achref Khrissi Over a year ago

i'm forced to do it with shell script but i didn't know how to start

Lalit Kumar B Over a year ago

@Achref, just follow my answer. Is there anything else concerning you?

Achref Khrissi Over a year ago

@LalitKumarB is there any tutorial that i can have

Lalit Kumar B Over a year ago

Tim Hall has some good stuff here oracle-base.com/articles/linux/cron-on-linux.php

Collectives™ on Stack Overflow

Shell script to automatically run PL/SQL

2 Answers 2

2 Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related