Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Passing regexp_replace backreference to a function; type conversion problem

Posted on 2006-06-07
28
Medium Priority
?
1,400 Views
Last Modified: 2008-01-09
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
0
Comment
Question by:ifp_support
  • 14
  • 11
  • 3
28 Comments
 
LVL 9

Expert Comment

by:Metanil
ID: 16851503
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
0
 
LVL 3

Author Comment

by:ifp_support
ID: 16851618
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.
0
 
LVL 9

Expert Comment

by:Metanil
ID: 16851787
ok
then try this

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

Metanil
0
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
LVL 3

Author Comment

by:ifp_support
ID: 16851888
Would this work only once or on every instance of <link xx> found?
0
 
LVL 9

Expert Comment

by:Metanil
ID: 16851903
only once.

Metanil
0
 
LVL 23

Expert Comment

by:paquicuba
ID: 16851960
Is this what you're trying to get?

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

REGEXP_REPL
-----------
GETLINK(12)
0
 
LVL 23

Expert Comment

by:paquicuba
ID: 16851999
I think this one should work:

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

Author Comment

by:ifp_support
ID: 16852508
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.
0
 
LVL 3

Author Comment

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

Expert Comment

by:paquicuba
ID: 16853777
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.
0
 
LVL 3

Author Comment

by:ifp_support
ID: 16861116
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.
0
 
LVL 23

Expert Comment

by:paquicuba
ID: 16861373
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...


0
 
LVL 3

Author Comment

by:ifp_support
ID: 16861474
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.
0
 
LVL 23

Expert Comment

by:paquicuba
ID: 16861848
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.
0
 
LVL 23

Expert Comment

by:paquicuba
ID: 16862108
Another question.

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

Author Comment

by:ifp_support
ID: 16886690
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".
0
 
LVL 23

Expert Comment

by:paquicuba
ID: 16888397
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

0
 
LVL 3

Author Comment

by:ifp_support
ID: 16892404
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.
0
 
LVL 23

Expert Comment

by:paquicuba
ID: 16893618
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;
0
 
LVL 3

Author Comment

by:ifp_support
ID: 16894066
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.
0
 
LVL 3

Author Comment

by:ifp_support
ID: 16894109
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'));
0
 
LVL 3

Author Comment

by:ifp_support
ID: 16894156
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?
0
 
LVL 3

Author Comment

by:ifp_support
ID: 16894312
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...
0
 
LVL 23

Expert Comment

by:paquicuba
ID: 16895036
Do you have more than 1 link in the CLOB?

i.e. 'text text text <link 12> text text <link 13> text text'
0
 
LVL 3

Author Comment

by:ifp_support
ID: 16895064
yes, there are potentially many links in the CLOB.
0
 
LVL 23

Accepted Solution

by:
paquicuba earned 1000 total points
ID: 16895856
One posible solution would be to use EXECUTE IMMEDIATE, but it doesn't like LOBs.

Are your CLOBs > 32676 bytes?

PAQUI@PROD > DECLARE
  2  tempcontent clob := 'TEXT TEXT <link 12> TEXT TEXT <link 12> TEXT TEXT TEXT';
  3  tempcontent2 CLOB;
  4  BEGIN
  5  EXECUTE IMMEDIATE 'SELECT '||Q'[']'||regexp_replace(TO_CHAR(tempcontent),
  6  '<link ([[:digit:]]+)>',Q'[']'||'||GETLINK('||Q'[']'||'\1'||Q'[']'||')||'||Q'[']')||Q'[']'||' FROM DUAL'
  7  INTO tempcontent2;
  8  DBMS_OUTPUT.PUT_LINE(tempcontent2);
  9  END;
 10  /
TEXT TEXT OK TEXT TEXT OK TEXT TEXT TEXT

PL/SQL procedure successfully completed.
0
 
LVL 3

Author Comment

by:ifp_support
ID: 16984781
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!
0
 
LVL 23

Expert Comment

by:paquicuba
ID: 16986161
Anytime!

Thanks a lot for the points!!
0

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

581 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question