How to run PL/SQL script using action?

Document ID : KB000045227
Last Modified Date : 14/02/2018
Show Technical Document Details

Question:

How to run PL/SQL script using action?

 

Answer:

You can use [Run SQL File on Oracle(c) Database Server] action type to run PL/SQL. However, default delimiter is [{; ,GO}]. As a result, RA doesn't understand PL/SQL block properly. So, you need to change the delimiter.

For example, if you want to run 2 queries in PL/SQL script, slash [/] is added to the end of script file.

DECLARE
BEGIN
  INSERT INTO SCOTT.DEPT (DEPTNO, DNAME, LOC) VALUES (50, 'SUPPORT', 'TOKYO');
  INSERT INTO SCOTT.DEPT (DEPTNO, DNAME, LOC) VALUES (60, 'HQ', 'ISLANDIA');
EXCEPTION
  WHEN OTHERS
  THEN
    DBMS_OUTPUT.put_line (SQLERRM);
END;
/

Set [/] to [Delimiters] of [Run SQL File on Oracle(c) Database Server] action, and then the action can understand PL/SQL block properly.
However, if you include comment like /* ... */ in script file, delimiter will not work properly. So, you need to remove the comment from PL/SQL script.