Nzplsql Assignment

In short, no.
Netezza (v7.x and earlier versions) does not support subtransactions, which are neccessary for that to work. To make this worse, the only way to use NZPLSQL is by wrapping it in a stored procedure (it does not support anonymous nzplsql blocks)

This only applies to custom exception handling though. Branching with IF or CASE works fine.
In my view, this tradeoff is one of the major differences compared to Oracle. One way to solve it is by putting the exception handling logic in an external script or application.

There is not much emphasis on this in the documentation, but there are hints and footnotes scattered throughout:

Some SQL commands are prohibited within the BEGIN/COMMIT transaction block. For example:
(+ some other DDL commands like ALTER TABLE, ...)

These SQL commands are also prohibited within the body of a Netezza stored procedure. If you use one of these commands within a transaction block or stored procedure, the system displays an error

  • From the documentation on NZPLSQL:

    This section describes the NZPLSQL language, its structure, and how to use the language to create stored procedures.

  • From the documentation on stored procedures:

    Important: Be careful not to confuse the use of BEGIN/END for grouping statements in NZPLSQL with the BEGIN/END SQL database commands for transaction control. The NZPLSQL BEGIN/END keywords are used only for grouping; they do not start or end a transaction. Procedures always run within a transaction established by an outer query; they cannot start or commit transactions, since IBM® Netezza® SQL does not have nested transactions.

answered Mar 20 '14 at 22:35

На экране высветилось предупреждение: Информация, содержащаяся в этом файле, предназначена исключительно для научного использования. Любые частные лица, которые попытаются создать описанные здесь изделия, рискуют подвергнуться смертоносному облучению или вызвать самопроизвольный взрыв.

- Самопроизвольный взрыв? - ужаснулась Соши.  - Господи Иисусе.

0 Thoughts to “Nzplsql Assignment

Leave a comment

L'indirizzo email non verrà pubblicato. I campi obbligatori sono contrassegnati *