Solved

User display while running Oracle Procedure from Access 2010

Posted on 2013-12-17
7
679 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 73

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
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
LVL 73

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 73

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

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.

Question has a verified solution.

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

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

831 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