ARGetMultipleEntries returning ARERR 552: ORA-00936: missing expression

2015-11-03 Thread Ben Chernys 
Hi All,

 

ARS / ITSM 8.1.0 >>8.1.00 201301251157<< on Cent OS against Oracle 11.2

 

Interesting trace on this one.  The query worked fine the first go (there is
no query argument to this f) and ARGetMultipleEntries() picked up 100
records from AST:BaseElement.

 

On the second go of 100 records, the SQL log shows a single SQL repeated 100
times for each record, and then (for the status history):

 

ARGetMultipleEntries ==>   
   

ARGetMultipleEntries ==> /* Tue Nov 03 2015 22:56:51.5891 */SELECT
entryId,T0,U0,T1,U1,T2,U2,T3,U3,T4,U4,T5,U5,T6,U6,T7

U7,T8,U8,T9,U9,T10,U10,T11,U11,T12,U12,T13,U13 FROM H1572 WHERE entryId
IN)

ARGetMultipleEntries ==>   
   

ARGetMultipleEntries ==> /* Tue Nov 03 2015 22:56:51.5907 */*** ERROR
***  ORA-00936: missing expression 

 

Interestingly, I think it may be related to not enough space allocated for
the IN expression - as each Id will be more than 15 bytes.  More
interestingly, this table has 0 records.

 

The table in Q (schemaid = 1572) is AST:BaseElement - an infamous join J

 

So, has this been fixed in a server patch?  Has anyone else seen this?

 

Cheers

Ben Chernys

  www.softwaretoolhouse.com

 


___
UNSUBSCRIBE or access ARSlist Archives at www.arslist.org
"Where the Answers Are, and have been for 20 years"

Re: ARGetMultipleEntries returning ARERR 552: ORA-00936: missing expression

2015-11-03 Thread Rick Cook 
I saw something similar on an AI Transformation (MSSQL) that was performing
a similar function.  I, too, thought it was a space issue.  I removed one
condition (order by) from the SQL query, and it worked.

Rick
On Nov 3, 2015 2:33 PM, "Ben Chernys" 
wrote:

> **
>
> Hi All,
>
>
>
> ARS / ITSM 8.1.0 >>8.1.00 201301251157<< on Cent OS against Oracle 11.2
>
>
>
> Interesting trace on this one.  The query worked fine the first go (there
> is no query argument to this f) and ARGetMultipleEntries() picked up 100
> records from AST:BaseElement.
>
>
>
> On the second go of 100 records, the SQL log shows a single SQL repeated
> 100 times for each record, and then (for the status history):
>
>
>
> ARGetMultipleEntries ==>   
>Demo > 
>
> ARGetMultipleEntries ==> /* Tue Nov 03 2015 22:56:51.5891 */SELECT
> entryId,T0,U0,T1,U1,T2,U2,T3,U3,T4,U4,T5,U5,T6,U6,T7
>
> U7,T8,U8,T9,U9,T10,U10,T11,U11,T12,U12,T13,U13 FROM H1572 WHERE entryId
> IN)
>
> ARGetMultipleEntries ==>   
>Demo > 
>
> ARGetMultipleEntries ==> /* Tue Nov 03 2015 22:56:51.5907 */*** ERROR
> ***  ORA-00936: missing expression
>
>
>
> Interestingly, I think it may be related to not enough space allocated for
> the IN expression – as each Id will be more than 15 bytes.  More
> interestingly, this table has 0 records.
>
>
>
> The table in Q (schemaid = 1572) is AST:BaseElement – an infamous join J
>
>
>
> So, has this been fixed in a server patch?  Has anyone else seen this?
>
>
>
> Cheers
>
> Ben Chernys
>
> www.softwaretoolhouse.com
>
>
> _ARSlist: "Where the Answers Are" and have been for 20 years_

___
UNSUBSCRIBE or access ARSlist Archives at www.arslist.org
"Where the Answers Are, and have been for 20 years"

Re: ARGetMultipleEntries returning ARERR 552: ORA-00936: missing expression

2015-11-03 Thread Ben Chernys 
Thanks Rick, but that can’t be it.  I am doing NO SQL queries.  ONLY an 
ARGetMuplipleEntries() call – which has no query terms at all – so there’s 
nothing I can adjust – unless BMC publishes source J

 

What’s interesting is that an IN expression was attempted to be generated BUT 
only for the Status History and 100 different SQL selects were done for the 100 
records (which worked).  All this is part of the single ARGetMultipleEntries 
call.

 

The question is – has this been fixed in a patch to 8.1.0?

 

Cheers

Ben

 

From: Action Request System discussion list(ARSList) 
[mailto:arslist@ARSLIST.ORG] On Behalf Of Rick Cook
Sent: November-03-15 15:36
To: arslist@ARSLIST.ORG
Subject: Re: ARGetMultipleEntries returning ARERR 552: ORA-00936: missing 
expression

 

** 

I saw something similar on an AI Transformation (MSSQL) that was performing a 
similar function.  I, too, thought it was a space issue.  I removed one 
condition (order by) from the SQL query, and it worked. 

Rick

On Nov 3, 2015 2:33 PM, "Ben Chernys" <ben.cher...@softwaretoolhouse.com> wrote:

** 

Hi All,

ARS / ITSM 8.1.0 >>8.1.00 201301251157<< on Cent OS against Oracle 11.2

Interesting trace on this one.  The query worked fine the first go (there is no 
query argument to this f) and ARGetMultipleEntries() picked up 100 records from 
AST:BaseElement.

On the second go of 100 records, the SQL log shows a single SQL repeated 100 
times for each record, and then (for the status history):

ARGetMultipleEntries ==>   
ARGetMultipleEntries ==> /* Tue Nov 03 2015 22:56:51.5891 */SELECT 
entryId,T0,U0,T1,U1,T2,U2,T3,U3,T4,U4,T5,U5,T6,U6,T7
U7,T8,U8,T9,U9,T10,U10,T11,U11,T12,U12,T13,U13 FROM H1572 WHERE entryId   IN)
ARGetMultipleEntries ==>   
ARGetMultipleEntries ==> /* Tue Nov 03 2015 22:56:51.5907 */*** ERROR ***  
ORA-00936: missing expression 

Interestingly, I think it may be related to not enough space allocated for the 
IN expression – as each Id will be more than 15 bytes.  More interestingly, 
this table has 0 records.

The table in Q (schemaid = 1572) is AST:BaseElement – an infamous join J

So, has this been fixed in a server patch?  Has anyone else seen this?

Cheers
Ben Chernys
 <http://www.softwaretoolhouse.com> www.softwaretoolhouse.com


___
UNSUBSCRIBE or access ARSlist Archives at www.arslist.org
"Where the Answers Are, and have been for 20 years"

Re: ARGetMultipleEntries returning ARERR 552: ORA-00936: missing expression

2015-11-03 Thread Ben Chernys 
More info:

 

Each AST:BaseElement is picked up with “(E0 = '368' and E1 = 
'120' )” and the successful pick up of the Status History seems to 
be missing some qualification terms:

 

ARGetMultipleEntries ==> /* Tue Nov 03 2015 22:55:57.4055 */SELECT 
entryId,T0,U0,T1,U1,T2,U2,T3,U3,T4,U4,T5,U5,T6,U6,T7,U7,T8,U8,T9,U9,T10,U10,T11,U11,T12,U12,T13,U13
 FROM H1572 WHERE entryId

ARARGetMultipleEntries ==> IN ('001', '002', 
'101', '102', '103', '104', 
'105', '106',

ARGetMultipleEntries ==> '107', '108', 
'109', '110', '111', '112', 
'113', '114',

ARGetMultipleEntries ==> '115', '116', 
'117', '118', '119', '120', 
'121', '122')

 

In the Join SQLs, E1 = NULL is often included.  It seems the IN clause on the 
Status History, is for those CIs where E1 is a real ID only.

 

Ben

 

From: Ben Chernys [mailto:ben.cher...@softwaretoolhouse.com] 
Sent: November-03-15 15:56
To: 'arslist@ARSLIST.ORG'
Subject: RE: ARGetMultipleEntries returning ARERR 552: ORA-00936: missing 
expression

 

Thanks Rick, but that can’t be it.  I am doing NO SQL queries.  ONLY an 
ARGetMuplipleEntries() call – which has no query terms at all – so there’s 
nothing I can adjust – unless BMC publishes source J

 

What’s interesting is that an IN expression was attempted to be generated BUT 
only for the Status History and 100 different SQL selects were done for the 100 
records (which worked).  All this is part of the single ARGetMultipleEntries 
call.

 

The question is – has this been fixed in a patch to 8.1.0?

 

Cheers

Ben

 

From: Action Request System discussion list(ARSList) 
[mailto:arslist@ARSLIST.ORG] On Behalf Of Rick Cook
Sent: November-03-15 15:36
To: arslist@ARSLIST.ORG
Subject: Re: ARGetMultipleEntries returning ARERR 552: ORA-00936: missing 
expression

 

** 

I saw something similar on an AI Transformation (MSSQL) that was performing a 
similar function.  I, too, thought it was a space issue.  I removed one 
condition (order by) from the SQL query, and it worked. 

Rick

On Nov 3, 2015 2:33 PM, "Ben Chernys" <ben.cher...@softwaretoolhouse.com> wrote:

** 

Hi All,

ARS / ITSM 8.1.0 >>8.1.00 201301251157<< on Cent OS against Oracle 11.2

Interesting trace on this one.  The query worked fine the first go (there is no 
query argument to this f) and ARGetMultipleEntries() picked up 100 records from 
AST:BaseElement.

On the second go of 100 records, the SQL log shows a single SQL repeated 100 
times for each record, and then (for the status history):

ARGetMultipleEntries ==>   
ARGetMultipleEntries ==> /* Tue Nov 03 2015 22:56:51.5891 */SELECT 
entryId,T0,U0,T1,U1,T2,U2,T3,U3,T4,U4,T5,U5,T6,U6,T7
U7,T8,U8,T9,U9,T10,U10,T11,U11,T12,U12,T13,U13 FROM H1572 WHERE entryId   IN)
ARGetMultipleEntries ==>   
ARGetMultipleEntries ==> /* Tue Nov 03 2015 22:56:51.5907 */*** ERROR ***  
ORA-00936: missing expression 

Interestingly, I think it may be related to not enough space allocated for the 
IN expression – as each Id will be more than 15 bytes.  More interestingly, 
this table has 0 records.

The table in Q (schemaid = 1572) is AST:BaseElement – an infamous join J

So, has this been fixed in a server patch?  Has anyone else seen this?

Cheers
Ben Chernys
 <http://www.softwaretoolhouse.com> www.softwaretoolhouse.com


___
UNSUBSCRIBE or access ARSlist Archives at www.arslist.org
"Where the Answers Are, and have been for 20 years"

Re: ARGetMultipleEntries returning ARERR 552: ORA-00936: missing expression

2015-11-03 Thread Ben Chernys 
Final points.

 

AST:BaseElement is an Outer Join, so there are many entries with no 
AST:Attributes record.  I just happen to have a block of 100 records from 
100-200 with no AST:Attributes.  If I start my query at 101 or 2, I get record 
201 in the same f() call and so the IN clause is generated correctly with one 
ID.  So, I need only to (manually) ensure that any 100 records with no 
AST:Attributes are not requested in one go.  Ugly but easy enough to work 
around for me.

 

The Status History of AST:BaseElement comes from AST:Attributes, so the bug is 
that if the 100 Ids passed to ARGetMultipleEntries() with all NULL as the 
second ID, the SQL is generated with an invalid IN clause.  This is BMC 
supplied data BTW.

 

Still, any word from BMC that this has been patched?  

 

Cheers

Ben

From: Ben Chernys [mailto:ben.cher...@softwaretoolhouse.com] 
Sent: November-03-15 16:16
To: 'arslist@ARSLIST.ORG'
Subject: RE: ARGetMultipleEntries returning ARERR 552: ORA-00936: missing 
expression

 

More info:

 

Each AST:BaseElement is picked up with “(E0 = '368' and E1 = 
'120' )” and the successful pick up of the Status History seems to 
be missing some qualification terms:

 

ARGetMultipleEntries ==> /* Tue Nov 03 2015 22:55:57.4055 */SELECT 
entryId,T0,U0,T1,U1,T2,U2,T3,U3,T4,U4,T5,U5,T6,U6,T7,U7,T8,U8,T9,U9,T10,U10,T11,U11,T12,U12,T13,U13
 FROM H1572 WHERE entryId

ARARGetMultipleEntries ==> IN ('001', '002', 
'101', '102', '103', '104', 
'105', '106',

ARGetMultipleEntries ==> '107', '108', 
'109', '110', '111', '112', 
'113', '114',

ARGetMultipleEntries ==> '115', '116', 
'117', '118', '119', '120', 
'121', '122')

 

In the Join SQLs, E1 = NULL is often included.  It seems the IN clause on the 
Status History, is for those CIs where E1 is a real ID only.

 

Ben

 

From: Ben Chernys [mailto:ben.cher...@softwaretoolhouse.com] 
Sent: November-03-15 15:56
To: 'arslist@ARSLIST.ORG'
Subject: RE: ARGetMultipleEntries returning ARERR 552: ORA-00936: missing 
expression

 

Thanks Rick, but that can’t be it.  I am doing NO SQL queries.  ONLY an 
ARGetMuplipleEntries() call – which has no query terms at all – so there’s 
nothing I can adjust – unless BMC publishes source J

 

What’s interesting is that an IN expression was attempted to be generated BUT 
only for the Status History and 100 different SQL selects were done for the 100 
records (which worked).  All this is part of the single ARGetMultipleEntries 
call.

 

The question is – has this been fixed in a patch to 8.1.0?

 

Cheers

Ben

 

From: Action Request System discussion list(ARSList) 
[mailto:arslist@ARSLIST.ORG] On Behalf Of Rick Cook
Sent: November-03-15 15:36
To: arslist@ARSLIST.ORG
Subject: Re: ARGetMultipleEntries returning ARERR 552: ORA-00936: missing 
expression

 

** 

I saw something similar on an AI Transformation (MSSQL) that was performing a 
similar function.  I, too, thought it was a space issue.  I removed one 
condition (order by) from the SQL query, and it worked. 

Rick

On Nov 3, 2015 2:33 PM, "Ben Chernys" <ben.cher...@softwaretoolhouse.com> wrote:

** 

Hi All,

ARS / ITSM 8.1.0 >>8.1.00 201301251157<< on Cent OS against Oracle 11.2

Interesting trace on this one.  The query worked fine the first go (there is no 
query argument to this f) and ARGetMultipleEntries() picked up 100 records from 
AST:BaseElement.

On the second go of 100 records, the SQL log shows a single SQL repeated 100 
times for each record, and then (for the status history):

ARGetMultipleEntries ==>   
ARGetMultipleEntries ==> /* Tue Nov 03 2015 22:56:51.5891 */SELECT 
entryId,T0,U0,T1,U1,T2,U2,T3,U3,T4,U4,T5,U5,T6,U6,T7
U7,T8,U8,T9,U9,T10,U10,T11,U11,T12,U12,T13,U13 FROM H1572 WHERE entryId   IN)
ARGetMultipleEntries ==>   
ARGetMultipleEntries ==> /* Tue Nov 03 2015 22:56:51.5907 */*** ERROR ***  
ORA-00936: missing expression 

Interestingly, I think it may be related to not enough space allocated for the 
IN expression – as each Id will be more than 15 bytes.  More interestingly, 
this table has 0 records.

The table in Q (schemaid = 1572) is AST:BaseElement – an infamous join J

So, has this been fixed in a server patch?  Has anyone else seen this?

Cheers
Ben Chernys
 <http://www.softwaretoolhouse.com> www.softwaretoolhouse.com


___
UNSUBSCRIBE or access ARSlist Archives at www.arslist.org
"Where the Answers Are, and have been for 20 years"