OCI_Execute() does not raise exceptions when executing PL/SQL statement raising no data was found PL/SQL exception

[Andreas741]

Hello,

I think I found a bug in ocilib 4.7.4. The problem also occurred in ocilib 4.5.1. A no data found exception in a pl/sql block is not thrown.

Kind regards

OcilibTest2.sql

create table TestTable2 (nr number, kg_id number);

CREATE OR REPLACE PACKAGE TestPackage2 IS
    type TestRecType is record
    (
        nr number,
        kg_id number
    );
    FUNCTION GetCurrentItem( nr_ number, item_ OUT TestPackage2.TestRecType) return number;
END;
/

CREATE OR REPLACE PACKAGE BODY TestPackage2 IS
    function GetCurrentItem( nr_ number, item_ OUT TestPackage2.TestRecType) return number
    is
    begin
        select nr, kg_id into item_.nr, item_.kg_id from TestTable where nr = 555;
        return item_.nr;
    end;
END;
/

// OcilibTest2.cpp

/*
No exception was raised if no data was found in PL/SQL block.
*/

#include "ocilib.hpp"
#include <iostream>
#include <cstdint>

int main(int argc, char** argv)
{
    if (argc < 4)
        return EXIT_FAILURE;

    const otext* usr = argv[1];
    const otext* pwd = argv[2];
    const otext* db = argv[3];

    try
    {
        ocilib::Environment::Initialize();
        ocilib::Connection connection(db, usr, pwd);
        ocilib::Statement statement(connection);

        int64_t verlauf_nr = 9453;
        int64_t kg_id = 0;  // 1743482
        int64_t ret = 0;
        const ocilib::ostring cmd =
            R"(
declare
    iItem TestPackage2.TestRecType;
begin
    :ret := TestPackage2.GetCurrentItem(:verlauf_nr, iItem);
    :kg_id := iItem.KG_ID;
end;
)";

        statement.Prepare(cmd);
        statement.Bind("verlauf_nr", verlauf_nr, ocilib::BindInfo::In);
        statement.Bind("kg_id", kg_id, ocilib::BindInfo::Out);
        statement.Bind("ret", ret, ocilib::BindInfo::Out);
        statement.ExecutePrepared();
        int x = 0;
    }
    catch (ocilib::Exception e)
    {
        std::cout << e.what() << std::endl;
        return EXIT_FAILURE;
    }

    return EXIT_SUCCESS;
}

My temporary fix in file statement.c line 1414:

// akh 09.02.2021
// No exception was raised if no data was found in PL/SQL block.
boolean success = ((OCI_SUCCESS   == ret) || (OCI_SUCCESS_WITH_INFO == ret) ||
                   (OCI_NEED_DATA == ret) /*|| (OCI_NO_DATA == ret)*/);

[vrogier]

Hi,

Shall the host API user (OCILIB) consider a PL/SQL statement execution returning OCI_NO_DATA as an error case? That's a good question. Other PL/SQL exceptions are mapped to OCI_ERROR while 'no data found' is mapped to OCI_NO_DATA. In Oracle OCI, OCI_NO_DATA is not an error in-fine. For example, executing a select from a table that returns zero rows also returns OCI_NO_DATA. Like also when fetching the last row. In these cases, OCI_NO_DATA is not an error. Thus making a special case for some PL/SQL executions is not hat trivial (how to identify them) and could lead to some regression. It is not as straightforward as it could seem. Let me think about it.

Regards,

Vincent

[Andreas741]

Hello, many thanks for the quick response

I think a no_data_found exception must be thrown because otherwise an error occurs without the user noticing. If a no_data_found exception should be ignored, you can build an exception handler in the PL/SQL block or use a cursor. The normal behavior in PL/SQL is that an exception is thrown if no data was found when selecting into, I think the behavior in ocilib should be identical. This would also make it much easier to migrate from other Oracle interfaces to ocilib.

I know it's not easy to do the right thing, especially in this case select function(abc) from table ... It is possible that no records are found which is not an error or a no_data_found exception may occur in the function() function which is an error.

Greetings from Germany

[vrogier]

Hi,

I committed a change in v4.7.5 branch that handles no_data_found from PL/SQL engine as an error while treating no_data_found from SQL engine as a success.

Regards,

Vincent

[Andreas741]

Thank you!