 |
SoftTree Technologies
Technical Support Forums
|
|
Author |
Message |
SqlExplorer
Joined: 18 Sep 2011 Posts: 124 Country: United States |
|
This type of result set or referenced data types are not spt |
|
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 |
|
 |
SysOp
Site Admin
Joined: 26 Nov 2006 Posts: 7948
|
|
|
|
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 |
|
 |
SqlExplorer
Joined: 18 Sep 2011 Posts: 124 Country: United States |
|
|
|
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 |
|
 |
SysOp
Site Admin
Joined: 26 Nov 2006 Posts: 7948
|
|
|
|
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 |
|
 |
SqlExplorer
Joined: 18 Sep 2011 Posts: 124 Country: United States |
|
|
|
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 |
|
 |
SysOp
Site Admin
Joined: 26 Nov 2006 Posts: 7948
|
|
|
Thu May 13, 2021 3:13 pm |
|
 |
SqlExplorer
Joined: 18 Sep 2011 Posts: 124 Country: United States |
|
|
|
Sonofagun. That worked.
Thanks, Sysop.
|
|
Thu May 13, 2021 4:48 pm |
|
 |
|
|
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
|
|
|