Object Type of %ROWTYPE doesn't work as Bind Parameter in Anonymous PLSQL Block

[mkmoisen]

1. What versions are you using?

I've reproduced the error on both Oracle 12.2 (Windows Server) and 19.0 (Unix Server)

Windows Client:

platform.platform: Windows-10-10.0.19042-SP0 sys.maxsize > 2**32: True platform.python_version: 3.9.5

cx_Oracle.version: 8.3.0 cx_Oracle.clientversion: (12, 2, 0, 1, 0)

19C Unix Client

platform.platform: Linux-4.18.0-305.34.2.el8_4.x86_64-x86_64-with-glibc2.17 sys.maxsize > 2**32: True platform.python_version: 3.9.5

cx_Oracle.version: 8.1.0 cx_Oracle.clientversion: (19, 11, 0, 0, 0)

3. Is it an error or a hang or a crash?

Error

4. What error(s) or behavior you are seeing?

cx_Oracle.DatabaseError: ORA-21525: attribute number or (collection element at index) %s violated its constraints

5. Include a runnable Python script that shows the problem.

cur = conn.cursor()

cur.execute('drop table foo')
cur.execute('create table foo (id int, name varchar2(10))')
cur.execute('insert into foo values (1, :name)', {'name': 'foo'})

row_type = conn.gettype('FOO%ROWTYPE')

row_obj = row_type.newobject()

cur.execute('''
    BEGIN
        SELECT id, name INTO :row_obj
        FROM foo
        WHERE id = 1;
    END;
''', {
    'row_obj': row_obj,
})

Using regular variables in an anonymous PL/SQL block works as expected:

id_var = cur.var(cx_Oracle.NUMBER)
name_var = cur.var(cx_Oracle.STRING)

cur.execute('''
    BEGIN
        SELECT id, name INTO :id_var, :name_var
        FROM foo
        WHERE id = 1;
    END;
''', {
    'id_var': id_var, 
    'name_var': name_var
})

assert id_var.getvalue() == 1
assert name_var.getvalue() == 'foo'

In addition, creating a function and calling it using the %ROWTYPE object works as expected:

create or replace function get_foo
return foo%rowtype
is
  l_row foo%rowtype;
begin
  select id, name into l_row
  from foo
  where id = 1;

  return l_row;
end;
/

row_type = conn.gettype('FOO%ROWTYPE')

row_obj = cur.callfunc('GET_FOO', row_type)

assert row_obj.ID == 1
assert row_obj.NAME == 'foo'

So, it looks like this error with %ROWTYPE only occurs when the object is used as a bind variable, such as an anonymous block.

I'm not able to use the workaround of creating a function/procedure, because I'm writing a framework and don't want to ask users to create a function/package in the database. I'm also not eager to use scalar variables.

Does anyone know if there is a way or workaround to get the %ROWTYPE working in anonymous plsql blocks as bind variables?

[anthony-tuininga]

This code works:

import cx_Oracle as oracledb

SQL = """
    declare
        t_Row       foo%rowtype;
    begin
        select id, name
        into t_Row
        from Foo
        where id = 1;

        :out_val := t_Row;
    end;"""

conn = oracledb.connect("user/password@host/service_name")

row_type = conn.gettype("FOO%ROWTYPE")
cursor = conn.cursor()
var = cursor.var(row_type)

cursor.execute(SQL, out_val=var)
row_obj = var.getvalue()
print("Row obj:", row_obj.ID, row_obj.NAME)

The key is that PL/SQL doesn't know anything about the bind variable when it is parsing and validating the PL/SQL that is about to execute -- so you have to use a local variable and copy the local variable to the output variable afterwards!

[mkmoisen]

Hi @anthony-tuininga, thank you, this works for me as well.

Do you know why the use of primitive data types works without having to copy a local variable into an output variable?

id_var = cur.var(cx_Oracle.NUMBER)
name_var = cur.var(cx_Oracle.STRING)

cur.execute('''
    BEGIN
        SELECT id, name INTO :id_var, :name_var
        FROM foo
        WHERE id = 1;
    END;
''', {
    'id_var': id_var, 
    'name_var': name_var
})

assert id_var.getvalue() == 1
assert name_var.getvalue() == 'foo'

[anthony-tuininga]

I suspect because they are scalar values. Collections/records must match exactly -- and that can't be known without examining the bind variable. @cjbj may be able to answer more definiteily -- or know who to ask to get a more definitive answer. :-)

[stale[bot]]

This issue has been automatically marked as inactive because it has not been updated recently. It will be closed if no further activity occurs. Thank you for your contributions.

[mkmoisen]

Hi @cjbj, would you happen to know the reason behind this?

[stale[bot]]

This issue has been automatically marked as inactive because it has not been updated recently. It will be closed if no further activity occurs. Thank you for your contributions.

[stale[bot]]

This issue has been automatically closed because it has not been updated for a month.