nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Oracle Communities

Oracle Analytics and AI

Child Item

Direct database request - string literal too long

User_M7MRM

Hi,

I'm facing an issue with Direct database request in OBIEE 12.

I have a direct query report with prompts, one of the prompt is "display_id " ( prompt variable: @{PV_STUDENT_ID_SELECTION)

Here is my SQL statement

select

display_id,

name,

city

from dwh_dim_student

where

INSTR(','||'@{PV_STUDENT_ID_SELECTION}'||',', ','||DISPLAY_ID||',') > 0

when I'm selecting more than 1000 values in the prompt, I'm receiving this error

01704. 00000 - "string literal too long"

*Cause: The string literal is longer than 4000 characters.

I understand error but I don't know how to solve it using Direct database request

Please advise,

Thanks

Alex

Find more posts tagged with

Accepted answers

All comments

Pedro F

Hi,

That has nothing to do with OBIEE but with the results being returned by the query itself. Have you tried getting that query and running it directly against the database? You'll get the same issue.

That error normally means that you have a string which is longer than 4000 characters which is the maximum supported by a SQL statement.

User_M7MRM

1. same error in the database

2. I know what the error means I'm looking for a solution using direct query in OBIEE

Gianni Ceresa

As a side note 4000 is also the max limit OBIEE accept, so you are playing with limits with quite many things

Honestly your query looks like a workaround for something. First it is a DDR which return just a bunch of information, to use to dump a long list of people info is OBIEE really the best tool for the job?

The way you had to build your WHERE condition is also another workaround.

How is the list of 1000 values selected? I'm kind of sure there isn't somebody picking name by name the 1000 students.

So you can probably achieve the same result by changing the query and also the prompts driving it.

The issue is that you are the only one knowing the usage of this thing, why it has been done and how you get there and where the list of 1000 students come from. So kind of impossible to point you in better directions.

Thomas Dodds

1. same error in the database

- so acknowledge that you get the same error directly in the database then...

2. I know what the error means I'm looking for a solution using direct query in OBIEE

- you want to circumvent the limits of your database with a direct query? via OBIEE?

^ not going to happen

OBIEE is not a data pump ... time to look at the requirement for validity or a new approach -- choosing 1000 items out of a prompt seems suspicious as well given you are using a really expensive on-screen analysis suite.

What's the end game? the use of? the business purpose? of the output? it is going to be used to feed another system (integration)? sent to a vendor (b2b)? what? if you have that then you can start on a path that will arrive you to the appropriate end.

Business requirements are to specify WHAT not HOW -- never relinquish your creativity to another who doesn't know what lies beneath.

[Deleted User]

Couldn't have said it better. Non-sense code executed by OBIEE is still non-sense code.

If there is no grasp or comprehension of how things are snd should be done conceptually in BI or in IT in General in terms of proper usage, clear structure and optimization then no one can help you.

User_M7MRM

"Honestly, your query looks like a workaround for something " - I can't publish the real query, I used this simple query to point out the problem.

How is the list of 1000 values selected? - using "Where in" clause will return with the following error ORA-01795: maximum number of expressions in a list is 1000 error

"I'm kind of sure there isn't somebody picking name by name the 1000 students "- not name by name ... by using the "More" option inside the prompt.

The only thing that I've asked is how to solve it, how to be able to choose more the 1000 values ( or 4000 characters ) ?

This is what my customer wants.so, I must find a solution regardless if it makes sense or not.

Bottom line "customer always right "

Thanks

User_M7MRM

please see my post below

[Deleted User]

3251920 wrote:"Honestly, your query looks like a workaround for something " - I can't publish the real query, I used this simple query to point out the problem.How is the list of 1000 values selected? - using "Where in" clause will return with the following error ORA-01795: maximum number of expressions in a list is 1000 error
"I'm kind of sure there isn't somebody picking name by name the 1000 students "- not name by name ... by using the "More" option inside the prompt.The only thing that I've asked is how to solve it, how to be able to choose more the 1000 values ( or 4000 characters ) ?

If the real query isn't posted or the concept at least explained precisely enough to be solveable with a different query then you will never get an answer. "I'd like a car but I won't tell you exactly which specs and options" will always leave you unsatisfied with the proposed model - bar a miracle of hitting exactly what you imagined.

There is no "read-my-mind" coding.

3251920 wrote:This is what my customer wants.so, I must find a solution regardless if it makes sense or not.Bottom line "customer always right "

That statement in itself is so wrong that it hurts. It's precisely why projects fail and most code is utter garbage. The customer is most definitely NOT always right. As a consultant your role is not to turn off your brain and mindlessly execute - it is to counsel, to advise.

Most of the time the customer simply doesn't know any better. How can he - it's not his Job but yours as his Consultant (check the definition of the word). If you tell him "listen you can achieve the same result by doing Z instead of A and at the same time staying closer to clean Standards, thus reducing administrative effort and General TCO" then he'll love you for it.

tl;dr "the customer wants it" is just an excuse

Gianni Ceresa

Why not to start from the beginning?

3251920 wrote:using "Where in" clause will return with the following error ORA-01795: maximum number of expressions in a list is 1000 error

Of course the best solution would be a join with the tables generating the IN values, in your case you don't have that.

Another solution is to use multiple IN by splitting the list of values in pieces and joining all the IN () by OR.

It always goes back to the same thing: the limits of 4000 will block all these ways.

The conclusion?

3251920 wrote:This is what my customer wants.so, I must find a solution regardless if it makes sense or not.Bottom line "customer always right "

Wrong, your job is to explain your customer why it can't work and it will not work in any possible way, why DDR is a good way to pay expensive licensing to do something a free tool does, why modelling things right the problem will never happen as they will select students in a smarter way.

But you probably still stay on "only thing that I've asked is how to solve it", and if it's the case you missed the answer

User_M7MRM

ok, thanks, I understand your point very clear but you need to understand also that there is no other possibility ( it was explained to the customer but still this is what the customer wants)

If there is no solution using direct query you can just say it, you don't need to tell me about what is good or bad, and what is the right approach.

thanks