Home » SQL & PL/SQL » SQL & PL/SQL » ORA-06512 (Oracle 10g, Windows xp)
ORA-06512 [message #329377] Wed, 25 June 2008 04:31 Go to next message
ShridharV
Messages: 29
Registered: January 2008
Junior Member
Hi all,

Can anyone tell me the reason for the following error:-

ORA-06512 :unimplemented or unreasonable conversion requested. I get this error in the application logs when the procedure is called from Java. Not all times does this error come. I have a line in the code like this:-

AND EMPLOYEE.EMPLOYEE_ID IN (var_employee_id). This var_employee_id is a comma separated list. I feel when the length of this string is very long this may happen. Can anyone help me.
Re: ORA-06512 [message #329385 is a reply to message #329377] Wed, 25 June 2008 05:15 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Quote:

SQL> !oerr ora 6512
06512, 00000, "at %sline %s"
// *Cause: Backtrace message as the stack is unwound by unhandled
// exceptions.
// *Action: Fix the problem causing the exception or write an exception
// handler for this condition. Or you may need to contact your
// application administrator or DBA.


It is not a very useful error message you can give it to us. If you could copy and paste the actual error message somebody will be able to point you in the right direction.

Hope that helps.

Regards

Raj
Re: ORA-06512 [message #329386 is a reply to message #329385] Wed, 25 June 2008 05:22 Go to previous messageGo to next message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member

Quote:
ORA-01460:unimplemented or unreasonable conversion requested
Cause: The requested format conversion is not supported.
Action: Remove the requested conversion from the SQL statement. Check the syntax for the TO_CHAR, TO_DATE, and TO_NUMBER functions to see which conversions are supported.
Re: ORA-06512 [message #329387 is a reply to message #329386] Wed, 25 June 2008 05:26 Go to previous messageGo to next message
ShridharV
Messages: 29
Registered: January 2008
Junior Member
But the error doesn't come all the time. So I am not able to reproduce it.
Re: ORA-06512 [message #329399 is a reply to message #329377] Wed, 25 June 2008 06:00 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
this line
AND EMPLOYEE.EMPLOYEE_ID IN (var_employee_id)
looks very suspect.

I think that you are trying to pass a string in and hoping that Oracle will treat it as a comma separated list of values.

Sadly, it won't.

It's possible that you are passing a string longer thant 4000 chrs in - that would probably give you an error.

Search for VARIABLE IN LIST as a solution to your problem.
Re: ORA-06512 [message #329400 is a reply to message #329399] Wed, 25 June 2008 06:02 Go to previous messageGo to next message
ShridharV
Messages: 29
Registered: January 2008
Junior Member
Actually I pass this string to a function which prses this string into set of comma separated values.

Like if the string is 'ww,rr,tt' , the function parses it to 'ww','rr','tt' .
Re: ORA-06512 [message #329402 is a reply to message #329400] Wed, 25 June 2008 06:09 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
And then you take that string, and use it in the IN statement.
I understand.
It doesn't work like that.

Observe:
create table in_test (col_1  varchar2(10));

insert into in_test values ('a');
insert into in_test values ('c');
insert into in_test values ('f');
insert into in_test values ('g');

DECLARE
  v_string   varchar2(30);
BEGIN
  dbms_output.put_line('Test 1');
  FOR rec in (SELECT col_1
              FROM   in_test
              WHERE  col_1 in ('a','b','c','d')) LOOP

    dbms_output.put_line('Test 1: Found record '||rec.col_1);
  END LOOP;
  
  v_string := '''a'',''b'',''c'',''d''';

  dbms_output.put_line('Test 2');
  dbms_output.put_line('v_string is '||v_string);
  
  FOR rec in (SELECT col_1
              FROM   in_test
              WHERE  col_1 in (v_string)) LOOP

    dbms_output.put_line('Test 2: Found record '||rec.col_1);
  END LOOP;  
END;
/

Test 1
Test 1: Found record a
Test 1: Found record c
Test 2
v_string is 'a','b','c','d'


Re: ORA-06512 [message #329405 is a reply to message #329377] Wed, 25 June 2008 06:10 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
> AND EMPLOYEE.EMPLOYEE_ID IN (var_employee_id). This var_employee_id is a comma separated list.

> Actually I pass this string to a function which prses this string into set of comma separated values.

Either you contradict your previous statement or you forgot to post the parsing function.
The approach posted in your first post does not parse anything, it simply does
AND EMPLOYEE.EMPLOYEE_ID IN ('ww,rr,tt')
(one string).

For real parsing, you shall follow these threads on AskTom:
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:210612357425
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:110612348061
Re: ORA-06512 [message #329406 is a reply to message #329405] Wed, 25 June 2008 06:15 Go to previous messageGo to next message
ShridharV
Messages: 29
Registered: January 2008
Junior Member
This is what the function does:-

'select '|| chr(39) || replace(V_Str, ',', chr(39) ||' from dual union all select '||chr(39)||'') || chr(39) ||' from dual';

which makes

select ww from dual
union all
select tt from dual
union all
select rr from dual;

I then insert into a table datatype. In the Proc I do
employee_id in ((TABLE)select func(V_STR) from dual)
Re: ORA-06512 [message #329415 is a reply to message #329406] Wed, 25 June 2008 06:41 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Well, what you told us originally was that you do
AND EMPLOYEE.EMPLOYEE_ID IN (var_employee_id)
. Why would you mislead us about the line that you think is causing the problem - in what way is that a sensible thing to do.

Feed us incorrect information - get incorrect diagnosis.

Your function would almost certainly run quicker using a row-generator to break down the sting, but that's a different topic.

Does the error that you're getting happen in this function, or in the SQL statement that calls.
Re: ORA-06512 [message #329417 is a reply to message #329415] Wed, 25 June 2008 06:45 Go to previous messageGo to next message
ShridharV
Messages: 29
Registered: January 2008
Junior Member
I am sorry that I didnt give out the complete info. Since our proc uses pagination logic, the whole sql is made a quoted string. so when the error is shown, it shows that point of code where the cursor is open. like

>1 open v_cursor for
2> 'select
3> employee_id
4> from employee';

If an error is thrown, it shows the line number as 1 no matter at what line in the code the bug raises.
Re: ORA-06512 [message #329431 is a reply to message #329417] Wed, 25 June 2008 07:24 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Looks lik the error is in your SQL then - if the error was in the function then the error message would list the function and line no.:
SQL> create or replace function err_test (p_in in varchar2) return varchar2 as
  2  begin
  3    raise_application_error(-20001,'Fish!');
  4  end;
  5  /

Function created.

SQL> 
SQL> 
SQL> DECLARE
  2    c   sys_refcursor;
  3    v1  varchar2(40);
  4    v2  varchar2(40);
  5  begin
  6    open c for 'select col_1,err_test(col_1) from in_test';
  7    fetch c into v1,v2;
  8    close c;
  9  end;
 10  /
DECLARE
*
ERROR at line 1:
ORA-20001: Fish!
ORA-06512: at "DEV.ERR_TEST", line 3
ORA-06512: at line 7


Can you post the SQL, and the exact error message that you're getting.
Ideally, run the query in SQL*Plus and copy/paste the exact error message.
Re: ORA-06512 [message #329602 is a reply to message #329431] Thu, 26 June 2008 00:16 Go to previous messageGo to next message
ShridharV
Messages: 29
Registered: January 2008
Junior Member
Actually the error is present in the log files of the application server. We are not getting this all the time. Only on few occasions. When the exception comes, it is masked by the hibernate. So the front end just shows a blank page. But the exception would be logged in the Server logs. Since it is the end user who uses the system and not the developer, we can't say when this occurs.

Anyways can you please tell when this error usually occurs? Generally I mean.
Re: ORA-06512 [message #329653 is a reply to message #329602] Thu, 26 June 2008 02:39 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
You can set a debug flag on hibernate, so the actual sql is displayed in the logs.
Re: ORA-06512 [message #329661 is a reply to message #329377] Thu, 26 June 2008 02:56 Go to previous message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
I am afraid that a few people faced this error.
But a quick search for "unimplemented or unreasonable conversion requested" revealed me these links:
http://www.dba-oracle.com/t_ora_01460_unimplemented_or_unreasonable_conversion_requested.htm
http://www.dbmotive.com/oracle_error_codes.php?errcode=01460

I do not know what is its cause in your case, but I tend to have the same opinion as JRowbottom:
Quote:
It's possible that you are passing a string longer that 4000 chars in - that would probably give you an error.
, which corresponds with the note from the first link:
Quote:
Oracle MetaLink documentation reveals that on Oracle versions 8.0.3 and higher, ORA-01460 is associated with a bug which is caused by "'mixing' PLSQL and SQL".
(as VARCHAR2 has different limits in SQL and PL/SQL).
Previous Topic: NVL() degrades the performance
Next Topic: Date format error
Goto Forum:
  


Current Time: Fri Dec 09 13:29:59 CST 2016

Total time taken to generate the page: 0.29187 seconds