Unable to find solution about missing left parenthesis in SQL statement

Question

SQL Developer says missing left parenthesis.

ORA-00906: missing left parenthesis 00906. 00000 - "missing left parenthesis" *Cause:
*Action: Error at Line: 8 Column: 108

Oracle SQL

SELECT  t.DSA_NAME as "DSA NAME",
    t.REVISION as REVISION,
    XMLTYPE(t.DSA_SOURCE).EXTRACT('//PreProcessor/statements/text()').getStringVal() AS "PreProcessor Temp Table", 
    XMLTYPE(t.DSA_SOURCE).EXTRACT('//PostProcessor/statements/text()').getStringVal() AS "PostProcessor Temp Table"
    FROM 
    DSA_SOURCE t
    where XMLCAST (XMLTYPE(t.DSA_SOURCE).EXTRACT('//PreProcessor/statements/text()').getStringVal() AS VARCHAR2) like '%create%temp%'
    and t.REVISION IN (SELECT MAX(REVISION)
                     FROM DSA_SOURCE
                     GROUP BY DSA_NAME);

I have tried using VARCHAR2(100) instead of VARCHAR in CAST function, but then I am getting below error.

ORA-00932: inconsistent datatypes: expected - got - 00932. 00000 - "inconsistent datatypes: expected %s got %s" *Cause: *Action:

Answer 1

This Stackoverflow answer sums it up quite nicely https://stackoverflow.com/a/10197710/9822083

SELECT  t.DSA_NAME as "DSA NAME",
t.REVISION as REVISION,
XMLTYPE(t.DSA_SOURCE).EXTRACT('//PreProcessor/statements/text()').getStringVal() AS "PreProcessor Temp Table", 
XMLTYPE(t.DSA_SOURCE).EXTRACT('//PostProcessor/statements/text()').getStringVal() AS "PostProcessor Temp Table"
FROM 
DSA_SOURCE t
where XMLCAST (XMLTYPE(t.DSA_SOURCE).EXTRACT('//PreProcessor/statements/text()').getStringVal() AS VARCHAR2(50)) like '%create%temp%'
and t.REVISION IN (SELECT MAX(REVISION)
                 FROM DSA_SOURCE
             GROUP BY DSA_NAME);

VARCHAR2 is a type that needs a maximum size/length. Your missing left parenthesis is the parenthesis that surrounds the size.

Answer 2

As @Jedo said, you need to specify the size of the varchar2; but you mentioned in your question that you already tries that and got "ORA-00932: inconsistent datatypes". That is because the you are using getStringValue() within that XMLCast() call, when it is expecting an XMLType.

where XMLCAST (XMLTYPE(t.DSA_SOURCE).EXTRACT('//PreProcessor/statements/text()').getStringVal() AS VARCHAR2(100)) like '%create%temp%'

Either stick to just the XMLCast():

where XMLCAST (XMLTYPE(t.DSA_SOURCE).EXTRACT('//PreProcessor/statements/text()') AS VARCHAR2(100)) like '%create%temp%'

or remove that and only use getStringVal():

where XMLTYPE(t.DSA_SOURCE).EXTRACT('//PreProcessor/statements/text()').getStringVal() like '%create%temp%'

... but not both.

---

You could also do this with XMLQuery() and XMLExists():

select t.dsa_name as "DSA NAME",
  t.revision as revision,
  xmlquery('//PreProcessor/statements/text()'
    passing xmltype(t.dsa_source)
    returning content) as "PreProcessor Temp Table",
  xmlquery('//PostProcessor/statements/text()'
    passing xmltype(t.dsa_source)
    returning content) as "PostProcessor Temp Table"
from dsa_source t
where xmlexists('//PreProcessor/statements[matches(text(), ''create.*temp'')]'
  passing xmltype(t.dsa_source))
and (t.dsa_name, t.revision) in (
  select dsa_name, max(revision)
  from dsa_source
  group by dsa_name
);

I've changed the final clause too; with your version you're finding a list of revisions (all the highest revisions across all names), but then looking for any of those revisions for any name. I've amended it to look for the pairs of name and the maximum revision for that name.

db<>fiddle (shown in 18c; this doesn't work in the 11gR2 version there, which is 11.2.0.2, but it does work in 11.2.0.4 and higher.)

If dsa_source is a varchar2 you could avoid querying the table twice by using an analytic function, but that won't work with clob so probably isn't helpful.