Skip to Main Content
Forums

ODP.NET

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Race condition in ODAC causes ORA-00933 command text corruption

Greg BachratyJun 1 2015 — edited Jul 2 2015

See the original issue in this thread:

Mysterious ORA-00933 on startup when using ODAC unmanaged/managed

Short version:

random ORA-00933 SQL command not properly ended on application startup.

When the error happens the generated command always ends with a double where clause and the second clause is "WHERE (ROWNUM <= (1) )"

It seems I found the root cause of the issue: when two independent commands are being generated on separate threads and one of them contains a DbLimitExpression the other command may borrow this fragment. This indicates some static variable is used during query generation that should be a context variable. This behavior seems to be present in both managed and unmanaged ODAC.

I'm mostly seeing this on startup because my startup queries are the most likely to run in parallel and one of them uses OrderBy then FirstOrDefault which seem to trigger the use of the shared state. After removing the FirstOrDefault() from this query I haven't seen this bug triggered yet.

For performance reasons I obviously cannot strip FirstOrDefault calls from all of my queries, nor can I set a global lock to serialize query generation. Is there a more suitable workaround?

@"2796195" could you verify?

Comments

Paulzip

select id, category, name from where category like '%'||&category||'%'

1 person found this helpful
jaramill 
palkodi wrote:
create table ter (ID number, category varchar2(250 byte), name varchar2(250 byte) ); 
insert into ter values (1, 'amd-visual theraphy','john'); 
insert into ter values (1, 'amd-visual theraphy','mike'); 
insert into ter values (2, 'amd-autmatic theraphy','mike'); 
insert into ter values (3, 'amd-autmatic theraphy','drane'); 
insert into ter values (3, 'cmd autmatic theraphy', 'traverse');

Thanks for providing your ddl and dml statements (always make sure to provide your database version in the future.

palkodi wrote:
so if i select this 
select id, category, name from where category like '%visual%' 
select id, category, name from where category like '%autmatic %' i get the result
but if i want to pass a parameter how to obtain that 
select id, category, name from where category like '%visual%' = &catogeory or category like '%autmatic %' =&category (wrong method but want similar like this)
If i enter &category = visual then i need to get this result select id, category, name from where category like '%visual%'
If i enter &category = autmatic then i need to get this result select id, category, name from where category like '%autmatic %' 
so what should be done?

You say the first two select statements return the result you DO want.

So you want to pass a parameter?  Just use the ampersand sign (&) to make it a substitution variable (needed in SQL* Plus)

See my formatted version of your code and my query

CODE

drop table ter cascade constraints purge;
create table ter
(
 id       number
,category varchar2(250 byte)
,name     varchar2(250 byte)
); 
insert into ter values (1, 'amd-visual theraphy','john');
insert into ter values (1, 'amd-visual theraphy','mike');
insert into ter values (2, 'amd-autmatic theraphy','mike');
insert into ter values (3, 'amd-autmatic theraphy','drane');
insert into ter values (3, 'cmd autmatic theraphy', 'traverse');
commit;

OUTPUT

Time Start: 08-22-2018 12:54:47
Table dropped.
Table created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
Commit complete.
Time End: 08-22-2018 12:54:48
Elapsed Time for Script Execution: 474 msecs

SQL

select id
      ,category
      ,name
  from ter
 where category like '%&visual%';

OUTPUT

Time Start: 08-22-2018 12:58:30
old: select id
      ,category
      ,name
  from ter
 where category like '%&visual%'
new: select id
      ,category
      ,name
  from ter
 where category like '%visual%'
   ID CATEGORY                       NAME      
----- ------------------------------ ----------
    1 amd-visual theraphy            john      
    1 amd-visual theraphy            mike      
2 rows selected.
Time End: 08-22-2018 12:58:31
Elapsed Time for Script Execution: 1 sec

SQL

column id format 9999
column category format a30
column name format a10
select id
      ,category
      ,name
  from ter
 where category like '%&autmatic %';

OUTPUT

Time Start: 08-22-2018 13:00:00
old: select id
      ,category
      ,name
  from ter
 where category like '%&autmatic %'
new: select id
      ,category
      ,name
  from ter
 where category like '%autmatic %'
   ID CATEGORY                       NAME      
----- ------------------------------ ----------
    2 amd-autmatic theraphy          mike      
    3 amd-autmatic theraphy          drane     
    3 cmd autmatic theraphy          traverse  
3 rows selected.
Time End: 08-22-2018 13:00:03
Elapsed Time for Script Execution: 2 secs
1 person found this helpful
Frank Kulash
Answer

Hi,

Paulzip wrote:
select id, category, name from where category like '%'||&category||'%'

If &category = visual, then that will expect a column named VISUAL.

If I understand OP's question correctly, you want &category to be inside single-quotes, like this:

select  id, category, name 
from    ter
where   category  like '%&category%';
Marked as Answer by palkodi · Sep 27 2020
Paulzip 
Frank Kulash wrote:
Hi,
Paulzip wrote:
select id, category, name from where category like '%'||&category||'%' 
If &category = visual, then that will expect a column named VISUAL.
If I understand OP's question correctly, you want &category to be inside single-quotes, like this:
select id, category, name 
from ter
where category like '%&category%';

You could well be correct there, Frank. It seems I've interpreted it differently / incorrectly.

mathguy 
Paulzip wrote:
select id, category, name from where category like '%'||&category||'%'

In this solution, the user would have to enter    'visual'     (including the single quotes) when prompted for &category; otherwise the query will interpret VISUAL as an identifier and throw an error.

You could rewrite this as  '%'||'&category'||'%'   This is exactly what Frank wrote, except that he wrote it much more simply.

1 person found this helpful
jaramill

Which is the same thing I did in my reply.  I just put the ampersand inside the quotes and when you run it, it looks for the value at the prompt.

mathguy

Not exactly (unless I am missing something). In your Reply you used the parameter values as the name of the substitution variable (as in, '%&visual&' and '%&autmatic %', respectively); the OP requires a single variable name, which he showed as &category. You show two different SQL statements for the two input strings; there should only be one SQL statement.

jaramill 
mathguy wrote:
Not exactly (unless I am missing something). In your Reply you used the parameter values as the name of the substitution variable (as in, '%&visual&' and '%&autmatic %', respectively); the OP requires a single variable name, which he showed as &category. You show two different SQL statements for the two input strings; there should only be one SQL statement.

Ah gotcha.  His original post I must have misread as it was a clear as mud

1 - 8
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Jul 30 2015
Added on Jun 1 2015
#odp-net
1 comment
3,229 views