asked on

Passing regexp_replace backreference to a function; type conversion problem

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:

<link 12>

which I wish to convert to

url_dest

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
linkstring VARCHAR2(4000);
linkchar VARCHAR(4000);
linkint NUMBER;
BEGIN

linkchar := TO_CHAR(linkid);
linkint := TO_NUMBER(linkchar);

SELECT url_dest INTO linkstring FROM TABLE WHERE ID = linkint;

RETURN linkstring;
END;

=====

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.

Ingram

Metanil

do regexp_replace support clob??

Instead of directly calling GETLINK in replace field
try using this:
tempcontent := GETLINK(regexp_replace(myClobField, '<link ([[:digit:]]*)>', '\1'));

Metanil

ifp_support

ASKER

Thanks for your reply.

regexp_replace does support CLOB FYI.

Your answer wouldn't work as I need to call the function with the value of the backreference as an Input parameter, not the whole of the CLOB as the INput parameter.

Metanil

ok
then try this

tempcontent := GETLINK(regexp_replace(myClobField, '^.*?<link ([[:digit:]]*)>.*$', '\1'));

Metanil

ifp_support

ASKER

Would this work only once or on every instance of <link xx> found?

Metanil

only once.

Metanil

paquicuba

Is this what you're trying to get?

> SELECT REGEXP_REPLACE('<link 12>','<link ([[:digit:]]+)>','GETLINK('||'\1'||')') from dual;

REGEXP_REPL
-----------
GETLINK(12)

paquicuba

I think this one should work:

tempcontent := regexp_replace(myClobField, '<link ([[:digit:]]*)>', 'GETLINK('||'\1'||')')

ifp_support

ASKER

Paquicuba - thanks, but it seems that all that does is replace the value with the string GETLINK(12) and doesnt call the function and replace the value with the output from the function.

Metanil - I am looking into using your function within the confines of a loop to replace each instance, however, no joy as yet. Will keep you updated.

ifp_support

ASKER

Metanil - that expression '^.*?<link ([[:digit:]]*)>.*$' seems not to work, I assume because the ^.*? is matching everything including the <link...

paquicuba

You may try something like this, see below:

PAQUI@PROD > CREATE OR REPLACE
2 FUNCTION GETLINK (linkid IN varchar2)
3 RETURN VARCHAR2 AS
4 linkstring VARCHAR2(4000);
5 BEGIN
6 CASE Linkid
7 WHEN '"12"' THEN
8 linkstring := 'URL 12';
9 WHEN '"13"' THEN
10 linkstring := 'URL 13';
11 WHEN '"14"' THEN
12 linkstring := 'URL 14';
13 ELSE
14 linkstring := 'UNKNOWN';
15 END CASE;
16 RETURN linkstring;
17 END;
18 /

Function created.

Elapsed: 00:00:00.18
PAQUI@PROD > DECLARE
2 myClobField CLOB := '<link 12><link 13><link 14>';
3 tempcontent VARCHAR2(2000);
4 temptab dbms_utility.uncl_array;
5 numvalues pls_integer;
6 my_url varchar2(100);
7 BEGIN
8 tempcontent := RTRIM(regexp_replace(myClobField, '<link ([[:digit:]]+)>', '"\1",'),',');
9 dbms_utility.comma_to_table(tempcontent,numvalues,temptab);
10 for i in 1..numvalues loop
11 my_url := GETLINK(temptab(i));
12 DBMS_OUTPUT.PUT_LINE(my_url);
13 end loop;
14 END;
15 /

URL 12
URL 13
URL 14

PL/SQL procedure successfully completed.

ifp_support

ASKER

Thanks, that should work for the iteration through each occurence, but it still wouldn't solve the initial problem of needing to take the Function's IN parameter and use it in the WHERE clause of the SELECT to look up the URL_DEST in a table. We have 1000s of records in the table being queried, so to add each one into the function as above would be impractical.

paquicuba

Are you talking about this SELECT: << SELECT url_dest INTO linkstring FROM TABLE WHERE ID = linkint; >> ?

Lets say you have something like this in your CLOB: '<link 12> xxxxx <link 13> xxxxxx <link 14>'

Do you want to extract all the link IDs or just the first one? Or one by one?

I'm a little confused here...

ifp_support

ASKER

I wish to replace every occurence of "<link xx>" with "<a href='result from SELECT statement within GETLINK that uses xx (the backreference) in the WHERE clause'>". Everything works fine with my original code, just that I get the numeric conversion error from the string of the backreference (e.g. "12" or "14" or "1395" etc.) to a NUMBER for the NUMBER column I am looking up the ID from. I hope that clarifies things.

BTW I am away until Monday from now so will not be able to reply until then. Thanks for your help.

paquicuba

That only happens if you pass a string like the one below to the function:

> select getlink('"12"') from dual;
select getlink('"12"') from dual
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at "PAQUI.GETLINK", line 9

Whenever you have a chance on Monday, please post your actual working call to the function.

paquicuba

Another question.

Is ID a number column? >> FROM TABLE WHERE ID = linkint;

ifp_support

ASKER

Yes the ID column is a NUMBER data type, hence the original error "character to number conversion error".

If I call the function directly from SQL*Plus with a string (e.g. > SELECT GETLINK("12") FROM DUAL;), it runs fine. It is just when I try to pass the value of the regexp_replace back reference to it, like:

tempcontent := regexp_replace(myClobField, '<link ([[:digit:]]*)>', GETLINK('\1'));

\1 is as you know the backreference for the value found within ([[:digit:]]*). In my example "12".

paquicuba

That's because you're still trying to pass the '\1' to GETLINK() function.

GETLINK() is being called before regexp_replace() replaces '\1' with 12, so GETLINK() treats '\1' as a string not as a backreference. See this example below (Note that when I call it this way:tempcontent := GETLINK(regexp_replace('<link 12>', '<link ([[:digit:]]*)>','\1'))) it works):

Elapsed: 00:00:00.04
PAQUI@PROD > DECLARE
2 tempcontent clob;
3 BEGIN
4 tempcontent := regexp_replace('<link 12>', '<link ([[:digit:]]*)>', GETLINK('\1'));
5 DBMS_OUTPUT.PUT_LINE(tempcontent);
6 END;
7 /
DECLARE
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at "PAQUI.GETLINK", line 9
ORA-06512: at line 4

Elapsed: 00:00:00.00
PAQUI@PROD > DECLARE
2 tempcontent clob;
3 BEGIN
4 tempcontent := GETLINK(regexp_replace('<link 12>', '<link ([[:digit:]]*)>','\1'));
5 DBMS_OUTPUT.PUT_LINE(tempcontent);
6 END;
7 /
OK

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.03
PAQUI@PROD > SELECT GETLINK('\1') FROM DUAL;
SELECT GETLINK('\1') FROM DUAL

ifp_support

ASKER

Ok, I see whewre you are going with this, however, I still get the error. Can you paste me the GETLINK function that you are using please, so I can check to see it is the same as mine. Thanks.

paquicuba

This is my function. It just returns 'OK' if I pass the number 12.

CREATE OR REPLACE FUNCTION GETLINK (linkid IN CLOB)
RETURN VARCHAR2 AS
linkstring VARCHAR2(4000);
linkchar VARCHAR(4000);
linkint NUMBER;
BEGIN

linkchar := TO_CHAR(linkid);
linkint := TO_NUMBER(linkchar);

SELECT 'OK' INTO linkstring FROM dual WHERE 12 = linkint;

RETURN linkstring;
END;

ifp_support

ASKER

Can you try making a table with a NUMBER / INTEGER column which you then use the IN value (linkid) to do a query on in GETLINK. This is where I get the data type error. If doing it from DUAL it isn't concerned by data types. Thanks.

ifp_support

ASKER

When calling your version of GETLINK from a Sproc with the full text CLOB as the text to search, I get:

ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at "IFP_WEB.GETLINK", line 9

I have tested the Sproc with the following line and it works:

tempcontent := GETLINK(regexp_replace('<link 12>', '<link ([[:digit:]]*)>','\1'));

This works fine. But if I try to use it with anything else, it dies, e.g.

tempcontent := GETLINK(regexp_replace('test text <link 12> more text text', '<link ([[:digit:]]*)>','\1'));

ifp_support

ASKER

If I use the code to find any character at beginning and end as per Metanil, then it works fine for doing the query in GETLINK:

tempcontent := GETLINK(regexp_replace('test text <link 12> more test text', '^.*?<link ([[:digit:]]*)>.*$','\1'));

With the output as:

> OK

However, I now wish to include the content either side so the final CLOB data is:

> test text OK more test text

Any clues?

ifp_support

ASKER

Upon further testing and using of temporary CLOBs I can get it to do what I want for one link only:

temped1 := editorcontent;
temped2 := editorcontent;
temped3 := editorcontent;

tempcontent := GETLINK(regexp_replace(temped1, '(^.*?)<link ([[:digit:]]*)>(.*$)','\1'), regexp_replace(temped2, '(^.*?)<link ([[:digit:]]*)>(.*$)','\2'), regexp_replace(temped3, '(^.*?)<link ([[:digit:]]*)>(.*$)','\3'));

But this only works for one instance of <link 12> and there may be many in a document, so I'm back to square one...

paquicuba

Do you have more than 1 link in the CLOB?

i.e. 'text text text <link 12> text text <link 13> text text'

ifp_support

ASKER

yes, there are potentially many links in the CLOB.

ASKER CERTIFIED SOLUTION

paquicuba

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ifp_support

ASKER

Sorry for the delay replying, I have been on holiday.

The majority of our clobs are bigger than 32676 bytes, so the execute immediate wouldn't work.

I have managed to get around this problem with some client side programming, which is not what I wanted, but timescales have been needed to be met. I believe that the issue really isn't solvable and others on the Oracle forums are saying the same.

I am closing this issue, but will award you some points Paquicuba as you have been most helpful and helped me with looking outside the box for a solution. Thanks!

paquicuba

Anytime!

Thanks a lot for the points!!