Solved

User display while running Oracle Procedure from Access 2010

Posted on 2013-12-17
7
702 Views
Last Modified: 2013-12-18
I run a rather lengthy Oracle procedure from Access 2010 that the user kicks off.  The procedure works great (and relatively fast compared to running it in Access).
However, since the procedure does take some time, the Access screen turns white and shows "Not Responding".

I have created a form that I display a message to the user before running the Oracle procedure and it comes up, bu the rest of the screen is white and the title bar of the form still says "Not Responding".

I could write a text file from Oracle as the procedure progresses (have to figure it out), but Access couldnt read it anyway since it is waiting for the call to Oracle to finish.

I would like to somehow have some display for the user to let them know if nothing else that something is happening. I want to avoid freezing the screen for the user since now at least they can minimize Access.

I'm very open to suggestions....
Options in my mind so far (if I knew how):
1) Have the procedure kick off and return control to Access so that I could read a text file created by Oracle.
2) Create a VB/PowerBasic exe that somehow takes control and does the same.
3)??
0
Comment
Question by:GNOVAK
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
7 Comments
 
LVL 38

Expert Comment

by:Geert Gruwez
ID: 39723802
3)
tune the oracle procedure so it takes less than a second
i'd run a trace 10046 to see what it does and where you can tune the procedure
if it's all network time, then there is no point in tuning

kick access out
it's amazing what the business can setup and how long it can take to get rid of all the tiny non integrated accesses
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 39723819
4 - submit the procedure as a job via dbms_scheduler or dbms_job packages.
Then, instead of writing to a file, have the procedure write to a table.  From access periodically poll the table.

The reason why I'd avoid a file is the database can only write to filesystems on the database server.  That doesn't mean it's impossible to use a file; but since your access app is already connected to the db, it's simply mean reading a table, which is quite easy to do.
0
 

Author Comment

by:GNOVAK
ID: 39723905
The procedure is doing quite a bit (its a wrapper for lots of sub procedures) and is quite fast given what it has to do. While something can always be tunned more, it would still require a solution on the Access end.

Sdstuber :
Thanks for the info on the text file, saves a lot of angst here. I'll create a table and just empty when done for that user....

Can I submit the procedure as a job that would take place right away?  I'm not familiar with that. I take it that I create a Procedure that creates the job that calls the Original Proc. If that's the case, I know how to call it from Access, but dont know how to code it from the PL/SQL side. Could you give me an example?
0
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
LVL 74

Accepted Solution

by:
sdstuber earned 500 total points
ID: 39723933
Try something like this...
just put whatever you need for your procedure to run inside the  [ ]
change the name and comment to something useful and then call submit_my_job from access


CREATE OR REPLACE PROCEDURE submit_my_job
AS
BEGIN
    DBMS_SCHEDULER.create_job(
        job_name          => 'MY_PROCEDURE_JOB',
        job_type          => 'PLSQL_BLOCK',
        job_action        => q'[
declare
    v_date date := sysdate - 1;
begin
    my_procedure(v_date,'GNOVAK');  
end;
]',
        start_date        => SYSDATE,  -- this says run it now
        repeat_interval   => null,  -- this says it's a one time action
        enabled           => TRUE,
        comments          => 'GNOVAK job to run my_procedure.'
    );
END;
/
0
 

Author Comment

by:GNOVAK
ID: 39724547
There will be a few users calling this procedure at any one time.
I take it that wont pose a problem as long as each job is named different.

My current procedure is called RUN_ANALYSIS and takes the following parameters:
(tmpSCHEMA IN VARCHAR2
 ,tmpANALYSIS_ID IN NUMBER
 ,tmpORDER_ID IN NUMBER
  ,tmpRUNID IN NUMBER
   ,tmpOUT OUT NUMBER)

Since I could monitor the table for finding out where it is, I wouldnt need the OUT.
Do I just add the parameters to the submit_my_job?
Does this look like a correct procedure?


procedure submit_my_job (tmpSCHEMA IN VARCHAR2
                         ,tmpANALYSIS_ID IN NUMBER
                         ,tmpORDER_ID IN NUMBER
                         ,tmpRUNID IN NUMBER)
AS
BEGIN
    DBMS_SCHEDULER.create_job(
        job_name          => 'RUN_ANALYSIS:' ||tmpSCHEMA ||tmpRUNID ,
        job_type          => 'PLSQL_BLOCK',
        job_action        => q'[
declare
    v_date date := sysdate - 1;
begin
    my_procedure(tmpSCHEMA,tmpANALYSIS_ID,tmpORDER_ID ,tmpRUNID);
end;
]',
        start_date        => SYSDATE,  -- this says run it now
        repeat_interval   => null,  -- this says it's a one time action
        enabled           => TRUE,
        comments          => tmpSCHEMA ||tmpRUNID
    );
END;
0
 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 500 total points
ID: 39724913
the job_action is dynamic pl/sql.  It's just text, not actual code.

So,

my_procedure(tmpSCHEMA,tmpANALYSIS_ID,tmpORDER_ID ,tmpRUNID);

the parameters are literally those names, not your submit parameters.
you'll need to concatenate them.

job_action => '
declare
    v_date date := sysdate - 1;
begin
    my_procedure(' || tmpSCHEMA || ',' || tmpANALYSIS_ID || ',' || tmpORDER_ID || ',' || tmpRUNID || ');
end;
'

if your variables are strings, then you'll need to wrap them in quotes
That can get kind of ugly but it's more tedious than difficult.

For example, if I assume tmpSCHEMA is a string rather than a number, I'd construct the job text like this...

job_action => '
declare
    v_date date := sysdate - 1;
begin
    my_procedure(''' || tmpSCHEMA || ''',' || tmpANALYSIS_ID || ',' || tmpORDER_ID || ',' || tmpRUNID || ');
end;
'

There are ways to pass parameters to jobs though if you really want.  You'll end up with cleaner code but more of it.  I don't have a handy example of doing that.  Check the dbms_scheduler documentation.
0
 

Author Closing Comment

by:GNOVAK
ID: 39727268
Excellent!  Thanks.
0

Featured Post

10 Questions to Ask when Buying Backup Software

Choosing the right backup solution for your organization can be a daunting task. To make the selection process easier, ask solution providers these 10 key questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses

617 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question