got this error mesg:
ORA-01460: unimplemented or unreasonable conversion requested
although we are not doing any conversions but trying to work with data of varchar2 type with length more than 4000
hi there thanks for ur reply but the problem is not that. i shall explain what exactly i need.
we are passing a variable to a function which is having data of length greater than 4000 bytes which is of type varchar2.
we are performing some modifications in the string and later returning the string through a variable declared in the function which is having length of 25000 bytes. but the same error is occuring again. what do u think is going wrong?
also is there any restriction on the string length that we send to a function as an input parameter?
Well, now that you've given some more information, let me try again: I tried to simulate your situation and did this:
create function my_fun (par_in in varchar2)
retval := substr(par_in, 1, 10);
create procedure my_proc
l_var := 'text_string_that_is_about_12000_characters_long';
l_ret := my_fun(l_var);
dbms_output.put_line(substr(l_ret, 1, 20));
Both compiled without errors.
Running the procedure (EXECUTE MY_PROC), though, returned an error. Shortening 'text_string_...' to less than 4000 characters, recompiling and running the procedure showed the result.
The literature says that a function argument's datatype cannot specify a lengt, precision or scale. Oracle derives them from the environment from which the function is called.
Therefore, I'd say that Oracle found out that environment was SQL and expected the argument to be less or equal to 4000 characters.
Now what? Don't know ... perhaps if you try to change a VARCHAR2 datatype to a LONG, CLOB or something like that? Let's hope someone reads about your problem and knows the solution.
thanks a lot . the problem was solved. actually we were passing SQL statement in the function and hence it was limiting the size of varchar2 datatype to 4000 bytes. the problem was solved when we gave PL/SQL statement in the function.