Re: InStr and Length Problem Ora-01460

Ashley Morgan wrote:
>
> 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!

That's bad news waiting to happen... take a look at:

http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:110612348061

hth
connor

-- 
Connor McDonald
Co-author: "Mastering Oracle PL/SQL - Practical Solutions"
Co-author: "Oracle Insight - Tales of the OakTable"

web: http://www.oracledba.co.uk
web: http://www.oaktable.net
email: connor_mcdonald_at_yahoo.com


"Semper in excremento, sole profundum qui variat."

------------------------------------------------------------