Home » SQL & PL/SQL » SQL & PL/SQL » Running procedure in background on button click (11g, XP)
Running procedure in background on button click [message #402958] Wed, 13 May 2009 05:00 Go to next message
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 #402963 is a reply to message #402958] Wed, 13 May 2009 05:15 Go to previous messageGo to next message
cookiemonster
Messages: 13965
Registered: September 2008
Location: Rainy Manchester
Senior Member
If you want to one once immediately you might as well use dbms_job - it's easier.
Re: Running procedure in background on button click [message #402967 is a reply to message #402958] Wed, 13 May 2009 05:22 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #402970 is a reply to message #402967] Wed, 13 May 2009 05:30 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Thanks cookiemonster and dr.s.raghunathan.

@cookiemonster,
As said by my seniors, I have to use DBMS_SCHEDULER package.

@dr.s.raghunathan,
I think you misunderstood my problem.
Running the procedure in background means it should not stop working in front-end. Front-end part should go as usual.

regards,
Delna
Re: Running procedure in background on button click [message #402978 is a reply to message #402970] Wed, 13 May 2009 05:45 Go to previous messageGo to next message
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 #403176 is a reply to message #402978] Thu, 14 May 2009 01:25 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Please anybody help me for the problem I described.
I have gone through documentation also but I am confused about passing argument.

I am having following table type
CREATE OR REPLACE TYPE nest_table_number is table of number(38);


and is used as argument type in following procedure of package as
CREATE OR REPLACE PACKAGE SEO_VERIFICATION
AS
...
    PROCEDURE VERIFY_DIR_SUBMISSION(P_WEBSITE_I IN NEST_TABLE_NUMBER, P_STATUS_I IN NUMBER);    
END;


I dont know how to pass these two parameters while scheduling this procedure on click of button in front-end.

regards,
Delna
Re: Running procedure in background on button click [message #403203 is a reply to message #403176] Thu, 14 May 2009 03:22 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
Ok, back one step then:

How would you schedule this if the procedure had NO arguments?
Re: Running procedure in background on button click [message #403208 is a reply to message #403203] Thu, 14 May 2009 03:49 Go to previous messageGo to next message
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 Go to previous message
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
Previous Topic: string inside the dbms_xmlgen.getxml function
Next Topic: Remove Special chracters and Carriage
Goto Forum:
  


Current Time: Sun Feb 16 00:34:08 CST 2025