Skip to Main Content
  • Questions

  • Large string for list greater than 1000 elements

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, anwesh.

Asked: July 08, 2019 - 6:59 pm UTC

Last updated: July 25, 2019 - 2:51 am UTC

Version: 10

Viewed 1000+ times

You Asked

Using 
 https://blogs.oracle.com/oraclemagazine/on-cursors-sql-and-analytics
,
I was able to deal with cx-oracle case of binding list with sql-statement.

I am not sure I am doing the best here though.

Sql-stm:

cursor.execute("Select * from table where table_column in (:column)", {"column":('A','B','C')})


I change the sql query to: 

cursor.execute ("with data as (select trim( substr (txt, instr (txt, ',', 1, level  ) + 1, instr (txt, ',', 1, level+1) - instr (txt, ',', 1, level) -1 ) ) as token from (select ','||:txt||',' txt from dual) connect by level <= length(:txt)-length(replace(:txt,',',''))+1)  Select * from table where table_column in (select * from data) ", {"txt":"A,B,C"})


I get the desired result considering I only have 'select' privilege and none other and I cannot execute procedure (or create them).

But what if the list is larger? Say I have more than 1000 elements in the list.
So the string is about 4000+ size and hence I get error "cx_Oracle.DatabaseError: ORA-01460: unimplemented or unreasonable conversion requested"

How can I deal with this case?


and Connor said...

You're starting to hit the limits of what you can do in a string

See https://asktom.oracle.com/pls/asktom/asktom.search?tag=ora-01460-unimplemented-or-unreasonable-conversion-requested for details and examples

So that text you are bringing in might need to be broken up into parts to handle this, or you might need to use a clob datatype in the cx_oracle driver rather than a varchar2.

Rating

  (1 rating)

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

Comments

How would I deal with that?

anwesh joshi, July 09, 2019 - 12:38 pm UTC

I have i am having issue understanding that. I can create a clob with the data but then I get an error

"Expected - got clob"

when I change string to to_clob(string).
[And I have Oracle 10 and I dont think I can use larger clob in where part of the query]
Connor McDonald
July 25, 2019 - 2:51 am UTC

I really think you should rethink the approach here. If you are getting into thousands of items in a list, then you're going to continually hit issues similar to the ones you're seeing, or have incredibly expensive parse times and/or query performance issues.

I'd strongly suggest looking at morphing this into

- load the elements into a temporary table
- querying with a join against that temporary table
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

CLOB/BLOB/etc

Complete documentation on Securefiles and Large Objects here