Executing the Query

This section provides an overview of the XML string and discusses the service operations to execute a query. These include:

  • SOAP based service operation:

    • QAS_EXECUTEQRYSYNC_OPER

    • QAS_EXECUTEQRYSYNCPOLL_OPER

    • QAS_EXECUTEQRYASYNC_OPER

    • QAS_EXECUTE_CPQ_OPER

  • RESTful based service operation:

    • QAS_EXECUTEQRY_REST_GET

    • QAS_EXECUTEQRY_REST_POST

    • QAS_EXECUTEQRYPOLL_REST_GET

    • QAS_EXECUTEQRYPOLL_REST_POST

When you request to execute a query, you can either select the query name if it exists in the database, or use the XML string to request an adhoc query. The same elements are used to define the XML string as defining the query to save. If you use XML string, it must contain at least one record, one field and one select.

<Records> Request must contain at least one record.

Records Elements

Description

RCDNUM

Record number.

RCDNAME

Record name.

RCDSELECTNUM

Select number.

CORRNAME

Alias Name such as A,B,C, and so on.

JOINTYPE

Type of join. Valid values are:

  • None

  • Hierarchy

  • Related

  • Tree

  • LeftOuter

  • RelatedLeftOuter

Value is required only when the corresponding record is part of a JOIN in the query.

JOINRCDALIAS

Record with which join is done.

Value is required only when the corresponding record is part of a JOIN in the query.

JOINFIELD

Field with which join is done.

Value is required only when the corresponding record is part of a JOIN in the query.

<Fields> Request must contain at least one field.

Fields Elements

Description

FIELDNUM

Field number.

FIELDNAME

Field name.

FIELDRCDNUM

Record number.

DESCR

Description.

AGGREGRATE_TYPE

Aggregation type.

Note: For valid values, see QAS_SAVE_QUERY_OPER.

HEADING_TYPE

Heading type.

Note: For valid values, see QAS_SAVE_QUERY_OPER.

HEADING

If the HEADING_TYPE is Heading, this element is used to enter the heading text.

COLUMNNUM

Column number.

ORDERBYNUM

Order by number.

ORDERBYDIR

Direction of field ordering.

Note: For valid values, see QAS_SAVE_QUERY_OPER.

FIELDSELECTNUM

Identifier of SELECT in which this field is included.

EXPRESSION_AS_FIELD

A valid expression number when an expression is used as a field.

<Criteria> Enter criteria information.

Criteria Elements

Description

CRTNUM

Criterion number.

CRTNAME

Criterion name.

CRTHAVINGFLAG

True if specifying a HAVING clause.

Note: For valid values, see QAS_SAVE_QUERY_OPER.

CRTSELECTNUM

Selectnumber.

CRTNEGATION

Negation in criterion.

Note: For valid values, see QAS_SAVE_QUERY_OPER.

CONDITION_TYPE

Condition type in criterion.

Note: For valid values, see QAS_SAVE_QUERY_OPER.

LEFT_PARENTHESIS_LEVEL

Left parenthesis level specified from 0 onwards. Used for GROUP BY.

RIGHT_PARENTHESIS_LEVEL

Right parenthesis level specified from 0 onwards. Used for GROUP BY.

CRTEXP1TYPE

Expression 1 type in criterion.

CRTEXP1TEXT

Expression text for criterion 1.

CRTEXP1RCDALIAS

Record alias for record used in expression 1 in criterion.

CRTEXP1FIELD

Field used in expression 1 for criterion.

CRTEXP2RCDALIAS

Record alias for record used in expression 2 for criterion.

CRTEXP2FIELD

Field used in expression 2 for criterion.

CRTEXP2TYPE

Expression 2 type in criterion.

Note: For valid values, see QAS_SAVE_QUERY_OPER.

CRTEXP2TEXT

Expression 2 text.

CRTLOGICALOPER

Logical operator that links the criteria.

Note: For valid values, see QAS_SAVE_QUERY_OPER.

<Expressions> Enter expression information.

Expressions Elements

Description

EXPNUM

Expression Number.

EXPSELECTNUM

Expression's SELECT Number.

EXPNAME

Expression Name.

EXPTYPE

Expression Type.

Note: For valid values, see QAS_SAVE_QUERY_OPER.

EXPLENGTH

Expression Length.

EXPDECIMALPOS

Number of Decimal Places.

EXPTEXT

Expression Text.

EXPAGGREGATE

Specifies if the expression is an aggregate function.

<Prompts> Enter prompt information.

Prompt Elements

Description

PROMPT_NUM

Prompt number.

PROMPT_NAME

Unique prompt name.

This is the UniquePromptName value returned from the service operation QAS_LISTQUERYPROMPTS_OPER.

PROMPT_FLDNAME

Name of the field used as prompt.

PROMPT_UNIQUE_NAME

Unique prompt name.

PROMPT_TABLE

Prompt table

PROMPT_EDITTYPE

Edit type.

Note: For valid values, see QAS_SAVE_QUERY_OPER.

PROMPT_HEADING

Heading of prompt.

PROMPT_HEADINGTYPE

Type of prompt heading.

Note: For valid values, see QAS_SAVE_QUERY_OPER.

PROMPT_FLDLENGTH

Field length for prompt.

PROMPT_FLDDECIMALPOS

Number of decimal positions in prompt.

<Select> XML String must include at least one select.

Select Elements

Description

SELECTNUM

SELECT number.

PARENTSELECTNUM

Number of parent SELECT. For main SELECT, this must be set to 0.

SELECTTYPE

Type of selection.

Note: For valid values, see QAS_SAVE_QUERY_OPER.

QRYDISTINCT

Indicate whether this query is distinct.

Note: For valid values, see QAS_SAVE_QUERY_OPER.

Use this service operation to synchronously execute a query and receive the query results in the format selected in the service operation request. The response message depends on the request was for a file or a non file format.

Request Message: QAS_EXEQRY_SYNC_REQ_MSG

Element Name

Description

QueryName

Required element unless you are using XML string

Query name. Required unless you are using an XML string.

isConnectedQuery

Required element

Indicate whether this is a connected query. Valid values are Y orN in either upper or lower case.

Note: The default value is N.

XMLString

XML string.

Note: XML string is used only for ad hoc queries. See XML String for the elements.

OwnerType

Required element

Query owner type, public or private in either uppercase or lowercase. Required.

BlockSizeKB

Required element

Blocksize is used only for synchronous poll. For synchronous execution, the value should be set to 0 (zero).

The default value is 0.

MaxRows

Required element

The maximum number of rows to be fetched.

OutResultType

Required element

Select the output type. Valid values are:

  • WEBROWSET

  • XMLP

  • EXCEL

  • HTML

Note: For connected queries, the output type must be XMLP.

See Output Format and Output Type.

OutResultFormat

Required element

Select the output format. Valid values are:

  • FILE

  • NONFILE

Note: For connected queries, the output format must be FILE.

<Prompts> These elements are used for Connected Query or queries containing prompts.

Prompt Elements

Description

PSQueryName

This field is used only for Connected Query. Enter the name of the query containing the prompt.

UniquePromptName

Unique prompt name defined in the query. This value is case-sensitive.

Use the service operation QAS_LISTQUERYPROMPTS_OPER to find the unique prompt name.

FieldValue

Field value for the prompt. This value is case-sensitive.

Note: Date fields require the date format as YYYY-MM-DD.

<FieldFilter> If you want to return only specific fields in the query, indicate each field you want returned.

FilterFieldName Element

Description

FilterFieldName

List of field names to be returned. This value is case sensitive and must be the unique field name as returned by the service operation QAS_LISTQUERYFIELDS.

Example Request:

This request will execute the query MSGCAT_PROMPT, the unique name for the prompt isSet and the prompt value is2.

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/"
xmlns:qas="http://xmlns.oracle.com/Enterprise/Tools/schemas/
QAS_EXEQRY_SYNC_REQ_MSG.VERSION_1"
xmlns:qas1="http://xmlns.oracle.com/Enterprise/Tools/schemas/
QAS_EXEQRY_SYNC_REQ.VERSION_1">
   <soapenv:Header/>
   <soapenv:Body>
      <qas:QAS_EXEQRY_SYNC_REQ_MSG>
         <qas1:QAS_EXEQRY_SYNC_REQ>
            <QueryName>MSGCAT_PROMPT</QueryName>
            <isConnectedQuery>N</isConnectedQuery>
            <OwnerType>PUBLIC</OwnerType>
            <BlockSizeKB>0</BlockSizeKB>
            <MaxRow>2</MaxRow>
            <OutResultType>XMLP</OutResultType>
            <OutResultFormat>FILE</OutResultFormat>
            <Prompts>
               <!--Zero or more repetitions:-->
               <PROMPT>
                  <PSQueryName></PSQueryName>
                  <UniquePromptName>BIND1</UniquePromptName>
                  <FieldValue>2</FieldValue>
               </PROMPT>
            </Prompts>
         </qas1:QAS_EXEQRY_SYNC_REQ>
      </qas:QAS_EXEQRY_SYNC_REQ_MSG>
   </soapenv:Body>
</soapenv:Envelope>

Response Message: QAS_QUERYRESULTS_FILE_RESP

This response is used when the output format requested is FILE.

Element Name

Description

QueryResults

File URL

Example Response:

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/"
xmlns:soapenc="http://schemas.xmlsoap.org/soap/encoding/"
xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
   <soapenv:Body>
      <QAS_GETQUERYRESULTS_RESP_MSG
      xmlns="http://xmlns.oracle.com/Enterprise/Tools/
      schemas/QAS_GETQUERYRESULTS_RESP_MSG.VERSION_1">
         <QAS_QUERYRESULTS_FILE_RESP xmlns="http://xmlns.oracle.com/
         Enterprise/Tools/schemas/QAS_QUERYRESULTS_FILE_RESP.VERSION_1">
            <FILEURL>http://ple-infodev-08.peoplesoft.com:8010/psreports/
            QEDMO/9999957/MSGCAT_PROMPT.xml</FILEURL>
         </QAS_QUERYRESULTS_FILE_RESP>
      </QAS_GETQUERYRESULTS_RESP_MSG>
   </soapenv:Body>
</soapenv:Envelope>

Response Message: QAS_QUERYRESULTS_WRS_RESP

This response is used when the requested output format is NONFILE and the output type isWEBROWSET.

Element Name

Description

QueryResults

Query Result in webrowset format.

Example Response for webrowset:

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/"
xmlns:soapenc="http://schemas.xmlsoap.org/soap/encoding/"
xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
   <soapenv:Body>
      <QAS_GETQUERYRESULTS_RESP_MSG
      xmlns="http://xmlns.oracle.com/Enterprise/Tools/schemas/
      QAS_GETQUERYRESULTS_RESP_MSG.VERSION_1">
         <webRowSet xmlns="http://java.sun.com/xml/ns/jdbc">
            <properties>
               <escape-processing>true</escape-processing>
               <fetch-direction>1000</fetch-direction>
               <fetch-size>0</fetch-size>
               <isolation-level>1</isolation-level>
               <key-columns/>
               <map/>
               <max-field-size>0</max-field-size>
               <max-rows>0</max-rows>
               <query-timeout>0</query-timeout>
               <read-only>true</read-only>
               <show-deleted>false</show-deleted>
               <table-name/>
            </properties>
            <metadata>
               <column-count>5</column-count>
               <column-definition>
                  <column-index>1</column-index>
                  <auto-increment>false</auto-increment>
                  <case-sensitive>false</case-sensitive>
                  <currency>false</currency>
                  <nullable>0</nullable>
                  <signed>false</signed>
                  <searchable>false</searchable>
                  <column-display-size>0</column-display-size>
                  <column-label>Set</column-label>
                  <column-name>A.MESSAGE_SET_NBR</column-name>
                  <schema-name/>
                  <column-precision>0</column-precision>
                  <column-scale>0</column-scale>
                  <table-name/>
                  <catalog-name/>
                  <column-type>4</column-type>
                  <column-type-name>INTEGER</column-type-name>
               </column-definition>
               <column-definition>
                  <column-index>2</column-index>
                  <auto-increment>false</auto-increment>
                  <case-sensitive>false</case-sensitive>
                  <currency>false</currency>
                  <nullable>0</nullable>
                  <signed>false</signed>
                  <searchable>false</searchable>
                  <column-display-size>0</column-display-size>
                  <column-label>Descr</column-label>
                  <column-name>A.DESCR</column-name>
                  <schema-name/>
                  <column-precision>0</column-precision>
                  <column-scale>0</column-scale>
                  <table-name/>
                  <catalog-name/>
                  <column-type>12</column-type>
                  <column-type-name>VARCHAR</column-type-name>
               </column-definition>
               <column-definition>
                  <column-index>3</column-index>
                  <auto-increment>false</auto-increment>
                  <case-sensitive>false</case-sensitive>
                  <currency>false</currency>
                  <nullable>0</nullable>
                  <signed>false</signed>
                  <searchable>false</searchable>
                  <column-display-size>0</column-display-size>
                  <column-label>Msg</column-label>
                  <column-name>B.MESSAGE_NBR</column-name>
                  <schema-name/>
                  <column-precision>0</column-precision>
                  <column-scale>0</column-scale>
                  <table-name/>
                  <catalog-name/>
                  <column-type>4</column-type>
                  <column-type-name>INTEGER</column-type-name>
               </column-definition>
               <column-definition>
                  <column-index>4</column-index>
                  <auto-increment>false</auto-increment>
                  <case-sensitive>false</case-sensitive>
                  <currency>false</currency>
                  <nullable>0</nullable>
                  <signed>false</signed>
                  <searchable>false</searchable>
                  <column-display-size>0</column-display-size>
                  <column-label>Message</column-label>
                  <column-name>B.MESSAGE_TEXT</column-name>
                  <schema-name/>
                  <column-precision>0</column-precision>
                  <column-scale>0</column-scale>
                  <table-name/>
                  <catalog-name/>
                  <column-type>12</column-type>
                  <column-type-name>VARCHAR</column-type-name>
               </column-definition>
               <column-definition>
                  <column-index>5</column-index>
                  <auto-increment>false</auto-increment>
                  <case-sensitive>false</case-sensitive>
                  <currency>false</currency>
                  <nullable>0</nullable>
                  <signed>false</signed>
                  <searchable>false</searchable>
                  <column-display-size>0</column-display-size>
                  <column-label>Severity</column-label>
                  <column-name>B.MSG_SEVERITY</column-name>
                  <schema-name/>
                  <column-precision>0</column-precision>
                  <column-scale>0</column-scale>
                  <table-name/>
                  <catalog-name/>
                  <column-type>12</column-type>
                  <column-type-name>VARCHAR</column-type-name>
               </column-definition>
            </metadata>
            <data>
               <currentRow>
                  <columnValue>2</columnValue>
                  <columnValue>PeopleCode</columnValue>
                  <columnValue>1</columnValue>
                  <columnValue>Invalid use of !.</columnValue>
                  <columnValue>E</columnValue>
               </currentRow>
               <currentRow>
                  <columnValue>2</columnValue>
                  <columnValue>PeopleCode</columnValue>
                  <columnValue>2</columnValue>
                  <columnValue>Literal is too long.</columnValue>
                  <columnValue>E</columnValue>
               </currentRow>
            </data>
         </webRowSet>
      </QAS_GETQUERYRESULTS_RESP_MSG>
   </soapenv:Body>
</soapenv:Envelope>

Response Message: QAS_QUERYRESULTS_XMLP_RESP

This response is used when the requested output is NONFILE and the output type isXMLP.

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/"
xmlns:soapenc="http://schemas.xmlsoap.org/soap/encoding/"
xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
   <soapenv:Body>
      <QAS_GETQUERYRESULTS_RESP_MSG
      xmlns="http://xmlns.oracle.com/Enterprise/Tools/
      schemas/QAS_GETQUERYRESULTS_RESP_MSG.VERSION_1">
         <query numrows="2" queryname="MSGCAT_PROMPT"
         xsi:noNamespaceSchemaLocation="">
            <row rownumber="1">
               <MESSAGE_SET_NBR>2</MESSAGE_SET_NBR>
               <DESCR>PeopleCode</DESCR>
               <MESSAGE_NBR>1</MESSAGE_NBR>
               <MESSAGE_TEXT>Invalid use of !.</MESSAGE_TEXT>
               <MSG_SEVERITY>E</MSG_SEVERITY>
            </row>
            <row rownumber="2">
               <MESSAGE_SET_NBR>2</MESSAGE_SET_NBR>
               <DESCR>PeopleCode</DESCR>
               <MESSAGE_NBR>2</MESSAGE_NBR>
               <MESSAGE_TEXT>Literal is too long.</MESSAGE_TEXT>
               <MSG_SEVERITY>E</MSG_SEVERITY>
            </row>
         </query>
      </QAS_GETQUERYRESULTS_RESP_MSG>
   </soapenv:Body>
</soapenv:Envelope>

Use this service operation to synchronously execute a query and receive the query results in the format selected in the service operation request. The response message depends on whether the request was for a file or a non file format.

Element Name

Description

QueryName

Query name.

It is a required variable.

OwnerName

Query owner type, public or private in either uppercase or lowercase.

It is a required variable.

OutResultType

Select the output type. Valid values are:

  • WEBROWSET

  • XMLP

  • EXCEL

  • HTML

It is a required variable.

OutResultFormat

Select the output format. Valid values are:

  • FILE

  • NONFILE

It is a required variable.

isConnectedQuery

Indicate whether this is a connected query. Valid values are Y or N in either upper or lower case.

MaxRow

The maximum number of rows to be fetched.

It is a required variable.

Prompt_PSQueryName

This field is used only for Connected Query. Enter the name of the query containing the prompt.

Prompt_UniquePromptName

Unique prompt name defined in the query. This value is case-sensitive.

Prompt_FieldValue

Field value for the prompt. This value is case-sensitive.

FilterFields

List of field names to be returned. This value is case sensitive and must be the unique field name as returned by the service operation QAS_LISTQUERYFIELDS.

json_response

Enter true for JSON response type; enter false for standard XML response. If no value is provided, the default is standard XML response.

Example of an URI template

{OwnerType}/{QueryName}/{OutResultType}/{OutResultFormat}?isconnectedquery=
{isConnectedQuery}&maxrows={MaxRow}&prompt_psqueryname={Prompt_PSQueryName*}
&prompt_uniquepromptname={Prompt_UniquePromptName*}&prompt_fieldvalue=
{Prompt_FieldValue*}&filterfields={FilterFields*}&json_resp={json_response}

Request Message: QAS_EXECUTEQRY_TEMPL

Element Name

Description

QueryName

Required element unless you are using XML string

Query name. Required unless you are using an XML string.

isConnectedQuery

Required element

Indicate whether this is a connected query. Valid values are Y orN in either upper or lower case.

Note: The default value is N.

XMLString

XML string.

Note: XML string is used only for ad hoc queries. See XML String for the elements.

OwnerType

Required element

Query owner type, public or private in either uppercase or lowercase. Required.

BlockSizeKB

Required element

Blocksize is used only for synchronous poll. For synchronous execution, the value should be set to 0 (zero).

The default value is 0.

MaxRows

Required element

The maximum number of rows to be fetched.

OutResultType

Required element

Select the output type. Valid values are:

  • WEBROWSET

  • XMLP

  • EXCEL

  • HTML

Note: For connected queries, the output type must be XMLP.

See Output Format and Output Type.

OutResultFormat

Required element

Select the output format. Valid values are:

  • FILE

  • NONFILE

Note: For connected queries, the output format must be FILE.

<Prompts> These elements are used for Connected Query or queries containing prompts.

Prompt Elements

Description

PSQueryName

This field is used only for Connected Query. Enter the name of the query containing the prompt.

UniquePromptName

Unique prompt name defined in the query. This value is case-sensitive.

Use the service operation QAS_LISTQUERYPROMPTS_OPER to find the unique prompt name.

FieldValue

Field value for the prompt. This value is case-sensitive.

Note: Date fields require the date format as YYYY-MM-DD.

<FieldFilter> If you want to return only specific fields in the query, indicate each field you want returned.

FilterFieldName Element

Description

FilterFieldName

List of field names to be returned. This value is case sensitive and must be the unique field name as returned by the service operation QAS_LISTQUERYFIELDS.

Example Request - WEBROWSET/NONFILE:

https://<servername>:<port>/PSIGW/RESTListeningConnector/<defaultlocal node>/ExecuteQuery.v1/public/MESSAGES_FOR_MSGSET/WEBROWSET/NONFILE?isconnectedquery=N&maxrows=1000&prompt_uniquepromptname=MESSAGE_SET_NBR&prompt_fieldvalue=28&filterfields=A.MESSAGE_SET_NBR,A.MESSAGE_NBR,A.MSG_SEVERITY,A.MESSAGE_TEXT

Example Response - WEBROWSET/NONFILE:

<?xml version="1.0"?>
<QAS_GETQUERYRESULTS_RESP_MSG xmlns="http://xmlns.oracle.com/Enterprise/
Tools/schemas/QAS_GETQUERYRESULTS_RESP_MSG.VERSION_1">
    <webRowSet xmlns="http://java.sun.com/xml/ns/jdbc">
        <properties>
            <escape-processing>true</escape-processing>
            <fetch-direction>1000</fetch-direction>
            <fetch-size>0</fetch-size>
            <isolation-level>1</isolation-level>
            <key-columns/>
            <map/>
            <max-field-size>0</max-field-size>
            <max-rows>0</max-rows>
            <query-timeout>0</query-timeout>
            <read-only>true</read-only>
            <show-deleted>false</show-deleted>
            <table-name/>
        </properties>
        <metadata>
            <column-count>4</column-count>
            <column-definition>
                <column-index>1</column-index>
                <auto-increment>false</auto-increment>
                <case-sensitive>false</case-sensitive>
                <currency>false</currency>
                <nullable>0</nullable>
                <signed>false</signed>
                <searchable>false</searchable>
                <column-display-size>0</column-display-size>
                <column-label>Set</column-label>
                <column-name>A.MESSAGE_SET_NBR</column-name>
                <schema-name/>
                <column-precision>0</column-precision>
                <column-scale>0</column-scale>
                <table-name/>
                <catalog-name/>
                <column-type>4</column-type>
                <column-type-name>INTEGER</column-type-name>
            </column-definition>
            <column-definition>
                <column-index>2</column-index>
                <auto-increment>false</auto-increment>
                <case-sensitive>false</case-sensitive>
                <currency>false</currency>
                <nullable>0</nullable>
                <signed>false</signed>
                <searchable>false</searchable>
                <column-display-size>0</column-display-size>
                <column-label>Msg</column-label>
                <column-name>A.MESSAGE_NBR</column-name>
                <schema-name/>
                <column-precision>0</column-precision>
                <column-scale>0</column-scale>
                <table-name/>
                <catalog-name/>
                <column-type>4</column-type>
                <column-type-name>INTEGER</column-type-name>
            </column-definition>
            <column-definition>
                <column-index>3</column-index>
                <auto-increment>false</auto-increment>
                <case-sensitive>false</case-sensitive>
                <currency>false</currency>
                <nullable>0</nullable>
                <signed>false</signed>
                <searchable>false</searchable>
                <column-display-size>0</column-display-size>
                <column-label>Severity</column-label>
                <column-name>A.MSG_SEVERITY</column-name>
                <schema-name/>
                <column-precision>0</column-precision>
                <column-scale>0</column-scale>
                <table-name/>
                <catalog-name/>
                <column-type>12</column-type>
                <column-type-name>VARCHAR</column-type-name>
            </column-definition>
            <column-definition>
                <column-index>4</column-index>
                <auto-increment>false</auto-increment>
                <case-sensitive>false</case-sensitive>
                <currency>false</currency>
                <nullable>0</nullable>
                <signed>false</signed>
                <searchable>false</searchable>
                <column-display-size>0</column-display-size>
                <column-label>Message</column-label>
                <column-name>A.MESSAGE_TEXT</column-name>
                <schema-name/>
                <column-precision>0</column-precision>
                <column-scale>0</column-scale>
                <table-name/>
                <catalog-name/>
                <column-type>12</column-type>
                <column-type-name>VARCHAR</column-type-name>
            </column-definition>
        </metadata>
        <data>
            <currentRow>
                <columnValue>28</columnValue>
                <columnValue>1</columnValue>
                <columnValue>
                    <![CDATA[M]]>
                </columnValue>
                <columnValue>
                    <![CDATA[Array not allocated.]]>
                </columnValue>
            </currentRow>
            <currentRow>
                <columnValue>28</columnValue>
                <columnValue>2</columnValue>
                <columnValue>
                    <![CDATA[M]]>
                </columnValue>
                <columnValue>
                    <![CDATA[Array not de-allocated.]]>
                </columnValue>
            </currentRow>
            <currentRow>
                <columnValue>28</columnValue>
                <columnValue>3</columnValue>
                <columnValue>
                    <![CDATA[M]]>
                </columnValue>
                <columnValue>
                    <![CDATA[Not enough free memory to continue.]]>
                </columnValue>
            </currentRow>
            <currentRow>
                <columnValue>28</columnValue>
                <columnValue>4</columnValue>
                <columnValue>
                    <![CDATA[E]]>
                </columnValue>
                <columnValue>
                    <![CDATA[Measure Field %1 not defined for ledger %2]]>
                </columnValue>
            </currentRow>
            <currentRow>
                <columnValue>28</columnValue>
                <columnValue>5</columnValue>
                <columnValue>
                    <![CDATA[M]]>
                </columnValue>
                <columnValue>
                    <![CDATA[Cannot create directory %1.]]>
                </columnValue>
            </currentRow>
            <currentRow>
                <columnValue>28</columnValue>
                <columnValue>6</columnValue>
                <columnValue>
                    <![CDATA[M]]>
                </columnValue>
                <columnValue>
                    <![CDATA[Invalid scope name:  %1]]>
                </columnValue>
            </currentRow>
            <currentRow>
                <columnValue>28</columnValue>
                <columnValue>7</columnValue>
                <columnValue>
                    <![CDATA[M]]>
                </columnValue>
                <columnValue>
                    <![CDATA[Variable %1 is invalid.]]>
                </columnValue>
            </currentRow>
            <currentRow>
                <columnValue>28</columnValue>
                <columnValue>8</columnValue>
                <columnValue>
                    <![CDATA[M]]>
                </columnValue>
                <columnValue>
                    <![CDATA[This Field is marked as "Not Used".]]>
                </columnValue>
            </currentRow>
            <currentRow>
                <columnValue>28</columnValue>
                <columnValue>9</columnValue>
                <columnValue>
                    <![CDATA[M]]>
                </columnValue>
                <columnValue>
                    <![CDATA[Missing ledger or query at row %1, column %2 of layout.]]>
                </columnValue>
            </currentRow>
            <currentRow>
                <columnValue>28</columnValue>
                <columnValue>10</columnValue>
                <columnValue>
                    <![CDATA[M]]>
                </columnValue>
                <columnValue>
                    <![CDATA[Missing percent sign on variable in template %1.]]>
                </columnValue>
            </currentRow>
        </data>
    </webRowSet>
</QAS_GETQUERYRESULTS_RESP_MSG>

Example Request - HTML/FILE:

https://<servername>:<port>/PSIGW/RESTListeningConnector/<defaultlocal node>/ExecuteQuery.v1/public/XRFWIN/HTML/FILE?isconnectedquery=N&maxrows=1000

Example Response - HTML/FILE:

<?xml version="1.0"?>
<QAS_GETQUERYRESULTS_RESP_MSG xmlns="http://xmlns.oracle.com/Enterprise/
Tools/schemas/QAS_GETQUERYRESULTS_RESP_MSG.VERSION_1">
    <QAS_QUERYRESULTS_FILE_RESP xmlns="http://xmlns.oracle.com/Enterprise/
     Tools/schemas/QAS_QUERYRESULTS_FILE_RESP.VERSION_1">
        <FILEURL>
            http://myserver.us.oracle.com/psreports/Q8549033/74/XRFWIN.html
        </FILEURL>
    </QAS_QUERYRESULTS_FILE_RESP>
</QAS_GETQUERYRESULTS_RESP_MSG>

Example Response (XMLP/NONFILE) using json_response=true:

{"status": "fail","data": "For JSON response and NONFILE output format, 
only JSON output result type is supported."}

This is the expected response as NONFILE and json_resp=true combination will only support JSON output result type. If using FILE output format however, all of the output result types are supported.

Example Response (JSON/NONFILE) using json_response=true:

{
	"status": "success","data": {"query": {	"numrows": 3,"queryname=": "MESSAGES_FOR_MSGSET","rows": [		{"attr:rownumber":1,"MESSAGE_SET_NBR":28,"MESSAGE_NBR":1,			"MSG_SEVERITY":"M","MESSAGE_TEXT":"Array not allocated."},			{"attr:rownumber":2,"MESSAGE_SET_NBR":28,"MESSAGE_NBR":2,				"MSG_SEVERITY":"M","MESSAGE_TEXT":"Array not de-allocated."},				{"attr:rownumber":3,"MESSAGE_SET_NBR":28,"MESSAGE_NBR":3,"MSG_SEVERITY":"M",					"MESSAGE_TEXT":"Not enough free memory to continue."}]		}}}

Example Response (HTML/FILE) using json_response=true:

{"status": "success","data": {"fileurl": "http:\/\/myserver.us.oracle.com\
/psreports\/Q8551093\/308\/XRFWIN.html","status": "posted"}}

Example Response (JSON/FILE) using json_response=true:

{"status": "success","data": {"fileurl":"http:\/\/myserver.us.oracle.com\
/psreports\/Q8551093\/309\/XRFWIN.json","status": "posted"} 

This service operation runs the query request in the Process Scheduler. This service operation should be used for long running queries or queries with large result sets. The query is executed in batch mode through Integration Broker asynchronous servers (Pub/Sub) and the Process Schedulers.

When a client request for QAS_EXECUTEQRY_SYNCPOLL_OPER is received by the Integration Broker, the following occurs:

  1. An asynchronous one way service operation which gets processed by Integration Broker.

  2. The query instance ID (transaction ID) is returned to the requester.

    The requester can use the query instance to poll for the status (QAS_QUERYSTATUS_OPER) and to retrieve the query results (QAS_GETQUERYRESULTS_OPER).

  3. The service operation schedules the application engine program QASEXEQRY in the Process Scheduler.

The user ID in the request header must have permission to execute the QASEXEQRY application engine program, as well as permission for the service operation. The PeopleSoft delivered role QAS Admin grants the necessary permissions.

Note: Pub/Sub and Process Scheduler must be running on the application server for the QAS_EXECUTEQRYSYNCPOLL_OPER service operations to be queued and processed.

When the execute query sync poll request is processed, it will validate the query fields and query prompts. If the client application sending the request caches the query definition, the client application can assume the query definition has not changed and it is not necessary to make calls to ListQueryFields and ListQueryPrompts. If the client application receives an error, such as incorrect field name or incorrect prompt value, then the client application should assume that the query definition has been updated. If the query definition has been updated, then the client application will need to make calls to ListQueryFields and ListQueryPrompts.

Request Message: QAS_EXEQRYSYNCPOLL_REQ_MSG

Element Name

Description

QueryName

Required element unless you are using XML string

Query name.

isConnectedQuery

Required element

Indicate whether this is a connected query. Valid values are Y andN.

XMLString

XML string.

Note: See XML String.

OwnerType

Required element

Query owner type.

BlockSizeKB

Required element

If the output format is nonfile, indicate the block size to use for chunking the response. BlockSizeKB is a number of length 10 with a decimal position of 0. Block size is in bytes.

Note: The number of calls made to GetQueryResult reduces as the block size value increases. Thus, increasing performance.

See the Performance Consideration section in the Query Execution Models topic for additional information.

MaxRows

Required element

The maximum number of rows to be fetched

OutResultType

Required element

Select the output type. Valid values are:

  • WEBROWSET

  • XMLP

  • EXCEL

  • HTML

Note: For connected queries, the output type must be XMLP.

See Output Format and Output Type.

OutResultFormat

Required element

Select the output format. Valid values are:

  • FILE

  • NONFILE

Note: For connected queries, the output format must be FILE.

<Prompts> These elements are used for Connected Query or queries containing prompts.

Prompt Elements

Description

PSQueryName

This field is used only for Connected Query. Enter the name of the query containing the prompt.

UniquePromptName

Unique prompt name defined in the query. This value is case-sensitive.

Use the service operation QAS_LISTQUERYPROMPTS_OPER to find the unique prompt name.

FieldValue

Field value for the prompt. This value is case-sensitive.

Note: Date fields require the date format as YYYY-MM-DD.

<FieldFilter> If you want to return only specific fields in the query, indicate each field you want returned.

FilterFieldName Element

Description

FilterFieldName

List of field names to be returned. This value is case sensitive and must be the unique field name as returned by the service operation QAS_LISTQUERYFIELDS.

Example Request:

This request will execute the query XRFWIN with an output type of XMLP and output format of FILE.

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/"
xmlns:qas="http://xmlns.oracle.com/Enterprise/Tools/schemas/
QAS_EXEQRY_SYNCPOLL_REQ_MSG.VERSION_1"
xmlns:qas1="http://xmlns.oracle.com/Enterprise/Tools/schemas/
QAS_EXEQRY_SYNCPOLL_REQ.VERSION_1">
   <soapenv:Header/>
   <soapenv:Body>
      <qas:QAS_EXEQRY_SYNCPOLL_REQ_MSG>
         <!--Zero or more repetitions:-->
         <qas1:QAS_EXEQRY_SYNCPOLL_REQ>
            <QueryName>XRFWIN</QueryName>
            <isConnectedQuery>n</isConnectedQuery>
            <XMLString/> 
            <OwnerType>public</OwnerType>
            <BlockSizeKB></BlockSizeKB>
            <MaxRow>3</MaxRow>
            <OutResultType>XMLP</OutResultType>
            <OutResultFormat>FILE</OutResultFormat>
        </qas1:QAS_EXEQRY_SYNCPOLL_REQ>
      </qas:QAS_EXEQRY_SYNCPOLL_REQ_MSG>
   </soapenv:Body>
</soapenv:Envelope>

Response Message: QAS_EXEQRYSYNCPOLL_RESP_MSG

Element Name

Description

QueryInstance

Query instance ID.

This instance ID is used for GetQueryStatus, CancelQuery, and GetQueryResult.

Example Response:

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/"
xmlns:soapenc="http://schemas.xmlsoap.org/soap/encoding/"
xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
   <soapenv:Body>
      <QAS_EXEQRYSYNCPOLL_RESP_MSG xmlns="http://xmlns.oracle.com/
      Enterprise/Tools/schemas/QAS_EXEQRYSYNCPOLL_RESP_MSG.VERSION_1">
         <QAS_EXEQRYSYNCPOLL_RESP>
            <PTQASWRK class="R" xmlns="http://xmlns.oracle.com/
            Enterprise/Tools/schemas/QAS_EXEQRYSYNCPOLL_RESP.VERSION_1">
               <QueryInstance>8995a01e-0a75-11dd-9c24-98a15db6aa18</QueryInstance>
            </PTQASWRK>
         </QAS_EXEQRYSYNCPOLL_RESP>
      </QAS_EXEQRYSYNCPOLL_RESP_MSG>
   </soapenv:Body>
</soapenv:Envelope>

QAS supports running a query asynchronously. The request is received and executed. When the query has been completed, the results are asynchronously sent back to the requesting system.

Note: Pub/Sub must be running on the applications server for asynchronous service operations to be queued and processed.

For asynchronous requests, parameters are mandatory in the SOAP header in order to receive the response. This table lists the Reply To elements in the SOAP header:

These are standard parameters for any asynchronous request/response web service.

Element

Description

Address

Address where the http listener is running on the client application.

The Address should be valid and should be accessible to Integration Broker in order to return the query result. If not, the results will never be returned.

ReferenceProperties

Any reference properties necessary for the client application.

PortType

Operations of the service.

Any additional elements

Any additional elements necessary for the client application.

MessageID

Unique message ID. Every request to execute query asynchronously should have a different value for MessageID.

This service operation is used to asynchronously request query execution and return the query results. The response message depends on the result type requested (OutResultType).

Request Message: QAS_EXEQRY_ASYNC_REQ_MSG

Element Name

Description

QueryName

Query name.

isConnectedQuery

Indicate whether this is a connected query. Valid values are Y andN.

XMLString

XML string.

Note: See XML String for the elements.

OwnerType

Query owner type.

BlockSizeKB

Required element

If the output format is nonfile, indicate the block size to use for chunking the response. BlockSizeKB is a number of length 10 with a decimal position of 0. Block size is in bytes.

Note: If the BlockSize is 0 all query results are retrieved in 1 block.

MaxRows

The maximum number of rows to be fetched

OutResultType

Select the output type. Valid values are:

  • WEBROWSET

  • XMLP

  • EXCEL

  • HTML

Note: For connected queries, the output type must be XMLP.

OutResultFormat

Select the output format. Valid values are:

  • FILE

  • NONFILE

Note: For connected queries, the output format must be FILE.

<Prompts> These elements are used for Connected Query or queries containing prompts. .

Prompt Elements

Description

PSQueryName

This field is used only for Connected Query. Enter the name of the query containing the prompt.

UniquePromptName

Unique prompt name defined in the query. This value is case-sensitive.

Use the service operation QAS_LISTQUERYPROMPTS_OPER to find the unique prompt name.

FieldValue

Field value for the prompt. This value is case-sensitive.

Note: Date fields require the date format as YYYY-MM-DD.

<FieldFilter> If you want to return only specific fields in the query, indicate each field you want returned.

FilterFieldName Element

Description

FilterFieldName

List of field names to be returned. This value is case sensitive and must be the unique field name as returned by the service operation QAS_LISTQUERYFIELDS.

Example Request:

This is an example request to run the query MSGCAT_PROMPT and notify the client application when the request has finished.

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/"
xmlns:add="http://schemas.xmlsoap.org/ws/2003/03/addressing"
xmlns:qas="http://xmlns.oracle.com/Enterprise/Tools/schemas/
QAS_EXEQRY_ASYNC_REQ_MSG.VERSION_1"
xmlns:qas1="http://xmlns.oracle.com/Enterprise/Tools/schemas/
QAS_EXEQRY_ASYNC_REQ.VERSION_1">
   <soapenv:Header>
      <add:ReplyTo>
         <add:Address>http://ple-indodev-08:8082/qasrefapp/callbackservice⇒
</add:Address>
      </add:ReplyTo>
      <add:MessageID>2ae456542b</add:MessageID>
   </soapenv:Header>
   <soapenv:Body>
      <qas:QAS_EXEQRY_ASYNC_REQ_MSG>
         <qas1:QAS_EXEQRY_ASYNC_REQ>
            <QueryName>xrfwin</QueryName>
            <isConnectedQuery>n</isConnectedQuery>
           <OwnerType>public</OwnerType>
            <BlockSizeKB>0</BlockSizeKB>
            <MaxRow></MaxRow>
            <OutResultType>xmlp</OutResultType>
            <OutResultFormat>NONFILE</OutResultFormat>
          </qas1:QAS_EXEQRY_ASYNC_REQ>
      </qas:QAS_EXEQRY_ASYNC_REQ_MSG>
   </soapenv:Body>
</soapenv:Envelope>

For Webrowset , the Response Message is QAS_EXECUTEQRYASYNC_RESP_MSG: QAS_QUERYRESULTS_WSR_RESP

Element Name

Description

QueryResults

Query Result in webrowset format

Example Response:

For File, the Response Message is QAS_QUERYRESULTS_FILE_RESP.

Element Name

Description

FILEURL

File URL

This service operation validates and verifies XML-format Composite Query

Request Message: QAS_ CREATE_CPQ_REQ_MSG

Element Name

Description

SelectFields

Contains the select fields from Queries. Fields in this element will show in the SELECT clause of the generated SQL statement in response message.

FROM

Contains a list of Queries and the Join Criteria. The sub-elements contain all information used to construct FROM clause in SQL statement of the Composite Query.

WHERE

Contains filtering criteria that will be in WHERE clause in the SQL statement of the composite query.

ORDERBy

Contains one or more Field element. You can either use FieldName or ColumnNumber to specify columns in OrderBy.

GROUPBy

Use FieldName only.

Example Request :

This request will return the SQL of the composite query.

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/"
xmlns:qas="http://xmlns.oracle.com/Enterprise/Tools/schemas/
QAS_EXECPQ_SYNC_REQ_MSG.VERSION_1"
xmlns:qas1="http://xmlns.oracle.com/Enterprise/Tools/schemas/
QAS_EXECPQ_SYNC_REQ.VERSION_1">
   <soapenv:Header/>
   <soapenv:Body>
      <qas:QAS_EXECPQ_SYNC_REQ_MSG>
         <!--Optional:-->
         <qas1:QAS_EXECPQ_SYNC_REQ>
            <SelectFields>
               <FIELD>
                  <FieldName>D173.A.TNODE1_DESC</FieldName>
                  <AggregateType>None</AggregateType>
               </FIELD>
               <FIELD>
                  <FieldName>D101.A.POSTED_TOTAL_AMT</FieldName>
                  <AggregateType>None</AggregateType>
               </FIELD>
               <FIELD>
                  <FieldName>D173.A.TNODE2</FieldName>
                  <AggregateType>None</AggregateType>
               </FIELD>
               <FIELD>
                  <FieldName>D173.A.TNODE3</FieldName>
                  <AggregateType>None</AggregateType>
               </FIELD>
               <FIELD>
                  <FieldName>D173.A.TNODE4</FieldName>
                  <AggregateType>None</AggregateType>
               </FIELD>
               <FIELD>
                  <FieldName>D173.A.TNODE5</FieldName>
                  <AggregateType>None</AggregateType>
               </FIELD>
               <FIELD>
                  <FieldName>D173.A.TNODE6</FieldName>
                  <AggregateType>None</AggregateType>
               </FIELD>
               <FIELD>
                  <FieldName>D173.A.ACCOUNT</FieldName>
                  <AggregateType>None</AggregateType>
               </FIELD>
               <FIELD>
                  <FieldName>D173.A.TNODE7</FieldName>
                  <AggregateType>None</AggregateType>
               </FIELD>
               <FIELD>
                  <FieldName>D173.A.TNODE1</FieldName>
                  <AggregateType>None</AggregateType>
               </FIELD>
            </SelectFields>
            <FROM>
               <QUERY>
                  <QueryName>PS_OTBI_LEDGER_VW</QueryName>
                  <OwnerType>Public</OwnerType>
                  <QueryAlias>D101</QueryAlias>
                  <ParentQueryAlias/>
                  <JoinType>Inner</JoinType>
               </QUERY>
               <QUERY>
                  <QueryName>PS_OI_TFLT_EFF_ACT</QueryName>
                  <OwnerType>Public</OwnerType>
                  <QueryAlias>D173</QueryAlias>
                  <ParentQueryAlias>D101</ParentQueryAlias>
                  <JoinType>Inner</JoinType>
                  <JoinCriteria>
                     <FieldName>A.ACCOUNT</FieldName>
                     <JoinCriteriaOperator>=</JoinCriteriaOperator>
                     <ParentFieldName>A.ACCOUNT</ParentFieldName>
                  </JoinCriteria>
                  <JoinCriteria>
                     <FieldName>A.SETID</FieldName>
                     <JoinCriteriaOperator>=</JoinCriteriaOperator>
                     <ParentFieldName>B.ACCOUNT_SETID</ParentFieldName>
                  </JoinCriteria>
                  <JoinCriteria>
                     <FieldName>A.SETID_TREE</FieldName>
                     <JoinCriteriaOperator>=</JoinCriteriaOperator>
                     <ParentFieldName>C.SETID</ParentFieldName>
                  </JoinCriteria>
               </QUERY>
            </FROM>
            <WHERE>
               <FilterCriteria>
                  <LogicalOperator/>
                  <LeftParenthesisLevel>0</LeftParenthesisLevel>
                  <RightParenthesisLevel>0</RightParenthesisLevel>
                  <Operand1FieldName>D173.A.TREE_NAME</Operand1FieldName>
                  <Operand2Type>Constant</Operand2Type>
                  <Operand2FieldName/>
                  <Operand2Text>ACCTROLLUPFIN</Operand2Text>
                  <Operator>=</Operator>
               </FilterCriteria>
               <FilterCriteria>
                  <LogicalOperator>and</LogicalOperator>
                  <LeftParenthesisLevel>0</LeftParenthesisLevel>
                  <RightParenthesisLevel>0</RightParenthesisLevel>
                  <Operand1FieldName>D173.A.EFF_END_DT</Operand1FieldName>
                  <Operand2Type>Constant</Operand2Type>
                  <Operand2FieldName/>
                  <Operand2Text>1900-01-01</Operand2Text>
                  <Operator>>=</Operator>
               </FilterCriteria>
               <FilterCriteria>
                  <LogicalOperator>and</LogicalOperator>
                  <LeftParenthesisLevel>0</LeftParenthesisLevel>
                  <RightParenthesisLevel>0</RightParenthesisLevel>
                  <Operand1FieldName>D173.A.EFF_ST_DT</Operand1FieldName>
                  <Operand2Type>Constant</Operand2Type>
                  <Operand2FieldName/>
                  <Operand2Text>1900-01-01</Operand2Text>
                  <Operator>>=</Operator>
               </FilterCriteria>
               <FilterCriteria>
                  <LogicalOperator>and</LogicalOperator>
                  <LeftParenthesisLevel>0</LeftParenthesisLevel>
                  <RightParenthesisLevel>0</RightParenthesisLevel>
                  <Operand1FieldName/>
                  <Operand2Type>Expression</Operand2Type>
                  <Operand2FieldName/>
                  <Operand2Text>
                         case  when D173."A.TNODE1" = '' then NULL 
                         else D173."A.TNODE1" end = 'ALLACCOUNTS'
                   </Operand2Text>
                  <Operator/>
               </FilterCriteria>
            </WHERE>
            <BlockSizeKB>0</BlockSizeKB>
            <MaxRow>1000</MaxRow>
            <OutResultType>WEBROWSET</OutResultType>
            <OutResultFormat>NONFILE</OutResultFormat>
            <CompositeQueryName/>
            <PruningType>NONE</PruningType>
         </qas1:QAS_EXECPQ_SYNC_REQ>
      </qas:QAS_EXECPQ_SYNC_REQ_MSG>
   </soapenv:Body>
</soapenv:Envelope>

Response Message: QAS_QUERYRESULTS_WRS_RESP_MSG

Element Name

Description

QueryResults

Query Result in webrowset format.

Example Request:

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/"
xmlns:soapenc="http://schemas.xmlsoap.org/soap/encoding/"
xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<soapenv:Body>
<QAS_GETQUERYRESULTS_RESP_MSG xmlns="http://xmlns.oracle.com/Enterprise/
   Tools/schemas/QAS_GETQUERYRESULTS_RESP_MSG.VERSION_1">
<webRowSet xmlns="http://java.sun.com/xml/ns/jdbc">
<properties>
<escape-processing>true</escape-processing>
<fetch-direction>1000</fetch-direction>
<fetch-size>0</fetch-size>
<isolation-level>1</isolation-level>
<key-columns/>
<map/>
<max-field-size>0</max-field-size>
<max-rows>0</max-rows>
<query-timeout>0</query-timeout>
<read-only>true</read-only>
<show-deleted>false</show-deleted>
<table-name/>
</properties>
<metadata>
<column-count>5</column-count>
<column-definition>
<column-index>1</column-index>
<auto-increment>false</auto-increment>
<case-sensitive>false</case-sensitive>
<currency>false</currency>
<nullable>0</nullable>
<signed>false</signed>
<searchable>false</searchable>
<column-display-size>0</column-display-size>
<column-label>Set</column-label>
<column-name>A.MESSAGE_SET_NBR</column-name>
<schema-name/>
<column-precision>0</column-precision>
<column-scale>0</column-scale>
<table-name/>
<catalog-name/>
<column-type>4</column-type>
<column-type-name>INTEGER</column-type-name>
</column-definition>
<column-definition>
<column-index>2</column-index>
<auto-increment>false</auto-increment>
<case-sensitive>false</case-sensitive>
<currency>false</currency>
<nullable>0</nullable>
<signed>false</signed>
<searchable>false</searchable>
<column-display-size>0</column-display-size>
<column-label>Descr</column-label>
<column-name>A.DESCR</column-name>
<schema-name/>
<column-precision>0</column-precision>
<column-scale>0</column-scale>
<table-name/>
<catalog-name/>
<column-type>12</column-type>
<column-type-name>VARCHAR</column-type-name>
</column-definition>
<column-definition>
<column-index>3</column-index>
<auto-increment>false</auto-increment>
<case-sensitive>false</case-sensitive>
<currency>false</currency>
<nullable>0</nullable>
<signed>false</signed>
<searchable>false</searchable>
<column-display-size>0</column-display-size>
<column-label>Msg</column-label>
<column-name>B.MESSAGE_NBR</column-name>
<schema-name/>
<column-precision>0</column-precision>
<column-scale>0</column-scale>
<table-name/>
<catalog-name/>
<column-type>4</column-type>
<column-type-name>INTEGER</column-type-name>
</column-definition>
<column-definition>
<column-index>4</column-index>
<auto-increment>false</auto-increment>
<case-sensitive>false</case-sensitive>
<currency>false</currency>
<nullable>0</nullable>
<signed>false</signed>
<searchable>false</searchable>
<column-display-size>0</column-display-size>
<column-label>Message</column-label>
<column-name>B.MESSAGE_TEXT</column-name>
<schema-name/>
<column-precision>0</column-precision>
<column-scale>0</column-scale>
<table-name/>
<catalog-name/>
<column-type>12</column-type>
<column-type-name>VARCHAR</column-type-name>
</column-definition>
<column-definition>
<column-index>5</column-index>
<auto-increment>false</auto-increment>
<case-sensitive>false</case-sensitive>
<currency>false</currency>
<nullable>0</nullable>
<signed>false</signed>
<searchable>false</searchable>
<column-display-size>0</column-display-size>
<column-label>Severity</column-label>
<column-name>B.MSG_SEVERITY</column-name>
<schema-name/>
<column-precision>0</column-precision>
<column-scale>0</column-scale>
<table-name/>
<catalog-name/>
<column-type>12</column-type>
<column-type-name>VARCHAR</column-type-name>
</column-definition>
</metadata>
<data>
<currentRow>
<columnValue>2</columnValue>
<columnValue>PeopleCode</columnValue>
<columnValue>1</columnValue>
<columnValue>Invalid use of !.</columnValue>
<columnValue>E</columnValue>
</currentRow>
<currentRow>
<columnValue>2</columnValue>
<columnValue>PeopleCode</columnValue>
<columnValue>2</columnValue>
<columnValue>Literal is too long.</columnValue>
<columnValue>E</columnValue>
</currentRow>
</data>
</webRowSet>
</QAS_GETQUERYRESULTS_RESP_MSG>
</soapenv:Body>
</soapenv:Envelope>

This service operation is for synchronously executing an ad-hoc query and receiving the query results in the format selected in the service operation request. The response message depends on whether the request was for a file or a non file format.

Example Request:

http://<servername>:<port>/PSIGW/RESTListeningConnector/<defaultlocal node>/ExecuteAdhocQuery.v1/

POST Message

<?xml version="1.0"?>
<QAS_EXEQRY_SYNC_REQ_MSG>
 	<QAS_EXEQRY_SYNC_REQ>
		<QueryName/>
		<isConnectedQuery>N</isConnectedQuery>
 		<XMLString>
		<!--1 or more repetitions:-->
	<RECORD>
		<RCDNUM>1</RCDNUM>
 		<RCDNAME>PSMSGSETDEFN</RCDNAME>
		<CORRNAME>A</CORRNAME>
		<JOINTYPE></JOINTYPE>
		<JOINRCDALIAS></JOINRCDALIAS>
		<JOINFIELD></JOINFIELD>
  		<RCDSELECTNUM>1</RCDSELECTNUM>
	</RECORD>
	<!--1 or more repetitions:-->
	<FIELD>
		<FIELDNUM>1</FIELDNUM>
 		<FIELDNAME>MESSAGE_set_nbr</FIELDNAME>
 		<FIELDRCDNUM>1</FIELDRCDNUM>
	 	<DESCR></DESCR>
		<AGGREGATE_TYPE>None</AGGREGATE_TYPE>
		<HEADING_TYPE>RFT Short</HEADING_TYPE>
 		<HEADING></HEADING>
		<COLUMNNUM>1</COLUMNNUM>
		<ORDERBYNUM>1</ORDERBYNUM>
		<ORDERBYDIR></ORDERBYDIR>
		<FIELDSELECTNUM>1</FIELDSELECTNUM>
		<EXPRESSION_AS_FIELD></EXPRESSION_AS_FIELD>
	</FIELD>
 	<FIELD>
		<FIELDNUM>2</FIELDNUM>
		<FIELDNAME>descr</FIELDNAME>
		<FIELDRCDNUM>1</FIELDRCDNUM>
		<DESCR></DESCR>
		<AGGREGATE_TYPE>None</AGGREGATE_TYPE>
		<HEADING_TYPE>RFT Short</HEADING_TYPE>
 		<HEADING></HEADING>
		<COLUMNNUM>2</COLUMNNUM>
		<ORDERBYNUM>0</ORDERBYNUM>
		<ORDERBYDIR></ORDERBYDIR>
		<FIELDSELECTNUM>1</FIELDSELECTNUM>
		<EXPRESSION_AS_FIELD></EXPRESSION_AS_FIELD>
	</FIELD>
 	<SELECT>
		<SELECTNUM>1</SELECTNUM>
		<PARENTSELECTNUM>0</PARENTSELECTNUM>
		<SELECTTYPE>Main</SELECTTYPE>
		<QRYDISTINCT></QRYDISTINCT>
	</SELECT>
 	</XMLString> 
 		<OwnerType/>
		<BlockSizeKB>0</BlockSizeKB>
		<MaxRow>1000</MaxRow>
		<OutResultType>XMLP</OutResultType>
		<OutResultFormat>NONFILE</OutResultFormat>
		<Prompts/>
	 </QAS_EXEQRY_SYNC_REQ>
</QAS_EXEQRY_SYNC_REQ_MSG>

Example Response:

<?xml version="1.0"?>
<QAS_GETQUERYRESULTS_RESP_MSG xmlns="http://xmlns.oracle.com/Enterprise/
Tools/schemas/QAS_GETQUERYRESULTS_RESP_MSG.VERSION_1">
    <query numrows="10" queryname="" xmlns="http://xmlns.oracle.com/
     Enterprise/Tools/schemas/QAS_QUERYRESULTS_XMLP_RESP.VERSION_1">
        <row rownumber="1">
            <MESSAGE_SET_NBR>1</MESSAGE_SET_NBR>
            <DESCR>
                <![CDATA[PeopleTools Message Bar Items]]>
            </DESCR>
        </row>
        <row rownumber="2">
            <MESSAGE_SET_NBR>2</MESSAGE_SET_NBR>
            <DESCR>
                <![CDATA[PeopleCode]]>
            </DESCR>
        </row>
        <row rownumber="3">
            <MESSAGE_SET_NBR>3</MESSAGE_SET_NBR>
            <DESCR>
                <![CDATA[General Tools Messages]]>
            </DESCR>
        </row>
        <row rownumber="4">
            <MESSAGE_SET_NBR>4</MESSAGE_SET_NBR>
            <DESCR>
                <![CDATA[Help Processor]]>
            </DESCR>
        </row>
        <row rownumber="5">
            <MESSAGE_SET_NBR>5</MESSAGE_SET_NBR>
            <DESCR>
                <![CDATA[Help Text Manager]]>
            </DESCR>
        </row>
        <row rownumber="6">
            <MESSAGE_SET_NBR>6</MESSAGE_SET_NBR>
            <DESCR>
                <![CDATA[Import Definition Manager]]>
            </DESCR>
        </row>
        <row rownumber="7">
            <MESSAGE_SET_NBR>7</MESSAGE_SET_NBR>
            <DESCR>
                <![CDATA[Open Query API]]>
            </DESCR>
        </row>
        <row rownumber="8">
            <MESSAGE_SET_NBR>8</MESSAGE_SET_NBR>
            <DESCR>
                <![CDATA[PeopleCode Editor]]>
            </DESCR>
        </row>
        <row rownumber="9">
            <MESSAGE_SET_NBR>9</MESSAGE_SET_NBR>
            <DESCR>
                <![CDATA[Menu Definition Manager]]>
            </DESCR>
        </row>
        <row rownumber="10">
            <MESSAGE_SET_NBR>10</MESSAGE_SET_NBR>
            <DESCR>
                <![CDATA[Change Control]]>
            </DESCR>
        </row>
    </query>
</QAS_GETQUERYRESULTS_RESP_MSG>

This service operation runs the query request in the Process Scheduler. This service operation should be used for long running queries or queries with large result sets. The query is executed in batch mode through Integration Broker asynchronous servers (Pub/Sub) and the Process Schedulers.

When a client request for QAS_EXECUTEQRYPOLL_REST_GET is received by the Integration Broker, the following occurs:

  1. An asynchronous one way service operation which gets processed by Integration Broker.

  2. The query instance ID (transaction ID) is returned to the requester. The requester can use the query instance to poll for the status (QAS_QUERYSTATUS_REST_GET) and to retrieve the query results (QAS_GETQUERYRESULTS_REST_GET).

  3. The service operation schedules the application engine program QASEXEQRY in the Process Scheduler.

The user ID in the request header must have permission to execute the QASEXEQRY application engine program, as well as permission for the service operation. The PeopleSoft delivered role QAS Admin grants the necessary permissions.

Element Name

Description

QueryName

Query name

It is a required variable.

OwnerType

Query owner type, public or private in either uppercase or lowercase.

It is a required variable.

OutResultType

Select the output type. Valid values are:

  • WEBROWSET

  • XMLP

  • EXCEL

  • HTML

It is a required variable.

OutResultFormat

Select the output format. Valid values are:

  • FILE

  • NONFILE

It is a required variable.

isConnectedQuery

Indicate whether this is a connected query. Valid values are Y or N in either upper or lower case.

It is a required variable.

MaxRow

The maximum number of rows to be fetched.

It is a required variable.

BlockSizeKB

If the output format is nonfile, indicate the block size to use for chunking the response. BlockSizeKB is a number of length 10 with a decimal position of 0. Block size is in bytes.

It is a required variable.

Prompt_PSQueryName

This field is used only for Connected Query. Enter the name of the query containing the prompt.

Prompt_UniquePromptName

Unique prompt name defined in the query. This value is case-sensitive.

Prompt_FieldValue

Field value for the prompt. This value is case-sensitive.

FilterFields

List of field names to be returned. This value is case sensitive and must be the unique field name as returned by the service operation QAS_LISTQUERYFIELDS.

json_response

Enter true for JSON response type; enter false for standard XML response. If no value is provided, the default is standard XML response.

Example of an URI template

{OwnerType}/{QueryName}/{OutResultType}/{OutResultFormat}?isconnectedquery=
{isConnectedQuery}&maxrows={MaxRow}&blocksize={BlockSizeKB}&prompt_psqueryname=
{Prompt_PSQueryName*}&prompt_uniquepromptname={Prompt_UniquePromptName*}
&prompt_fieldvalue={Prompt_FieldValue*}&filterfields={FilterFields*}
&json_resp={json_response}

Example Request:

https://<servername>:<port>/PSIGW/RESTListeningConnector/<defaultlocal node>/ExecuteQueryPoll.v1/public/MESSAGES_FOR_MSGSET/WEBROWSET/NONFILE?isconnectedquery=N&maxrows=1000&blocksize=500&prompt_uniquepromptname=MESSAGE_SET_NBR&prompt_fieldvalue=28&filterfields=A.MESSAGE_SET_NBR,A.MESSAGE_NBR,A.MSG_SEVERITY,A.MESSAGE_TEXT

Example Response:


<?xml version="1.0"?>
<QAS_EXEQRYSYNCPOLL_RESP_MSG xmlns="http://xmlns.oracle.com/Enterprise/
Tools/schemas/QAS_EXEQRYSYNCPOLL_RESP_MSG.VERSION_1">
    <QAS_EXEQRYSYNCPOLL_RESP>
        <PTQASWRK class="R" xmlns="http://xmlns.oracle.com/Enterprise/
         Tools/schemas/QAS_EXEQRYSYNCPOLL_RESP.VERSION_1">
            <QueryInstance>fbf8a6f3-f1b8-11e3-b37c-9e8b0079144b</QueryInstance>
        </PTQASWRK>
    </QAS_EXEQRYSYNCPOLL_RESP>
</QAS_EXEQRYSYNCPOLL_RESP_MSG>

Note: Use the service operation QAS_QUERYSTATUS_REST_GET to get the status of the scheduled Query process, and QAS_GETQUERYRESULTS_REST_GET to retrieve the query results. These are detailed further below, under “Retrieving Query Results” section.

Example Response when json_response=true:

{"status": "success","data": {"queryInstance": "971968ee-032a-11e5-ae7b-e5e5391e9b13"}}

This service operation is similar to QAS_EXECUTEQRYPOLL_REST_GET.v1, but is used for running an ad-hoc query request in the Process Scheduler. This service operation should be used for long running queries or queries with large result sets. The query is executed in batch mode through Integration Broker asynchronous servers (Pub/Sub) and the Process Schedulers.

Example Request:

http://<servername>:<port>/PSIGW/RESTListeningConnector/<defaultlocal node>/ExecuteAdhocQueryPoll.v1/executeadhocquerypoll

POST Message

<?xml version="1.0"?>
<QAS_EXEQRY_SYNC_REQ_MSG>
 	<QAS_EXEQRY_SYNC_REQ>
		<QueryName/>
		<isConnectedQuery>N</isConnectedQuery>
 		<XMLString>
		<!--1 or more repetitions:-->
	<RECORD>
		<RCDNUM>1</RCDNUM>
 		<RCDNAME>PSMSGSETDEFN</RCDNAME>
		<CORRNAME>A</CORRNAME>
		<JOINTYPE></JOINTYPE>
		<JOINRCDALIAS></JOINRCDALIAS>
		<JOINFIELD></JOINFIELD>
  		<RCDSELECTNUM>1</RCDSELECTNUM>
	</RECORD>
	<!--1 or more repetitions:-->
	<FIELD>
		<FIELDNUM>1</FIELDNUM>
 		<FIELDNAME>MESSAGE_set_nbr</FIELDNAME>
 		<FIELDRCDNUM>1</FIELDRCDNUM>
	 	<DESCR></DESCR>
		<AGGREGATE_TYPE>None</AGGREGATE_TYPE>
		<HEADING_TYPE>RFT Short</HEADING_TYPE>
 		<HEADING></HEADING>
		<COLUMNNUM>1</COLUMNNUM>
		<ORDERBYNUM>1</ORDERBYNUM>
		<ORDERBYDIR></ORDERBYDIR>
		<FIELDSELECTNUM>1</FIELDSELECTNUM>
		<EXPRESSION_AS_FIELD></EXPRESSION_AS_FIELD>
	</FIELD>
 	<FIELD>
		<FIELDNUM>2</FIELDNUM>
		<FIELDNAME>descr</FIELDNAME>
		<FIELDRCDNUM>1</FIELDRCDNUM>
		<DESCR></DESCR>
		<AGGREGATE_TYPE>None</AGGREGATE_TYPE>
		<HEADING_TYPE>RFT Short</HEADING_TYPE>
 		<HEADING></HEADING>
		<COLUMNNUM>2</COLUMNNUM>
		<ORDERBYNUM>0</ORDERBYNUM>
		<ORDERBYDIR></ORDERBYDIR>
		<FIELDSELECTNUM>1</FIELDSELECTNUM>
		<EXPRESSION_AS_FIELD></EXPRESSION_AS_FIELD>
	</FIELD>
 	<SELECT>
		<SELECTNUM>1</SELECTNUM>
		<PARENTSELECTNUM>0</PARENTSELECTNUM>
		<SELECTTYPE>Main</SELECTTYPE>
		<QRYDISTINCT></QRYDISTINCT>
	</SELECT>
 	</XMLString> 
 		<OwnerType/>
		<BlockSizeKB>0</BlockSizeKB>
		<MaxRow>1000</MaxRow>
		<OutResultType>XMLP</OutResultType>
		<OutResultFormat>NONFILE</OutResultFormat>
		<Prompts/>
	 </QAS_EXEQRY_SYNC_REQ>
</QAS_EXEQRY_SYNC_REQ_MSG>

Example Response:

<?xml version="1.0"?>
<QAS_EXEQRYSYNCPOLL_RESP_MSG xmlns="http://xmlns.oracle.com/Enterprise/
Tools/schemas/QAS_EXEQRYSYNCPOLL_RESP_MSG.VERSION_1">
    <QAS_EXEQRYSYNCPOLL_RESP>
        <PTQASWRK class="R" xmlns="http://xmlns.oracle.com/Enterprise/
         Tools/schemas/QAS_EXEQRYSYNCPOLL_RESP.VERSION_1">
            <QueryInstance>fbf8a6f3-f1b8-11e3-b37c-9e8b0079144b</QueryInstance>
        </PTQASWRK>
    </QAS_EXEQRYSYNCPOLL_RESP>
</QAS_EXEQRYSYNCPOLL_RESP_MSG>