Solved

User display while running Oracle Procedure from Access 2010

Posted on 2013-12-17
7
686 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 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

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.

Question has a verified solution.

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

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

856 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