Home » SQL & PL/SQL » SQL & PL/SQL » PL/SQL Variable Size>>>> (10G)
PL/SQL Variable Size>>>> [message #397275] Thu, 09 April 2009 14:20 Go to next message
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 141 times)
Re: PL/SQL Variable Size>>>> [message #397277 is a reply to message #397275] Thu, 09 April 2009 14:26 Go to previous messageGo to next message
ThomasG
Messages: 3189
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 #397278 is a reply to message #397275] Thu, 09 April 2009 14:27 Go to previous messageGo to next message
BlackSwan
Messages: 25047
Registered: January 2009
Location: SoCal
Senior Member
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Please, please, please Read & Follow Posting Guidelines above.
Go to the section labeled "Practice" & do as directed.


PL/SQL is a sub-optimal tool to be interacting with the WEB.
It is like trying to use a hammer to divide one board into 2 boards.

While it can be done, it will take more effort than using a better tool.

In 25 words or less, describe the real world problem you are attempting to implement.
Re: PL/SQL Variable Size>>>> [message #397280 is a reply to message #397275] Thu, 09 April 2009 14:38 Go to previous messageGo to next message
manu_d
Messages: 3
Registered: April 2009
Junior Member
Point taken.....

and thanks for the help.....
Re: PL/SQL Variable Size>>>> [message #397283 is a reply to message #397280] Thu, 09 April 2009 15:39 Go to previous messageGo to next message
ThomasG
Messages: 3189
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 Go to previous message
Frank
Messages: 7880
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,
Previous Topic: how to know sequence name on a table?
Next Topic: Insert from select not same result as select
Goto Forum:
  


Current Time: Wed Dec 07 14:24:28 CST 2016

Total time taken to generate the page: 0.05772 seconds