Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: InStr and Length Problem Ora-01460

Re: InStr and Length Problem Ora-01460

From: Ashley Morgan <amorgan_at_onet.co.uk>
Date: Tue, 15 Nov 2005 08:40:30 -0000
Message-ID: <43799eff$0$82641$ed2619ec@ptn-nntp-reader03.plus.net>


Hi Daniel,

What I am trying to do is created a variable lenght In clause within a Sp. ie.

Select * from Table where field in (pindata).

As this is not possible I am trying to use the InStr function.

Select * from Table where Instr(String1,string2) > 0

Where String1 is a parameter passed in from the SP e.g. ',1,2,3,4,5,6,7,' and String2 is a Cast of an Integer from the Database into a Varchar2. With
the subsitutions it woud look something like this ...

Select * from Table where InStr(',1,2,3,4,5,6,7',',4,') > 0

As explained previously this works upto 4000 Characters. I have also changed
the code and put the InStr into a Package e.g.

Select * from Table where myPackage.MyInStr(String1,String2) > 0

but this returns the same problem.

I am using Oracle 9.2.04

Any help with this matter would be much appreciated.

"DA Morgan" <damorgan_at_psoug.org> wrote in message news:1132017354.540368_at_yasure...
> Ashley Morgan wrote:
>> I wonder if anybody can help. I have a where clause which looks for a
>> string pattern e.g.
>>
>> Select * from Table where Instr(String1,string2) > 0
>>
>> When String1 is less than 4000 characters the query works, when this is
>> increased to 6000 characters, I get the error message "ORA-01460:
>> unimplemented or unreasonable conversion requested". I have checked the
>> reference data and it says that a string up 32767 charcaters can be
>> handled in Pl/SQL.
>>
>> Can somebody please explain why this is happening and how I can resolve
>> it ?
>>
>> Thanks in Advance.
>
> From your SQL statement it is impossible to tell what you are doing.
> But since no VARCHAR2 column exceeds 4000 bytes and you are claiming
> to be doing:
>
> where Instr(String1,string2) > 0
>
> You are either looking at a CLOB or something else is not as you
> have presented it. That is precisely what Oracle is trying to tell
> you in a more cryptic manner.
> --
> Daniel A. Morgan
> http://www.psoug.org
> damorgan_at_x.washington.edu
> (replace x with u to respond)



I am using the free version of SPAMfighter for private users. It has removed 391 spam emails to date.
Paying users do not have this message in their emails. Try www.SPAMfighter.com for free now! Received on Tue Nov 15 2005 - 02:40:30 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US