Passing regexp_replace backreference to a function; type conversion problem
Posted on 2006-06-07
I am using 10.2.0.1.0 on Windows.
I am trying to convert some text within my CLOB field to HTML links.
The format of the (part of the link I am having problems with) is:
which I wish to convert to
I am trying to pass the backreference \1 (being the number 12 in this case) in regexp_replace to a simple function I have made and when I run it I receive the error:
ORA-06502: PL/SQL: numeric or value error: character to number conversion error
I have tested the function on its own with passing a string and it works fine.
I have passed the string from the backreference to the function and back out as the return value and it works fine.
However, when I try to pass the string back reference (a set of digits) to use as the NUMBER ID in my where clause, it always returns this error. I have tried CASTing / TO_NUMBER in every way possible I can think of, using temporary variables etc. and still the same error.
CREATE OR REPLACE
FUNCTION GETLINK (linkid IN CLOB)
RETURN VARCHAR2 AS
linkchar := TO_CHAR(linkid);
linkint := TO_NUMBER(linkchar);
SELECT url_dest INTO linkstring FROM TABLE WHERE ID = linkint;
Offending calling code:
tempcontent := regexp_replace(myClobField, '<link ([[:digit:]]*)>', GETLINK('\1'));
I have tried implicit and explicit type conversions that vary the above function; in parameters as VARCHAR2; etc.; no joy.
Is this a bug within the database, is it by design, or is it just me making a hash of things? I really don't want to have to pull the XML into PHP then run the PHP regex functions with Oracle DB queries to do this!
Many thanks for your assistance.