Procedure blocks in Oracle

[jduke99]

PL/SQL is finicky with procedure blocks in a series of commands. However the fix is very easy. Just need a slash on a separate line after the END; statement of each procedure. I have replicated the error and fix using two IDEs (Sql Developer and Aqua Data Studio), but not sure if this is unique to certain versions of Oracle.

So this

BEGIN
  EXECUTE IMMEDIATE 'TRUNCATE TABLE  HTN_t7';
  EXECUTE IMMEDIATE 'DROP TABLE  HTN_t7';
EXCEPTION
  WHEN OTHERS THEN
    IF SQLCODE != -942 THEN
      RAISE;
    END IF;
END;

BEGIN
  EXECUTE IMMEDIATE 'TRUNCATE TABLE  HTN_t8';
  EXECUTE IMMEDIATE 'DROP TABLE  HTN_t8';
EXCEPTION
  WHEN OTHERS THEN
    IF SQLCODE != -942 THEN
      RAISE;
    END IF;
END;

Should be this

BEGIN
  EXECUTE IMMEDIATE 'TRUNCATE TABLE  HTN_t7';
  EXECUTE IMMEDIATE 'DROP TABLE  HTN_t7';
EXCEPTION
  WHEN OTHERS THEN
    IF SQLCODE != -942 THEN
      RAISE;
    END IF;
END;

/

BEGIN
  EXECUTE IMMEDIATE 'TRUNCATE TABLE  HTN_t8';
  EXECUTE IMMEDIATE 'DROP TABLE  HTN_t8';
EXCEPTION
  WHEN OTHERS THEN
    IF SQLCODE != -942 THEN
      RAISE;
    END IF;
END;

[pbr6cornell]

I wonder if this is really a PL/SQL thing, and not just a SQL Developer console issue. It seems similar to the use of GO in SQL Server Management Studio. When the code was executed via JDBC, did you experience this issue?

On Wed, Dec 3, 2014 at 11:52 AM, Jon Duke notifications@github.com wrote:

PL/SQL is finicky with procedure blocks in a series of commands. However the fix is very easy. Just need a slash on a separate line after the END; statement of each procedure. So this

BEGIN EXECUTE IMMEDIATE 'TRUNCATE TABLE HTN_t7'; EXECUTE IMMEDIATE 'DROP TABLE HTN_t7'; EXCEPTION WHEN OTHERS THEN IF SQLCODE != -942 THEN RAISE; END IF; END;

BEGIN EXECUTE IMMEDIATE 'TRUNCATE TABLE HTN_t8'; EXECUTE IMMEDIATE 'DROP TABLE HTN_t8'; EXCEPTION WHEN OTHERS THEN IF SQLCODE != -942 THEN RAISE; END IF; END;

Should be this

BEGIN EXECUTE IMMEDIATE 'TRUNCATE TABLE HTN_t7'; EXECUTE IMMEDIATE 'DROP TABLE HTN_t7'; EXCEPTION WHEN OTHERS THEN IF SQLCODE != -942 THEN RAISE; END IF; END;

/

BEGIN EXECUTE IMMEDIATE 'TRUNCATE TABLE HTN_t8'; EXECUTE IMMEDIATE 'DROP TABLE HTN_t8'; EXCEPTION WHEN OTHERS THEN IF SQLCODE != -942 THEN RAISE; END IF; END;

— Reply to this email directly or view it on GitHub https://github.com/OHDSI/SqlRender/issues/5.

[schuemie]

This is a know issue in SqlDeveloper and SqlPlus. You don't have to add the slashes when using JDBC.

[jduke99]

Yes it occurs across multiiple oracle IDEs but does not occur through JDBC. However, as long as the fix does not break JDBC runs, I would request this be done. Why? Because watching the progress of scripts running (and using autotrace etc for performance monitoring) is valuable. Running through R is a black box in this regard. If you would like provide support for autotrace and more verbose progress details in the R version, then that may be sufficient.

However, seems easier to me to fix the slashes. If they will break the JDBC runs, then let us discuss further.

[schuemie]

The executeSql() function in our DatabaseConnector package already has a parameter profile = FALSE which, when set to true will:

1. Write every separate statement (SQL statement, procedure block) to a text file prior to executing
2. Print the time it takes to run a statement

Would that meet you needs?

If we added the slashes right now, it would break the JDBC version (Oracle would throw an error). We could modify our connector functions to remove the slashes prior to sending it to the server. I already have it removing semicolons at the end of SQL statements (or else Oracle would break), but leave them at the end of procedure blocks (or else Oracle would break), so what's one more exception built in just for Oracle? ;-)

[jduke99]

That is more than sufficient Martijn! Thank you!