Postgresql raise application error

Use the RAISE statement to report messages and raise errors.

RAISE [ level ] 'format' [, expression [, ... ]] [ USING option = expression [, ... ] ];
RAISE [ level ] condition_name [ USING option = expression [, ... ] ];
RAISE [ level ] SQLSTATE 'sqlstate' [ USING option = expression [, ... ] ];
RAISE [ level ] USING option = expression [, ... ];
RAISE ;

The level option specifies the error severity. Allowed levels are DEBUG, LOG, INFO, NOTICE, WARNING, and EXCEPTION, with EXCEPTION being the default. EXCEPTION raises an error (which normally aborts the current transaction); the other levels only generate messages of different priority levels. Whether messages of a particular priority are reported to the client, written to the server log, or both is controlled by the log_min_messages and client_min_messages configuration variables. See Chapter 20 for more information.

After level if any, you can specify a format string (which must be a simple string literal, not an expression). The format string specifies the error message text to be reported. The format string can be followed by optional argument expressions to be inserted into the message. Inside the format string, % is replaced by the string representation of the next optional argument’s value. Write %% to emit a literal %. The number of arguments must match the number of % placeholders in the format string, or an error is raised during the compilation of the function.

In this example, the value of v_job_id will replace the % in the string:

RAISE NOTICE 'Calling cs_create_job(%)', v_job_id;

You can attach additional information to the error report by writing USING followed by option = expression items. Each expression can be any string-valued expression. The allowed option key words are:

This example will abort the transaction with the given error message and hint:

RAISE EXCEPTION 'Nonexistent ID --> %', user_id
      USING HINT = 'Please check your user ID';

These two examples show equivalent ways of setting the SQLSTATE:

RAISE 'Duplicate user ID: %', user_id USING ERRCODE = 'unique_violation';
RAISE 'Duplicate user ID: %', user_id USING ERRCODE = '23505';

There is a second RAISE syntax in which the main argument is the condition name or SQLSTATE to be reported, for example:

RAISE division_by_zero;
RAISE SQLSTATE '22012';

In this syntax, USING can be used to supply a custom error message, detail, or hint. Another way to do the earlier example is

RAISE unique_violation USING MESSAGE = 'Duplicate user ID: ' || user_id;

Still another variant is to write RAISE USING or RAISE level USING and put everything else into the USING list.

The last variant of RAISE has no parameters at all. This form can only be used inside a BEGIN block’s EXCEPTION clause; it causes the error currently being handled to be re-thrown.

If no condition name nor SQLSTATE is specified in a RAISE EXCEPTION command, the default is to use ERRCODE_RAISE_EXCEPTION (P0001). If no message text is specified, the default is to use the condition name or SQLSTATE as message text.

The ASSERT statement is a convenient shorthand for inserting debugging checks into PL/pgSQL functions.

ASSERT condition [ , message ];

The condition is a Boolean expression that is expected to always evaluate to true; if it does, the ASSERT statement does nothing further. If the result is false or null, then an ASSERT_FAILURE exception is raised. (If an error occurs while evaluating the condition, it is reported as a normal error.)

If the optional message is provided, it is an expression whose result (if not null) replaces the default error message text “assertion failed”, should the condition fail. The message expression is not evaluated in the normal case where the assertion succeeds.

Testing of assertions can be enabled or disabled via the configuration parameter plpgsql.check_asserts, which takes a Boolean value; the default is on. If this parameter is off then ASSERT statements do nothing.

Note that ASSERT is meant for detecting program bugs, not for reporting ordinary error conditions. Use the RAISE statement, described above, for that.

Introduction to PostgreSQL RAISE EXCEPTION

PostgreSQL raises an exception is used to raise the statement for reporting the warnings, errors and other type of reported message within a function or stored procedure. We are raising the exception in function and stored procedures in PostgreSQL; there are different level available of raise exception, i.e. info, notice, warning, debug, log and notice. We can basically use the raise exception statement to raise errors and report the messages; by default, the exception level is used in the raise exception. We can also add the parameter and variable in the raise exception statement; also, we can print our variable’s value.

Syntax:

Given below is the syntax:

RAISE [LEVEL] (Level which we have used with raise exception statement.) [FORMAT]

OR

RAISE [ LEVEL] USING option (Raise statement using option) = expression

OR

RAISE;

Parameters Description:

• RAISE: This is defined as an exception statement that was used to raise the exception in PostgreSQL. We have basically pass two parameters with raise exception, i.e. level and format. There is various parameter available to raise an error.
• LEVEL: Level in raise exception is defined as defining the error severity. We can use level in raise exception, i.e. log, notice, warning, info, debug and exception. Every level generates detailed information about the error or warning message based on the priority of levels. By default, the exception level is used with raise statement in PostgreSQL, log_min_messages and client_min_messages parameter will be used to control the database server logging.
• FORMAT: This is defined as an error message which we want to display. If our message needs some variable value, then we need to use the % sign. This sign acts as a placeholder that replaces the variable value with a given command.
• expression: We can use multiple expression with raise expression statement in it. The expression is an optional parameter that was used with the raise expression statement.
• option: We can use options with raise exception statement in PostgreSQL.

How RAISE EXCEPTION work in PostgreSQL?

We can raise the exception by violating the data integrity constraints in PostgreSQL. Raise exception is basically used to raise the error and report the messages.

Given below shows the raise statement-level options, which was specifying the error severity in PostgreSQL:

• Notice
• Log
• Debug
• Warning
• Info
• Exception

If we need to raise an error, we need to use the exception level after using the raise statement in it.

We can also add more detailed information about the error by using the following clause with the raise exception statement in it.

USING option = expression

We can also provide an error message that is used to find the root cause of the error easier, and it is possible to discover the error. The exception gives an error in the error code; we will identify the error using the error code; it will define either a SQL State code condition. When the raise statement does not specify the level, it will specify the printed message as an error. After printing, the error message currently running transaction is aborted, and the next raise statement is not executed.

It is used in various parameters or options to produce an error message that is more informative and readable. When we are not specifying any level, then by default, the exception level is used in the raise statement. The exception level is aborted the current transaction with a raise statement in it. The exception level is very useful and important to raise the statement to abort the current transaction in it.

Examples of PostgreSQL RAISE EXCEPTION

Given below are the examples mentioned:

Example #1

Raise an exception statement, which reports different messages.

• The below example shows the raise exception statement, which reports different messages.
• The below example shows the raise statement, which was used to report the different messages at the current time stamp.

Code:

DO $$
BEGIN
RAISE INFO 'Print the message of information %', now() ;
RAISE LOG 'Print the message of log %', now();
RAISE DEBUG 'Print the message of debug %', now();
RAISE WARNING 'Print the message of warning %', now();
RAISE NOTICE 'Print the message of notice %', now();
RAISE EXCEPTION 'Print the message of exception %', now();
END $$;

Output:

In the above example, only info, warning, notice and exception will display the information. But debug and log will not display the output information.

Example #2

Raise error using raise exception.

• The below example shows that raise the error using raise exception in PostgreSQL.
• We have added more detailed information by adding an exception.

Code:

DO $$
DECLARE
personal_email varchar(100) := 'raise@exceptions.com';
BEGIN
-- First check the user email id is correct as well as duplicate or not.
-- If user email id is duplicate then report mail as duplicate.
RAISE EXCEPTION 'Enter email is duplicate: %', personal_email
USING HINT = 'Check email and enter correct email ID of user';
END $$;

Output:

Example #3

Raise exception by creating function.

• The below example shows that raise exception in PostgreSQL by creating the function.

Code:

create or replace function test_exp() returns void as
$$
begin
raise exception using message = 'S 167', detail = 'D 167', hint = 'H 167', errcode = 'P3333';
end;
$$ language plpgsql
;

Output:

Advantages of PostgreSQL RAISE EXCEPTION

Below are the advantages:

• The main advantage of raise exception is to raise the statement for reporting the warnings.
• We have used raise exception in various parameters.
• Raise exception to have multiple levels to raise the error and warning.
• Raise statement is used the level of exception to show warnings and error.

Conclusion

RAISE EXCEPTION in PostgreSQL is basically used to raise the warning and error message. It is very useful and important. There are six levels of raise exception is available in PostgreSQL, i.e. notice, log, debug, warning info and exception. It is used in various parameters.

This is a guide to PostgreSQL RAISE EXCEPTION. Here we discuss how to RAISE EXCEPTION work in PostgreSQL, its advantages and examples. You may also have a look at the following articles to learn more –

1. PostgreSQL ARRAY_AGG()
2. PostgreSQL IF Statement
3. PostgreSQL Auto Increment
4. PostgreSQL replace

In this article, we will look into the Errors in that are inbuilt in PostgreSQL and the process of raising an error in PostgreSQL through RAISE statement and to use the ASSERT statement to insert debugging checks into PL/pgSQL blocks.

To raise an error message user can implement the RAISE statement as follows:

Syntax: RAISE level format;

Let’s explore into the raise statement a bit more. Following the RAISE statement is the level option that specifies the error severity. PostgreSQL provides the following levels:

• DEBUG
• LOG
• NOTICE
• INFO
• WARNING
• EXCEPTION

If users don’t specify the level, by default, the RAISE statement will use the EXCEPTION level that raises an error and stops the current transaction. We will discuss the RAISE EXCEPTION later in the next section.

The format is a string that specifies the message. The format uses percentage ( %) placeholders that will be substituted by the next arguments. The number of placeholders must match the number of arguments, otherwise, PostgreSQL will report the following error message:

[Err] ERROR:  too many parameters specified for RAISE

Example:

The following example illustrates the RAISE statement that reports different messages at the current time.

DO $$ 
BEGIN 
  RAISE INFO 'information message %', now() ;
  RAISE LOG 'log message %', now();
  RAISE DEBUG 'debug message %', now();
  RAISE WARNING 'warning message %', now();
  RAISE NOTICE 'notice message %', now();
END $$;

Output:

Note: Not all messages are reported back to the client, only INFO, WARNING, and NOTICE level messages are reported to the client. This is controlled by the client_min_messages and log_min_messages configuration parameters.

Raising Errors:

To raise errors, you use the EXCEPTION level after the RAISE statement. Note that the RAISE statement uses the EXCEPTION level by default. Besides raising an error, you can add more detailed information by using the following clause with the RAISE statement:

USING option = expression

The options can be any one of the below:

• MESSAGE: set error message text
• HINT: provide the hint message so that the root cause of the error is easier to be discovered.
• DETAIL:  give detailed information about the error.
• ERRCODE: identify the error code, which can be either by condition name or directly five-character SQLSTATE code.

Example 1:

DO $$ 
DECLARE
  email varchar(255) := 'raju@geeksforgeeks.org';
BEGIN 
  -- check email for duplicate
  -- ...
  -- report duplicate email
  RAISE EXCEPTION 'Duplicate email: %', email 
        USING HINT = 'Check the email again';
END $$;

Output:

Example 2:

The following examples illustrate how to raise an SQLSTATE and its corresponding condition:

DO $$ 
BEGIN 
    --...
    RAISE SQLSTATE '2210B';
END $$;
DO $$ 
BEGIN 
    --...
    RAISE invalid_regular_expression;
END $$;

Output:

Postgresql raise exception примеры

Use the RAISE statement to report messages and raise errors.

The level option specifies the error severity. Allowed levels are DEBUG , LOG , INFO , NOTICE , WARNING , and EXCEPTION , with EXCEPTION being the default. EXCEPTION raises an error (which normally aborts the current transaction); the other levels only generate messages of different priority levels. Whether messages of a particular priority are reported to the client, written to the server log, or both is controlled by the log_min_messages and client_min_messages configuration variables. See Chapter 20 for more information.

After level if any, you can specify a format string (which must be a simple string literal, not an expression). The format string specifies the error message text to be reported. The format string can be followed by optional argument expressions to be inserted into the message. Inside the format string, % is replaced by the string representation of the next optional argument’s value. Write %% to emit a literal % . The number of arguments must match the number of % placeholders in the format string, or an error is raised during the compilation of the function.

In this example, the value of v_job_id will replace the % in the string:

You can attach additional information to the error report by writing USING followed by option = expression items. Each expression can be any string-valued expression. The allowed option key words are:

Sets the error message text. This option can’t be used in the form of RAISE that includes a format string before USING .

Supplies an error detail message.

Supplies a hint message.

Specifies the error code (SQLSTATE) to report, either by condition name, as shown in Appendix A, or directly as a five-character SQLSTATE code.

COLUMN
CONSTRAINT
DATATYPE
TABLE
SCHEMA

Supplies the name of a related object.

This example will abort the transaction with the given error message and hint:

These two examples show equivalent ways of setting the SQLSTATE:

There is a second RAISE syntax in which the main argument is the condition name or SQLSTATE to be reported, for example:

In this syntax, USING can be used to supply a custom error message, detail, or hint. Another way to do the earlier example is

Still another variant is to write RAISE USING or RAISE level USING and put everything else into the USING list.

The last variant of RAISE has no parameters at all. This form can only be used inside a BEGIN block’s EXCEPTION clause; it causes the error currently being handled to be re-thrown.

Before PostgreSQL 9.1, RAISE without parameters was interpreted as re-throwing the error from the block containing the active exception handler. Thus an EXCEPTION clause nested within that handler could not catch it, even if the RAISE was within the nested EXCEPTION clause’s block. This was deemed surprising as well as being incompatible with Oracle’s PL/SQL.

If no condition name nor SQLSTATE is specified in a RAISE EXCEPTION command, the default is to use ERRCODE_RAISE_EXCEPTION ( P0001 ). If no message text is specified, the default is to use the condition name or SQLSTATE as message text.

When specifying an error code by SQLSTATE code, you are not limited to the predefined error codes, but can select any error code consisting of five digits and/or upper-case ASCII letters, other than 00000 . It is recommended that you avoid throwing error codes that end in three zeroes, because these are category codes and can only be trapped by trapping the whole category.

43.9.2. Checking Assertions

The ASSERT statement is a convenient shorthand for inserting debugging checks into PL/pgSQL functions.

The condition is a Boolean expression that is expected to always evaluate to true; if it does, the ASSERT statement does nothing further. If the result is false or null, then an ASSERT_FAILURE exception is raised. (If an error occurs while evaluating the condition , it is reported as a normal error.)

If the optional message is provided, it is an expression whose result (if not null) replaces the default error message text “ assertion failed ” , should the condition fail. The message expression is not evaluated in the normal case where the assertion succeeds.

Testing of assertions can be enabled or disabled via the configuration parameter plpgsql.check_asserts , which takes a Boolean value; the default is on . If this parameter is off then ASSERT statements do nothing.

Note that ASSERT is meant for detecting program bugs, not for reporting ordinary error conditions. Use the RAISE statement, described above, for that.

Submit correction

If you see anything in the documentation that is not correct, does not match your experience with the particular feature or requires further clarification, please use this form to report a documentation issue.

Copyright © 1996-2023 The PostgreSQL Global Development Group

Источник

Postgresql raise exception примеры

Команда RAISE предназначена для вывода сообщений и вызова ошибок.

уровень задаёт уровень важности ошибки. Возможные значения: DEBUG , LOG , INFO , NOTICE , WARNING и EXCEPTION . По умолчанию используется EXCEPTION . EXCEPTION вызывает ошибку (что обычно прерывает текущую транзакцию), остальные значения уровня только генерируют сообщения с различными уровнями приоритета. Будут ли сообщения конкретного приоритета переданы клиенту или записаны в журнал сервера, или и то, и другое, зависит от конфигурационных переменных log_min_messages и client_min_messages. За дополнительными сведениями обратитесь к Главе 18.

После указания уровня , если оно есть, можно задать строку формата (это должна быть простая строковая константа, не выражение). Строка формата определяет вид текста об ошибке, который будет выдан. За строкой формата могут следовать необязательные выражения аргументов, которые будут вставлены в сообщение. Внутри строки формата знак % заменяется строковым представлением значения очередного аргумента. Чтобы выдать символ % буквально, продублируйте его (как %% ). Число аргументов должно совпадать с числом местозаполнителей % в строке формата, иначе при компиляции функции возникнет ошибка.

В следующем примере символ % будет заменён на значение v_job_id :

При помощи USING и последующих элементов параметр = выражение можно добавить дополнительную информацию к отчёту об ошибке. Все выражения представляют собой строковые выражения. Возможные ключевые слова для параметра следующие:

Устанавливает текст сообщения об ошибке. Этот параметр не может использоваться, если в команде RAISE присутствует формат перед USING . DETAIL

Предоставляет детальное сообщение об ошибке. HINT

Предоставляет подсказку по вызванной ошибке. ERRCODE

Устанавливает код ошибки ( SQLSTATE ). Код ошибки задаётся либо по имени, как показано в Приложении A, или напрямую, пятисимвольный код SQLSTATE . COLUMN
CONSTRAINT
DATATYPE
TABLE
SCHEMA

Предоставляет имя соответствующего объекта, связанного с ошибкой.

Этот пример прерывает транзакцию и устанавливает сообщение об ошибке с подсказкой:

Следующие два примера демонстрируют эквивалентные способы задания SQLSTATE :

У команды RAISE есть и другой синтаксис, в котором в качестве главного аргумента используется имя или код SQLSTATE ошибки. Например:

Предложение USING в этом синтаксисе можно использовать для того, чтобы переопределить стандартное сообщение об ошибке, детальное сообщение, подсказку. Ещё один вариант предыдущего примера:

Ещё один вариант — использовать RAISE USING или RAISE уровень USING , а всё остальное записать в списке USING .

И заключительный вариант, в котором RAISE не имеет параметров вообще. Эта форма может использоваться только в секции EXCEPTION блока и предназначена для того, чтобы повторно вызвать ошибку, которая сейчас перехвачена и обрабатывается.

Примечание

До версии PostgreSQL 9.1 команда RAISE без параметров всегда вызывала ошибку с выходом из блока, содержащего активную секцию EXCEPTION . Эту ошибку нельзя было перехватить, даже если RAISE в секции EXCEPTION поместить во вложенный блок со своей секцией EXCEPTION . Это было сочтено удивительным и не совместимым с Oracle PL/SQL.

Если в команде RAISE EXCEPTION не задано ни имя условия, ни код SQLSTATE, по умолчанию выдаётся исключение ERRCODE_RAISE_EXCEPTION ( P0001 ). Если не задан текст сообщения, по умолчанию в качестве этого текста передаётся имя условия или код SQLSTATE.

Примечание

При задании SQLSTATE кода необязательно использовать только список предопределённых кодов ошибок. В качестве кода ошибки может быть любое пятисимвольное значение, состоящее из цифр и/или ASCII символов в верхнем регистре, кроме 00000 . Не рекомендуется использовать коды ошибок, которые заканчиваются на 000 , потому что так обозначаются коды категорий. И чтобы их перехватить, нужно перехватывать целую категорию.

41.9.2. Проверка утверждений

Оператор ASSERT представляет удобное средство вставлять отладочные проверки в функции PL/pgSQL .

Здесь условие — это логическое выражение, которое, как ожидается, должно быть всегда истинным; если это так, оператор ASSERT больше ничего не делает. Если же оно возвращает ложь или NULL, этот оператор выдаёт исключение ASSERT_FAILURE . (Если ошибка происходит при вычислении условия , она выдаётся как обычная ошибка.)

Если в нём задаётся необязательное сообщение , результат этого выражения (если он не NULL) заменяет сообщение об ошибке по умолчанию « assertion failed » (нарушение истинности), в случае, если условие не выполняется. В обычном случае, когда условие утверждения выполняется, выражение сообщения не вычисляется.

Проверку утверждений можно включить или отключить с помощью конфигурационного параметра plpgsql.check_asserts , принимающего логическое значение; по умолчанию она включена ( on ). Если этот параметр отключён ( off ), операторы ASSERT ничего не делают.

Учтите, что оператор ASSERT предназначен для выявления программных дефектов, а не для вывода обычных ошибок (для этого используется оператор RAISE , описанный выше).

Источник

Postgresql raise exception примеры

Команда RAISE предназначена для вывода сообщений и вызова ошибок.

уровень задаёт уровень важности ошибки. Возможные значения: DEBUG , LOG , INFO , NOTICE , WARNING и EXCEPTION . По умолчанию используется EXCEPTION . EXCEPTION вызывает ошибку (что обычно прерывает текущую транзакцию), остальные значения уровня только генерируют сообщения с различными уровнями приоритета. Будут ли сообщения конкретного приоритета переданы клиенту или записаны в журнал сервера, или и то, и другое, зависит от конфигурационных переменных log_min_messages и client_min_messages. За дополнительными сведениями обратитесь к Главе 20.

После указания уровня , если оно есть, можно задать строку формата (это должна быть простая строковая константа, не выражение). Строка формата определяет вид текста об ошибке, который будет выдан. За строкой формата могут следовать необязательные выражения аргументов, которые будут вставлены в сообщение. Внутри строки формата знак % заменяется строковым представлением значения очередного аргумента. Чтобы выдать символ % буквально, продублируйте его (как %% ). Число аргументов должно совпадать с числом местозаполнителей % в строке формата, иначе при компиляции функции возникнет ошибка.

В следующем примере символ % будет заменён на значение v_job_id :

При помощи USING и последующих элементов параметр = выражение можно добавить дополнительную информацию к отчёту об ошибке. Все выражения представляют собой строковые выражения. Возможные ключевые слова для параметра следующие:

Устанавливает текст сообщения об ошибке. Этот параметр не может использоваться, если в команде RAISE присутствует формат перед USING . DETAIL

Предоставляет детальное сообщение об ошибке. HINT

Предоставляет подсказку по вызванной ошибке. ERRCODE

Устанавливает код ошибки ( SQLSTATE ). Код ошибки задаётся либо по имени, как показано в Приложении A, или напрямую, пятисимвольный код SQLSTATE . COLUMN
CONSTRAINT
DATATYPE
TABLE
SCHEMA

Предоставляет имя соответствующего объекта, связанного с ошибкой.

Этот пример прерывает транзакцию и устанавливает сообщение об ошибке с подсказкой:

Следующие два примера демонстрируют эквивалентные способы задания SQLSTATE :

У команды RAISE есть и другой синтаксис, в котором в качестве главного аргумента используется имя или код SQLSTATE ошибки. Например:

Предложение USING в этом синтаксисе можно использовать для того, чтобы переопределить стандартное сообщение об ошибке, детальное сообщение, подсказку. Ещё один вариант предыдущего примера:

Ещё один вариант — использовать RAISE USING или RAISE уровень USING , а всё остальное записать в списке USING .

И заключительный вариант, в котором RAISE не имеет параметров вообще. Эта форма может использоваться только в секции EXCEPTION блока и предназначена для того, чтобы повторно вызвать ошибку, которая сейчас перехвачена и обрабатывается.

Примечание

До версии PostgreSQL 9.1 команда RAISE без параметров всегда вызывала ошибку с выходом из блока, содержащего активную секцию EXCEPTION . Эту ошибку нельзя было перехватить, даже если RAISE в секции EXCEPTION поместить во вложенный блок со своей секцией EXCEPTION . Это было сочтено удивительным и не совместимым с Oracle PL/SQL.

Если в команде RAISE EXCEPTION не задано ни имя условия, ни код SQLSTATE, по умолчанию выдаётся исключение ERRCODE_RAISE_EXCEPTION ( P0001 ). Если не задан текст сообщения, по умолчанию в качестве этого текста передаётся имя условия или код SQLSTATE.

Примечание

При задании SQLSTATE кода необязательно использовать только список предопределённых кодов ошибок. В качестве кода ошибки может быть любое пятисимвольное значение, состоящее из цифр и/или ASCII символов в верхнем регистре, кроме 00000 . Не рекомендуется использовать коды ошибок, которые заканчиваются на 000 , потому что так обозначаются коды категорий. И чтобы их перехватить, нужно перехватывать целую категорию.

43.9.2. Проверка утверждений

Оператор ASSERT представляет удобное средство вставлять отладочные проверки в функции PL/pgSQL .

Здесь условие — это логическое выражение, которое, как ожидается, должно быть всегда истинным; если это так, оператор ASSERT больше ничего не делает. Если же оно возвращает ложь или NULL, этот оператор выдаёт исключение ASSERT_FAILURE . (Если ошибка происходит при вычислении условия , она выдаётся как обычная ошибка.)

Если в нём задаётся необязательное сообщение , результат этого выражения (если он не NULL) заменяет сообщение об ошибке по умолчанию « assertion failed » (нарушение истинности), в случае, если условие не выполняется. В обычном случае, когда условие утверждения выполняется, выражение сообщения не вычисляется.

Проверку утверждений можно включить или отключить с помощью конфигурационного параметра plpgsql.check_asserts , принимающего логическое значение; по умолчанию она включена ( on ). Если этот параметр отключён ( off ), операторы ASSERT ничего не делают.

Учтите, что оператор ASSERT предназначен для выявления программных дефектов, а не для вывода обычных ошибок (для этого используется оператор RAISE , описанный выше).

Источник

Chapter 5 Migrating PL/SQL

This chapter explains how to migrate Oracle database PL/SQL. Note that in this document, PL/SQL refers to the language to be migrated to PostgreSQL PL/pgSQL.

5.1 Notes on Migrating from PL/SQL to PL/pgSQL

This section provides notes on migration from PL/SQL to PL/pgSQL.

5.1.1 Transaction Control

PL/pgSQL does not allow transaction control within a process. Terminate a procedure whenever a transaction is terminated in the Oracle database and execute a transaction control statement from the application.

5.2 Basic Elements

This section explains how to migrate the basic elements of PL/SQL.

5.2.1 Migrating Data Types

The table below lists the PostgreSQL data types that correspond to data types unique to PL/SQL.

Data type correspondence with PL/SQL

• Character

|Oracle database Data type|Remarks|Migratability|PostgreSQL Data type|Remarks|
|:—|:—|:—:|:—|:—|
| STRING | The number of bytes or number of characters can be specified. | MR | varchar | Only the number of characters can be specified. |

• Numeric

|Oracle database Data type|Remarks|Migratability|PostgreSQL Data type|Remarks|
|:—|:—|:—:|:—|:—|
| BINARY_INTEGER | | M | integer | |
| NATURAL | | M | integer | |
| NATURALN | Type with NOT NULL constraints | MR | integer | Set «not null» constraints for variable declarations. |
| PLS_INTEGER | | M | integer | |
| POSITIVE | | M | integer | |
| POSITIVEN | Type with NOT NULL constraints | MR | integer | Set «not null» constraints for variable declarations. |
| SIGNTYPE | | M | smallint | |
| SIMPLE_DOUBLE | Type with NOT NULL constraints | MR | double precision | Set «not null» constraints for variable declarations. |
| SIMPLE_FLOAT | Type with NOT NULL constraints | MR | real | Set «not null» constraints for variable declarations. |
| SIMPLE_INTEGER | Type with NOT NULL constraints | MR | integer | Set «not null» constraints for variable declarations. |

• Date and time

|Oracle database Data type|Remarks|Migratability|PostgreSQL Data type|Remarks|
|:—|:—|:—:|:—|:—|
| DSINTERVAL_UNCONSTRAINED | | N | | |
| TIME_TZ_UNCONSTRAINED | | N | | |
| TIME_UNCONSTRAINED | | N | | |
| TIMESTAMP_LTZ_UNCONSTRAINED | | N | | |
| TIMESTAMP_TZ_UNCONSTRAINED | | N | | |
| TIMESTAMP_UNCONSTRAINED | | N | | |
| YMINTERVAL_UNCONSTRAINED | | N | | |

• Other

|Oracle database Data type|Remarks|Migratability|PostgreSQL Data type|Remarks|
|:—|:—|:—:|:—|:—|
| BOOLEAN | | Y | boolean | |
| RECORD | | M | Complex type | |
| REF CURSOR (cursor variable) | | M | refcursor type | |
| Subtype with constraints | | N | | |
| Subtype that uses the base type within the same data type family | | N | | |
| Unconstrained subtype | | N | | |

Y: Data type can be migrated as is

M: Modified data type can be migrated

N: Cannot be migrated

MR: Modified data type can be migrated with restrictions

See

Refer to «Data Types» for information on migrating data types other than those unique to PL/SQL.

5.2.2 Error-Related Elements

This section explains elements related to PL/SQL errors.

5.2.2.1 Predefined Exceptions

Description

A predefined exception is an error defined beforehand in an Oracle database.

Functional differences

• Oracle database
  • Predefined exceptions can be used.
• PostgreSQL
  • Predefined exceptions cannot be used. Use PostgreSQL error codes instead.

Migration procedure

Use the following procedure to perform migration:

1. Identify where predefined exceptions are used.
2. Refer to the table below and replace the values of predefined exceptions with PostgreSQL error codes.

|Predefined exception
(Oracle database)|Migratability|Corresponding PostgreSQL error code|
|:—|:—:|:—|
| ACCESS_INTO_NULL | N | Not generated |
| CASE_NOT_FOUND | Y | case_not_found |
| COLLECTION_IS_NULL | N | Not generated |
| CURSOR_ALREADY_OPEN | Y | duplicate_cursor |
| DUP_VAL_ON_INDEX | Y | unique_violation |
| INVALID_CURSOR | Y | invalid_cursor_name |
| INVALID_NUMBER | Y | invalid_text_representation |
| LOGIN_DENIED | Y | invalid_authorization_specification
invalid_password |
| NO_DATA_FOUND | Y | no_data_found |
| NO_DATA_NEEDED | N | Not generated |
| NOT_LOGGED_ON | N | Not generated |
| PROGRAM_ERROR | Y | internal_error |
| ROWTYPE_MISMATCH | N | Not generated |
| SELF_IS_NULL | N | Not generated |
| STORAGE_ERROR | Y | out_of_memory |
| SUBSCRIPT_BEYOND_COUNT | N | Not generated |
| SUBSCRIPT_OUTSIDE_LIMIT | N | Not generated |
| SYS_INVALID_ROWID | N | Not generated |
| TIMEOUT_ON_RESOURCE | N | Not generated |
| TOO_MANY_ROWS | Y | too_many_rows |
| VALUE_ERROR | Y | null_value_not_allowed
invalid_text_representation
string_data_right_truncation
invalid_parameter_value |
| ZERO_DIVIDE | Y | division_by_zero |

Y: Can be migrated

N: Cannot be migrated

Migration example

The example below shows how to migrate the VALUE_ERROR exception. Note that OR is used in the migration example to group error codes so that VALUE_ERROR corresponds to multiple PostgreSQL error codes.

SET SERVEROUTPUT ON; 
 DECLARE 
  variety VARCHAR2(20) := 'television'; 
  company VARCHAR2(20) := 'Fullmoon Industry'; 
  name VARCHAR2(30); 
 BEGIN 


  name := ( variety || 'from' || company ); 
 EXCEPTION 
  WHEN VALUE_ERROR THEN 

  DBMS_OUTPUT.PUT_LINE ( 
   'ERR: Category length is out of range.' ); 
 END; 
 / 
 

DO $$ 
 DECLARE 
  variety VARCHAR(20) := 'television'; 
  company VARCHAR(20) := 'Fullmoon Industry'; 
 name VARCHAR(30); 
 BEGIN 
  PERFORM DBMS_OUTPUT.SERVEROUTPUT(TRUE); 
  name := ( variety || 'from' || company ); 
 EXCEPTION 
  WHEN null_value_not_allowed 
       OR invalid_text_representation 
       OR string_data_right_truncation 
       OR invalid_parameter_value THEN 
  PERFORM DBMS_OUTPUT.PUT_LINE ( 
   'ERR: Category length is out of range.' ); 
 END; 
 $$ 
 ; 
 

5.2.2.2 SQLCODE

Description

SQLCODE returns the error code of an error.

Functional differences

• Oracle database
  • SQLCODE can be specified to obtain an error code.
• PostgreSQL
  • SQLCODE cannot be specified to obtain an error code.

Migration procedure

Use the following procedure to perform migration:

1. Search for the keyword SQLCODE and identify where it is used.
2. Change the portion that calls SQLCODE to SQLSTATE.

Migration example

The example below shows migration when the code of an error is displayed.

SET SERVEROUTPUT ON; 
 DECLARE 
  v_i_number SMALLINT := 401; 
  v_i_name VARCHAR2(30) := 'Blu-ray and DVD recorder'; 
  v_i_quantity INTEGER := 10; 
  v_i_warehouse SMALLINT := 2; 
 BEGIN 


 INSERT INTO inventory_table 
  VALUES ( v_i_number, 
           v_i_name, 
           v_i_quantity, 
           v_i_warehouse ); 
 EXCEPTION 
  WHEN OTHERS THEN 
   DBMS_OUTPUT.PUT_LINE( 
    'ERR:' || SQLCODE || 
    ': Failure of INSERT.' ); 
 END; 
 / 
 

DO $$ 
 DECLARE 
  v_i_number SMALLINT := 401; 
  v_i_name VARCHAR(30) := 'Blu-ray and DVD recorder'; 
  v_i_quantity INTEGER := 10; 
  v_i_warehouse SMALLINT := 2; 
 BEGIN 
 PERFORM DBMS_OUTPUT.SERVEROUTPUT(TRUE); 
 INSERT INTO inventory_table 
  VALUES ( v_i_number, 
           v_i_name, 
           v_i_quantity, 
           v_i_warehouse ); 
 EXCEPTION 
  WHEN OTHERS THEN 
   PERFORM DBMS_OUTPUT.PUT_LINE( 
    'ERR:' || SQLSTATE || 
    ': Failure of INSERT.' ); 
 END; 
 $$ 
 ;

Note

Oracle databases and PostgreSQL have different error codes, so the set SQLCODE values and SQLSTATE values are different. Refer to «Appendix A. PostgreSQL Error Codes» in the PostgreSQL Documentation for information on the error codes to be defined in PostgreSQL.

5.2.2.3 EXCEPTION Declarations

Description

An EXCEPTION declaration defines an error.

Functional differences

• Oracle database
  • EXCEPTION declarations can be used to define errors.
• PostgreSQL
  • EXCEPTION declarations cannot be used.

Migration procedure

EXCEPTION declarations cannot be used, so specify the error number in a RAISE statement to achieve equivalent operation. Use the following procedure to perform migration:

1. Search for the keyword EXCEPTION, identify where an EXCEPTION declaration is used, and check the error name.
2. Search for the keyword RAISE and identify where the error created using the EXCEPTION declaration is used.
3. Delete the error name from the RAISE statement and instead specify the error code using ERRCODE in a USING clause.
4. Change the portion of the EXCEPTION clause where the error name is used to capture the error to SQLSTATE ‘errCodeSpecifiedInStep3’.
5. Delete the EXCEPTION declaration.

Migration example

The example below shows migration when a user-defined error is generated.

SET SERVEROUTPUT ON; 
 DECLARE 
  v_i_number SMALLINT := 200; 
  v_i_name VARCHAR2(20) := 'television'; 
  v_i_quantity INTEGER := 10; 
  v_i_warehouse SMALLINT := 3; 
  warehouse_num_err EXCEPTION; 
 BEGIN 


  IF ( v_i_warehouse = 1 ) OR ( v_i_warehouse = 2 ) THEN 
   INSERT INTO inventory_table 
    VALUES ( v_i_number, 
             v_i_name, 
             v_i_quantity, 
             v_i_warehouse ); 
  ELSE 
   RAISE warehouse_num_err; 
  END IF; 
 EXCEPTION 
  WHEN warehouse_num_err THEN 
   DBMS_OUTPUT.PUT_LINE( 
    'ERR: Warehouse number is out of range.' ); 


 END; 
 /

SET SERVEROUTPUT ON; 
 DECLARE 
  v_i_number SMALLINT := 200; 
  v_i_name VARCHAR2(20) := 'television'; 
  v_i_quantity INTEGER := 10; 
  v_i_warehouse SMALLINT := 3; 


 BEGIN 


  IF ( v_i_warehouse = 1 ) OR ( v_i_warehouse = 2 ) THEN 
   INSERT INTO inventory_table 
    VALUES ( v_i_number, 
             v_i_name, 
             v_i_quantity, 
             v_i_warehouse ); 
  ELSE 
   RAISE USING ERRCODE = '20001'; 
  END IF; 
 EXCEPTION 
  WHEN SQLSTATE '20001' THEN 
   DBMS_OUTPUT.PUT_LINE( 
    'ERR: Warehouse number is out of range.' ); 


 END; 
 /

5.2.2.4 PRAGMA EXCEPTION_INIT and RAISE_APPLICATION_ERROR

Description

An EXCEPTION_INIT pragma associates a user-defined error name with an Oracle database error code. RAISE_APPLICATION_ERROR uses a user-defined error code and error message to issue an error.

Functional differences

• Oracle database
  • EXCEPTION_INIT pragmas and RAISE_APPLICATION_ERROR statements can be used.
• PostgreSQL
  • EXCEPTION_INIT pragmas and RAISE_APPLICATION_ERROR statements cannot be used.

Migration procedure

EXCEPTION_INIT pragmas and RAISE_APPLICATION_ERROR statements cannot be used, so specify an error message and error code in a RAISE statement to achieve equivalent operation. Use the following procedure to perform migration:

1. Search for the keywords EXCEPTION and PRAGMA, and check for an EXCEPTION_INIT pragma and the specified error and error code.
2. Search for the keyword RAISE_APPLICATION_ERROR and check where an error is used.
3. Replace the error message and error code called by RAISE_APPLICATION_ERROR with syntax that uses a USING clause in RAISE.
4. Change the portion of the EXCEPTION clause where the user-defined error name is used to capture the error to SQLSTATE ‘errCodeSpecifiedInStep3’. To display the error message and error code in the EXCEPTION clause, use SQLERRM and SQLSTATE.
5. Delete the EXCEPTION declaration and EXCEPTION INIT pragma.

Migration example

The example below shows migration when an EXCEPTION INIT pragma and RAISE APPLICATION ERROR statement are used.

SET SERVEROUTPUT ON; 
 DECLARE 
  v_i_number SMALLINT := 200; 
  v_i_name VARCHAR2(30) := ' liquid crystal?television'; 
  v_i_quantity INTEGER := 10; 
  v_i_warehouse SMALLINT := 3; 
  invalid_length EXCEPTION; 
  PRAGMA EXCEPTION_INIT ( invalid_length, -20001 ); 
 BEGIN 


  IF ( LENGTH( v_i_name ) <= 20 ) THEN 
   INSERT INTO inventory_table 
   VALUES ( v_i_number, 
            v_i_name, 
            v_i_quantity, 
            v_i_warehouse ); 
  ELSE 
  RAISE_APPLICATION_ERROR( 
    -20001, 'ERR: i_name is invalid length.' ); 
  END IF; 
 EXCEPTION 
  WHEN invalid_length THEN 
   DBMS_OUTPUT.PUT_LINE( 
    TO_CHAR(SQLERRM(-20001)) ); 


 END; 
 /

DO $$ 
 DECLARE 
  v_i_number SMALLINT := 200; 
  v_i_name VARCHAR(30) := ' liquid crystal television'; 
  v_i_quantity INTEGER := 10; 
  v_i_warehouse SMALLINT := 3; 

 BEGIN 
  PERFORM DBMS_OUTPUT.SERVEROUTPUT( TRUE ); 
  IF ( LENGTH( v_i_name ) <= 20 ) THEN 
   INSERT INTO inventory_table 
    VALUES ( v_i_number, 
             v_i_name, 
             v_i_quantity, 
             v_i_warehouse ); 
  ELSE 
   RAISE 'ERR: i_name is invalid length.' 
    USING ERRCODE = '20001'; 
  END IF; 
 EXCEPTION 
  WHEN SQLSTATE '20001' THEN 
   PERFORM DBMS_OUTPUT.PUT_LINE( 
    SQLSTATE || ':' || SQLERRM ); 
 END; 
 $$ 
 ;

Note

SQLERRM provided by PostgreSQL cannot specify an error code in its argument.

5.2.2.5 WHENEVER

Description

WHENEVER SQLERROR predefines the processing to be run when an error occurs in an SQL statement or PL/SQL.
WHENEVER OSERROR predefines the processing to be run when an operating system error occurs.

Functional differences

• Oracle database
  • WHENEVER can be used to predefine the processing to be run when an error occurs.
• PostgreSQL
  • WHENEVER cannot be used.

Migration procedure

Use the following procedure to perform migration:

1. Search for the keyword WHENEVER and identify where it is used.
2. Replace WHENEVER SQLERROR EXIT FAILURE syntax or WHENEVER OSERROR EXIT FAILURE syntax with set ON_ERROR_STOP ON.

Migration example

The example below shows migration when an active script that encounters an error is stopped.

WHENEVER SQLERROR EXIT FAILURE 


 DECLARE 
  v_i_number SMALLINT := 401; 
  v_i_name VARCHAR2(30) := 'liquid crystal television'; 
  v_i_quantity INTEGER := 100; 
  v_i_warehouse SMALLINT := 2; 
 BEGIN 
  INSERT INTO inventory_table 
   VALUES ( v_i_number, 
            v_i_name, 
            v_i_quantity, 
            v_i_warehouse ); 
 END; 
 / 
 

set ON_ERROR_STOP ON 
 DO $$ 
 DECLARE 
  v_i_number SMALLINT := 401; 
  v_i_name VARCHAR(30) := 'liquid crystal television'; 
  v_i_quantity INTEGER := 100; 
  v_i_warehouse SMALLINT := 2; 
 BEGIN 
  INSERT INTO inventory_table 
   VALUES ( v_i_number, 
            v_i_name, 
            v_i_quantity, 
            v_i_warehouse ); 
 END; 
 $$ 
 ;

Note

• WHENEVER SQLERROR and WHENEVER OSERROR are SQL*Plus features. Migrate them to the psql feature in PostgreSQL.
• Of the values that can be specified in WHENEVER, only EXIT FAILURE and CONTINUE NONE can be migrated. If CONTINUE NONE is specified, replace it with set ON_ERROR_ROLLBACK ON.

5.2.3 Cursor-Related Elements

This section explains elements related to PL/SQL cursors.

5.2.3.1 %FOUND

Description

%FOUND obtains information on whether an SQL statement affected one or more rows.

Functional differences

• Oracle database
  • %FOUND can be used.
• PostgreSQL
  • %FOUND cannot be used. Use FOUND instead.

Migration procedure

Use the following procedure to perform migration with FOUND:

• When there is one implicit or explicit cursor
  1. Search for the keyword %FOUND and identify where it is used.
  2. Change the portion that calls cursorName%FOUND to FOUND.
• When there are multiple explicit cursors
  1. Search for the keyword %FOUND and identify where it is used.
  2. Using DECLARE, declare the same number of BOOLEAN variables as explicit cursors.
  3. Immediately after each FETCH statement, store the value obtained by FOUND in the variable declared in step 2.
  4. Replace the portion that calls cursorName%FOUND with the variable used in step 3.

Migration example

The example below shows migration when update of a row by an implicit cursor is checked.

SET SERVEROUTPUT ON; 
 BEGIN 


  UPDATE inventory_table SET i_warehouse = 3 
   WHERE i_name = 'television'; 
  IF SQL%FOUND THEN 
   DBMS_OUTPUT.PUT_LINE ( 'Updated!' ); 
  ELSE 
   DBMS_OUTPUT.PUT_LINE ( 'Not Updated!' ); 
  END IF; 
 END; 
 / 
 

DO $$ 
 BEGIN 
  PERFORM DBMS_OUTPUT.SERVEROUTPUT(TRUE); 
  UPDATE inventory_table SET i_warehouse = 3 
   WHERE i_name = 'television'; 
  IF FOUND THEN 
   PERFORM DBMS_OUTPUT.PUT_LINE( 'Updated!' ); 
  ELSE 
   PERFORM DBMS_OUTPUT.PUT_LINE( 'Not updated!' ); 
  END IF; 
 END; 
 $$ 
 ;

Note

Statements in which %FOUND is determined to be NULL cannot be migrated. If SQL has not been executed at all, FOUND is set to FALSE, which is the same return value as when no row has been affected.

5.2.3.2 %NOTFOUND

Description

%NOTFOUND obtains information on whether an SQL statement affected no rows.

Functional differences

• Oracle database
  • %NOTFOUND can be used.
• PostgreSQL
  • %NOTFOUND cannot be used. Use NOT FOUND instead.

Migration procedure

Use the following procedure to perform migration:

• When there is one implicit or explicit cursor
  1. Search for the keyword %NOTFOUND and identify where it is used.
  2. Change the portion that calls cursorName%NOTFOUND to NOT FOUND.
• When there are multiple explicit cursors
  1. Search for the keyword %NOTFOUND and identify where it is used.
  2. Using DECLARE, declare the same number of BOOLEAN variables as explicit cursors.
  3. Immediately after each FETCH statement, store the value obtained by FOUND in the variable declared in step 2.
  4. Replace the portion that calls cursorName%NOTFOUND with negation of the variable used in step 3.

Migration example

The example below shows migration when multiple explicit cursors are used to repeat FETCH until there are no more rows in one of the tables.

 SET SERVEROUTPUT ON; 
 DECLARE 
  CURSOR cur1 IS 
   SELECT i_number, i_name 
    FROM inventory_table 
    WHERE i_name = 'television'; 
  CURSOR cur2 IS 
   SELECT i_number, i_name 
    FROM inventory_table 
    WHERE i_name = 'cd player'; 
  v1_i_number inventory_table.i_number%TYPE; 
  v2_i_number inventory_table.i_number%TYPE; 
  v1_i_name inventory_table.i_name%TYPE; 
  v2_i_name inventory_table.i_name%TYPE; 

 BEGIN 


  OPEN cur1; 
  OPEN cur2; 
  LOOP 
   FETCH cur1 into v1_i_number, v1_i_name; 


   FETCH cur2 into v2_i_number, v2_i_name; 


   EXIT WHEN ( cur1%NOTFOUND ) OR ( cur2%NOTFOUND ); 
   DBMS_OUTPUT.PUT_LINE( 
    'No.' || v1_i_number || ': ' || v1_i_name ); 
   DBMS_OUTPUT.PUT_LINE( 
    'No.' || v2_i_number || ': ' || v2_i_name ); 
  END LOOP; 
  CLOSE cur1; 
  CLOSE cur2; 
 END; 
 / 
 

DO $$ 
 DECLARE 
  cur1 CURSOR FOR 
   SELECT i_number, i_name 
    FROM inventory_table 
    WHERE i_name = 'television'; 
  cur2 CURSOR FOR 
   SELECT i_number, i_name 
    FROM inventory_table 
    WHERE i_name = 'cd player'; 
  v1_i_number inventory_table.i_number%TYPE; 
  v2_i_number inventory_table.i_number%TYPE; 
  v1_i_name inventory_table.i_name%TYPE; 
  v2_i_name inventory_table.i_name%TYPE; 
  flg1 BOOLEAN; 
  flg2 BOOLEAN; 
 BEGIN 
  PERFORM DBMS_OUTPUT.SERVEROUTPUT( TRUE ); 
  OPEN cur1; 
  OPEN cur2; 
  LOOP 
   FETCH cur1 into v1_i_number, v1_i_name; 
   flg1 := FOUND; 
   FETCH cur2 into v2_i_number, v2_i_name; 
   flg2 := FOUND; 
   EXIT WHEN ( NOT flg1 ) OR ( NOT flg2 ); 
   PERFORM DBMS_OUTPUT.PUT_LINE( 
    'No.' || v1_i_number || ': ' || v1_i_name ); 
   PERFORM DBMS_OUTPUT.PUT_LINE( 
    'No.' || v2_i_number || ': ' || v2_i_name ); 
  END LOOP; 
  CLOSE cur1; 
  CLOSE cur2; 
 END; 
 $$ 
 ;

Note

Statements in which %NOTFOUND is determined to be NULL cannot be migrated. If SQL has not been executed at all, FOUND is set to FALSE, which is the same return value as when no row has been affected.

5.2.3.3 %ROWCOUNT

Description

%ROWCOUNT indicates the number of rows processed by an SQL statement.

Functional differences

• Oracle database
  • %ROWCOUNT can be used.
• PostgreSQL
  • %ROWCOUNT cannot be used. Use ROW_COUNT instead.

Migration procedure

Use the following procedure to perform migration:

1. Search for the keyword %ROWCOUNT and identify where it is used.
2. Declare the variable that will store the value obtained by ROW_COUNT.
3. Use GET DIAGNOSTICS immediately in front of %ROWCOUNT identified in step 1. It obtains ROW_COUNT and stores its value in the variable declared in step 2.
4. Replace the portion that calls %ROWCOUNT with the variable used in step 3.

Migration example

The example below shows migration when the number of deleted rows is obtained.

SET SERVEROUTPUT ON; 

 BEGIN 


  DELETE FROM inventory_table 
   WHERE i_name = 'television'; 


  DBMS_OUTPUT.PUT_LINE ( 
   TO_CHAR( SQL%ROWCOUNT ) || 'rows deleted!' ); 
 END; 
 / 
 

DO $$ 
 DECLARE 
  row_num INTEGER; 
 BEGIN 
  PERFORM DBMS_OUTPUT.SERVEROUTPUT( TRUE ); 
  DELETE FROM inventory_table 
   WHERE i_name = 'television'; 
  GET DIAGNOSTICS row_num := ROW_COUNT; 
  PERFORM DBMS_OUTPUT.PUT_LINE( 
   TO_CHAR( row_num ) || 'rows deleted!' ); 
 END; 
 $$ 
 ;

Note

Statements in which %ROWCOUNT is determined to be NULL cannot be migrated. If SQL has not been executed at all, 0 is set.

5.2.3.4 REF CURSOR

Description

REF CURSOR is a data type that represents the cursor in Oracle databases.

Functional differences

• Oracle database
  • REF CURSOR type variables can be defined.
• PostgreSQL
  • REF CURSOR type variables cannot be defined. Use refcursor type variables instead.

Migration procedure

Use the following procedure to perform migration:

1. Search for the keyword REF CURSOR and identify where it is used.
2. Delete the REF CURSOR type definition and the portion where the cursor variable is declared using that type.
3. Change the specification so that the cursor variable is declared using the refcursor type.

Migration example

The example below shows migration when the cursor variable is used to FETCH a row.

SET SERVEROUTPUT ON; 
 DECLARE 
  TYPE curtype IS REF CURSOR; 
  cur curtype; 
  v_inventory inventory_table%ROWTYPE; 
 BEGIN 


  OPEN cur FOR 
   SELECT * FROM inventory_table 
    WHERE i_warehouse = 2; 
  DBMS_OUTPUT.PUT_LINE( 'In warehouse no.2 is :' ); 


  LOOP 
   FETCH cur into v_inventory; 
   EXIT WHEN cur%NOTFOUND; 
   DBMS_OUTPUT.PUT_LINE( 
    'No.' || v_inventory.i_number || 
    ': ' || v_inventory.i_name || 
    '(' || v_inventory.i_quantity || ')' ); 
  END LOOP; 
  CLOSE cur; 
 END; 
 / 
 

DO $$ 
 DECLARE 
  cur refcursor; 


  v_inventory inventory_table%ROWTYPE; 
 BEGIN 
  PERFORM DBMS_OUTPUT.SERVEROUTPUT(TRUE); 
  OPEN cur FOR 
   SELECT * FROM inventory_table 
    WHERE i_warehouse = 2; 
  PERFORM DBMS_OUTPUT.PUT_LINE( 
   'In warehouse no.2 is :' ); 
  LOOP 
   FETCH cur into v_inventory; 
   EXIT WHEN NOT FOUND; 
   PERFORM DBMS_OUTPUT.PUT_LINE( 
    'No.' || v_inventory.i_number || 
    ': ' || v_inventory.i_name || 
    '(' || v_inventory.i_quantity || ')' ); 
  END LOOP; 
  CLOSE cur; 
 END; 
 $$ 
 ;

Note

The RETURN clause (specifies the return type of the cursor itself) cannot be specified in the refcursor type provided by PostgreSQL.

5.2.3.5 FORALL

Description

FORALL uses the changing value of the VALUES clause or WHERE clause to execute a single command multiple times.

Functional differences

• Oracle database
  • FORALL statements can be used.
• PostgreSQL
  • FORALL statements cannot be used.

Migration procedure

FORALL statements cannot be used, so replace them with FOR statements so that the same result is returned. Use the following procedure to perform migration:

1. Search for the keyword FORALL and identify where it is used.
2. Store the elements used by commands within FORALL in array type variables. In addition, delete Oracle database array definitions.
3. Replace FORALL statements with FOR — LOOP statements.
4. Replace portions that reference an array in the Oracle database with referencing of the array type variable defined in step 2. The portions changed in the migration example and details of the changes are as follows:
   • Start of the loop: Change i_numL.FIRST to 1.
   • End of the loop: Replace i_numL.LAST with ARRAY_LENGTH.
   • Referencing of array elements: Change i_numL(i) to i_numL[i].

Migration example

The example below shows migration when FORALL is used to execute INSERT.

 
 DECLARE 
   TYPE NumList IS TABLE OF SMALLINT; 
   i_numL NumList := NumList( 151, 
                              152, 
                              153, 
                              154, 
                              155 ); 
 BEGIN 
   FORALL i IN i_numL.FIRST .. i_numL.LAST 
     INSERT INTO inventory_table 
     VALUES ( i_numL(i), 'television', 10, 2 ); 


 END; 
 / 
 

DO $$ 
 DECLARE 

i_numL SMALLINT ARRAY := '{ 151, 
                               152, 
                               153, 
                               154, 
                               155 }'; 
 BEGIN 
  FOR i IN 1..ARRAY_LENGTH( i_numL, 1 ) LOOP 
     INSERT INTO inventory_table 
     VALUES ( i_numL[i], 'television', 10, 2 ); 
   END LOOP; 
 END; 
 $$ 
 ;

5.3 Migrating Functions

This section explains how to migrate PL/SQL functions.

Description

A stored function is a user-defined function that returns a value.

5.3.1 Defining Functions

Functional differences

• Oracle database
  • A RETURN clause within a function prototype is specified as RETURN.
    DECLARE does not need to be specified as the definition portion of a variable used within a function.
• PostgreSQL
  • Use RETURNS to specify a RETURN clause within a function prototype.
    DECLARE must be specified as the definition portion of a variable to be used within a function.

Migration procedure

Use the following procedure to perform migration:

1. Search for the keywords CREATE and FUNCTION, and identify where user-defined functions are created.
2. If an IN or OUT qualifier is specified in an argument, move it to the beginning of the parameters.
3. Change RETURN within the function prototype to RETURNS.
4. Change the AS clause to AS $$. (If the keyword is IS, change it to AS.)
5. If a variable is defined, add the DECLARE keyword after $$.
6. Delete the final slash (/) and specify $$ and a LANGUAGE clause.

Migration example

The example below shows migration when CREATE FUNCTION is used to define a function.

CREATE FUNCTION PROFIT_FUNC( 
  selling IN INTEGER, 
  sales_num IN INTEGER, 
  cost IN INTEGER 
 ) RETURN INTEGER AS 


     profit INTEGER; 
   BEGIN 
     profit := ( ( selling * sales_num ) - cost ); 
     RETURN profit; 
   END; 
 /

CREATE FUNCTION PROFIT_FUNC( 
  IN selling INTEGER, 
  IN sales_num INTEGER, 
  IN cost INTEGER 
 ) RETURNS INTEGER AS $$ 
 DECLARE 
     profit INTEGER; 
 BEGIN 
     profit := ( ( selling * sales_num ) - cost ); 
     RETURN profit; 
 END; 
 $$ LANGUAGE plpgsql;

5.4 Migrating Procedures

This section explains how to migrate PL/SQL procedures.

Description

A stored procedure is a single procedure into which multiple processes have been grouped.

5.4.1 Defining Procedures

Functional differences

• Oracle database
  • Procedures can be created.
• PostgreSQL
  • Procedures cannot be created.

Migration procedure

Procedures cannot be created in PostgreSQL. Therefore, replace them with functions. Use the following procedure to perform migration:

1. Search for the keywords CREATE and PROCEDURE, and identify where a procedure is defined.
2. Replace the CREATE PROCEDURE statement with the CREATE FUNCTION statement.
3. Change the AS clause to RETURNS VOID AS $$. (If the keyword is IS, change it to AS.)
4. If a variable is defined, add the DECLARE keyword after $$.
5. Delete the final slash (/) and specify $$ and a LANGUAGE clause.

Note

If the OUT or INOUT keywords are specified in the arguments, a different migration method must be used. Refer to «Defining Procedures That Return a Value».

Migration example

The example below shows migration when a procedure is defined.

CREATE PROCEDURE UPD_QUANTITY ( 
  upd_number SMALLINT, 
  upd_quantity INTEGER 
 ) AS 
   BEGIN 
     UPDATE inventory_table 
 SET i_quantity = upd_quantity 
       WHERE i_number = upd_number; 
   END; 
 / 
 ------------------------------------------------- 


 DECLARE 
   v_i_number SMALLINT := 110; 
   v_i_quantity INTEGER := 100; 
 BEGIN 
   upd_quantity( v_i_number, v_i_quantity ); 
 END; 
 / 
 

CREATE FUNCTION UPD_QUANTITY ( 
  upd_number SMALLINT, 
  upd_quantity INTEGER 
 ) RETURNS VOID AS $$ 
 BEGIN 
     UPDATE inventory_table 
 SET i_quantity = upd_quantity 
       WHERE i_number = upd_number; 
 END; 
 $$ LANGUAGE plpgsql; 
 ------------------------------------------------- 
 DO $$ 
 DECLARE 
   v_i_number SMALLINT := 110; 
   v_i_quantity INTEGER := 100; 
 BEGIN 
   PERFORM upd_quantity( v_i_number, v_i_quantity ); 
 END; 
 $$ 
 ;

5.4.2 Calling Procedures

Functional differences

• Oracle database
  • A procedure can be called as a statement.
• PostgreSQL
  • Procedures cannot be used. Instead, call the procedure as a function that does not return a value.

Migration procedure

Use the following procedure to perform migration:

1. Identify where each procedure is called.
2. Specify PERFORM in front of the procedure call.

Migration example

The example below shows migration when a procedure is called.

SET SERVEROUTPUT ON; 
 BEGIN 


   DBMS_OUTPUT.PUT_LINE( 'Hello World.' ); 
 END; 
 / 
 

DO $$ 
 BEGIN 
   PERFORM DBMS_OUTPUT.SERVEROUTPUT( TRUE ); 
   PERFORM DBMS_OUTPUT.PUT_LINE( 'Hello World.' ); 
 END; 
 $$ 
 ;

5.4.3 Defining Procedures That Return a Value

Functional differences

• Oracle database
  • Procedures that return a value can be created.
• PostgreSQL
  • Procedures that return a value cannot be created.

Migration procedure

Use the following procedure to perform migration:

1. Search for the CREATE and PROCEDURE keywords, and identify where a procedure is defined.
2. Confirm that the OUT or INOUT keyword is specified in the arguments.
3. Replace the CREATE PROCEDURE statement with the CREATE FUNCTION statement.
4. If the IN, OUT, or INOUT keyword is specified in the arguments, move it to the beginning of the arguments.
5. Change the AS clause to AS $$. (If the keyword is IS, change it to AS.)
6. If a variable is defined, add the DECLARE keyword after $$.
7. Delete the final slash (/) and specify $$ and a LANGUAGE clause.
8. If calling a function, call it without specifying arguments in the OUT parameter and store the return value in the variable. If there are multiple OUT parameters, use a SELECT INTO statement.

Migration example

The example below shows migration when the OUT parameter of CREATE PROCEDURE is used to define a procedure that returns a value.

 CREATE PROCEDURE remove_row ( 
  del_name VARCHAR2, 
  del_row OUT INTEGER 
 ) AS 
  BEGIN 
   DELETE FROM inventory_table 
    WHERE i_name = del_name; 
   del_row := SQL%ROWCOUNT; 
  END; 
 / 
 ------------------------------------------------- 
 SET SERVEROUTPUT ON; 
 DECLARE 
  rtn_row INTEGER; 
  v_i_name VARCHAR2(20) := 'television'; 
 BEGIN 

remove_row( v_i_name, rtn_row ); 
  DBMS_OUTPUT.PUT_LINE( 
   TO_CHAR( rtn_row ) || 'rows deleted!' ); 
 END; 
 / 
 

CREATE FUNCTION remove_row ( 
  del_name VARCHAR, 
  OUT del_row INTEGER 
 ) AS $$ 
  BEGIN 
   DELETE FROM inventory_table 
    WHERE i_name = del_name; 
   GET DIAGNOSTICS del_row := ROW_COUNT; 
  END; 
 $$ LANGUAGE plpgsql; 
 ------------------------------------------------- 
 DO $$ 
 DECLARE 
  rtn_row INTEGER; 
  v_i_name VARCHAR(20) := 'television'; 
 BEGIN 
  PERFORM DBMS_OUTPUT.SERVEROUTPUT( TRUE ); 
  rtn_row := remove_row( v_i_name ); 
  PERFORM DBMS_OUTPUT.PUT_LINE( 
   TO_CHAR( rtn_row ) || 'rows deleted!' ); 
 END; 
 $$ 
 ;

See

Refer to «Defining Nested Procedures» for examples of migrating a call portion that uses a SELECT INTO statement.

5.4.4 Defining Nested Procedures

Functional differences

• Oracle database
  • Nested procedures can be defined.
• PostgreSQL
  • Nested procedures cannot be defined.

Migration procedure

Procedures must be replaced with functions, but functions cannot be nested in PostgreSQL. Therefore, define and call the functions separately. Use the following procedure to perform migration:

1. Search for the CREATE and PROCEDURE keywords, and identify where a procedure is defined.
2. If a PROCEDURE statement is defined in a DECLARE clause, regard it as a nested procedure.
3. Check for variables that are used by both the procedure and the nested procedure.
4. Replace a nested procedure (from PROCEDURE procedureName to END procedureName;) with a CREATE FUNCTION statement. Specify the variables you found in step 3 in the INOUT argument of CREATE FUNCTION.
5. Replace the portion that calls the nested procedure with a SELECT INTO statement. Specify the common variables you found in step 3 in both the variables used for calling the function and the variables used for accepting the INTO clause.

Migration example

The example below shows migration when nested procedures are used and a variable is shared by a procedure and its call portion.

SET SERVEROUTPUT ON; 
 DECLARE 
  sales_num INTEGER; 
  stock_num INTEGER; 
  v_i_quantity INTEGER; 
  PROCEDURE quantity_check ( 
   sales INTEGER, 
   stock INTEGER 
  ) IS 
   quantity_err EXCEPTION; 
  BEGIN 


   v_i_quantity := ( stock - sales ); 
   IF ( v_i_quantity < 0 ) THEN 
    RAISE quantity_err; 
   END IF; 
  EXCEPTION 
   WHEN quantity_err THEN 
    DBMS_OUTPUT.PUT_LINE( 
     'ERR: i_quantity is negative value.' ); 
  END quantity_check; 

 BEGIN 


  sales_num := 80; 
  stock_num := 100; 
  quantity_check( sales_num, stock_num ); 


  DBMS_OUTPUT.PUT_LINE( 
   'i_quantity: ' || v_i_quantity ); 


  sales_num := 100; 
  stock_num := 80; 
  quantity_check( sales_num, stock_num ); 


  DBMS_OUTPUT.PUT_LINE( 
   'i_quantity: ' || v_i_quantity ); 
 END; 
 / 

 
 CREATE FUNCTION quantity_check( 
  sales INTEGER, 
  stock INTEGER, 
  INOUT quantity INTEGER 
 ) AS $$ 


  BEGIN 
   PERFORM DBMS_OUTPUT.SERVEROUTPUT( TRUE ); 
   quantity := ( stock - sales ); 
   IF ( quantity < 0 ) THEN 
    RAISE USING ERRCODE = '20001'; 
   END IF; 
  EXCEPTION 
   WHEN SQLSTATE '20001' THEN 
    PERFORM DBMS_OUTPUT.PUT_LINE( 
     'ERR: i_quantity is negative value.' ); 
  END; 
 $$ LANGUAGE plpgsql; 
 ------------------------------------------------- 
 DO $$ 
 DECLARE 
  sales_num INTEGER; 
  stock_num INTEGER; 
  v_i_quantity INTEGER; 
 BEGIN 
  PERFORM DBMS_OUTPUT.SERVEROUTPUT( TRUE ); 
  sales_num := 80; 
  stock_num := 100; 
  SELECT quantity INTO v_i_quantity 
   FROM quantity_check( sales_num, 
                        stock_num, 
                        v_i_quantity ); 
  PERFORM DBMS_OUTPUT.PUT_LINE( 
   'i_quantity: ' || v_i_quantity ); 


  sales_num := 100; 
  stock_num := 80; 
  SELECT quantity INTO v_i_quantity 
   FROM quantity_check( sales_num, 
                        stock_num, 
                        v_i_quantity ); 
  PERFORM DBMS_OUTPUT.PUT_LINE( 
  'i_quantity: ' || v_i_quantity ); 
 END; 
 $$ 
 ;

5.4.5 Defining Anonymous Code Blocks

Description

An anonymous code block generates and executes a temporary function within a procedural language.

Functional differences

• Oracle database
  • Anonymous code blocks that are enclosed with (DECLARE) BEGIN to END can be executed.
• PostgreSQL
  • PL/pgSQL blocks ((DECLARE) BEGIN to END) that are enclosed with DO $$ to $$ can be executed.

Migration procedure

Use the following procedure to perform migration:

1. Search for the keywords DECLARE and BEGIN, and identify where an anonymous code block is defined.
2. Specify DO $$ at the beginning of the anonymous code block.
3. Delete the final slash (/) and specify $$.

Migration example

The example below shows migration when an anonymous code block is defined.

SET SERVEROUTPUT ON; 
 BEGIN 


     DBMS_OUTPUT.PUT_LINE( 'Hello World.' ); 
 END; 
 / 
 

DO $$ 
 BEGIN 
     PERFORM DBMS_OUTPUT.SERVEROUTPUT( TRUE ); 
     PERFORM DBMS_OUTPUT.PUT_LINE( 'Hello World.' ); 
 END; 
 $$ 
 ;

5.5 Migrating Packages

This section explains how to migrate PL/SQL packages.

Description

A package defines and contains procedures and functions as a single relationship group in the database.

Functional differences

• Oracle database
  • Packages can be created.
• PostgreSQL
  • Packages cannot be created.

Packages cannot be created in PostgreSQL, so define a schema with the same name as the package and define functions that have a relationship in the schema so that they are treated as a single group.
In the following sections, the migration procedure is explained for each feature to be defined in a package.

5.5.1 Defining Functions Within a Package

Functional differences

• Oracle database
  • Functions can be created within a package.
• PostgreSQL
  • The package itself cannot be created.

Migration procedure

Use the following procedure to perform migration:

1. Search for the keywords CREATE and PACKAGE, and identify where they are defined.
2. Define a schema with the same name as the package.
3. If a FUNCTION statement is specified within a CREATE PACKAGE BODY statement, define, within the schema created in step 2, the functions that were defined within the package.

Migration example

The example below shows migration when a package is defined and functions are created within that package.

CREATE PACKAGE smpl_pkg AS 
   FUNCTION remove_row( rm_i_name VARCHAR2 ) 
     RETURN INTEGER; 
 END smpl_pkg; 
 / 
 CREATE PACKAGE BODY smpl_pkg AS 
   FUNCTION remove_row( rm_i_name VARCHAR2 ) 
     RETURN INTEGER IS 


       rtn_row INTEGER; 
     BEGIN 
       DELETE FROM inventory_table 
 WHERE i_name = rm_i_name; 


       RETURN(SQL%ROWCOUNT); 
     END; 
 END smpl_pkg; 
 /

CREATE SCHEMA smpl_scm; 
CREATE FUNCTION smpl_scm.remove_row( 
  rm_i_name VARCHAR 
 ) RETURNS INTEGER AS $$ 
 DECLARE 
   rtn_row INTEGER; 
 BEGIN 
   DELETE FROM inventory_table 
     WHERE i_name = rm_i_name; 
   GET DIAGNOSTICS rtn_row := ROW_COUNT; 
   RETURN rtn_row; 
 END; 
 $$ LANGUAGE plpgsql;

See

Refer to «Defining Functions» for information on migrating FUNCTION statements within a package.

5.5.2 Defining Procedures Within a Package

Functional differences

• Oracle database
  • Procedures can be created within a package.
• PostgreSQL
  • The package itself cannot be created.

Migration procedure

Use the following procedure to perform migration:

1. Search for the keywords CREATE and PACKAGE, and identify where they are defined.
2. Define a schema with the same name as the package.
3. If a PROCEDURE statement is specified within a CREATE PACKAGE BODY statement, migrate the procedures that were defined within the package to functions and define them within the schema created in step 2.

Migration example

The example below shows migration when a package is defined and procedures are created within that package.

CREATE PACKAGE smpl_pkg AS 
  PROCEDURE increase_row( 
   add_i_num SMALLINT, 
   add_i_name VARCHAR2, 
   add_i_quantity INTEGER, 
   add_i_warehouse SMALLINT 
  ); 
  END smpl_pkg; 
 / 
 CREATE PACKAGE BODY smpl_pkg AS 
  PROCEDURE increase_row( 
   add_i_num SMALLINT, 
   add_i_name VARCHAR2, 
   add_i_quantity INTEGER, 
   add_i_warehouse SMALLINT 
  ) IS 
   BEGIN 
    INSERT INTO inventory_table 
     VALUES ( add_i_num, 
              add_i_name, 
              add_i_quantity, 
              add_i_warehouse ); 
     END; 
 END smpl_pkg; 
 /

CREATE SCHEMA smpl_scm; 
CREATE FUNCTION smpl_scm.increase_row( 
  add_i_num SMALLINT, 
  add_i_name VARCHAR, 
  add_i_quantity INTEGER, 
  add_i_warehouse SMALLINT 
 ) RETURNS VOID AS $$ 
 BEGIN 
  INSERT INTO inventory_table 
   VALUES ( add_i_num, 
            add_i_name, 
            add_i_quantity, 
            add_i_warehouse ); 
 END; 
 $$ LANGUAGE plpgsql;

See

Refer to «Defining Procedures» for information on migrating PROCEDURE statements within a package.

5.5.3 Sharing Variables Within a Package

Functional differences

• Oracle database
  • Variables can be shared within a package.
• PostgreSQL
  • A package cannot be created, so variables cannot be shared.

Migration procedure

Use a temporary table instead of variables within a package. Use the following procedure to perform migration:

1. Search for the keywords CREATE and PACKAGE, and identify where they are defined.
2. Check for variables defined directly in a package.
3. Create a temporary table that defines the variables checked in step 2 in a column.
4. Insert one record to the temporary table created in step 3. (The set value is the initial value specified within the package.)
5. Replace the respective portions that reference a variable and set a variable with SQL statements.
   • To reference a variable, use a SELECT INTO statement to store a value in the variable and then reference it. (A variable for referencing a variable must be defined separately.)
   • To update a variable, use an UPDATE statement and update the target column.

Migration example

The example below shows migration when a package is defined and variables within the package are shared.

SET SERVEROUTPUT ON; 
 CREATE PACKAGE row_pkg AS 
   PROCEDURE set_item( item INTEGER ); 
   i_item INTEGER; 
 END row_pkg; 
 / 
 CREATE PACKAGE BODY row_pkg AS 


 PROCEDURE set_item( 
 item INTEGER 
 ) IS 
     BEGIN 
       i_item := item; 
     END; 
 END row_pkg; 
 / 
 ------------------------------------------------- 
 SET SERVEROUTPUT ON; 

 BEGIN 


   row_pkg.set_item( 1000 ); 

   DBMS_OUTPUT.PUT_LINE( 
          'ITEM :' || row_pkg.i_item ); 
   row_pkg.set_item(2000); 

   DBMS_OUTPUT.PUT_LINE( 
          'ITEM :' || row_pkg.i_item ); 
 END; 
 / 
 

CREATE SCHEMA row_pkg; 

 CREATE FUNCTION row_pkg.set_item( 
 item INTEGER 
 ) RETURNS VOID AS $$ 
   BEGIN 
     UPDATE row_pkg_variables SET i_item = item; 
   END; 
 $$ LANGUAGE plpgsql; 


 ------------------------------------------------- 
 CREATE TEMP TABLE row_pkg_variables ( i_item INTEGER ); 
 INSERT INTO row_pkg_variables VALUES (0); 


 DO $$ 
 DECLARE 
   g_item INTEGER; 
 BEGIN 
   PERFORM DBMS_OUTPUT.SERVEROUTPUT( TRUE ); 
   PERFORM row_pkg.set_item( 1000 ); 
   SELECT i_item INTO g_item 
     FROM row_pkg_variables; 
   PERFORM DBMS_OUTPUT.PUT_LINE( 
                  'ITEM :' || g_item ); 
   PERFORM row_pkg.set_item(2000); 
   SELECT i_item INTO g_item 
     FROM row_pkg_variables; 
   PERFORM DBMS_OUTPUT.PUT_LINE( 
                  'ITEM :' || g_item ); 
 END; 
 $$ 
 ;