Thanks for the question, Roberto.

Asked: January 16, 2017 - 6:08 pm UTC

Last updated: September 29, 2022 - 12:29 am UTC

Version: 12.1.0.2

Viewed 50K+ times! This question is

You Asked

Hi,
We are running PeopleSoft and ocassionally we get error: ORA-01460: unimplemented or unreasonable conversion requested. There is nothing in the alert.log file and we can move past the error by just restarting the failed process without making any changes.
What are these errors related to? In other words, where can I start my troubleshooting since I dont see any entries in the alert.log or any trace files, or anything?
We have had this happen maybe three times in a year.
Sorry if the question seems vague but that's all I have at the moment.

Roberto.

and Connor said...

The most common cause is trying to bind a variable that exceeds the allowable limits for that variable's datatype, eg

QL> declare
  2    s1  varchar2(4001) := rpad(' ',4001);   -- exceeds the 4k bind limit for varchar2
  3    s2  varchar2(4000);
  4  begin
  5    select s1 into s2 from dual;
  6  end;
  7  /
declare
*
ERROR at line 1:
ORA-01460: unimplemented or unreasonable conversion requested
ORA-06512: at line 5

So this is most probably an issue with the data that being send to the database by Peoplesoft.

If you can't detect it easily, perhaps set an event to dump the errorstack when 1460 occurs, and get in touch with Support when you get a trace file.

Rating

(7 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Stil stuck

Roberto, November 12, 2017 - 6:02 pm UTC

Hi,
This is happening again. I did open a case with support but we havent made much progress. I did set an event to dump the error stack when the error happens and I have provided it to support multiple times (because it is not always the same).
What other options do we have to identify what is going on? The issue comes and goes. It is not always the same SQL and/or even the same PeopleSoft program -- but always on SQR programs.

Any thoughts?

November 13, 2017 - 3:27 pm UTC

I would log a call with Support and provide that trace.

Already did

A reader, November 13, 2017 - 4:05 pm UTC

Multiple traces.
No solution

November 14, 2017 - 1:15 am UTC

If you're unhappy with the response from Support, ask for an escalation. You pay good money for Support - use it to your best advantage.

Other avenues of exploration - are you using a multibyte characterset ? I'm wondering whether a characterset conversion somewhere along the line is taking you over a limit.

Server error triggers

Rajeshwaran Jeyabal, November 14, 2017 - 12:16 pm UTC

Team,

Can we do something like this ( http://tinyurl.com/groje4f ) server error trigger to capture the SQL causing this errors?

November 15, 2017 - 2:05 am UTC

Certainly worth looking at.

See DIAGNOSTIC_DEST

Gh, November 14, 2017 - 6:43 pm UTC

DIAGNOSTIC_DEST parametrr value?

ORA-01460 in 12cR1 and 12cR2

suk, August 15, 2018 - 5:25 am UTC

Run this code Block in 12cR1 and 12cR2 will have different behavior. Maybe this will help.

declare
 l_str varchar2(4001) := rpad('x', 4001, 'k');
  l_x   varchar2(1);
begin
  l_x := substr(l_str, 1, 1);
  dbms_output.put_line('l_x@p='||l_x);
  
  select substr(rpad('x', 4001, 'k'), 1, 1) into l_x from dual;
  dbms_output.put_line('l_x@s1='||l_x);
  
  select substr(l_str, 1, 1) into l_x from dual;
  dbms_output.put_line('l_x@s2='||l_x);
end;
/

August 22, 2018 - 1:44 am UTC

Thanks for the input.

Facing same issue

Ruperto Herrera, September 23, 2022 - 3:05 pm UTC

Was there a resolution to this problem? Random ORA-01460 errors also starting popping up at our institution. Same symptoms as reported here: PeopleSoft, while executing SQRs.

In our case, restarting the SQR doesn't fix it, and the only reliable solution we've found so far is to either restart the database or flush the shared pool.

Oracle support has also not been helpful.

Any input will be appreciated.

September 26, 2022 - 10:31 am UTC

Have you captured a full errorstack?

alter system set events '1460 trace name errorstack level 3';

for one ocurrence, or

alter system set events '1460 trace name errorstack level 3, forever'

for all of them?

That's what Support will need.

thanks for suggestion

Ruperto Herrera, September 28, 2022 - 9:32 pm UTC

Thanks for the suggestion on the errorstack. It's been set, and we now wait for the next occurrence.

Ruperto

September 29, 2022 - 12:29 am UTC

glad to help - keep us posted on how things go

Live SQL

Share and learn SQL and PL/SQL; free access to the latest version of Oracle Database!

Dev Gym

Classes, workouts and quizzes on Oracle Database technologies. Expertise through exercise!

Breadcrumb

Question and Answer