I have noticed a weird bug in our Oracle database as we are moving from one database (Oracle 11.2.0.3.0) to another database (Oracle 11.2.4.0). Database servers are in different data center with different operating systems and support teams. So the version is not the only thing that has changed.
So, this is the bug I am facing: When I execute this little SQL statement I get different results:
</>SELECT 'hello' FROM DUAL;</>
On the old database the output is: </>"hello"</>
On the new database the output is: </>"hello[Many Whitespaces here, but I don't know how to show them at Ask Tom] "</>
Where are these whitespaces coming from?
I am using TOAD for Oracle in version 12.10.0.30.
And another test:
</>SELECT '|' || dummy || '|' || 'Hello' || '|' FROM DUAL;
SELECT '|' || dummy || '|' FROM DUAL;
SELECT '|' || 'Hello' || '|' FROM DUAL;</>
And the results:
</>"|X|Hello|"</>
</>"|X|"</>
</>"|Hello|[Many Whitespaces here, but I don't know how to show them at Ask Tom] "</>
Interestingly it works for the first SQL, but the third one is filled up with whitespaces again.
I have provided an LiveSQL link where you can also reproduce this behavior. But you won't see it in your browser, you have to download csv result file and open it in ex. Notepad.
UPDATE:
-------
I am really sorry, this was my fault, it isn't reproduceable on the live system... don't know what I have seen in my editor..., sorry!
The Business case is something like this:
We execute a select statement, store result to CSV file and than import from CSV file. I have updated LiveSQL for a (hopefully) better explanation.
Reproduceable on my local machine with TOAD, SQL*Plus and on our Linux machine with a Java Software.
On my local machine I could connect to the old Oracle database, where the </>SELECT 'hello' FROM DUAL;</> statement delivers a result without trailing whitespaces.
Possible Solution:
------------------
DB Parameter *.cursor_sharing was "EXACT" on the old database and "FORCE" on the new database. So we will have to change it to "EXCACT" too.
But why is there such a bug with "FORCE"? Any good explanation?
So.
When you set cursor_sharing to force, Oracle Database converts all literals to bind variables. So two otherwise identical statements selecting different strings become the same statement.
Shorter strings get bound to a varchar2(32). Which causes some clients to read these as 32 length strings, leading to the whitespace padding you see:
SQL> exec dbms_monitor.session_trace_enable();
PL/SQL procedure successfully completed.
SQL> alter session set cursor_sharing = force;
Session altered.
SQL>
SQL> select /*+ force */'Hellooooooooooooo' from dual;
'HELLOOOOOOOOOOOOO'
--------------------------------
Hellooooooooooooo
SQL> select /*+ force */'Hello' from dual;
'HELLO'
--------------------------------
Hello
SQL>
SQL> alter session set cursor_sharing = exact;
Session altered.
SQL>
SQL> select /*+ exact */'Hellooooooooooooo' from dual;
'HELLOOOOOOOOOOOO
-----------------
Hellooooooooooooo
SQL> select /*+ exact */'Hello' from dual;
'HELL
-----
Hello
Notice that the number of dashes is 32 for both the force statements! But exactly matches the length of the selected string when you have exact cursor_sharing.
Looking in the trace file, the "force" statements only appear once as:
select /*+ force */:"SYS_B_0"
from
dual
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.00 0.00 0 0 0 0
Execute 2 0.00 0.00 0 0 0 0
Fetch 4 0.00 0.00 0 0 0 2
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 8 0.00 0.00 0 0 0 2
Notice parse & execute both = 2.
But the exact statements appear separately:
select /*+ exact */'Hellooooooooooooo'
from
dual
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 0 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.00 0 0 0 1
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 112
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
1 1 1 FAST DUAL (cr=0 pr=0 pw=0 time=7 us cost=2 size=0 card=1)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 2 0.00 0.00
SQL*Net message from client 2 0.20 0.39
********************************************************************************
SQL ID: g9d9mghmsardu Plan Hash: 308129442
select /*+ exact */'Hello'
from
dual
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 0 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.00 0 0 0 1How do I know it bound to a varchar2(32)?
Well, you can look it up with this statement:
select s.sql_text, b.name, b.datatype_string, b.max_length
from v$sql s
left join v$sql_bind_capture b
on s.sql_id = b.sql_id
where s.sql_text like '%+ force%'
and s.sql_text not like '%not this%';
SQL_TEXT NAME DATATYPE_STRING MAX_LENGTH
select /*+ force */:"SYS_B_0" from dual :SYS_B_0 VARCHAR2(32) 32