AnsweredAssumed Answered

IGL 7.0.2 - How to create/use WF array variable containing change items PLUS cr/lf

Question asked by Andy Cheek on Jul 31, 2017
Latest reply on Aug 1, 2017 by Andy Cheek

Like • Show 0 Likes0
Comment • 9

Trying to retrieve the changes for any application within an access change request in a suitable format so that it can be included in the XML payload for a Create Ticket webservice call.

We cannot use the OOTB ${access_request_details} variable as ...

It includes HTML list tags
It includes the entitlement alternate name (and we want the raw name)
It just includes the user name, where we want to include userid and (where removing access) the related accountid.

Basically it doesn't work for us.

Ideally it would be possible to create an array variable as that would presumably be less subject to length constraints. If I try to concatenate all the changes together into a single string using something like the Oracle listagg function that has a maximum length of 4000 characters and it is not too hard to create a change request with sufficient changes to breach that limit.

It looks, though, as if the way that WF variables and in particular array variables are held in IGL has changed from IMG v6 (i.e. losing all that old XML) but how could we use these to include the line breaks (and ideally NOT display the comma separator)?

Sean Miller Edwin Mullie Frank Schubert Boris Lekumovich

Apologies for tagging you guys but this is really driving me crazy and I refuse to believe it is not solvable.

Andy Cheek Aug 1, 2017 5:12 AM

Correct Answer

Fair comment, but I don't know if/how we could truncate it "nicely", i.e. between individual CRI details. Plus the folk handling the Remedy tickets don't usually have access back into IGL. Not sure the xmlagg option is worth the effort - looks inordinately complicated.

The alternative (which I think is acceptable) would be (somehow) to check the length of the generated array variable and if too big then bypass the ticket altogether and go to Plan B, which is an email requesting the ticket to be created manually. This is already plan B in case there are issues with the ticket interface (e.g. Remedy down, user not exist in Remedy) and the frequency of cases like this should be minimal. I've done some analysis on our CR history and almost all the extra large ones were users erroneously selecting ALL entitlements for an application and they should have been cancelled/rejected before they got to fulfilment anyway.

See the reply in context

No one else had this question

Outcomes

Frank Schubert Jul 31, 2017 10:07 AM

WF variables gets stored as a CLOB in the database, so for that no restrictions should apply. I suggest that you create the XML snippet you need yourself with SQL using XMLELEMENT/XMLTABLE and others.

depending on how you structure they query you may be fine like that. XMLAGG could also help. 4k not enough for one request? maybe if you group the request differently you could assure each job is not too big?

Also, what if you don't listagg/xmlagg all records (in XML) together but just leave the workflow engine to do the heavy lifting. I believe that means when you use the variable containing an array of XML records it will simply concat the rows. If your XML per row is in order, you should be fine.

Actions

Andy Cheek @ Frank Schubert on Jul 31, 2017 10:37 AM

Obviously missed out some context - the variable I'm trying to create should not be actual XML, but will be included along with other text in an XML element in the Remedy webservice message, e.g.

<notes>

User name : Charles Smith

Userid : smithc2

Application : SAP

Changes requested:

Add entitlement SAP : A:ACCOUNTS_DISPLAY to Smith, Charles (NTID: smithc2)
Add entitlement SAP : A:ANC_UPDATE_GR to Smith, Charles (NTID: smithc2)
Add entitlement SAP : A:AP_PROC_EHP_BPS_EH to Smith, Charles (NTID: smithc2)
Add entitlement SAP : A:AP_PROC_GP_EAO to Smith, Charles (NTID: smithc2)
Add entitlement SAP : A:AP_PROC_GP_GOE to Smith, Charles (NTID: smithc2)

Request notes :

Notes as entered on submission of the change request

</notes>

The <notes> element is the body of the ticket, so we can't include additional XML tags within it - it's the Add entitlement xxxx content in italics that I need to create and store and have displayed with line feeds.

If I just create an array variable and include that in the XML payload as above, it simply writes it out in one long line, e.g.

Add entitlement SAP : A:ACCOUNTS_DISPLAY to Smith, Charles (NTID: smithc2),Add entitlement SAP : A:ANC_UPDATE_GR to Smith, Charles (NTID: smithc2),Add entitlement SAP : A:AP_PROC_EHP_BPS_EH to Smith, Charles (NTID: smithc2),Add entitlement SAP : A:AP_PROC_GP_EAO to Smith, Charles (NTID: smithc2),Add entitlement SAP : A:AP_PROC_GP_GOE to Smith, Charles (NTID: smithc2)

I can include <br> in my query to produce an array variable containing HTML that works, but cannot, for the life of me, work it out in so that it will display correctly WITHIN an XML element.

Actions

Andy Cheek @ Andy Cheek on Jul 31, 2017 10:51 AM

Hmmm. Interesting thought - can I use [lf] ? That's what the WF engine stores as line feeds in the rest of the XML, i.e. the post body. Would the variable substitution take place first though before any [lf] replacement?

Actions

Andy Cheek @ Andy Cheek on Jul 31, 2017 11:02 AM

Nope. That doesn't work - it just updates the original query as stored with the line feed, not the variable created when it runs.

Actions

Frank Schubert @ Andy Cheek on Jul 31, 2017 1:20 PM

what if you insert the linefeed as a special character e.g.

select 'Hello' || chr(10) ||' world' from dual

Actions

Andy Cheek @ Frank Schubert on Aug 1, 2017 3:32 AM

That didn't work either but .... (sudden 3am inspiration moment) I tried including a literal string in the original query to create the array variable, then created another variable in another query editing that literal string to CHR(10) - and that worked - sort of.

So the first query goes something like select '##LF##' || operation || ' ' || resource || ' : ' || action as CHANGEITEMS from xxxx - which creates the array variable. Then the second query is select replace(${jobUserData_CHANGEITEMS},'##LF##',CHR(10)) as CHANGEITEMSXML from dual

This only seems to work however if the resulting data is less than 4,000 characters as the second query creates a normal WF variable not a WF array. I tried the same processing on a large change request I found and got an ORA-01460: unimplemented or unreasonable conversion requested error.

Is there any way to force the second query to create a CLOB/BLOB rather than VARCHAR? Would that work and be stored OK in the IGL database?

Got very excited there for a short while

Actions

Frank Schubert @ Andy Cheek on Aug 1, 2017 3:59 AM

hmmm just taking a step back here. Say you have a big request. What if the Remedy Ticket would contain the first 3900 characters with an appended link to the real ticket in RSA IGL? listagg in oracle 12 (if I remember correctly) can have some kind of overflow control implemented. Hence if a request is too big, the listagg would not fail but generate the string up to e.g. character 3900 an you could append "For further details click here <a href....".

Just thinking....

But to you current problem... have you looked into xmlagg? It could be used to avoid the listagg limits: Listagg function excdding 4000 characters | Oracle Community

Actions

Andy Cheek @ Frank Schubert on Aug 1, 2017 5:12 AM

Actions

Frank Schubert @ Andy Cheek on Aug 1, 2017 4:03 AM

creating a CLOB with the query might just work. the values get stored in a CLOB anyways... but I never tried it out myself.

Actions

9 Replies

Frank Schubert Jul 31, 2017 10:07 AM
Mark Correct
Correct Answer
WF variables gets stored as a CLOB in the database, so for that no restrictions should apply. I suggest that you create the XML snippet you need yourself with SQL using XMLELEMENT/XMLTABLE and others.
depending on how you structure they query you may be fine like that. XMLAGG could also help. 4k not enough for one request? maybe if you group the request differently you could assure each job is not too big?
Also, what if you don't listagg/xmlagg all records (in XML) together but just leave the workflow engine to do the heavy lifting. I believe that means when you use the variable containing an array of XML records it will simply concat the rows. If your XML per row is in order, you should be fine.
Like • Show 0 Likes0
Actions
- Andy Cheek @ Frank Schubert on Jul 31, 2017 10:37 AM
  Mark Correct
  Correct Answer
  Obviously missed out some context - the variable I'm trying to create should not be actual XML, but will be included along with other text in an XML element in the Remedy webservice message, e.g.
  
  <notes>
  User name : Charles Smith
  Userid : smithc2
  
  Application : SAP
  
  Changes requested:
  Add entitlement SAP : A:ACCOUNTS_DISPLAY to Smith, Charles (NTID: smithc2)
  Add entitlement SAP : A:ANC_UPDATE_GR to Smith, Charles (NTID: smithc2)
  Add entitlement SAP : A:AP_PROC_EHP_BPS_EH to Smith, Charles (NTID: smithc2)
  Add entitlement SAP : A:AP_PROC_GP_EAO to Smith, Charles (NTID: smithc2)
  Add entitlement SAP : A:AP_PROC_GP_GOE to Smith, Charles (NTID: smithc2)
  
  Request notes :
  Notes as entered on submission of the change request
  </notes>
  
  The <notes> element is the body of the ticket, so we can't include additional XML tags within it - it's the Add entitlement xxxx content in italics that I need to create and store and have displayed with line feeds.
  
  If I just create an array variable and include that in the XML payload as above, it simply writes it out in one long line, e.g.
  Add entitlement SAP : A:ACCOUNTS_DISPLAY to Smith, Charles (NTID: smithc2),Add entitlement SAP : A:ANC_UPDATE_GR to Smith, Charles (NTID: smithc2),Add entitlement SAP : A:AP_PROC_EHP_BPS_EH to Smith, Charles (NTID: smithc2),Add entitlement SAP : A:AP_PROC_GP_EAO to Smith, Charles (NTID: smithc2),Add entitlement SAP : A:AP_PROC_GP_GOE to Smith, Charles (NTID: smithc2)
  
  I can include <br> in my query to produce an array variable containing HTML that works, but cannot, for the life of me, work it out in so that it will display correctly WITHIN an XML element.
  Like • Show 0 Likes0
  Actions
  - Andy Cheek @ Andy Cheek on Jul 31, 2017 10:51 AM
    Mark Correct
    Correct Answer
    Hmmm. Interesting thought - can I use [lf] ? That's what the WF engine stores as line feeds in the rest of the XML, i.e. the post body. Would the variable substitution take place first though before any [lf] replacement?
    Like • Show 0 Likes0
    Actions
    - Andy Cheek @ Andy Cheek on Jul 31, 2017 11:02 AM
      Mark Correct
      Correct Answer
      Nope. That doesn't work - it just updates the original query as stored with the line feed, not the variable created when it runs.
      Like • Show 0 Likes0
      Actions
      - Frank Schubert @ Andy Cheek on Jul 31, 2017 1:20 PM
        Mark Correct
        Correct Answer
        what if you insert the linefeed as a special character e.g.
        
        select 'Hello' || chr(10) ||' world' from dual
        ?
        Like • Show 0 Likes0
        Actions
        Andy Cheek @ Frank Schubert on Aug 1, 2017 3:32 AM
        Mark Correct
        Correct Answer
        That didn't work either but .... (sudden 3am inspiration moment) I tried including a literal string in the original query to create the array variable, then created another variable in another query editing that literal string to CHR(10) - and that worked - sort of.
        
        So the first query goes something like select '##LF##' || operation || ' ' || resource || ' : ' || action as CHANGEITEMS from xxxx - which creates the array variable. Then the second query is select replace(${jobUserData_CHANGEITEMS},'##LF##',CHR(10)) as CHANGEITEMSXML from dual
        
        This only seems to work however if the resulting data is less than 4,000 characters as the second query creates a normal WF variable not a WF array. I tried the same processing on a large change request I found and got an ORA-01460: unimplemented or unreasonable conversion requested error.
        
        Is there any way to force the second query to create a CLOB/BLOB rather than VARCHAR? Would that work and be stored OK in the IGL database?
        
        Got very excited there for a short while
        Like • Show 0 Likes0
        Actions
        Frank Schubert @ Andy Cheek on Aug 1, 2017 3:59 AM
        Mark Correct
        Correct Answer
        hmmm just taking a step back here. Say you have a big request. What if the Remedy Ticket would contain the first 3900 characters with an appended link to the real ticket in RSA IGL? listagg in oracle 12 (if I remember correctly) can have some kind of overflow control implemented. Hence if a request is too big, the listagg would not fail but generate the string up to e.g. character 3900 an you could append "For further details click here <a href....".
        Just thinking....
        
        But to you current problem... have you looked into xmlagg? It could be used to avoid the listagg limits: Listagg function excdding 4000 characters | Oracle Community
        Like • Show 0 Likes0
        Actions
        Andy Cheek @ Frank Schubert on Aug 1, 2017 5:12 AM
        Unmark Correct
        Correct Answer
        Fair comment, but I don't know if/how we could truncate it "nicely", i.e. between individual CRI details. Plus the folk handling the Remedy tickets don't usually have access back into IGL. Not sure the xmlagg option is worth the effort - looks inordinately complicated.
        
        The alternative (which I think is acceptable) would be (somehow) to check the length of the generated array variable and if too big then bypass the ticket altogether and go to Plan B, which is an email requesting the ticket to be created manually. This is already plan B in case there are issues with the ticket interface (e.g. Remedy down, user not exist in Remedy) and the frequency of cases like this should be minimal. I've done some analysis on our CR history and almost all the extra large ones were users erroneously selecting ALL entitlements for an application and they should have been cancelled/rejected before they got to fulfilment anyway.
        Like • Show 0 Likes0
        Actions
        Frank Schubert @ Andy Cheek on Aug 1, 2017 4:03 AM
        Mark Correct
        Correct Answer
        creating a CLOB with the query might just work. the values get stored in a CLOB anyways... but I never tried it out myself.
        Like • Show 0 Likes0
        Actions

IGL 7.0.2 - How to create/use WF array variable containing change items PLUS cr/lf

Outcomes

Related Content

Recommended Content