| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: InStr and Length Problem Ora-01460
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)
 |
 |