SoftTree Technologies SoftTree Technologies
Technical Support Forums
RegisterSearchFAQMemberlistUsergroupsLog in
This type of result set or referenced data types are not spt

 
Reply to topic    SoftTree Technologies Forum Index » SQL Assistant View previous topic
View next topic
This type of result set or referenced data types are not spt
Author Message
SqlExplorer



Joined: 18 Sep 2011
Posts: 103

Post This type of result set or referenced data types are not spt Reply with quote
Hi,

I'm nearly certain this is related to the oci driver, and not sql assistant.

I'm able to run certain queries in Sql Developer, which uses jdbc. But the Windows oracle oci driver I use for some of the same queries are resulting in an error:

This type of result set or referenced data types are not supported.

This query is a SELECT, with some SUBSELECTS, and an IN clause, and aliases.


I don't specifically what they mean by referenced data type.

Can you offer a suggestion on where I can search for info on this? Or maybe which Oracle driver works best, with Sql Assistant?


Thanks,
Thu May 13, 2021 9:41 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7833

Post Reply with quote
Can you please share sample query that you can't run?

Which version of SQL Assistant do you use?
Thu May 13, 2021 10:05 am View user's profile Send private message
SqlExplorer



Joined: 18 Sep 2011
Posts: 103

Post Reply with quote
Version 11.5.362.


The query is something like this (with tables and field renamed):


code]SELECT
RECEIVED_ID,
RECEIVED_DETAIL,
STATEXX_AGENCY_APP_SENT_ID
FROM
RECEIVED_TRANSACTIONS
WHERE MY_AGENCY_ID IN
( SELECT
MY_AGENCY_ID
FROM
STATEXX_Agency_App_Sent AAS,
( SELECT
STATEXX_APP_ID,
STATEXX_COM_DATA_ID AS CBA,
STATEXX_AGENCY_SPEC_APP_ID
FROM
STATEXX_agency_lic_specific_app
WHERE STATEXX_APP_ID IN ( 'XXXXXXXX' )
) LSPC
WHERE AAS.STATEXX_AGENCY_SPEC_APP_ID = LSPC.STATEXX_AGENCY_SPEC_APP_ID
)
ORDER BY
MY_AGENCY_ID DESC,
CREATED_TIME DESC ; [/code] [/code]
Thu May 13, 2021 11:51 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7833

Post Reply with quote
It may or may not be an OCI driver issue. May I ask about the data type of RECEIVED_DETAIL? Is that a nested table, XML collection, or some other kind of an object?
Thu May 13, 2021 1:11 pm View user's profile Send private message
SqlExplorer



Joined: 18 Sep 2011
Posts: 103

Post Reply with quote
Good guess. It's an XML type.

It wasn't only the oci driver that had the problem, the microsoft odbc driver also had problems.

I guess there's no way to 'break up' the query in any way that would spit out that xml field. I was hoping this was some other kind of problem, where we could subsitute views for some of the subqueries, or do something with a virtual tabls.
Thu May 13, 2021 2:17 pm View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7833

Post Reply with quote
If it's XMLTYPE, you should be able to convert it to CLOB string like

SELECT ...,
XmlSerialize(DOCUMENT RECEIVED_DETAIL AS CLOB) AS RECEIVED_DETAIL,
...,
...

Depending on how XML is structured, you may need to use either DOCUMENT or CONTENT
For usage and options, please see
https://docs.oracle.com/database/121/SQLRF/functions268.htm#SQLRF06231
Thu May 13, 2021 3:13 pm View user's profile Send private message
SqlExplorer



Joined: 18 Sep 2011
Posts: 103

Post Reply with quote
Sonofagun. That worked.

Thanks, Sysop.
Thu May 13, 2021 4:48 pm View user's profile Send private message
Display posts from previous:    
Reply to topic    SoftTree Technologies Forum Index » SQL Assistant All times are GMT - 4 Hours
Page 1 of 1

 
Jump to: 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


 

 

Powered by phpBB © 2001, 2005 phpBB Group
Design by Freestyle XL / Flowers Online.