ORA-32108: max column or parameter size not specified using several OUT parameters

[enarvaez]

I am having problems with this code, i am executing an ORACLE proc that has 2 in string parameters and 4 out parameters, 3 of those are strings and one is a ref cursor...

This is getting me this error (ORACE-32108) even when specificating the parameter size regarding strings. Is there a way to specify max column or what am i doing wrong in this code ...

p_merchantcode = '00030312152';
p_language = 'ESPA'; // oraconn.execute("call IC_INTC_COMERCIO.ObtenerLiquidaComercioRefCur(:1,:2,:3,:4,:5,:6)", [p_merchantcode, p_language, new oracle.OutParam(oracle.OCCICURSOR), new oracle.OutParam(oracle.OCCISTRING, {size: 1000}), new oracle.OutParam(oracle.OCCISTRING, {size: 1000}), new oracle.OutParam(oracle.OCCISTRING, {size: 1000})], function (err, results) { .........

[enarvaez]

I took out the 3 strings... and now the code is just like this...

        oraconn.execute("call IC_INTC_COMERCIO.ObtenerLiquidaComercioRefCur(:1,:2,:3)",
        ['005000593', 'ESPA',
        new oracle.OutParam(oracle.OCCICURSOR)], function.....

It is still telling me error ORA-32108 so probably something regarding the ref_cursor... should i put it max column or something like that ? the cursor returns 14 columns and works ok from SQL-Developer

[vasa-chi]

I used to get that error when some columns of returned cursor contained explicit uncasted NULL. CAST (NULL as the type of the column) should help (if you have control of the pl/sql codebase)

[enarvaez]

Thanks, i have control of the pl/sql code but i don't understand very well what i'm supposed to do. Should i convert all NULLs into empty strings (like '' in Oracle) ? In fact, some of the columns returned are NULL...

[enarvaez]

In fact, now it's working but the issue remains if somebody will like to fix it. I had just one null in the column results and that was not the problem. I had one description, an empty one ('') and that created the problem so i'm guessing there is a bad handling for string objects with 0 length in ref cursors, something that can happen from time to time. So it was weird, if anybody wants to check this out in the code will be appreciated...

in the ORACLE code, it was something like this...

select field1, field2, '' field3 from dual...

field3 was the one creating the problem.

And well, when the columns are NULL there is no problem at all in this case... If this is not an issue this can be closed...

[vasa-chi]

My problem was solved when I changed select ..., NULL from DUAL to select ..., CAST (NULL AS VARCHAR2)) from DUAL

[johannish]

Mainly as a note to self, here are posts describing how to fix this issue:

https://community.oracle.com/thread/469953 https://community.oracle.com/thread/619916

Essentially we'd need to call rs->setMaxColumnSize(1,1); before calling rs->next(), but the challenge is that that would have to be done on each column containing null, and I'm not sure how to figure that out before calling rs->next(). From the second post listed, I assume we can somehow get that info from oracle::occi::MetaData.