Aron's Tech Blog

I don’t run across this very often but I’m going to start referencing errors here for my reference, this information is strait from MOS support doc (1047894.1)

Applies to:
Oracle Taleo Platform Cloud Service – Connect – Version 12A and later
Information in this document applies to any platform.

Purpose
ORA-00932: inconsistent datatypes: expected – got CLOB
ORA-00932: inconsistent datatypes: expected – got BLOB

These errors are raised when an export file has the Prevent Duplicates flag checked and include a projection for a field that is stored as a BLOB or CLOB in the database.

The BLOB and CLOB type fields include:

JobInformation,DescriptionExternalHTML
JobInformation,DescriptionInternalHTML
JobInformation,ExternalQualificationHTML
JobInformation,InternalQualificationHTML
CandidateAttachedFile,Content

Troubleshooting Steps
To resolve the issue, use one of the following solutions:

Uncheck the Prevent Duplicates flag in the selection query (sq.xml) file.
Remove the problematic projections from the export file.
Apply the To char function to the projection.

I never can remember the format for putting it in manually and I’m usually pulling it from the XML created on the LRD out of the profiler. So here is the between to use as a complex filter so that TCC will translate it for you.

<quer:filtering xmlns:quer="http://www.taleo.com/ws/integration/query">
	<quer:between>
		<quer:field path="Process,Steps,EndDate"/>
		<quer:date>2016-01-12T07:45:06-05:00</quer:date>
		<quer:date>2016-01-13T07:45:06-05:00</quer:date>
	</quer:between>
</quer:filtering>

So there are times when you want to use not like to eliminate some elements from a query. A prime example is one I’m looking at now, I’m trying to pull all the folks with an application to a list of requisitions to post them to new requisitions I’ve created, but a lot of them that I’m looking at have ‘Candidate Rejected New’ or ‘Candidate Rejected Pre’.
In oracle you can just use not like ‘%rejected%’ but that’s not really an option with TCC, you can however use contains strait out of the box so it’s pretty easy to say contains ‘reject’, if you take this and use a not on either side, your using (does) not contain(s) and it looks a little something like this:

<quer:filtering xmlns:quer="http://www.taleo.com/ws/integration/query">
  <quer:not>
  <quer:containsIgnoreCase>
	<quer:field path="CSWLatestStatus,Mnemonic"/>
	<quer:string>reject</quer:string>
  </quer:containsIgnoreCase>
  </quer:not>
</quer:filtering>

Ran across this for SSO setup and thought that it may be useful here.

0 Your user permissions do not authorize you to access this page.,
1 The user name and password combination you entered does not correspond to a registered user.,
2 The password you have entered is invalid.,
3 Your account has been locked. Please contact your System Administrator.,
4 Your account has been deleted. For further details, please contact your System Administrator.,
5 Your account has expired. Please contact your System Administrator.,
6 Invalid assertion.,
7 Please provide a user name and a password.,
8 You cannot access this product since you belong to no group. Please contact your System Administrator.,
9 Invalid answer to secret question.,
10 Your account has been deactivated. Please contact your System Administrator to reactivate it.,
11 Your password is expired, default message

So more of the fun of projecting candidate answers to diversity questions is actually figuring out what the form, question and answers can be. So to make our lives easier here is the export file to glean this information.

Create an empty file named: DiversityPossibleAnswerExport_sq.xml

Copy this information into the file, build the config and run and all questions (and answers) will be revealed.

<?xml version="1.0" encoding="UTF-8"?>
<quer:query productCode="RC1401" model="http://www.taleo.com/ws/tee800/2009/01" projectedClass="DiversityPossibleAnswer" locale="en" mode="CSV" csvheader="true" csvdelimiter="|" csvquote="&quot;" largegraph="true" preventDuplicates="false" xmlns:quer="http://www.taleo.com/ws/integration/query"><quer:subQueries/><quer:projections><quer:projection><quer:field path="Question,DiversityQuestion.RegulationForm,Number"/></quer:projection><quer:projection><quer:field path="Question,DiversityQuestion.RegulationForm,Code"/></quer:projection><quer:projection><quer:field path="Question,DiversityQuestion.RegulationForm,Name"/></quer:projection><quer:projection><quer:field path="Question,DiversityQuestion.RegulationForm,Active"/></quer:projection><quer:projection><quer:field path="Question,DiversityQuestion.RegulationForm,Status,Code"/></quer:projection><quer:projection><quer:field path="Question,Number"/></quer:projection><quer:projection><quer:field path="Question,Code"/></quer:projection><quer:projection><quer:field path="Question,Name"/></quer:projection><quer:projection><quer:field path="Question,Active"/></quer:projection><quer:projection><quer:field path="Question,Status,Code"/></quer:projection><quer:projection><quer:field path="Number"/></quer:projection><quer:projection><quer:field path="Description"/></quer:projection></quer:projections><quer:projectionFilterings/><quer:filterings/><quer:sortings/><quer:sortingFilterings/><quer:groupings/><quer:joinings/></quer:query>

Sometimes you get to export a field where someone could have placed CR/LF’s, which is an enter in a text area sending carriage return line feed. This is the replace statement that will removed it and replace it with a blank:

<quer:projection alias="OfferNotes" projectedValueType="string" xmlns:quer="http://www.taleo.com/ws/integration/query">
  <quer:replace>
    <quer:replace>
      <quer:field path="CurrentOffer,Notes"/>
      <quer:customFunction name="CHR">
        <quer:integer>10</quer:integer>
      </quer:customFunction>
      <quer:string/>
    </quer:replace>
    <quer:customFunction name="CHR">
      <quer:integer>13</quer:integer>
    </quer:customFunction>
    <quer:string/>
  </quer:replace>
</quer:projection>

In advanced export options:
Check Prevent Duplicates
Add pageindex with a value of 1 for the first page, 2 for the second, etc.
Add pagingsize with a value of the number of records to be returned.

Saw this on the KX and thought it would serve me well one day (Doc ID 1045362.1)

One of the more troublesome aspects of EEO is trying to figure out what a candidate entered as it’s masked on the front end and it’s really hard to tell.

Create a file called CandidateDiversityAnswers_sq.xml

Copy and paste the following to get everything that they’ve entered for EEO (please note this is from the standard EEO questions, not custom):

<?xml version="1.0" encoding="UTF-8"?>
<quer:query productCode="RC1401" model="http://www.taleo.com/ws/tee800/2009/01" projectedClass="Candidate" locale="en" mode="CSV" csvheader="true" csvdelimiter="|" csvquote="&quot;" largegraph="true" preventDuplicates="false" xmlns:quer="http://www.taleo.com/ws/integration/query"><quer:subQueries/><quer:projections><quer:projection><quer:field path="Number"/></quer:projection><quer:projection><quer:field path="FirstName"/></quer:projection><quer:projection><quer:field path="LastName"/></quer:projection><quer:projection><quer:field path="DiversityAnswers,Question,DiversityQuestion.RegulationForm,Number"/></quer:projection><quer:projection><quer:field path="DiversityAnswers,Question,DiversityQuestion.RegulationForm,Code"/></quer:projection><quer:projection><quer:field path="DiversityAnswers,Question,DiversityQuestion.RegulationForm,Name"/></quer:projection><quer:projection><quer:field path="DiversityAnswers,Question,Number"/></quer:projection><quer:projection><quer:field path="DiversityAnswers,Question,Code"/></quer:projection><quer:projection><quer:field path="DiversityAnswers,Question,Name"/></quer:projection><quer:projection alias="CreationDate" projectedValueType="string" projectedField="DiversityAnswers,RuntimeString:CreationDateFormatted"><quer:customFunction name="TO_CHAR"><quer:field path="DiversityAnswers,CreationDate"/><quer:string>yyyy-MM-dd</quer:string></quer:customFunction></quer:projection><quer:projection><quer:field path="DiversityAnswers,PossibleAnswer,Number"/></quer:projection><quer:projection><quer:field path="DiversityAnswers,PossibleAnswer,Sequence"/></quer:projection><quer:projection><quer:field path="DiversityAnswers,PossibleAnswer,Description"/></quer:projection></quer:projections><quer:projectionFilterings/><quer:filterings><quer:filtering><quer:equal><quer:field path="Number"/><quer:long>348272</quer:long></quer:equal></quer:filtering></quer:filterings><quer:sortings/><quer:sortingFilterings/><quer:groupings/><quer:joinings/></quer:query>