oracle db returns additional padding

Question

Could you advise why on one of our database below query:

select ' ' from dual

returns 32 blank characters:

'                                '

This happens ONLY on one environment, all other databases returns correctly only one space. I checked nls_parameters, they are the same. Any idea what could be reason for that? Some DB parameter? Some patch was not applied on that instance?

Other example:

select "!" || "!" from dual

produces:

"!!                                                              "

so it's !! + 62 spaces

Littlefoot · Accepted Answer · 2021-08-16 12:11:10Z

2

Check database parameter cursor_sharing. What is it set to? Set it to EXACT.

SQL> show parameters cursor_sharing

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------
cursor_sharing                       string      EXACT
SQL>

answered Aug 16, 2021 at 12:11

Littlefoot

144k15 gold badges41 silver badges65 bronze badges

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

2 Comments

user1632454 Over a year ago

value was set to FORCE, when I changed it on session level, then it works fine. However, I can't reproduce this issue on other environments by setting this value to FORCE - is it possible to do this?

Marmite Bomber Over a year ago

Check here for explanation and reproducible example. Note also that changing cursor_sharing can have side effects on other statements...

Collectives™ on Stack Overflow

oracle db returns additional padding

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related