0

I have a package where I am calling a procedure to insert records to a table and I am calling this procedure twice with a interval of 2 minutes using sys.DBMS_LOCK.sleep (<>);

Problem I facing is my calling form which is from application is still open till the insertion completes.

How can I make sure that when I submit my page and page should close, insertion should happen in backend some kind of asynchronous call. In database procedure are there any asynchronous key word to do this kind of activity?

Thanks

Update

putData(empNo,EmpName);
sys.DBMS_LOCK.sleep (<>);
putData(empNo,EmpName);

and due to the above my page stays till the second procedure finishes. I would like to close the page as soon as the first procedure finishes or when user submits the page.

Update 2

DBMS_JOB.SUBMIT(ln_dummy, 'begin putData('||empNo,EmpName||'); end;');

gives me compilation error wrong number of arguments to call Submit.

How can I resolve this?

APC
  • 144,005
  • 19
  • 170
  • 281
Jacob
  • 14,463
  • 65
  • 207
  • 320
  • Did you see this? http://stackoverflow.com/questions/576802/can-we-use-threading-in-pl-sql – Martin Wilson Oct 03 '12 at 12:52
  • @MartinWilson See my update 2 above. How can I resolve this? – Jacob Oct 03 '12 at 13:18
  • *Why* are you doing this? Is it just because the insert is taking too long to complete, so you want to have it run asynchronously? If so, I'd look at why the insert is so slow and fix that. – Jeffrey Kemp Oct 04 '12 at 01:31

1 Answers1

5

Your PutData() procedure is expecting two parameters. You might think you're passing two parameters but you're not. Also, if EmpName is a string - which seems likely - you'll need to wrap it in escaped quotes. Basically you're writing dynamic SQL here, which is always tricky.

Try this:

DBMS_JOB.SUBMIT(ln_dummy, 'begin putData('||empNo||','''||EmpName||'''); end;');

"Other problem is how to run the these jobs at a interval of 10 minutes"

SUBMIT() can take an INTERVAL parameter. It's in the documentation for DBMS_JOB. Find out more.

However, if you want each iterationn to work with different parameter values you probably need to re-think your application design. You should have a procedure which polls a table for values to process.

Or use a queue. It depends on what you're really trying to achieve.

APC
  • 144,005
  • 19
  • 170
  • 281
  • Thanks that resolved the issue. Other problem is how to run the these jobs at a interval of 10 minutes? – Jacob Oct 03 '12 at 14:59