How to execute plsql procedure in shell script?

Question

So I created a procedure in plsql but I want to automatically execute it in shellscript. Like if I insert a table using csv file then the procedure will automatically run using unix shellscript. The software that I'm using is cygwin.

Answer 1

I'm on MS Windows and don't know Unix nor have I ever used Cygwin, which - as tag description says - is ...

... a collection of GNU and other Unix-like FOSS tools which run on MS Window

If I understand it correctly, you'd then

• create a stored procedure (which does some job; mine just displays a message),
• SQL (.sql) script which calls the stored procedure, and
• MS DOS batch (.bat) script whose task is to establish connection to the database and call the previously mentioned .sql script

This is a procedure:

SQL> create or replace procedure p_test is
  2  begin
  3    dbms_output.put_line('Hello!');
  4  end;
  5  /

Procedure created.

SQL>

.SQL script (its name is run_proc.sql):

set serveroutput on

begin
  p_test;
end;
/

exit;

MS DOS batch (.bat) script; its name is run_proc.bat:

sqlplus scott/tiger@pdb1 @run_proc.sql

---

Testing is simple - at MS DOS prompt, call the batch script:

c:\temp>run_proc

c:\temp>sqlplus scott/tiger@pdb1 @run_proc.sql

SQL*Plus: Release 21.0.0.0.0 - Production on Sun Sep 18 07:51:59 2022
Version 21.3.0.0.0

Copyright (c) 1982, 2021, Oracle.  All rights reserved.

Last Successful login time: Sat Sep 17 2022 17:43:08 +02:00

Connected to:
Oracle Database 21c Express Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0

Hello!

PL/SQL procedure successfully completed.

Disconnected from Oracle Database 21c Express Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0

c:\temp>