CODEWITHSUNDEEP
oracle-databasebashshellplsqldbms-scheduler
Achref Khrissi
Achref Khrissi

Reputation: 11

Shell script to automatically run PL/SQL

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

Upvotes: 0

Views: 2614

Answers (2)

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59503

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.

Upvotes: 3

Lalit Kumar B
Lalit Kumar B

Reputation: 49082

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.

Upvotes: 2

Related Questions