I saw this somewhat cryptic error message while using my Oracle Regular Expression Workbench tool, before i restricted the size of the sample (multiline) string to 4000 bytes.
This error can be quickly simulated in the following fashion:
create table test_1460(string_col varchar2(4000));The problem is with the size of bind variable str1 that exceeds 4000 bytes.
declare
str1 varchar2(4001) := rpad(' ',4001);
str2 varchar2(4000);
begin
execute immediate 'select :sample_string from test_1460 ' into str2 using str1;
end;
Error report:
ORA-01460: unimplemented or unreasonable conversion requested
ORA-06512: at line 5
You can also get the same error while using SQL Developer with a simple query containing a bind variable as follows:
select :sample_string as swhen the pop-up window asking for the value to be passed to the bind variable appears, enter a large string, longer than 4000 bytes and you will get ORA-01460.
from dual;
See message translations for ORA-01460 and search additional resources.
17 comments:
Good to know
a risolverlo...
use CLOBs instead of varchar2:
set serveroutput on
declare
clob1 clob;
clob2 clob;
begin
clob1 := empty_clob();
clob1 := rpad('.',4000,'.') || rpad('%',4000,'%') || rpad('x',4000,'x') || rpad('-',4000,'-');
execute immediate 'select :sample_string from dual ' into clob2 using clob1;
dbms_output.put_line(dbms_lob.substr(clob2,10,12000));
dbms_output.put_line(length(clob2));
end;
/
x---------
16000
I'm trying to upgrade from APEX 4.0 to 4.2.1 and some (but not all) application pages that use shared items on query are reporting the ORA-01460 error.
Trying queries without the shared item reference, all is OK.
Suggestions?
Regards
May be this applies to you?
http://docs.oracle.com/cd/E37097_01/doc/doc.42/e35121/toc.htm#CHDJEIGE
Thanks for your answer.
Doing some more tests I've better defined the problem:
APEX is running on dedicated 11.2 server, but the application need to retrive data from an old financial application based on db 8.1.7 and from an erp application based on db 10.2.
To do this, on APEX db we have some views using, via dblink, views defined on target servers.
The error rises executing query on the 8.1.7 db if the query uses APEX shared items.
As example:
The application has to work for different companies of the group: when the user logs in, he must choose which company to work for; this information (2 char code) is stored in a shared item (APPL_COMPANY).
When I go to exec the query:
select a,b,c from myview where company = :APPL_COMPANY
I have the error if the target db is the old one (on 10.2 I had no errors, at the moment).
But if I try to modify the APEX query:
select a,b,c from myview where company = ‘XX’
all is ok.
And, obviously, all is ok using APEX 4.0.
Regards
What is the current status of the Compatibility Mode Attribute of this application?
You can find it in the application definition under shared components.
Is there any difference in behavior if you change this attribute?
I also suspect that the problem might be caused by the following:
http://docs.oracle.com/cd/E37097_01/doc/doc.42/e35121/toc.htm#BABFBHEF
The compatibility mode was "Pre 4.1".
I've changed it to "4.1" and to "4.2", but nothing changes (supposing a "dynamic" change, without APEX restart).
Looking at what is changed between APEX 4.2 and 4.1 (and also between APEX 4.1 and 4.0), I can't find topics ispiring me something more about this problem.
Regards
Ok,
could you please tell me where are you executing that query (e.g. a before header process, an on-demand process, etc.)?
Sorry for late answer, but happy new year .....
It is a general problem, with all queries in application.
The most are normal report or LOV queries.
Maybe someone can find this usefull ...
I solved the ORA-01460 problem in this way:
on APEX server I have created a simple function accepting a VARCHAR2 parameter and returning a fixed length CHAR.
CREATE FUNCTION set_app_var (p_val IN VARCHAR2)
RETURN CHAR
IS
l_val char(400);
BEGIN
l_val := trim(p_val);
RETURN l_val;
END;
/
Then I changed all application queries using shared items to use the function with the shared item as parameter.
Now application is running ok.
Thanks for support
Did you try to see what happens if you return a varchar2 instead of a char?
Does it raise ORA-01460 again?
If yes, then you actually found a solution (at least for your app), if not, then the problem must be elsewhere and the solution works accidentally.
I can try it, and I'll notify the result.
But let me explain the reasons for the solution:
First of all, on APEX repository tables we found that page items (we have no problems with) have an attribute of "max length"; for text item this attribute is setted by default at 4000; but we can't found a similar information on shared items tables.
We think that APEX guys have changed the default definition for shared items, probably going over the 4000 bytes, surely not managed in Oracle DB 8.
So, the purpose of the function is to reduce the variable length BEFORE the query on the old DB
I see, I remember someone in Apex team telling me that Oracle was about to change something in the way Apex manages session state in 4.2, so may be, as you say, that this affects those applications where items store large strings, which becomes a problem when talking to an old database.
I guess that distributed queries involving old oracle versions probably are not on the top of the list when it comes to testing a new version of Apex and perhaps they are not supported at all so you are forced to research your own workaround.
At any rate, I am glad for you if you solved the problem.
Thank you
Well, I can confirm that application is working ok also if the function return a VARCHAR2.
Regards
This sounds interesting, I wonder if the presence of this function forces Oracle to carry out the query in a different fashion which results in avoiding the error.
Thanks for reporting it.
Even i get same error:
public void OracleCommand_PreInit(string MySql)
{
MyCmd = new OracleCommand(MySql);
MyCmd.Connection = MydbCon;
}
for public string GetSqlValue(string s_CompID, string t_Sql)
{
//t_Sql = dpid_whereclouse(t_Sql, s_DPID);
s_DSNSTR = utl_fnGetDSNStr(SessionHandle.Current.SessionCompanyCode);
OracleConnect obj_oc = new OracleConnect(s_DSNSTR);
object t_ValUTL = null;
obj_oc.OracleCommand_PreInit(t_Sql);
t_ValUTL = obj_oc.ExecuteScalar();
My query:
public string qry_CheckGroupDetails_UserRightsMaster(int Company_ID, int groupid) // For USER_RIGHTS ........
{
StringBuilder strBuilder = new StringBuilder();
strBuilder.Append(" SELECT ID ");
strBuilder.Append(" FROM USER_RIGHTS_MASTER ");
strBuilder.Append(" WHERE ");
strBuilder.Append(" GROUP_ID=:groupid AND COMPANY_ID=:Company_ID ");
s_dsnstr = o_Cls_Utility.utl_fnGetDSNStr(SessionHandle.Current.SessionCompanyCode);
OracleConnect o_Cls_OracleConnect = new OracleConnect(s_dsnstr);
o_Cls_OracleConnect.OracleCommand_PreInit(strBuilder.ToString());
o_Cls_OracleConnect.Parameter_Int32("groupid", groupid);
o_Cls_OracleConnect.Parameter_Int32("Company_ID", Company_ID);
o_Cls_OracleConnect.ExecNonQuery();
return strBuilder.ToString();
Post a Comment