1 Reply Latest reply on Jan 17, 2013 7:42 AM by Fred DERNBACH

    Problem with Human Task query TasksAssignedAsPotentialOwnerByStatus

    Fred DERNBACH Newbie

      Hi,

       

      I'm setting up JBPM 5.4.0.Final over hibernate and Oracle. I'm using LocalTaskService, JPA 1 persistence unit over hibernate. It is working, a sample process can be started.

       

      But I'm struggling with a named query that does not return tasks while it should.

       

      To retrieve tasks, I'm now trying to call:

       

      public List<TaskSummary> getTasksAssignedAsPotentialOwnerByStatus(String userId, List<Status> status, String language) of LocalTaskService .

       

      That calls JPA named query TasksAssignedAsPotentialOwnerByStatus included in Taskorm.xml file.

       

      For parameter 'status' (a list is expected), I'm passing an ArrayList of size 1 with org.jbpm.task.Status.Ready enum value in it.

       

      JPA Query does not retrieve any task while it should.

       

      I've logged generated SQL and when run nativtely on database, it returns records as expected.

       

      However, the log file shows that the binding of 'status' list parameter does not serialize correctly. See log file below in red. The status list is binded in binary format !

       

      Any idea what is wrong ?

       

      Fred

       

       

      JPA query (extract of Taskorm.xml, see status binding variable in red):

      </named-query>

            <named-query name="TasksAssignedAsPotentialOwnerByStatus">

                <query>

      select

           new org.jbpm.task.query.TaskSummary(

           t.id,

           t.taskData.processInstanceId,

           name.text,

           subject.text,

           description.text,

           t.taskData.status,

           t.priority,

           t.taskData.skipable,

           actualOwner,

           createdBy,

           t.taskData.createdOn,

           t.taskData.activationTime,

           t.taskData.expirationTime,

           t.taskData.processId,

           t.taskData.processSessionId)

      from

          Task t

          left join t.taskData.createdBy as createdBy

          left join t.taskData.actualOwner as actualOwner

          left join t.subjects as subject

          left join t.descriptions as description

          left join t.names as name,

          OrganizationalEntity potentialOwners

      where

          t.archived = 0 and

          potentialOwners.id = :userId and

          potentialOwners in elements ( t.peopleAssignments.potentialOwners  )  and

       

          (

          name.language = :language

          or t.names.size = 0

          ) and

       

          (

          subject.language = :language

          or t.subjects.size = 0

          ) and

       

          (

          description.language = :language

          or t.descriptions.size = 0

          ) and

       

          t.taskData.status in (:status) and

       

          t.taskData.expirationTime is null

                </query>

       

       

      Log file (see binding of status list variable in red):

      DEBUG 11439685[RMICallHandler-20](Printer.java:106) - listing entities:

      DEBUG 11439685[RMICallHandler-20](Printer.java:113) - org.jbpm.task.User{id=ITCH}

      DEBUG 11439685[RMICallHandler-20](Printer.java:113) - org.jbpm.task.Group{id=SECURITY_GLOBAL}

      DEBUG 11439700[RMICallHandler-20](Printer.java:113) - org.jbpm.task.Group{id=SECURITY_AUTHORITY}

      DEBUG 11439700[RMICallHandler-20](DefaultAutoFlushEventListener.java:76) - Dont need to execute flush

      DEBUG 11439700[RMICallHandler-20](HQLQueryPlan.java:174) - find: select new org.jbpm.task.query.TaskSummary( t.id, t.taskData.processInstanceId, name.text, subject.text, description.text, t.taskData.status, t.priority, t.taskData.skipable, actualOwner, createdBy, t.taskData.createdOn, t.taskData.activationTime, t.taskData.expirationTime, t.taskData.processId, t.taskData.processSessionId) from Task t left join t.taskData.createdBy as createdBy left join t.taskData.actualOwner as actualOwner left join t.subjects as subject left join t.descriptions as description left join t.names as name, OrganizationalEntity potentialOwners where t.archived = 0 and ( potentialOwners.id = :userId or potentialOwners.id in (:groupIds0_, :groupIds1_) ) and potentialOwners in elements ( t.peopleAssignments.potentialOwners ) and ( name.language = :language or t.names.size = 0 ) and ( subject.language = :language or t.subjects.size = 0 ) and ( description.language = :language or t.descriptions.size = 0 ) and t.taskData.status in (:status) and t.taskData.expirationTime is null

      DEBUG 11439732[RMICallHandler-20](SerializationHelper.java:114) - Starting serialization of object [Ready]

      DEBUG 11439732[RMICallHandler-20](QueryParameters.java:278) - named parameters: {userId=ITCH, groupIds1_=SECURITY_AUTHORITY, groupIds0_=SECURITY_GLOBAL, status=2c6d8085fef28094eff2e7aeeae2f0edaef4e1f3ebaed3f4e1f4f5f38080808080808080928080f8f2808eeae1f6e1aeece1eee7aec5eef5ed8080808080808080928080f8f0f48085d2e5e1e4f9, language=en-UK}

      DEBUG 11439732[RMICallHandler-20](QueryTranslatorImpl.java:307) - HQL: select new org.jbpm.task.query.TaskSummary( t.id, t.taskData.processInstanceId, name.text, subject.text, description.text, t.taskData.status, t.priority, t.taskData.skipable, actualOwner, createdBy, t.taskData.createdOn, t.taskData.activationTime, t.taskData.expirationTime, t.taskData.processId, t.taskData.processSessionId) from org.jbpm.task.Task t left join t.taskData.createdBy as createdBy left join t.taskData.actualOwner as actualOwner left join t.subjects as subject left join t.descriptions as description left join t.names as name, org.jbpm.task.OrganizationalEntity potentialOwners where t.archived = 0 and ( potentialOwners.id = :userId or potentialOwners.id in (:groupIds0_, :groupIds1_) ) and potentialOwners in elements ( t.peopleAssignments.potentialOwners ) and ( name.language = :language or t.names.size = 0 ) and ( subject.language = :language or t.subjects.size = 0 ) and ( description.language = :language or t.descriptions.size = 0 ) and t.taskData.status in (:status) and t.taskData.expirationTime is null

      DEBUG 11439747[RMICallHandler-20](QueryTranslatorImpl.java:308) - SQL: select user2_.id as id67_0_, user1_.id as id67_1_, task0_.id as col_0_0_, task0_.processInstanceId as col_1_0_, names5_.text as col_2_0_, subjects3_.text as col_3_0_, descriptio4_.text as col_4_0_, task0_.status as col_5_0_, task0_.priority as col_6_0_, task0_.skipable as col_7_0_, user2_.id as col_8_0_, user1_.id as col_9_0_, task0_.createdOn as col_10_0_, task0_.activationTime as col_11_0_, task0_.expirationTime as col_12_0_, task0_.processId as col_13_0_, task0_.processSessionId as col_14_0_ from Task task0_ left outer join OrganizationalEntity user1_ on task0_.createdBy_id=user1_.id left outer join OrganizationalEntity user2_ on task0_.actualOwner_id=user2_.id left outer join I18NText subjects3_ on task0_.id=subjects3_.Task_Subjects_Id left outer join I18NText descriptio4_ on task0_.id=descriptio4_.Task_Descriptions_Id left outer join I18NText names5_ on task0_.id=names5_.Task_Names_Id, OrganizationalEntity organizati6_ where (task0_.archived=0 )and((organizati6_.id=? )or(organizati6_.id in(? , ?)))and(organizati6_.id in(select potentialo7_.entity_id from PeopleAssignments_PotOwners potentialo7_ where task0_.id=potentialo7_.task_id))and((names5_.language=? )or((select count(names8_.Task_Names_Id) from I18NText names8_ where task0_.id=names8_.Task_Names_Id)=0 ))and((subjects3_.language=? )or((select count(subjects9_.Task_Subjects_Id) from I18NText subjects9_ where task0_.id=subjects9_.Task_Subjects_Id)=0 ))and((descriptio4_.language=? )or((select count(descriptio10_.Task_Descriptions_Id) from I18NText descriptio10_ where task0_.id=descriptio10_.Task_Descriptions_Id)=0 ))and(task0_.status in(?))and(task0_.expirationTime is null )

      DEBUG 11439763[RMICallHandler-20](AbstractBatcher.java:410) - about to open PreparedStatement (open PreparedStatements: 0, globally: 0)

      DEBUG 11439763[RMICallHandler-20](ConnectionManager.java:444) - opening JDBC connection

      DEBUG 11439763[RMICallHandler-20](SQLStatementLogger.java:111) - select user2_.id as id67_0_, user1_.id as id67_1_, task0_.id as col_0_0_, task0_.processInstanceId as col_1_0_, names5_.text as col_2_0_, subjects3_.text as col_3_0_, descriptio4_.text as col_4_0_, task0_.status as col_5_0_, task0_.priority as col_6_0_, task0_.skipable as col_7_0_, user2_.id as col_8_0_, user1_.id as col_9_0_, task0_.createdOn as col_10_0_, task0_.activationTime as col_11_0_, task0_.expirationTime as col_12_0_, task0_.processId as col_13_0_, task0_.processSessionId as col_14_0_ from Task task0_ left outer join OrganizationalEntity user1_ on task0_.createdBy_id=user1_.id left outer join OrganizationalEntity user2_ on task0_.actualOwner_id=user2_.id left outer join I18NText subjects3_ on task0_.id=subjects3_.Task_Subjects_Id left outer join I18NText descriptio4_ on task0_.id=descriptio4_.Task_Descriptions_Id left outer join I18NText names5_ on task0_.id=names5_.Task_Names_Id, OrganizationalEntity organizati6_ where (task0_.archived=0 )and((organizati6_.id=? )or(organizati6_.id in(? , ?)))and(organizati6_.id in(select potentialo7_.entity_id from PeopleAssignments_PotOwners potentialo7_ where task0_.id=potentialo7_.task_id))and((names5_.language=? )or((select count(names8_.Task_Names_Id) from I18NText names8_ where task0_.id=names8_.Task_Names_Id)=0 ))and((subjects3_.language=? )or((select count(subjects9_.Task_Subjects_Id) from I18NText subjects9_ where task0_.id=subjects9_.Task_Subjects_Id)=0 ))and((descriptio4_.language=? )or((select count(descriptio10_.Task_Descriptions_Id) from I18NText descriptio10_ where task0_.id=descriptio10_.Task_Descriptions_Id)=0 ))and(task0_.status in(?))and(task0_.expirationTime is null )

      13/01/16 19:00:57 Hibernate: select user2_.id as id67_0_, user1_.id as id67_1_, task0_.id as col_0_0_, task0_.processInstanceId as col_1_0_, names5_.text as col_2_0_, subjects3_.text as col_3_0_, descriptio4_.text as col_4_0_, task0_.status as col_5_0_, task0_.priority as col_6_0_, task0_.skipable as col_7_0_, user2_.id as col_8_0_, user1_.id as col_9_0_, task0_.createdOn as col_10_0_, task0_.activationTime as col_11_0_, task0_.expirationTime as col_12_0_, task0_.processId as col_13_0_, task0_.processSessionId as col_14_0_ from Task task0_ left outer join OrganizationalEntity user1_ on task0_.createdBy_id=user1_.id left outer join OrganizationalEntity user2_ on task0_.actualOwner_id=user2_.id left outer join I18NText subjects3_ on task0_.id=subjects3_.Task_Subjects_Id left outer join I18NText descriptio4_ on task0_.id=descriptio4_.Task_Descriptions_Id left outer join I18NText names5_ on task0_.id=names5_.Task_Names_Id, OrganizationalEntity organizati6_ where (task0_.archived=0 )and((organizati6_.id=? )or(organizati6_.id in(? , ?)))and(organizati6_.id in(select potentialo7_.entity_id from PeopleAssignments_PotOwners potentialo7_ where task0_.id=potentialo7_.task_id))and((names5_.language=? )or((select count(names8_.Task_Names_Id) from I18NText names8_ where task0_.id=names8_.Task_Names_Id)=0 ))and((subjects3_.language=? )or((select count(subjects9_.Task_Subjects_Id) from I18NText subjects9_ where task0_.id=subjects9_.Task_Subjects_Id)=0 ))and((descriptio4_.language=? )or((select count(descriptio10_.Task_Descriptions_Id) from I18NText descriptio10_ where task0_.id=descriptio10_.Task_Descriptions_Id)=0 ))and(task0_.status in(?))and(task0_.expirationTime is null )

      DEBUG 11439778[RMICallHandler-20](AbstractBatcher.java:513) - preparing statement

      DEBUG 11439794[RMICallHandler-20](Loader.java:1772) - bindNamedParameters() SECURITY_AUTHORITY -> groupIds1_ [3]

      DEBUG 11439794[RMICallHandler-20](NullableType.java:151) - binding 'SECURITY_AUTHORITY' to parameter: 3

      DEBUG 11439794[RMICallHandler-20](Loader.java:1772) - bindNamedParameters() ITCH -> userId [1]

      DEBUG 11439794[RMICallHandler-20](NullableType.java:151) - binding 'ITCH' to parameter: 1

      DEBUG 11439810[RMICallHandler-20](Loader.java:1772) - bindNamedParameters() SECURITY_GLOBAL -> groupIds0_ [2]

      DEBUG 11439810[RMICallHandler-20](NullableType.java:151) - binding 'SECURITY_GLOBAL' to parameter: 2

      DEBUG 11439810[RMICallHandler-20](Loader.java:1772) - bindNamedParameters() Ready -> status [7]

      DEBUG 11439825[RMICallHandler-20](SerializationHelper.java:114) - Starting serialization of object [Ready]

      DEBUG 11439825[RMICallHandler-20](NullableType.java:151) - binding '2c6d8085fef28094eff2e7aeeae2f0edaef4e1f3ebaed3f4e1f4f5f38080808080808080928080f8f2808eeae1f6e1aeece1eee7aec5eef5ed8080808080808080928080f8f0f48085d2e5e1e4f9' to parameter: 7

      DEBUG 11439841[RMICallHandler-20](SerializationHelper.java:114) - Starting serialization of object [Ready]

      DEBUG 11439841[RMICallHandler-20](Loader.java:1772) - bindNamedParameters() en-UK -> language [4]

      DEBUG 11439841[RMICallHandler-20](NullableType.java:151) - binding 'en-UK' to parameter: 4

      DEBUG 11439857[RMICallHandler-20](Loader.java:1772) - bindNamedParameters() en-UK -> language [5]

      DEBUG 11439857[RMICallHandler-20](NullableType.java:151) - binding 'en-UK' to parameter: 5

      DEBUG 11439857[RMICallHandler-20](Loader.java:1772) - bindNamedParameters() en-UK -> language [6]

      DEBUG 11439872[RMICallHandler-20](NullableType.java:151) - binding 'en-UK' to parameter: 6

      DEBUG 11439950[RMICallHandler-20](AbstractBatcher.java:426) - about to open ResultSet (open ResultSets: 0, globally: 0)

      DEBUG 11439966[RMICallHandler-20](Loader.java:717) - processing result set

      DEBUG 11439966[RMICallHandler-20](Loader.java:744) - done processing result set (0 rows)

      DEBUG 11439966[RMICallHandler-20](AbstractBatcher.java:433) - about to close ResultSet (open ResultSets: 1, globally: 1)

      DEBUG 11439981[RMICallHandler-20](AbstractBatcher.java:418) - about to close PreparedStatement (open PreparedStatements: 1, globally: 1)

      DEBUG 11439981[RMICallHandler-20](AbstractBatcher.java:562) - closing statement

      DEBUG 11439981[RMICallHandler-20](ConnectionManager.java:427) - aggressively releasing JDBC connection

      DEBUG 11439997[RMICallHandler-20](ConnectionManager.java:464) - releasing JDBC connection [ (open PreparedStatements: 0, globally: 0) (open ResultSets: 0, globally: 0)]

      DEBUG 11439997[RMICallHandler-20](Loader.java:874) - total objects hydrated: 0

      DEBUG 11439997[RMICallHandler-20](StatefulPersistenceContext.java:860) - initializing non-lazy collections

      DEBUG 11440013[RMICallHandler-20](AbstractPlatformTransactionManager.java:922) - Triggering beforeCommit synchronization

      DEBUG 11440013[RMICallHandler-20](AbstractPlatformTransactionManager.java:935) - Triggering beforeCompletion synchronization

      DEBUG 11440013[RMICallHandler-20](AbstractPlatformTransactionManager.java:948) - Triggering afterCommit synchronization

      DEBUG 11440028[RMICallHandler-20](TransactionSynchronizationManager.java:331) - Clearing transaction synchronization

       

      persistence.xml:

      <persistence-unit name="org.jbpm.persistence.jta" transaction-type="JTA">

              <provider>org.hibernate.ejb.HibernatePersistence</provider>

              <jta-data-source>jdbc/HUITDS</jta-data-source>

       

              <!-- Use this if you are using JPA1 / Hibernate3 -->

              <mapping-file>META-INF/JBPMorm.xml</mapping-file>

              <mapping-file>META-INF/ProcessInstanceInfo.hbm.xml</mapping-file>

              <!-- Use this if you are using JPA2 / Hibernate4 -->

              <!--mapping-file>META-INF/JBPMorm-JPA2.xml</mapping-file-->

              <mapping-file>META-INF/Taskorm.xml</mapping-file>

       

              <!-- JBPM classes -->

              <class>org.drools.persistence.info.SessionInfo</class>

              <class>org.jbpm.persistence.processinstance.ProcessInstanceInfo</class>

              <class>org.drools.persistence.info.WorkItemInfo</class>

              <class>org.jbpm.process.audit.ProcessInstanceLog</class>

              <class>org.jbpm.process.audit.NodeInstanceLog</class>

              <class>org.jbpm.process.audit.VariableInstanceLog</class>

       

              <!--  HumanTask classes -->

              <class>org.jbpm.task.Attachment</class>

              <class>org.jbpm.task.Content</class>

              <class>org.jbpm.task.BooleanExpression</class>

              <class>org.jbpm.task.Comment</class>

              <class>org.jbpm.task.Deadline</class>

              <class>org.jbpm.task.Comment</class>

              <class>org.jbpm.task.Deadline</class>

              <class>org.jbpm.task.Delegation</class>

              <class>org.jbpm.task.Escalation</class>

              <class>org.jbpm.task.Group</class>

              <class>org.jbpm.task.I18NText</class>

              <class>org.jbpm.task.Notification</class>

              <class>org.jbpm.task.EmailNotification</class>

              <class>org.jbpm.task.EmailNotificationHeader</class>

              <class>org.jbpm.task.PeopleAssignments</class>

              <class>org.jbpm.task.Reassignment</class>

              <class>org.jbpm.task.Status</class>

              <class>org.jbpm.task.Task</class>

              <class>org.jbpm.task.TaskData</class>

              <class>org.jbpm.task.SubTasksStrategy</class>

              <class>org.jbpm.task.OnParentAbortAllSubTasksEndStrategy</class>

              <class>org.jbpm.task.OnAllSubTasksEndParentEndStrategy</class>

              <class>org.jbpm.task.User</class>

       

              <properties>

                  <property name="hibernate.max_fetch_depth" value="3"/>

       

                  <!-- Do not allow DDL statement at deployment time -->

                  <!--

                  <property name="hibernate.hbm2ddl.auto" value="update"/>

                  -->

       

                  <!--  Show/hide SQL statement at runtime -->

                  <!--

                  <property name="hibernate.show_sql" value="false"/>

                  -->

                  <property name="hibernate.show_sql" value="true"/>

       

                  <!--  Set Oracle dialect for hibernate -->

                  <!--

                  <property name="hibernate.dialect" value="org.hibernate.dialect.H2Dialect"/>

                  -->

                  <property name="hibernate.dialect" value="org.hibernate.dialect.Oracle10gDialect"/>

       

                  <!--  Use OC4J transaction manager -->

                  <!--

                  <property name="hibernate.transaction.manager_lookup_class" value="org.hibernate.transaction.BTMTransactionManagerLookup" />

                  -->

                  <property name="hibernate.transaction.manager_lookup_class" value="org.hibernate.transaction.OC4JTransactionManagerLookup" />

                  <!-- Toplink-related setting: needed since the problem revolved around a conflicting versions of antlr.jar.

                       Hibernate  depends uses version of antlr.jar (antlr 2.7.6 I believe) and Oracle Application Server (OC4J)

                       has a conflicting version of antlr.jar enabled by default in its TopLink library for object-relational mapping

                   -->

                  <property name="hibernate.query.factory_class" value="org.hibernate.hql.classic.ClassicQueryTranslatorFactory" />

       

              </properties>

       

          </persistence-unit>

        • 1. Re: Problem with Human Task query TasksAssignedAsPotentialOwnerByStatus
          Fred DERNBACH Newbie

          I confirm the issue. Oracle does not receive proper binding variable for status list. below is the Oracle trace of the hibernate query. See in bold.

           

          SQL_ID  1fnn7qr6kqp5y, child number 1

          -------------------------------------

          select user2_.id as id175_0_, user1_.id as id175_1_, task0_.id as col_0_0_,

          task0_.processInstanceId as col_1_0_, names5_.text as col_2_0_, subjects3_.text as col_3_0_,

          descriptio4_.text as col_4_0_, task0_.status as col_5_0_, task0_.priority as col_6_0_,

          task0_.skipable as col_7_0_, user2_.id as col_8_0_, user1_.id as col_9_0_, task0_.createdOn as

          col_10_0_, task0_.activationTime as col_11_0_, task0_.expirationTime as col_12_0_,

          task0_.processId as col_13_0_, task0_.processSessionId as col_14_0_ from Task task0_ left outer

          join OrganizationalEntity user1_ on task0_.createdBy_id=user1_.id left outer join

          OrganizationalEntity user2_ on task0_.actualOwner_id=user2_.id left outer join I18NText

          subjects3_ on task0_.id=subjects3_.Task_Subjects_Id left outer join I18NText descriptio4_ on

          task0_.id=descriptio4_.Task_Descriptions_Id left outer join I18NText names5_ on

          task0_.id=names5_.Task_Names_Id, OrganizationalEntity organizati6_ where (task0_.archived=0

          )and((organizati6_.id=:1 )or(organi

           

          Plan hash value: 2720357931

           

          ---------------------------------------------------------------------------------------------------------

          | Id  | Operation                 | Name                        | Rows  | Bytes | Cost (%CPU)| Time     |

          ---------------------------------------------------------------------------------------------------------

          |   0 | SELECT STATEMENT          |                             |       |       |    40 (100)|          |

          |*  1 |  FILTER                   |                             |       |       |            |          |

          |*  2 |   HASH JOIN OUTER         |                             |     1 |  7594 |    40   (5)| 00:00:04 |

          |*  3 |    HASH JOIN OUTER        |                             |     1 |  5450 |    33   (7)| 00:00:04 |

          |*  4 |     HASH JOIN OUTER       |                             |     1 |  3306 |    25   (4)| 00:00:03 |

          |   5 |      NESTED LOOPS         |                             |     1 |  1162 |    18   (6)| 00:00:02 |

          |*  6 |       HASH JOIN           |                             |     1 |  1033 |    17   (6)| 00:00:02 |

          |   7 |        NESTED LOOPS OUTER |                             |     1 |   891 |     9   (0)| 00:00:01 |

          |   8 |         NESTED LOOPS OUTER|                             |     1 |   762 |     8   (0)| 00:00:01 |

          |*  9 |          TABLE ACCESS FULL| TASK                        |     1 |   633 |     7   (0)| 00:00:01 |

          |* 10 |          INDEX UNIQUE SCAN| SYS_C00144479               |     1 |   129 |     1   (0)| 00:00:01 |

          |* 11 |         INDEX UNIQUE SCAN | SYS_C00144479               |     1 |   129 |     1   (0)| 00:00:01 |

          |  12 |        SORT UNIQUE        |                             |    10 |  1420 |     7   (0)| 00:00:01 |

          |* 13 |         TABLE ACCESS FULL | PEOPLEASSIGNMENTS_POTOWNERS |    10 |  1420 |     7   (0)| 00:00:01 |

          |* 14 |       INDEX UNIQUE SCAN   | SYS_C00144479               |     1 |   129 |     1   (0)| 00:00:01 |

          |  15 |      TABLE ACCESS FULL    | I18NTEXT                    |    30 | 64320 |     7   (0)| 00:00:01 |

          |  16 |     TABLE ACCESS FULL     | I18NTEXT                    |    30 | 64320 |     7   (0)| 00:00:01 |

          |  17 |    TABLE ACCESS FULL      | I18NTEXT                    |    30 | 64320 |     7   (0)| 00:00:01 |

          |  18 |   SORT AGGREGATE          |                             |     1 |    13 |            |          |

          |* 19 |    TABLE ACCESS FULL      | I18NTEXT                    |     1 |    13 |     7   (0)| 00:00:01 |

          |  20 |     SORT AGGREGATE        |                             |     1 |    13 |            |          |

          |* 21 |      TABLE ACCESS FULL    | I18NTEXT                    |     1 |    13 |     7   (0)| 00:00:01 |

          |  22 |       SORT AGGREGATE      |                             |     1 |    13 |            |          |

          |* 23 |        TABLE ACCESS FULL  | I18NTEXT                    |     1 |    13 |     7   (0)| 00:00:01 |

          ---------------------------------------------------------------------------------------------------------

           

          Query Block Name / Object Alias (identified by operation id):

          -------------------------------------------------------------

           

             1 - SEL$1B69B172

             9 - SEL$1B69B172 / TASK0_@SEL$2

            10 - SEL$1B69B172 / USER2_@SEL$3

            11 - SEL$1B69B172 / USER1_@SEL$1

            13 - SEL$1B69B172 / POTENTIALO7_@SEL$12

            14 - SEL$1B69B172 / ORGANIZATI6_@SEL$11

            15 - SEL$1B69B172 / NAMES5_@SEL$9

            16 - SEL$1B69B172 / DESCRIPTIO4_@SEL$7

            17 - SEL$1B69B172 / SUBJECTS3_@SEL$5

            18 - SEL$13     

            19 - SEL$13       / NAMES8_@SEL$13

            20 - SEL$14     

            21 - SEL$14       / SUBJECTS9_@SEL$14

            22 - SEL$15     

            23 - SEL$15       / DESCRIPTIO10_@SEL$15

           

          Outline Data

          -------------

           

            /*+

                BEGIN_OUTLINE_DATA

                IGNORE_OPTIM_EMBEDDED_HINTS

                OPTIMIZER_FEATURES_ENABLE('10.2.0.4')

                OPT_PARAM('_optimizer_connect_by_cost_based' 'false')

                OPT_PARAM('star_transformation_enabled' 'temp_disable')

                OPT_PARAM('optimizer_index_caching' 50)

                ALL_ROWS

                OUTLINE_LEAF(@"SEL$13")

                OUTLINE_LEAF(@"SEL$14")

                OUTLINE_LEAF(@"SEL$15")

                OUTLINE_LEAF(@"SEL$1B69B172")

                UNNEST(@"SEL$12")

                OUTLINE(@"SEL$13")

                OUTLINE(@"SEL$14")

                OUTLINE(@"SEL$15")

                OUTLINE(@"SEL$48266A83")

                MERGE(@"SEL$B97648DD")

                OUTLINE(@"SEL$12")

                OUTLINE(@"SEL$11")

                OUTLINE(@"SEL$B97648DD")

                MERGE(@"SEL$096E5AED")

                MERGE(@"SEL$9")

                OUTLINE(@"SEL$10")

                OUTLINE(@"SEL$096E5AED")

                MERGE(@"SEL$15E987C1")

                MERGE(@"SEL$7")

                OUTLINE(@"SEL$9")

                OUTLINE(@"SEL$8")

                OUTLINE(@"SEL$15E987C1")

                MERGE(@"SEL$5")

                MERGE(@"SEL$7237DA6D")

                OUTLINE(@"SEL$7")

                OUTLINE(@"SEL$6")

                OUTLINE(@"SEL$5")

                OUTLINE(@"SEL$7237DA6D")

                MERGE(@"SEL$3")

                MERGE(@"SEL$58A6D7F6")

                OUTLINE(@"SEL$4")

                OUTLINE(@"SEL$3")

                OUTLINE(@"SEL$58A6D7F6")

                MERGE(@"SEL$1")

                OUTLINE(@"SEL$2")

                OUTLINE(@"SEL$1")

                FULL(@"SEL$1B69B172" "TASK0_"@"SEL$2")

                INDEX(@"SEL$1B69B172" "USER2_"@"SEL$3" ("ORGANIZATIONALENTITY"."ID"))

                INDEX(@"SEL$1B69B172" "USER1_"@"SEL$1" ("ORGANIZATIONALENTITY"."ID"))

                FULL(@"SEL$1B69B172" "POTENTIALO7_"@"SEL$12")

                INDEX(@"SEL$1B69B172" "ORGANIZATI6_"@"SEL$11" ("ORGANIZATIONALENTITY"."ID"))

                FULL(@"SEL$1B69B172" "NAMES5_"@"SEL$9")

                FULL(@"SEL$1B69B172" "DESCRIPTIO4_"@"SEL$7")

                FULL(@"SEL$1B69B172" "SUBJECTS3_"@"SEL$5")

                LEADING(@"SEL$1B69B172" "TASK0_"@"SEL$2" "USER2_"@"SEL$3" "USER1_"@"SEL$1"

                        "POTENTIALO7_"@"SEL$12" "ORGANIZATI6_"@"SEL$11" "NAMES5_"@"SEL$9" "DESCRIPTIO4_"@"SEL$7"

                        "SUBJECTS3_"@"SEL$5")

                USE_NL(@"SEL$1B69B172" "USER2_"@"SEL$3")

                USE_NL(@"SEL$1B69B172" "USER1_"@"SEL$1")

                USE_HASH(@"SEL$1B69B172" "POTENTIALO7_"@"SEL$12")

                USE_NL(@"SEL$1B69B172" "ORGANIZATI6_"@"SEL$11")

                USE_HASH(@"SEL$1B69B172" "NAMES5_"@"SEL$9")

                USE_HASH(@"SEL$1B69B172" "DESCRIPTIO4_"@"SEL$7")

                USE_HASH(@"SEL$1B69B172" "SUBJECTS3_"@"SEL$5")

                FULL(@"SEL$15" "DESCRIPTIO10_"@"SEL$15")

                FULL(@"SEL$14" "SUBJECTS9_"@"SEL$14")

                FULL(@"SEL$13" "NAMES8_"@"SEL$13")

                END_OUTLINE_DATA

            */

           

          Peeked Binds (identified by position):

          --------------------------------------

           

             1 - :1 (VARCHAR2(30), CSID=46): 'ITCH'

             2 - :2 (VARCHAR2(30), CSID=46): 'SECURITY_GLOBAL'

             3 - :3 (VARCHAR2(30), CSID=46): 'SECURITY_AUTHORITY'

             7 - :7 (RAW(30)): aced00057e7200146f72672e6a62706d2e7461736b2e537461747573000000000000000012000078720

                 00e6a6176612e6c616e672e456e756d000000000000000012000078707400055265616479

           

          Predicate Information (identified by operation id):

          ---------------------------------------------------

           

             1 - filter((("NAMES5_"."LANGUAGE"=:4 OR =0) AND ("SUBJECTS3_"."LANGUAGE"=:5 OR =0) AND

                        ("DESCRIPTIO4_"."LANGUAGE"=:6 OR =0)))

             2 - access("TASK0_"."ID"="SUBJECTS3_"."TASK_SUBJECTS_ID")

             3 - access("TASK0_"."ID"="DESCRIPTIO4_"."TASK_DESCRIPTIONS_ID")

             4 - access("TASK0_"."ID"="NAMES5_"."TASK_NAMES_ID")

             6 - access("TASK0_"."ID"="POTENTIALO7_"."TASK_ID")

             9 - filter(("TASK0_"."EXPIRATIONTIME" IS NULL AND "TASK0_"."ARCHIVED"=0 AND

                        "TASK0_"."STATUS"=RAWTOHEX(:7)))

            10 - access("TASK0_"."ACTUALOWNER_ID"="USER2_"."ID")

            11 - access("TASK0_"."CREATEDBY_ID"="USER1_"."ID")

            13 - filter(("POTENTIALO7_"."ENTITY_ID"=:1 OR "POTENTIALO7_"."ENTITY_ID"=:2 OR

                        "POTENTIALO7_"."ENTITY_ID"=:3))

            14 - access("ORGANIZATI6_"."ID"="POTENTIALO7_"."ENTITY_ID")

                 filter(("ORGANIZATI6_"."ID"=:1 OR "ORGANIZATI6_"."ID"=:2 OR "ORGANIZATI6_"."ID"=:3))

            19 - filter("NAMES8_"."TASK_NAMES_ID"=:B1)

            21 - filter("SUBJECTS9_"."TASK_SUBJECTS_ID"=:B1)

            23 - filter("DESCRIPTIO10_"."TASK_DESCRIPTIONS_ID"=:B1)

           

          Column Projection Information (identified by operation id):

          -----------------------------------------------------------

           

             1 - "TASK0_"."ID"[NUMBER,22], "DESCRIPTIO4_"."TEXT"[LOB,4000], "NAMES5_"."TEXT"[LOB,4000],

                 "USER1_"."ID"[VARCHAR2,255], "TASK0_"."PRIORITY"[NUMBER,22],

                 "TASK0_"."ACTIVATIONTIME"[TIMESTAMP,11], "TASK0_"."CREATEDON"[TIMESTAMP,11],

                 "TASK0_"."EXPIRATIONTIME"[TIMESTAMP,11], "TASK0_"."PROCESSID"[VARCHAR2,255],

                 "TASK0_"."PROCESSINSTANCEID"[NUMBER,22], "TASK0_"."PROCESSSESSIONID"[NUMBER,22],

                 "TASK0_"."SKIPABLE"[NUMBER,22], "TASK0_"."STATUS"[VARCHAR2,255], "USER2_"."ID"[VARCHAR2,255],

                 "SUBJECTS3_"."TEXT"[LOB,4000]

             2 - (#keys=1) "TASK0_"."ID"[NUMBER,22], "DESCRIPTIO4_"."TEXT"[LOB,4000],

                 "NAMES5_"."TEXT"[LOB,4000], "USER1_"."ID"[VARCHAR2,255], "TASK0_"."PRIORITY"[NUMBER,22],

                 "TASK0_"."ACTIVATIONTIME"[TIMESTAMP,11], "TASK0_"."CREATEDON"[TIMESTAMP,11],

                 "TASK0_"."EXPIRATIONTIME"[TIMESTAMP,11], "TASK0_"."PROCESSID"[VARCHAR2,255],

                 "TASK0_"."PROCESSINSTANCEID"[NUMBER,22], "TASK0_"."PROCESSSESSIONID"[NUMBER,22],

                 "TASK0_"."SKIPABLE"[NUMBER,22], "TASK0_"."STATUS"[VARCHAR2,255], "USER2_"."ID"[VARCHAR2,255],

                 "NAMES5_"."LANGUAGE"[VARCHAR2,255], "DESCRIPTIO4_"."LANGUAGE"[VARCHAR2,255],

                 "SUBJECTS3_"."LANGUAGE"[VARCHAR2,255], "SUBJECTS3_"."TEXT"[LOB,4000]

             3 - (#keys=1) "TASK0_"."ID"[NUMBER,22], "DESCRIPTIO4_"."TASK_DESCRIPTIONS_ID"[NUMBER,22],

                 "NAMES5_"."TEXT"[LOB,4000], "NAMES5_"."TASK_NAMES_ID"[NUMBER,22],

                 "ORGANIZATI6_"."ID"[VARCHAR2,255], "POTENTIALO7_"."TASK_ID"[NUMBER,22],

                 "USER1_"."ID"[VARCHAR2,255], "TASK0_"."ARCHIVED"[NUMBER,22], "TASK0_"."PRIORITY"[NUMBER,22],

                 "TASK0_"."ACTIVATIONTIME"[TIMESTAMP,11], "TASK0_"."CREATEDON"[TIMESTAMP,11],

                 "TASK0_"."EXPIRATIONTIME"[TIMESTAMP,11], "TASK0_"."PROCESSID"[VARCHAR2,255],

                 "TASK0_"."PROCESSINSTANCEID"[NUMBER,22], "TASK0_"."PROCESSSESSIONID"[NUMBER,22],

                 "TASK0_"."SKIPABLE"[NUMBER,22], "TASK0_"."STATUS"[VARCHAR2,255],

                 "TASK0_"."ACTUALOWNER_ID"[VARCHAR2,255], "TASK0_"."CREATEDBY_ID"[VARCHAR2,255],

                 "USER2_".ROWID[ROWID,10], "USER2_"."ID"[VARCHAR2,255], "USER1_".ROWID[ROWID,10],

                 "POTENTIALO7_"."ENTITY_ID"[VARCHAR2,255], "ORGANIZATI6_".ROWID[ROWID,10],

                 "NAMES5_"."LANGUAGE"[VARCHAR2,255], "DESCRIPTIO4_"."LANGUAGE"[VARCHAR2,255],

                 "DESCRIPTIO4_"."TEXT"[LOB,4000]

             4 - (#keys=1) "TASK0_"."ID"[NUMBER,22], "NAMES5_"."TASK_NAMES_ID"[NUMBER,22],

                 "ORGANIZATI6_"."ID"[VARCHAR2,255], "POTENTIALO7_"."TASK_ID"[NUMBER,22],

                 "USER1_"."ID"[VARCHAR2,255], "TASK0_"."ARCHIVED"[NUMBER,22], "TASK0_"."PRIORITY"[NUMBER,22],

                 "TASK0_"."ACTIVATIONTIME"[TIMESTAMP,11], "TASK0_"."CREATEDON"[TIMESTAMP,11],

                 "TASK0_"."EXPIRATIONTIME"[TIMESTAMP,11], "TASK0_"."PROCESSID"[VARCHAR2,255],

                 "TASK0_"."PROCESSINSTANCEID"[NUMBER,22], "TASK0_"."PROCESSSESSIONID"[NUMBER,22],

                 "TASK0_"."SKIPABLE"[NUMBER,22], "TASK0_"."STATUS"[VARCHAR2,255],

                 "TASK0_"."ACTUALOWNER_ID"[VARCHAR2,255], "TASK0_"."CREATEDBY_ID"[VARCHAR2,255],

                 "USER2_".ROWID[ROWID,10], "USER2_"."ID"[VARCHAR2,255], "USER1_".ROWID[ROWID,10],

                 "POTENTIALO7_"."ENTITY_ID"[VARCHAR2,255], "ORGANIZATI6_".ROWID[ROWID,10],

                 "NAMES5_"."LANGUAGE"[VARCHAR2,255], "NAMES5_"."TEXT"[LOB,4000]

             5 - "TASK0_"."ID"[NUMBER,22], "POTENTIALO7_"."TASK_ID"[NUMBER,22],

                 "USER1_"."ID"[VARCHAR2,255], "TASK0_"."ARCHIVED"[NUMBER,22], "TASK0_"."PRIORITY"[NUMBER,22],

                 "TASK0_"."ACTIVATIONTIME"[TIMESTAMP,11], "TASK0_"."CREATEDON"[TIMESTAMP,11],

                 "TASK0_"."EXPIRATIONTIME"[TIMESTAMP,11], "TASK0_"."PROCESSID"[VARCHAR2,255],

                 "TASK0_"."PROCESSINSTANCEID"[NUMBER,22], "TASK0_"."PROCESSSESSIONID"[NUMBER,22],

                 "TASK0_"."SKIPABLE"[NUMBER,22], "TASK0_"."STATUS"[VARCHAR2,255],

                 "TASK0_"."ACTUALOWNER_ID"[VARCHAR2,255], "TASK0_"."CREATEDBY_ID"[VARCHAR2,255],

                 "USER2_".ROWID[ROWID,10], "USER2_"."ID"[VARCHAR2,255], "USER1_".ROWID[ROWID,10],

                 "POTENTIALO7_"."ENTITY_ID"[VARCHAR2,255], "ORGANIZATI6_".ROWID[ROWID,10],

                 "ORGANIZATI6_"."ID"[VARCHAR2,255]

             6 - (#keys=1) "TASK0_"."ID"[NUMBER,22], "POTENTIALO7_"."TASK_ID"[NUMBER,22],

                 "USER1_"."ID"[VARCHAR2,255], "TASK0_"."ARCHIVED"[NUMBER,22], "TASK0_"."PRIORITY"[NUMBER,22],

                 "TASK0_"."ACTIVATIONTIME"[TIMESTAMP,11], "TASK0_"."CREATEDON"[TIMESTAMP,11],

                 "TASK0_"."EXPIRATIONTIME"[TIMESTAMP,11], "TASK0_"."PROCESSID"[VARCHAR2,255],

                 "TASK0_"."PROCESSINSTANCEID"[NUMBER,22], "TASK0_"."PROCESSSESSIONID"[NUMBER,22],

                 "TASK0_"."SKIPABLE"[NUMBER,22], "TASK0_"."STATUS"[VARCHAR2,255],

                 "TASK0_"."ACTUALOWNER_ID"[VARCHAR2,255], "TASK0_"."CREATEDBY_ID"[VARCHAR2,255],

                 "USER2_".ROWID[ROWID,10], "USER2_"."ID"[VARCHAR2,255], "USER1_".ROWID[ROWID,10],

                 "POTENTIALO7_"."ENTITY_ID"[VARCHAR2,255]

             7 - "TASK0_"."ID"[NUMBER,22], "TASK0_"."ARCHIVED"[NUMBER,22], "TASK0_"."PRIORITY"[NUMBER,22],

                 "TASK0_"."ACTIVATIONTIME"[TIMESTAMP,11], "TASK0_"."CREATEDON"[TIMESTAMP,11],

                 "TASK0_"."EXPIRATIONTIME"[TIMESTAMP,11], "TASK0_"."PROCESSID"[VARCHAR2,255],

                 "TASK0_"."PROCESSINSTANCEID"[NUMBER,22], "TASK0_"."PROCESSSESSIONID"[NUMBER,22],

                 "TASK0_"."SKIPABLE"[NUMBER,22], "TASK0_"."STATUS"[VARCHAR2,255],

                 "TASK0_"."ACTUALOWNER_ID"[VARCHAR2,255], "TASK0_"."CREATEDBY_ID"[VARCHAR2,255],

                 "USER2_".ROWID[ROWID,10], "USER2_"."ID"[VARCHAR2,255], "USER1_".ROWID[ROWID,10],

                 "USER1_"."ID"[VARCHAR2,255]

             8 - "TASK0_"."ID"[NUMBER,22], "TASK0_"."ARCHIVED"[NUMBER,22], "TASK0_"."PRIORITY"[NUMBER,22],

                 "TASK0_"."ACTIVATIONTIME"[TIMESTAMP,11], "TASK0_"."CREATEDON"[TIMESTAMP,11],

                 "TASK0_"."EXPIRATIONTIME"[TIMESTAMP,11], "TASK0_"."PROCESSID"[VARCHAR2,255],

                 "TASK0_"."PROCESSINSTANCEID"[NUMBER,22], "TASK0_"."PROCESSSESSIONID"[NUMBER,22],

                 "TASK0_"."SKIPABLE"[NUMBER,22], "TASK0_"."STATUS"[VARCHAR2,255],

                 "TASK0_"."ACTUALOWNER_ID"[VARCHAR2,255], "TASK0_"."CREATEDBY_ID"[VARCHAR2,255],

                 "USER2_".ROWID[ROWID,10], "USER2_"."ID"[VARCHAR2,255]

             9 - "TASK0_"."ID"[NUMBER,22], "TASK0_"."ARCHIVED"[NUMBER,22], "TASK0_"."PRIORITY"[NUMBER,22],

                 "TASK0_"."ACTIVATIONTIME"[TIMESTAMP,11], "TASK0_"."CREATEDON"[TIMESTAMP,11],

                 "TASK0_"."EXPIRATIONTIME"[TIMESTAMP,11], "TASK0_"."PROCESSID"[VARCHAR2,255],

                 "TASK0_"."PROCESSINSTANCEID"[NUMBER,22], "TASK0_"."PROCESSSESSIONID"[NUMBER,22],

                 "TASK0_"."SKIPABLE"[NUMBER,22], "TASK0_"."STATUS"[VARCHAR2,255],

                 "TASK0_"."ACTUALOWNER_ID"[VARCHAR2,255], "TASK0_"."CREATEDBY_ID"[VARCHAR2,255]

            10 - "USER2_".ROWID[ROWID,10], "USER2_"."ID"[VARCHAR2,255]

            11 - "USER1_".ROWID[ROWID,10], "USER1_"."ID"[VARCHAR2,255]

            12 - (#keys=2) "POTENTIALO7_"."TASK_ID"[NUMBER,22], "POTENTIALO7_"."ENTITY_ID"[VARCHAR2,255]

            13 - "POTENTIALO7_"."TASK_ID"[NUMBER,22], "POTENTIALO7_"."ENTITY_ID"[VARCHAR2,255]

            14 - "ORGANIZATI6_".ROWID[ROWID,10], "ORGANIZATI6_"."ID"[VARCHAR2,255]

            15 - "NAMES5_"."LANGUAGE"[VARCHAR2,255], "NAMES5_"."TEXT"[LOB,4000],

                 "NAMES5_"."TASK_NAMES_ID"[NUMBER,22]

            16 - "DESCRIPTIO4_"."LANGUAGE"[VARCHAR2,255], "DESCRIPTIO4_"."TEXT"[LOB,4000],

                 "DESCRIPTIO4_"."TASK_DESCRIPTIONS_ID"[NUMBER,22]

            17 - "SUBJECTS3_"."LANGUAGE"[VARCHAR2,255], "SUBJECTS3_"."TEXT"[LOB,4000],

                 "SUBJECTS3_"."TASK_SUBJECTS_ID"[NUMBER,22]

            18 - (#keys=0) COUNT("NAMES8_"."TASK_NAMES_ID")[22]

            19 - "NAMES8_"."TASK_NAMES_ID"[NUMBER,22]

            20 - (#keys=0) COUNT("SUBJECTS9_"."TASK_SUBJECTS_ID")[22]

            21 - "SUBJECTS9_"."TASK_SUBJECTS_ID"[NUMBER,22]

            22 - (#keys=0) COUNT("DESCRIPTIO10_"."TASK_DESCRIPTIONS_ID")[22]

            23 - "DESCRIPTIO10_"."TASK_DESCRIPTIONS_ID"[NUMBER,22]

           

          Note

          -----

             - dynamic sampling used for this statement

           

             

              Subject

              Requete Task