APEX

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Excel Upload in APEX 5.1

Denes KubicekFeb 28 2017 — edited Jun 7 2017

I just found out that the upload from excel (xls) to a collection doesn't work with 5.1 any more. We upgraded an existing 5.0 to 5.1 and this is now bugging us. The setting is normally done in the ords by adding the four lines to support uploading excel:

<entry key="apex.excel2collection.name">EXCEL_COLLECTION</entry>

However in APEX 5.1 the simple page with a button to start the upload and the file browse item produces an error.

I tested this on multiple environments where there was an upgrade from 5.0 to 5.1 and it is the same thing.

Anyone with an idea ?

Thanks,

Denes Kubicek

This post has been answered by Marc Sewtz-Oracle on Feb 28 2017

Jump to Answer

Frank Kulash

Answer

Hi, @jian-cdo
wonder if it's possible I can store the case when part for each study as a static parameter, then later feed it into the sql query when the study_id is passed.
Sure. That's how dynamic SQL is usually generated: some parts of the query are static (never changing), and other parts depend on something like the data in some table. If you have trouble, post a concrete example. (You can simplify the example: instead of posting a CASE expression with 200 WHEN clauses, post one with only 3 WHEN clauses, but just point out that you really have 200.
Maybe a CASE expression isn't the best tool for the job. Perhaps you can get the same results (perhaps more efficiently) by joining to a table, or with a user-defined function.

Marked as Answer by Jian-cdo · Oct 16 2021

Jian-cdo

Thanks for the prompt reply. So far I will stick with the case expression since it's a legacy query. So here is the example of the query (regardless of other tons of table join and sub query):
case when study_id=38 and maxvisit_order<4 then 1
case when study_id=38 and maxvisit_order>4 and maxvisit_order<10 then 2
......
case when study_id=27 and maxvisit_order<6 then 1
case when study_id=27 and maxvisit_order>6 and maxvisit_order<15 then 2
.....
So like the above two parts (for 2 studies), how I can save them somewhere as a static parameter. Thanks.

mathguy

That kind of case expression is exactly what Mr. Kulash had in mind when he said something like "storing data in a table and using a join may be a better approach". A separate table with columns for study_id, maxvisit_order_low, maxvisit_order_high and the values you must assign - and a join on study_id and inequalities for maxvisit_order - will be much easier to maintain than the monster you have now. When needs change, you would only need to change the data (the additional table), you wouldn't have to hunt through the query itself to change its code. The queries will also run much faster.
You say "I will stick with the case expression." WHY? I don't know about other posters here; but when I see a rejection like that (rejection of excellent advice provided here to help you), without a good reason being given by the poster, I choose to move on to the next thread. Good luck!

1 person found this helpful

Jian-cdo

Thanks for further explanation, now I understand what it means by using join. Yes, now I'd like to get rid of Case.

1 - 4

Locked Post

New comments cannot be posted to this locked post.

Locked on Jul 5 2017

Added on Feb 28 2017

#apex-discussions

11 comments

13,706 views

APEX

Excel Upload in APEX 5.1

Comments

Post Details