Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am trying to use built-in XML functions, but I'm getting an %s: invalid identifier error. Selects works fine without the XML functions.

Any ideas where is the mistake?

select id,full_name from users where id in(
with sample_data as
(
 (select pm_id 
 from pm  where p_id=30369)
)
select cast(t.column_value.extract('//text()') as varchar2(40)) val
  from sample_data,
  table( xmlsequence( xmltype(
    '<x><x>' || replace(pm_id, '#@#', '</x><x>') || '</x></x>'
  ).extract('//x/*'))) t)
share|improve this question
1  
What oracle version? –  Phil May 10 '13 at 8:31
    
the version is 11g –  elimerv May 10 '13 at 8:36
    
Can you at least try and execute it in SQL developer or sql plus? It'll tell you where the error is –  Phil May 10 '13 at 8:36
    
sql exception: ORA-04023: Object could not be validated or authorized –  elimerv May 10 '13 at 8:45
    
I got this error. in sql plus. And in sql developer it says it is the first line –  elimerv May 10 '13 at 8:46

3 Answers 3

@elimerv,

Are you sure you are not having something else in place of "single-quote" that is expected there? Also, you don't tell what kind of data does the "pm" table has in the pm_id column (BTW, your latest SQL doesn't appear to be correct as your sample_data subquery has an "id" column whereas your main query refers to "pm_id" column). This is what I get on 11.2.0.1 First, your latest query, as it is (except for tables used):

with sample_data as (
   select '123#@#' id
   from dual
  )
  select sysdate from dual where dummy  in(
    select cast(t.column_value.extract('//text()') as varchar2(400)) val
    from sample_data,
         table(xmlsequence(xmltype('<x><x>'||replace(pm_id,'#@#','</x><x>')||'</x></x>').extract('//x/*'))) t
  )
 10  /
         table(xmlsequence(xmltype('<x><x>'||replace(pm_id,'#@#','</x><x>')||'</x></x>').extract('//x/*'))) t
                                                     *
ERROR at line 8:
ORA-00904: "PM_ID": invalid identifier

Now, with correct column name used:

with sample_data as (
   select '123#@#' pm_id
   from dual
  )
  select sysdate from dual where dummy  in(
    select cast(t.column_value.extract('//text()') as varchar2(400)) val
    from sample_data,
         table(xmlsequence(xmltype('<x><x>'||replace(pm_id,'#@#','</x><x>')||'</x></x>').extract('//x/*'))) t
  )
 10  /

no rows selected

And, when I replaced one of the "single-quotes" with a different (but similar looking) character :

with sample_data as (
   select '123#@#' pm_id
   from dual
  )
  select sysdate from dual where dummy  in(
    select cast(t.column_value.extract('//text()') as varchar2(400)) val
    from sample_data,
         table(xmlsequence(xmltype('<x><x>'||replace(pm_id,'#@#','</x><x>')||'</x></x>').extract(`//x/*'))) t
  )
 10  /
         table(xmlsequence(xmltype('<x><x>'||replace(pm_id,'#@#','</x><x>')||'</x></x>').extract(`//x/*'))) t
                                                                                                 *
ERROR at line 8:
ORA-00911: invalid character

Does this give you any idea? BTW, are you sure you need XML processing to solve your problem ?

share|improve this answer

I think Phil is on the right track. You need to move the CTE ("WITH clause") out of the sub-select

Try this one:

with sample_data as (
 select pm_id 
 from pm  
 where p_id=30369
)
select id,
       full_name 
from users 
where id in(
  select cast(t.column_value.extract('//text()') as varchar2(40)) val
  from sample_data,
       table(xmlsequence(xmltype('<x><x>'||replace(pm_id,'#@#','</x><x>')||'</x></x>').extract('//x/*'))) t
)
share|improve this answer
    
Thanks your reply but no luck, got the same error –  elimerv May 10 '13 at 11:15
    
Edit your answer with the full error messages when run through sqlplus. Cut and paste the query and EVERYTHING up to where the SQL> prompt returns after the message. –  Phil May 10 '13 at 11:17
SQL> with sample_data as (
  2   select  id
  3   from pm
  4   where p_id=30369
  5  )
  6  select id, full_name from users where id  in(
  7    select cast(t.column_value.extract('//text()') as varchar2(400)) val
  8    from sample_data,
  9         table(xmlsequence(xmltype('<x><x>'||replace(pm_id,'#@#','</x><x>')||'</x></x>').extract('//x/*'))) t
 10  )
 11  /
       table(xmlsequence(xmltype('<x><x>'||replace(pm_id,'
#@#','</x><x>')||'</x></x>').extract('//x/*'))) t
                                     *
ERROR at line 9:
ORA-00904: : invalid identifier


SQL>
share|improve this answer
    
I got the above error –  elimerv May 10 '13 at 12:53

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.