Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

User display while running Oracle Procedure from Access 2010

Posted on 2013-12-17
7
Medium Priority
?
709 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
  • 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 74

Accepted Solution

by:
sdstuber earned 2000 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 2000 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

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

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

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 …
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

886 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