| PL/SQL Variable Size>>>> [message #397275] |
Thu, 09 April 2009 14:20  |
manu_d
Messages: 3
Registered: April 2009
|
Junior Member |
|
|
Little help with this code.... not code actually.....but
issue... this function below basically scan the HTML code and then place tracking ids in the links... the problem is when my data file exceeds more then 4000 characters (HTML_Email PL/SQL variable used for handling data file is defined as varchar2 (32767)) my function fails with following error message.. basically all the string functions fails.....
FUNCTION
=========
create or replace FUNCTION Add_Links( HTML IN VARCHAR2 )
RETURN VARCHAR2 IS
HTML_Email VARCHAR2(32767 BYTE) := Null;
v_URL VARCHAR2(32767 BYTE) := NULL;
Escaped_URL VARCHAR2(32767 BYTE) := NULL;
v_Count NUMBER := 0;
v_Occu NUMBER(12) := NULL;
BEGIN
HTML_Email := HTML;
HTML_Email := REPLACE(REPLACE(REPLACE(HTML_Email, '</A>','</a>'),
'HREF="','href="'),'<A ','<a ') ;
SELECT (LENGTH(HTML_Email)-LENGTH(REPLACE(HTML_Email,'href="',
'' ))) / LENGTH('href="') as Occurrence into v_Occu
from dual;
WHILE v_Occu > v_count
LOOP
SELECT substr (HTML_Email,(instr (HTML_Email ,'href="' )) +6 ,
(instr (HTML_Email ,'/a>' )- (instr
(HTML_Email ,'href="' )) -6) ) INTO v_URL FROM dual;
Escaped_URL := REPLACE( REPLACE( v_URL, '?', '%3F'
), '&', '%26' );
HTML_Email := REPLACE (HTML_Email, 'href="' ||
v_URL || '/a>',
'START_hr'|| U.Get_URL( 'WWW' ) ||
'/api?Id=[Id]=&URL=' || Escaped_URL || 'END_A' );
v_count := v_count +1;
END LOOP;
HTML_Email := REPLACE(REPLACE (HTML_Email,
'START_hr','href="'), 'END_A','/a>');
RETURN HTML_Email;
END Parse_Email_Links;
Error Message
===========
ORA-01460: unimplemented or unreasonable conversion requested
ORA-06512: at "TEST_APP.PARSE_EMAIL_LINKS", line 14
ORA-06512: at line 7
Data file attached :
=====================
-
Attachment: data.txt
(Size: 4.04KB, Downloaded 757 times)
|
|
|
|
| Re: PL/SQL Variable Size>>>> [message #397277 is a reply to message #397275] |
Thu, 09 April 2009 14:26  |
ThomasG
Messages: 3212
Registered: April 2005
Location: Heilbronn, Germany
|
Senior Member |
|
|
1) The size of a varchar2 is limited to 4000 characters when it is used in SQL, like it would be in that select from dual.
2) Read the forum guide on how to format your post, and what information is generally needed to solve a problem.
For example, We don't know which line is line 14,
|
|
|
|
|
|
|
|
| Re: PL/SQL Variable Size>>>> [message #397283 is a reply to message #397280] |
Thu, 09 April 2009 15:39 |
ThomasG
Messages: 3212
Registered: April 2005
Location: Heilbronn, Germany
|
Senior Member |
|
|
| Quote: | PL/SQL is a sub-optimal tool to be interacting with the WEB.
|
That is not ENTIRELY true.
The way this is done here, with the HTML document as a big String and using replaces is indeed sub-optimal.
But IF the HTML data is a valid XML document, too, then one can basically use XMLTypes to do some pretty nice things with XML/HTML Data.
A lot of manipulations can be done by using XSLT transformations.
And you can use the entire XSQL Framework.
Of course all this would depend on the HTML data being valid XML, too.
|
|
|
|
| Re: PL/SQL Variable Size>>>> [message #397297 is a reply to message #397283] |
Thu, 09 April 2009 23:42 |
Frank
Messages: 7901
Registered: March 2000
|
Senior Member |
|
|
| ThomasG wrote on Thu, 09 April 2009 22:39 | | Quote: | PL/SQL is a sub-optimal tool to be interacting with the WEB.
|
That is not ENTIRELY true.
The way this is done here, with the HTML document as a big String and using replaces is indeed sub-optimal.
But IF the HTML data is a valid XML document, too, then one can basically use XMLTypes to do some pretty nice things with XML/HTML Data.
|
Which in the end would still show that PL/SQL is sub-optimal for web-bing.
Not impossible, but there are far better tools to do the job,
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
