Solved

User display while running Oracle Procedure from Access 2010

Posted on 2013-12-17
7
696 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 37

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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

735 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