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:
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:
Note: For connected queries, the output type must be XMLP. |
|
OutResultFormat Required element |
Select the output format. Valid values are:
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.example.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:
It is a required variable. |
|
OutResultFormat |
Select the output format. Valid values are:
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:
Note: For connected queries, the output type must be XMLP. |
|
OutResultFormat Required element |
Select the output format. Valid values are:
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.example.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.example.com\
/psreports\/Q8551093\/308\/XRFWIN.html","status": "posted"}}Example Response (JSON/FILE) using json_response=true:
{"status": "success","data": {"fileurl":"http:\/\/myserver.example.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:
An asynchronous one way service operation which gets processed by Integration Broker.
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).
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:
Note: For connected queries, the output type must be XMLP. |
|
OutResultFormat Required element |
Select the output format. Valid values are:
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:
Note: For connected queries, the output type must be XMLP. |
|
OutResultFormat |
Select the output format. Valid values are:
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:
An asynchronous one way service operation which gets processed by Integration Broker.
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).
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:
It is a required variable. |
|
OutResultFormat |
Select the output format. Valid values are:
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>