Home » Developer & Programmer » Forms » ora-01489 result of concatenation is too long (a workaround to resolve ora-01489)
ora-01489 result of concatenation is too long [message #274737] Wed, 17 October 2007 01:06 Go to next message
anisam10674
Messages: 18
Registered: March 2007
Junior Member
Friends,

While developing a form I came across ora-01489 error as a result of piping of quite lengthy strings. from what I can gather piping results should not be greater than 4000 chars. a suggestive workaround can be by writing a database function and passing the original string and string to be appended. following is the code for the function: (any suggestions will be of great help)

One restriction to this function is that the result will be max of 30000 characters
create or replace function Concatenate 
	(in_originalstr 	in varchar2, --Original String
	in_appendstr 		in varchar2) --String to be appended to Original String
return varchar2 is
	v_originalstr 		varchar2(30000);	--transactional variable to store original string
	v_originalstrlen 	integer; 		--length of original string (v_originalstr)
	v_appendstr 		varchar2(30000);	--transactional variable to store string to be appended
	v_appendstrlen 		integer; 		--length of string to be appended (v_appendstr)
	v_maxstr 		varchar2(30000);	--transactional variable to store v_originalstr appended with v_appendstr
	v_finalstr 		varchar2(30000);	--result of concatenate function
begin
	v_originalstr 		:= in_originalstr;
	v_appendstr 		:= in_appendstr;
	v_originalstrlen 	:= nvl(length(v_originalstr),0);
	v_appendstrlen 		:= nvl(length(v_appendstr),0);
	v_maxstr 		:= rpad(v_originalstr,30000,v_appendstr);
	v_finalstr 		:= substr(v_maxstr,1,v_originalstrlen+v_appendstrlen);
return v_finalstr;
end;

calling the function;

finalstr := concatenate('abcdefghijklmn','rtuv');

result
finalstr := 'abcdefghijklmnrtuv'

Upd-mod: Please use 'code' tags and format your listing.

[Updated on: Wed, 17 October 2007 01:11] by Moderator

Report message to a moderator

Re: ora-01489 result of concatenation is too long [message #274746 is a reply to message #274737] Wed, 17 October 2007 01:13 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
Instead of the 'rpad' I would suggest simply concatenating the strings together. However, make sure that the final length is not greater than 30000 by determining their lengths and do a test on their sum before doing the '||'.

David
Re: ora-01489 result of concatenation is too long [message #274767 is a reply to message #274737] Wed, 17 October 2007 01:50 Go to previous message
anisam10674
Messages: 18
Registered: March 2007
Junior Member
Thanks Sir,

Actually exploring further I came to a conclusion that the restriction is for Select statement

for e.g. select lpad('a',4000,'*')||'b' from dual;
would produce ora-01489 error....

a simple || of variables wont cause the error....


Previous Topic: How to display DD-MON-YYY HH24:MI:SS in form field time has to gets refreshed every min and second
Next Topic: how to perform a tree?
Goto Forum:
  


Current Time: Sun Dec 11 00:11:48 CST 2016

Total time taken to generate the page: 0.11994 seconds