How to return empty string in resultset (using sys_refcursor) in ORACLE DB

Question

After facing the problem:

ORA-32108: max column or parameter size not specified

I researched a little and I found these two questions: Why does Oracle 9i treat an empty string as NULL? and Oracle not distinguishing between nulls and empty strings? .

These questions explain the error I get. But this led me to the next question - how can I return empty string as a field value in a ResultSet in Oracle?

What I have so far is:

create the statement
register output parameter as oracle::occi::OCCICURSOR
execute the statement
the I call GetCursor to use the returned resultset

The execute fails, because of the error, described above.

So, how can I return such field in the resultset, which has value an empty string ('')?

In other words, I don't know how to apply the action, specified HERE - is it something server-side? Or I should add something in my code? Or in the stored procedure, that returns this resultset?

I use OCCI (Oracle C++ Call Interface). My current workaround is to return a string, containing a single space (' '), but I don't like it.

This means you want a completely null column returned? You could see if cast will work, cast( null as varchar2(1)). — Ben
– Ben, Commented Dec 17, 2012 at 11:28
Ah, this cast works like a charm! Thanks! Can you add it as an answer? — Kiril Kirov
– Kiril Kirov, Commented Dec 17, 2012 at 11:36

Ben · Accepted Answer · 2012-12-17 11:42:12Z

2

If you want a completely NULL column returned you can use CAST(), which converts one data type into another.

For instance the following would generate a completely null column.

select a.*, cast(null as varchar2(1)) as null_column
  from my_table a

But, the database now understands that this should be a column of VARCHAR2(1), whereas if you use null as null_column there has been no maximum length specified.

answered Dec 17, 2012 at 11:42

Ben

53.1k36 gold badges133 silver badges156 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to return empty string in resultset (using sys_refcursor) in ORACLE DB

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related