Stored procedure output to a tables?

From: jobs <jobs_at_webdos.com>
Date: 3 Feb 2007 14:15:55 -0800
Message-ID: <1170540955.231610.279590@m58g2000cwm.googlegroups.com>

Greetings.

I have a vb.net oledb procedure that can schedules an oracle procedure to run and returns to me an oracle job number.

It looks like this:

FUNCTION CPM_SUBMIT (iprocname in varchar) return number
is

jobno number;
BEGIN

	DBMS_JOB.SUBMIT(jobno,iprocname||';',sysdate);
	commit;
	return (jobno);

END; we are still in the design phase for an asp.net front end that will manage both sqlserver and oracle processes. Nothing too fancy, looking more for easy than features.

We need Oracle output, both the extracts and the any execution logs to be accessable to this asp.net front end and the sql server databases that will track processes. In the mix (soon), will be a new NAS access able to both sides.

My Oracle Noobie questions - Your insite is greatly appreciated!!!

Any crafty way for us to to spool execution logs to files that contain the oracle job number? from inside or outside the sp? can the sp be aware of the job number it's running as?
Is it worth our time and performance to looking into changing oracle sp extracts to write direct sql server tables? If so, at a high level how?
The above SP is sitting inside a particular database schema. Say we might want to launch sps on other Oracle databases/schemas/servers.. what would be the best way to attack that? Im thinking worse case we have to leave a copy of that sp on every database. I have not tried from ado.net, but I don't think I can run DBMS_JOB.SUBMIT outside of an sp or function. Or maybe construct the sp (or job if such a thing exsist in oracle) on the fly which is the way we are doing it in sql server.

Again, thank you for *any* information you might have! Received on Sat Feb 03 2007 - 16:15:55 CST