Data360 Analyze

I'm trying to fetch an XML stored in a database table using a DB Query node with an intent to use an XML Data node afterwards to convert it into tabular form.

Is there another way to implement the logic above or a way to resolve the issue below when fetching the column with CLOB data type using the DB Query node?

Execution Warning:
Unable to translate OCI type: 112
Execution Error:
Node execution terminated while opening i/o streams by error: Unable to translate OCI type: 112

I have a few places where I select from a clob and then do JSON node - which is more or less the same thing as I think you're trying to do. It works out of the box for me, but I do use the JDBC driver to connect to Oracle.

Since you have a clob already, you could also consider pushing down the xml table operation to the database and just selecting out what you need from the XML as columns instead of the XML node. But I guess you thought of that already. 

(Further down the body of this post shows extraction).

https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:44892646023494

Good luck.

I'm trying to fetch an XML stored in a database table using a DB Query node with an intent to use an XML Data node afterwards to convert it into tabular form.

Is there another way to implement the logic above or a way to resolve the issue below when fetching the column with CLOB data type using the DB Query node?

Execution Warning:
Unable to translate OCI type: 112
Execution Error:
Node execution terminated while opening i/o streams by error: Unable to translate OCI type: 112

I reproduced your problem with the Db Query node.

I think this is a very low level issue due to the CLOB datatype and OCI. I would stay away and use the JDBC interface to get the data / as below with the same SQL it works for me. If you can-t you need to raise a ticket with support.

I have a few places where I select from a clob and then do JSON node - which is more or less the same thing as I think you're trying to do. It works out of the box for me, but I do use the JDBC driver to connect to Oracle.

Since you have a clob already, you could also consider pushing down the xml table operation to the database and just selecting out what you need from the XML as columns instead of the XML node. But I guess you thought of that already. 

(Further down the body of this post shows extraction).

https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:44892646023494

Good luck.

I'm trying to fetch an XML stored in a database table using a DB Query node with an intent to use an XML Data node afterwards to convert it into tabular form.

Is there another way to implement the logic above or a way to resolve the issue below when fetching the column with CLOB data type using the DB Query node?

Execution Warning:
Unable to translate OCI type: 112
Execution Error:
Node execution terminated while opening i/o streams by error: Unable to translate OCI type: 112

Thank you Peter. I really appreciate your help. I'm very new to Data360 as I only started a week ago. I'll try to ask some colleagues on how to configure the JDBC Query node.

I reproduced your problem with the Db Query node.

I think this is a very low level issue due to the CLOB datatype and OCI. I would stay away and use the JDBC interface to get the data / as below with the same SQL it works for me. If you can-t you need to raise a ticket with support.

I have a few places where I select from a clob and then do JSON node - which is more or less the same thing as I think you're trying to do. It works out of the box for me, but I do use the JDBC driver to connect to Oracle.

Since you have a clob already, you could also consider pushing down the xml table operation to the database and just selecting out what you need from the XML as columns instead of the XML node. But I guess you thought of that already. 

(Further down the body of this post shows extraction).

https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:44892646023494

Good luck.

I'm trying to fetch an XML stored in a database table using a DB Query node with an intent to use an XML Data node afterwards to convert it into tabular form.

Is there another way to implement the logic above or a way to resolve the issue below when fetching the column with CLOB data type using the DB Query node?

Execution Warning:
Unable to translate OCI type: 112
Execution Error:
Node execution terminated while opening i/o streams by error: Unable to translate OCI type: 112

It should be more or less the same (use the same DB Query parameters now in the more or less the same fields on the JDBC node). Depending on how friendly the tnsnames has been set up in your organization that will work. If that is not so nice, e.g. if you use ldap, then you will need to use the same JDBC connection URL as other applications use to connect - and in that case you will need the JDBC URL parameter in the "advanced" area of the JDBC Query node.

Thank you Peter. I really appreciate your help. I'm very new to Data360 as I only started a week ago. I'll try to ask some colleagues on how to configure the JDBC Query node.

I reproduced your problem with the Db Query node.

I think this is a very low level issue due to the CLOB datatype and OCI. I would stay away and use the JDBC interface to get the data / as below with the same SQL it works for me. If you can-t you need to raise a ticket with support.

I have a few places where I select from a clob and then do JSON node - which is more or less the same thing as I think you're trying to do. It works out of the box for me, but I do use the JDBC driver to connect to Oracle.

Since you have a clob already, you could also consider pushing down the xml table operation to the database and just selecting out what you need from the XML as columns instead of the XML node. But I guess you thought of that already. 

(Further down the body of this post shows extraction).

https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:44892646023494

Good luck.

I'm trying to fetch an XML stored in a database table using a DB Query node with an intent to use an XML Data node afterwards to convert it into tabular form.

Is there another way to implement the logic above or a way to resolve the issue below when fetching the column with CLOB data type using the DB Query node?

Execution Warning:
Unable to translate OCI type: 112
Execution Error:
Node execution terminated while opening i/o streams by error: Unable to translate OCI type: 112