Toad World® Forums

ORA-12704 when comparing SYS_REFCURSOR to expected query


#1

Hello,

I recently went through the quick start guide on comparing expected results to ref curosr variables, and everything worked correctly. However, when I tried to apply the same approach to one of my own procedures, I received an “ORA-12704: character set mismatch” at compile time. The query I am attempting to run works just fine through sql*plus:

SELECT EMPLOYEEID, SURNAME
FROM MYSCHEMA.MYTABLE
WHERE EMPLOYEEID = ‘0000011111’

The compile is failing on

           OPEN CV FOR
                 SELECT 1
                   FROM DUAL
                  WHERE EXISTS
                           ( (SELECT *
                                FROM TABLE (Q##1015536677#NTF)
                              MINUS
                              SELECT EMPLOYEEID, SURNAME
                                FROM MYSCHEMA.MYTABLE
                               WHERE EMPLOYEEID = '0000011111')
                            UNION ALL
                            (SELECT EMPLOYEEID, SURNAME
                                FROM MYSCHEMA.MYTABLE
                               WHERE EMPLOYEEID = '0000011111'
                             MINUS
                             SELECT *
                               FROM TABLE (Q##1015536677#NTF)));

The procedure IN parameters are both declared as CHAR(32767) in Quest Code Tester. The expected results type is a ‘Query’ with the above query inserted. The “Evaluate string as expression” box is not checked. I am out of ideas as to why the generation of the “quick start” test case works and my test case does not. Any thoughts?


#2

Can you please provide the structure of the table - what are the types of EMPLOYEEID and SURNAME?


#3

Besides answering my previous question, could you please create a support bundle when you get this error, make sure that it includes the program you are testing, and send it to me:

steven.feuerstein@quest.com

Thanks, SF


#4

Thank you for your response. The table info is as follows:

Surname: NVARCHAR2(64)
Employeeid: NVARCHAR2(50)

I will send the remaining information as requested.


#5

To finish up this thread, the problem is that we are using DBMS_DESCRIBE to get column datatypes and NVARCHAR2 columns are “revealed” to be VARCHAR2. Leading to this bug. We are now in 1.8.4 providing you with a way to override the default type conversion and specify your own, via a comment in the outcome initialization code.