QAS_EXECUTEQRY_REST

QAS_EXECUTEQRY_REST_GET

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 4-7 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 or N 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.

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 or N 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.

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"}