Calling SQL from within an E1 App

I wrote a custom app and and warned the folks it was going to be a resource hog as it goes to all ends of the earth to get information, they insist it has perform this logic every time they click Find and then the data has to be sorted for presentation. Guess what - it's live and the users are complaining about response time when they click Find.

An Oracle view would help but not totally alleviate the problem. I thought about caching it but that would not solve the problem either.

My only only thought was to write an SQL script to populate the work table being used (only way to get the data sorted/manipulated and populated correctly). The app would then just have to read the work table. I would actually have to write 2 possibly 3 SQL scripts as the program has to retrieve available inventory (from custom license plate tables and views) from 3 different places. I think I could figure out how run the SQLs (there are BSFNs that call a command) if I could figure out how to open the SQL session.

Is this even possible? I have to think it is but I'm not quite sure how to get started. We have an Oracle database for 8.11. Thanks in advance for any pointers or help in finding any documentation on this.

Dian,

Welcome to JDEList.

You full system configuration would help here. Your running 8.11 on Oracle database, but what platform are you using?

We do sort of what you want. We use the Execute OS Command business function to execute a unix shell script which in turn runs an Oracle script (ie. Stored Database Procedure) using SQL Plus on the enterprise server.

I hope that helps.

Dian,

There is a developers forum on JDElist. The folks on that forum might be able to give you better direction than this forum.