Toad World® Forums

XMLAGG not working in TDP?

#1

The following syntax works fine in Toad for Oracle 12.1:

** SELECT deptid,**
** XMLAGG (XMLELEMENT (E,lastname||’; ‘) ).EXTRACT (’//text()’) AS “EMPS”**
FROM
(SELECT deptid, lastname FROM emptest)
GROUP BY deptid;

The results look like this:

DEPTID
EMPS
10
SMITH; HARRIS; JONES;
20
TYLER;
However this same query does not work in Toad Data Point 3.6 when I’m connected to the same Oracle database. Instead I get this error:

ORA-00942: table or view does not exist

How can I get XMLAGG to work in TDP? I am on version 3.6.1.3294.

(I know that LISTAGG would solve the problem, but sometimes XMLAGG works better than LISTAGG.)

If you want to try recreating the above test, below are the commands I used to create the temp table EMPTEST:

CREATE TABLE EMPTEST
( EMPID NUMBER,
LASTNAME VARCHAR2(20),
DEPTID NUMBER);

INSERT INTO EMPTEST VALUES
(1,‘SMITH’,10);
INSERT INTO EMPTEST VALUES
(2,‘JONES’,10);
INSERT INTO EMPTEST VALUES
(3,‘TYLER’,20);
INSERT INTO EMPTEST VALUES
(4,‘HARRIS’,10);

#2

Works fine for me. Might it be some kind of a permission issue on your side?

Igor.

#3

Hi Igor,

Are you using the same version of TDP as me, which is 3.6.1.3294 ?

I don’t think permissions are involved because I’m using the same logon and same database for both TDP and Toad for Oracle. There should be no difference in my permissions when using these applications.

Another user at our site is getting the same error as me, so the issue is not limited to my PC or my logon.

Further testing indicates the problem is related to XMLELEMENT, because the following query (without XMLAGG) gives the same error in TDP as before:

SELECT XMLELEMENT (EMP,lastname)FROM emptest;

I also tried using a database table, rather than my own temp table EMPTEST, and I got the same error.

I also tried deleting my application data directory (after saving a copy) so that I was starting with all defaults. Didn’t help.

Can you suggest anything else I should try? Should I contact support?

#4

This error occurs because you probably are using a Direct Connection to Oracle. This is a very simple TCP/IP type connection that does not handle XMl type objects. If you switch to an Orcle Client it will work. I am able to execute the queries with Oracle Client and can get the same error with Direct Connect. This is not a bug. Just a limitation of that type of connection.

#5

I am not using a Direct Connection, instead I'm already using Oracle Client. Here is a screenshot:

Connection screen.png
Connection screen.png389×815 12.5 KB

Both TDP and Toad for Oracle are using the same TNSNAMES.ORA file to connect to this database. The file says (PROTOCOL = TCP)(HOST = 192.xx.xx.xx)(PORT = 1521)

(The file has the full IP address, I changed the numbers to x's above for security)

#6

Maybe you need to upgrade your OCI client? You are using 11G client with 12.1 db. Something is occurring in how Oracle is being accessed. Igor and I tried your example and have no issues. As discussed I did get your error when not using an Oracle client.

#7

Toad for Oracle is version 12.1, not our database. Our Oracle database is 11.2.0.1.0. Yes, I understand that it works OK for you and Igor. I need help to figure out why it doesn’t work here. Particularly when this command still works fine with your other product Toad for Oracle. Are you and Igor using the same version of TDP as me, which is 3.6.1.3294 ? Thanks for any additional suggestions you can offer.

P.S. My query works fine in Toad for Oracle and also in Oracle SQL Developer. Toad Data Point gives the error. Here’s another sample query that illustrates the problem without needing any temp tables:

SELECT XMLELEMENT (“date”, sysdate) FROM dual;

Output should look like this, with no errors:

2015-02-11

FYI, I have escalated this issue to Dell Support, as Service Request Number 2733844.

#8

Yes, I am using same version. Still can’t reproduce. Let’s see if support can reproduce.