Looks like , some of the table name is large .
Error:The length specified for the statement is either 0 or too large.
Action: Specify a valid length for the statement.
Could you first do Tools->integity check in the universe and check for “Parse Objects” and Universe Structure. If you check all it’ll take long time to go. so for quick results , just check just 2 of them.
Let me know if it helps.
sun (BOB member since 2005-10-25)
These are results of “Check Integrity” without checking cardinalities and Thorough parsing
Integrity Status
Structure: OK
Parse Objects: OK
Parse Joins: OK
Parse Conditions: OK
Check Loop: OK
Check for Contexts: OK
apo777 (BOB member since 2005-07-05)
Hi Guys!
I got the similar problem even when I am using a single derived table with simplest possible query.
Does any universe setting are associated with this.
Any help is greatly appreciated.
Thanks & Regards
Girish
girish.grover 
(BOB member since 2004-11-19)
Please, which version of Oracle and Business Objects are you using? What is the exact SQL statement for your derived table causing the error?
Andreas 
(BOB member since 2002-06-20)
Hi,
I am using BO 6.5 and Oracle 9i.
The query I tried in Derived table was a combination of “Inline queries”…
SELECT B.REP_ID, B.TABC3, B.TABC4, M AS MISDATE, YTD YTD_DATA FROM
(SELECT REP_ID, TABC3,TABN32 YTD ,MISDATE M1, TABC4 FROM <table_name> WHERE REP_ID='x') A,
(SELECT REP_ID, TABC3,TABN32 N32 ,MISDATE M, TABC4 FROM <table_name> WHERE REP_ID='x') B
WHERE A.REP_ID=B.REP_ID AND A.TABC3=B.TABC3 AND TO_CHAR(A.M1,'DDMONYYYY')='31DEC'||TO_CHAR(TO_NUMBER(TO_CHAR(M,'YYYY'))-1) AND A.TABC4=B.TABC4 AND A.TABC3 = B.TABC3Even the query as simple as
SELECT REP_ID, TABC3,TABN32 YTD ,MISDATE M1, TABC4 FROM <table_name> WHERE REP_ID='x'throws the same error.
Please let me know if you need more info on this.
PS: I don’t get any error when I parse the SQL and I am able to run the sql in TOAD without any errors.
Thanks & Regards
Girish :? :? :?
girish.grover (BOB member since 2004-11-19)
girish.grover, I have a universe with 14 derived tables and there is no problem. Derived tables do have limitation on query size.
What exactly is your error message?
substring 
(BOB member since 2004-01-16)
The error message is
girish.grover (BOB member since 2004-11-19)
Hi,
I don’t think girish and apo777 are having the same problem. apo777 is talking about crashing (I think he means designer crashes during import) and girish has a parsing problem.
For both: look at the connection, are both pc’s using the EXACT same connection and user to logon ? I have seen a lot of errors with this, when it was a simple problem of not having teh proper rights…
For girish: the ORA error you get points to a column with the name State. What’s the sql here ?
regards,
Gerard
highandstoned 
(BOB member since 2005-08-01)
Hi All,
Let me rephrase my problem. This shall answer all the questions raised by Gerard, substring & Andreas.
I am using following tools.
BO 6.5
Oracle 9i.
I have a universe with derived table in it. The objective of derived table was to use inline queries with one table. When I created the universe with derived table on my machine, it worked fine. Any query that can be run on sqlplus works in the universe. I created reports using these derived tables. I didn’t find any problem working on my machine. After finishing my work I exported the universe and reports in the repository.
Later when I import the same universe on another PC, nothing works. In universe I can’t see the derived table definition. The report created using those derived table fails as it can’t find the table definition in the universe. The error I got while importing the universe on other machine is:
“Exception: DBD, ORA-24373:invalid length specified for statement State: N/A”
SELECT B.REP_ID, B.TABC3, B.TABC4, M AS MISDATE, YTD YTD_DATA FROM
(SELECT REP_ID, TABC3,TABN32 YTD ,MISDATE M1, TABC4 FROM <table_name> WHERE REP_ID='x') A,
(SELECT REP_ID, TABC3,TABN32 N32 ,MISDATE M, TABC4 FROM <table_name> WHERE REP_ID='x') B
WHERE A.REP_ID=B.REP_ID AND A.TABC3=B.TABC3 AND TO_CHAR(A.M1,'DDMONYYYY')='31DEC'||TO_CHAR(TO_NUMBER(TO_CHAR(M,'YYYY'))-1) AND A.TABC4=B.TABC4 AND A.TABC3 = B.TABC3SELECT REP_ID, TABC3,TABN32 YTD ,MISDATE M1, TABC4 FROM <table_name> WHERE REP_ID='x'SELECT REP_ID, TABC3,TABN32 FROM <table_name>Hi Gerard,
I don’t think its a connection problem. What I have learnt that the secured connection is available in BO repository. So the same shall be available on all the machines. In my case I am using the same user to access the same universe on two different machines. Please guide me if I am wrong here. What are the other problems you see related to rights. May be I can talk to our BO administrator if something is missing.
Looking forward to the experts for help.
Warm Regards
Girish
girish.grover (BOB member since 2004-11-19)
Hi,
just to be clear: you get the ORA error when improting the universe ? Not while using the universe ?
Did you try copying the universe by just copying the .unv file to the other pc ? See if it works then on the other machine ?
What happens if you delete the actual .unv file on your source machine (MAKE A BACKUP!!) and import it there ?
Regards,
Gerard
highandstoned (BOB member since 2005-08-01)
Hi Gerard,
Best Regards
Girish :?
girish.grover (BOB member since 2004-11-19)
Hi,
ok. It means that the universe itself is ok (and I hope you have a backup before you deleted it… 
) and the problem is in the import/export facility somewhere and isn’t pc related.
Try creating a new universe, create the same derived table in it with some objects and try import/export with that one (including the delete before import).
It sounds like there’s some problem in the repository. Did you check the repository for errors ? (in Supervisor)
If there were no reports on it, I would delete the universe from the repository but I wouldn’t do it now…
Perhaps stupid, but try exporting the copied universe in the other pc to the repository and import it on the source pc.
Regards and keep your backup safe…
Gerard
highandstoned (BOB member since 2005-08-01)
Hi Gerard,
I have the backup so its not a problem.
I tried creating a new universe the problem remains as is.
I tried creating a new universe through another user on the same repository, problem remains as is.
Now as u said…
Although I don’t have access to Supervisor. If u guide me how it can be checked. I will talk to BO Adminstrator about the same.
Well I am doing all these experiments on a new test universe so no problem for backups.
Still looking for answers.
Warm Regards
Girish 
girish.grover (BOB member since 2004-11-19)
Hi,
do you have only 1 repository ? Otherwise try also the other repositories (ie: if you have physicaly separated test/acceptance/prod environments)
Supervisor:
See, if you get any errors.
Other possibilty is to do a trace in Oracle to see the exact statement which is giving you the problem.
(BTW: the repository is also 6.5 ??.. :? )
The error itself is more an insert error, and it’s strange that’s it’s appearing during the import of the universe. Second thing is the reference to ‘State’, like it’s some kind of field but there’s no field with that name in the repository.
Regards,
Gerard
highandstoned (BOB member since 2005-08-01)
Hi,
Many tahanks for your reply. For testing purpose we have oly one repository, so can’t go on other repository and check. The supervisor has checked the integrity of the Universe domain and we got following error.
We have checked the universe connection its working properly.
Now what can be the problem.
The repository must be 6.5, the supervisor is not sure about it. He is trying to figure out. Is there any way to find out the version of repository.
It appears to me we are close to solution to the problem… May be u can help us to find out what can be the problem with universe domain considering connection is ok.
Looking forward to ur help.
Warm Regards
Girish 
girish.grover (BOB member since 2004-11-19)
Hi,
this posthttps://bobj-board.org/t/44731 has the same problem, but no fix is posted.
Did you by any chance upgrade to 6.5 from an earlier version ?
Check the number of tables in the repository, there should be 31 tables starting with UNV_
I’m certain I’ve heard of the upgrade problem before and since the derived table is new since 6.5 you won’t notice problems with older universes and think the upgrade went well.
Let me know,
Regards,
Gerard
highandstoned (BOB member since 2005-08-01)
Hi,
Can you please send me the listing of tables in your repository.
Thanks for your support so far. We are closer to solution and everyone visiting BOB with this kind of problem will really appreciate your effort for the solution.
Let’s assume that the upgrade didn’t go well i.e. the guy who upgraded BO from 5.1.7 to 6.5 forgot to upgrade the repository. What are implications if we try to upgrade the repository now. We have to see that lot of users who are using the universe domain do not face any new problem in future bcoz of repository upgrade.
Warm Regards
Girish
girish.grover (BOB member since 2004-11-19)
Hi,
here’s a 6.5 listing for the UNV_ tables.
[list]UNV_AUDIT
UNV_CLASS
UNV_CLASS_DATA
UNV_COLUMN_DATA
UNV_COLUMNS
UNV_CONTEXT
UNV_CONTEXT_DATA
UNV_CTX_JOIN
UNV_DIM_OBJ
UNV_DIMENSION
UNV_JOIN
UNV_JOIN_DATA
UNV_JOIN_OBJECT
UNV_JOINCONTENT
UNV_OBJ_COLUMN
UNV_OBJ_TAB
UNV_OBJCONTENT
UNV_OBJECT
UNV_OBJECT_DATA
UNV_OBJECT_KEY
UNV_PROP_DATA
UNV_PROP_TAB
UNV_PROPERTY
UNV_RELATIONS
UNV_TAB_OBJ
UNV_TAB_PROP
UNV_TABLE
UNV_TABLE_DATA
UNV_UNIVERSE
UNV_UNIVERSE_DATA
UNV_X_UNIVERSES
[/list]
Hey, it’s just repayment for what BOB gave me…although I never get answers… 
You can buy me a beer when I visit India…
About the upgrade…what can I say…
For now, i assume this topic is closed since it’s no longer a derived table problem.
Good luck with the upgrade.
Gerard
highandstoned (BOB member since 2005-08-01)
Hi,
Many thanks for the support!!!
Well we solved the problem in development environment by creating a new Universe domain. So far people are working out how they can repair this repository. They don’t have answers at the moment but hope to find soon.
Well U surely deserve a Beer!!! 
Thanks
Girish
girish.grover (BOB member since 2004-11-19)
Hi,
I hope you’re aware that there should also be a document domain with the same connection string ? (since you’ve created a new domain)
Otherwise everything will work except the custom LOV’s…
Regards,
Gerard
highandstoned (BOB member since 2005-08-01)