Reputation: 415

Convert a PL/SQL script to a stored procedure

Right now I'm importing and transforming data into an Oracle database as follows:

A program regularly polls specific folders, once a file is found it executes a batch file which does some light transformation in Python & bash and then calls SQL*Loader to load the CSV file into a staging table.
Then, the batch script calls an SQL script (via SQLPlus) to do the final transformation and insert the transformed data into master tables for their respective staging table.

The problem with this method is there's no error-handling on the SQLPlus side, eg. if an 'insert into' statement fails because of a violated constraint (or any other reason), it will still continue to execute the rest of the statements contained in the SQL script.

Ideally, if any exception occurs, I'd prefer all changes to be rolled back and details of the exception inserted into an etl log table.

Stored procedures seem to be a good fit as exception handling is built-in. However, I'm struggling with the syntax - specifically how I can take my big SQL scripts (which are just a combination of INSERT INTO, UPDATE, CREATE, DROP, DELETE, etc statements) and throw them into a stored procedure with some very basic error handling.

What I'm hoping for is either:

a quick & dirty dummy's guide to taking my depressing blob of PL/SQL and get it to execute within a stored procedure OR
Any alternative (if a stored proc isn't appropriate) which offers the same functionality, ie. a way to execute a bunch of SQL statements and rollback if any of these statements throw an exception.

About my attempts - I've tried copying portions of my SQL scripts into a stored procedure but they always fail to compile with the error 'PLS-00103 Encountered the symbol when expecting one of the following'. eg.

CREATE OR REPLACE PROCEDURE ETL_2618A AS 
   BEGIN
       DROP SEQUENCE "METER_REPORTING"."SEQ_2618"; 
       CREATE SEQUENCE SEQ_2618; 
END ETL_2618A;

Oracle documentation isn't terribly accessible and I've not had much luck with googling/searching StackOverflow, but I apologise if I've missed something obvious.

Upvotes: 2

Answers (3)

Bob Jarvis - Слава Україні

Reputation: 50067

Adding exception handling to a PL/SQL proc or script isn't difficult, but of course some coding is required. Here's your procedure dressed up slightly with some very basic error reporting added:

CREATE OR REPLACE PROCEDURE ETL_2618A AS
  nCheckpoint  NUMBER;
BEGIN
  nCheckpoint := 1;

  EXECUTE IMMEDIATE 'DROP SEQUENCE "METER_REPORTING"."SEQ_2618"';

  nCheckpoint := 2;

  EXECUTE IMMEDIATE 'CREATE SEQUENCE SEQ_2618';

  RETURN;
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('ETL_2618A failed at checkpoint ' || nCheckpoint ||
                         ' with error ' || SQLCODE || ' : ' || SQLERRM);
    RAISE;
END ETL_2618A;

Not tested on animals - you'll be first! :-)

Upvotes: 0

Satya

Reputation: 8881

To do DDL in PL/SQL you need to use dynamic sql.

CREATE OR REPLACE PROCEDURE testProc IS
   s_sql    VARCHAR2(500);
BEGIN
   s_sql := 'DROP SEQUENCE "METER_REPORTING"."SEQ_2618"';
   EXECUTE IMMEDIATE s_sql;


   s_sql := 'CREATE SEQUENCE "METER_REPORTING"."SEQ_2618"';
   EXECUTE IMMEDIATE s_sql;

EXCEPTION

   WHEN OTHERS THEN
       NULL;
end testProc;
/

Upvotes: 3

Rene

Reputation: 10551

If you run the script in sqlplus you can use:

whenever sqlerror

to control what should happen when an error occurs.

http://docs.oracle.com/cd/B19306_01/server.102/b14357/ch12052.htm

Upvotes: 0

Convert a PL/SQL script to a stored procedure

Answers (3)

Related Questions