Results 1 to 5 of 5

Thread: error ORA-01460

  1. #1
    Join Date
    Dec 2004
    Posts
    12

    Exclamation Unanswered: error ORA-01460

    hi,
    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

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Check this table. I hope it'll help (hint: size of a variable length is different in SQL and PL/SQL (4000 bytes vs. 32767 bytes in Oracle 9).

  3. #3
    Join Date
    Dec 2004
    Posts
    12

    Exclamation

    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?
    thanks !!!

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Well, now that you've given some more information, let me try again: I tried to simulate your situation and did this:
    Code:
    create function my_fun (par_in in varchar2)
    return char
    is
      retval varchar2(25000);
    begin
      retval := substr(par_in, 1, 10);
      return retval;
    end;
    /
     
    create procedure my_proc 
    is
      l_var varchar2(10000);
      l_ret varchar2(25000);
    begin
      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));
    end;
    /
    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.

  5. #5
    Join Date
    Dec 2004
    Posts
    12

    Exclamation

    hi man,
    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.

    thanks again
    reg,

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •