search

yajra / pdo-via-oci8

PHP PDO_OCI functions via OCI8 extension
Other
88 stars 61 forks source link

ORA-00932 with tabletype columns #39

Open ircop opened 8 years ago

ircop commented 8 years ago

got ORA-00932: inconsistent datatypes when one of table/view column defined as tabletype

I'm working with some oracle API, that i can't change. In the api's last update developers has added to several views column T_TAGS. Here is it's definition:

CREATE OR REPLACE FORCE VIEW "AIS_NET"."SI_V_SUBJECTS" AS SELECT ... SI_SUBJECTS_PKG_S.GET_VC_NAME(N_CREATOR_ID) VC_CREATOR_NAME, CAST( MULTISET( SELECT VC_CODE FROM SI_V_ENTITIES_TAGS WHERE N_ENTITY_ID = S.N_SUBJECT_ID AND N_ENTITY_TYPE_ID = SYS_CONTEXT('CONST', 'ENTITY_TYPE_Subject') ORDER BY N_LINE_NO) AS TAGS) T_TAGS, (SELECT LISTAGG(VC_CODE, ', ') WITHIN GROUP (ORDER BY N_LINE_NO) FROM SI_V_ENTITIES_TAGS ...

And when i'm trying simple select * from SI_V_SUBJECTS', im getting:

oci_fetch_assoc(): ORA-00932: inconsistent datatypes: expected CHAR got TABLE (SQL: select * from (select * fr om SI_V_SUBJECTS where vc_code = wingmanzz) where rownum = 1)

Can we fix this issue somehow, or only define selecting columns without T_TAGS?

Thanks and sorry for my bad english

yajra commented 7 years ago

Whoops, sorry! I have overlooked this issue. Were you able to resolve this? I am not really familiar with T_TAGS so I may not be able to help you much with this matter. :(

ircop commented 7 years ago

Unfortunately no, i have not found a way to fix this :( At this moment i've defined in my views all the columns except table typed, and when selecting, i replace * to columns list