@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 ?
I found out what the problem is, it seems that GoldenGate doesn't work with SQL Express. The server I was connecting to is SQL Express, I'll need to use the Enterprise Edition.
Best Answer
You have to call
before installing the package to ensure that
"
and>
are not recognized as substitution variables. See also docs.