ORA-21602: operation does not support the specified typecode

[tgulacsi]

Hi,

I'd like to use dpiObject_setAttribute to build an object wich attributes are themselves objects, too.

Sometimes (for some types) this works, but sometimes it fails with OCI-21602.
For example

  TYPE font_rt IS RECORD (
    Bold         BOOLEAN,
    Italic       BOOLEAN,
    Underline    vc30,
    Family       vc30,
    Size_         BINARY_DOUBLE,
    Strike       BOOLEAN,
    Color        vc30,
    ColorIndexed PLS_INTEGER,
    ColorTheme   PLS_INTEGER,
    ColorTint    BINARY_DOUBLE,
    VertAlign    vc30
  );
TYPE rich_text_rt IS RECORD (font font_rt, text VARCHAR2(1000));

BrunoOwner_DbXlsx_RichTextRt={
"font": {"bold":true,"italic":true,"underline":"xxxxxxxxxxxxxx","family":"xxxxxxxxxxxxxx","size":3.1464,"strike":true,"color":"x","colorindexed":-999999932,"colortheme":-999999932,"colortint":3.1464,"vertalign":"x"},
"text":"xxxxxx"
}.ToObject: 

SetAttribute(attr=FONT

, data={ObjectType:BRUNO_OWNER.FONT_RT 
  dpiData:{isNull:0 _:[0 0 0 0] value:[96 94 7 44 212 127 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 00 0]} implicitObj:false NativeTypeNum:3009}): dpiObject_setAttributeValue NativeTypeNum=3009 ObjectType=BRUNO_OWNER.FONT_RT typeInfo={oracleTypeNum:2023 defaultNativeTypeNum:3009 ociTypeCode:250 dbSizeInBytes:0 clientSizeInBytes:0 sizeInChars:0 precision:0 scale:0 fsPrecision:0 objectType:0x7fd42c06e7a0 isJson:0 domainSchema:<nil> domainSchemaLength:0 domainName:<nil> domainNameLength:0 numAnnotations:0 annotations:<nil> isOson:0 vectorDimensions:0 vectorFormat:0 vectorFlags:0 _:[0 0 0 0 0 0]}: 
  ORA-21602: OCI-21602:  operation does not support the specified typecode

dpiObjectAttr_getInfo returns ociTypeCode=250 = SQLT_REC = OCI_TYPECODE_RECORD for this FONT attribute.

Any help would be appreciated, thanks in advance!

[cjbj]

Hi @tgulacsi, what DB & client lib version are you using? When you say sometimes, do you mean 'always fails for some types'?

[tgulacsi]

Client: 19.23.0.0.0
Server: 19.23.0.0.0

[tgulacsi]

Somtimes = some types. For this type, this fails always, reproducibly.

[tgulacsi]

It is the same (errors) with

• Client: 23.6.0.24.10.234 ({ServerRelease: Version:23 Release:6 Update:0 PortRelease:24 PortUpdate:10 Full:234})
• Server: 19.23.0.0.192 ({ServerRelease:Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production; Version 19.23.0.0.0 Version:19 Release:23 Update:0 PortRelease:0 PortUpdate:0 Full:192})

[tgulacsi]

I've tried to assemble a minimal reproductor:

ORA21062.zip

unzip ORA21062.zip
cd ORA21062
env ORACALL_DSN=user/passw@host:1521/service_name go test

[anthony-tuininga]

I tried your code but it fails with the type not existing. Is there something that creates it?

[anthony-tuininga]

I found a way to test with Python instead and can confirm that this error occurs when you try to set an attribute of a record with an instance of another record. I am asking internally if there is a solution or if this is simply not possible. It may be possible with PL/SQL -- which you can generate if necessary -- but that's an unpleasant workaround.

[tgulacsi]

Thanks for the idea!

func setAttribute(ctx context.Context, ex godror.Execer, obj *godror.Object, name string, data *godror.Data) error {
	if err := obj.SetAttribute(name, data); err != nil {
		var ec interface{ Code() int }
		if !errors.As(err, &ec) && ec.Code() == 21602 {
			return err
		}
		qry := fmt.Sprintf(`DECLARE
  v_obj %s := :1;
BEGIN
  v_obj.%s := :2;
  :3 := v_obj;
END;`,
			obj.ObjectType.PackageName+"."+obj.ObjectType.Name,
			name,
		)
		_, xErr := ex.ExecContext(ctx, qry, obj, data.GetObject(), sql.Out{Dest: obj})
		if xErr != nil {
			return fmt.Errorf("%s: %w: %w", qry, xErr, err)
		}
	}
	return nil
}

works, thank you!

(I'll wait with closing this issue till you confirm that this is a bug/missing feature in OCI).

[tgulacsi]

(Another strange phenomenon is that is a record type contains a NUMBER(10), represented as SQLT_INT (int64), and this record type is an attribute of another record type, and this gets this ORA-21602, thus I try the aforementioned PL/SQL workaround, then I get ORA-21525. If I handle such numbers with strings, then this is error does not appear.)