Running procedure in background on button click [message #402958] |
Wed, 13 May 2009 05:00  |
 |
delna.sexy
Messages: 941 Registered: December 2008 Location: Surat, The Diamond City
|
Senior Member |
|
|
Hello all gurus,
I need some help for one problem described below.
I have one procedure, say PROC1.
And as it takes so much time to execute, I want it to run in background.
This procedure should be executed when one button is pressed on front-end.
I know that I can achieve this using DBMS_SCHEDULER package. But I want to execute this procedure only once and at the time of button press event. So please help me that which CREATE_JOB() procedure (out of many overloaded forms) should be used and what should be the values of parameters.
Please tell me if more information is required.
regards,
Delna
|
|
|
|
Re: Running procedure in background on button click [message #402967 is a reply to message #402958] |
Wed, 13 May 2009 05:22   |
dr.s.raghunathan
Messages: 540 Registered: February 2008
|
Senior Member |
|
|
1. once you press the button pass some value to the called procedure. at the end of the procedure withdrew the set value of the button. However, after completion of proceedure only it will allow next operation.
2. once you click the button, execute the proceedure and disable the button. The same button may be enabled through some other routine.
Ofcourse, i circumvent through available logics. There may be straight call of doing this and let me also wait for some suggestion.
yours
dr.s.raghunathan
|
|
|
Re: Running procedure in background on button click [message #402968 is a reply to message #402967] |
Wed, 13 May 2009 05:27   |
cookiemonster
Messages: 13965 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
dr.s.raghunathan wrote on Wed, 13 May 2009 11:22 | 1. once you press the button pass some value to the called procedure. at the end of the procedure withdrew the set value of the button. However, after completion of proceedure only it will allow next operation.
2. once you click the button, execute the proceedure and disable the button. The same button may be enabled through some other routine.
Ofcourse, i circumvent through available logics. There may be straight call of doing this and let me also wait for some suggestion.
yours
dr.s.raghunathan
|
?????
What has that got to do with the question asked?
|
|
|
|
Re: Running procedure in background on button click [message #402978 is a reply to message #402970] |
Wed, 13 May 2009 05:45   |
cookiemonster
Messages: 13965 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
delna.sexy wrote on Wed, 13 May 2009 11:30 | @cookiemonster,
As said by my seniors, I have to use DBMS_SCHEDULER package.
|
Why use a sledge hammer to crack a nut?
Don't know how to do it in dbms_scheduler - never used it - but, like all oracle packages, the documentation is extensive - have a read.
|
|
|
|
|
Re: Running procedure in background on button click [message #403208 is a reply to message #403203] |
Thu, 14 May 2009 03:49   |
 |
Barbara Boehmer
Messages: 9104 Registered: November 2002 Location: California, USA
|
Senior Member |
|
|
This seemed like a simple problem until I started to try to put together a demo. The issue is that the job_action parameter of dbms_scheduler.create_job expects a literal value and I don't see any way to pass a type to it directly or bind it. The only workaround I found was to unnest the table values, so that they could be concatenated as literals. I am assuming that the parameters will not be constant and will be passed from somewhere, so you would have to do something like this. Please see the demo below that solves the problem of passing the parameters to dbms_scheduler.create_job. For other problems regarding how to run this code from Forms or APEX using a when-button-pressed trigger or some such thing, please see the Forms or APEX forums.
SCOTT@orcl_11g> -- table for testing:
SCOTT@orcl_11g> CREATE TABLE test_tab
2 (test_num NUMBER,
3 test_status NUMBER)
4 /
Table created.
SCOTT@orcl_11g> -- type you provided:
SCOTT@orcl_11g> CREATE OR REPLACE TYPE nest_table_number is table of number(38);
2 /
Type created.
SCOTT@orcl_11g> -- package spec you provided:
SCOTT@orcl_11g> CREATE OR REPLACE PACKAGE SEO_VERIFICATION
2 AS
3 PROCEDURE VERIFY_DIR_SUBMISSION
4 (P_WEBSITE_I IN NEST_TABLE_NUMBER,
5 P_STATUS_I IN NUMBER);
6 END SEO_VERIFICATION;
7 /
Package created.
SCOTT@orcl_11g> -- package body for testing:
SCOTT@orcl_11g> CREATE OR REPLACE PACKAGE BODY SEO_VERIFICATION
2 AS
3 PROCEDURE VERIFY_DIR_SUBMISSION
4 (P_WEBSITE_I IN NEST_TABLE_NUMBER,
5 P_STATUS_I IN NUMBER)
6 IS
7 BEGIN
8 FOR i IN 1 .. p_website_i.COUNT LOOP
9 INSERT INTO test_tab VALUES (p_website_i(i), p_status_i);
10 END LOOP;
11 END VERIFY_DIR_SUBMISSION;
12 END SEO_VERIFICATION;
13 /
Package body created.
SCOTT@orcl_11g> -- schedule job to run once immediately using variables with assigned values:
SCOTT@orcl_11g> DECLARE
2 p_num_tab nest_table_number := nest_table_number (42, 23);
3 p_stat NUMBER := 1;
4 p_nums VARCHAR2 (32767);
5 BEGIN
6 FOR i IN 1 .. p_num_tab.COUNT LOOP
7 p_nums := p_nums || ',' || p_num_tab(i);
8 END LOOP;
9 p_nums := LTRIM (p_nums, ',');
10 DBMS_SCHEDULER.CREATE_JOB
11 (JOB_NAME => 'test_job',
12 JOB_TYPE => 'PLSQL_BLOCK',
13 JOB_ACTION =>
14 'BEGIN
15 seo_verification.verify_dir_submission
16 (nest_table_number (' || p_nums|| '), ' || p_stat || ');
17 END;',
18 ENABLED => TRUE);
19 END;
20 /
PL/SQL procedure successfully completed.
SCOTT@orcl_11g> -- wait for the job to finish running:
SCOTT@orcl_11g> EXEC DBMS_LOCK.SLEEP (5)
PL/SQL procedure successfully completed.
SCOTT@orcl_11g> -- check results:
SCOTT@orcl_11g> SELECT * FROM test_tab
2 /
TEST_NUM TEST_STATUS
---------- -----------
42 1
23 1
SCOTT@orcl_11g>
|
|
|
Re: Running procedure in background on button click [message #403209 is a reply to message #403203] |
Thu, 14 May 2009 03:56  |
 |
delna.sexy
Messages: 941 Registered: December 2008 Location: Surat, The Diamond City
|
Senior Member |
|
|
for that using DBMS_SCHEDULER,
I would CREATE_PROGRAM for VERIFY_DIR_SUBMISSION.
No need to DEFINE_PROGRAM_ARGUMENT.
CREATE_JOB.
ENABLE program.
ENABLE job.
RUN_JOB.
But in my case, I have to pass 2 argument and for that I have to use DEFINE_PROGRAM_ARGUMENT. And I am confused with that.
regards,
Delna
|
|
|