dbms_job.submit, stored procedure with parameters

From: Manuela Mueller <mueller_m_at_fiz-chemie.de>
Date: Fri, 11 Oct 2002 18:29:27 +0200
Message-ID: <3DA6FC67.C02FF62E@fiz-chemie.de>

OS: Linux Linux 2.4.18-4GB (Suse 8.0)
RDBMS: Oracle 9.2.0.1.0, Standard Edition

Dear all,
maybe an easy question, but I have some trouble with this. Maybe someone can point me in the right direction.

I created a stored procedure which queries Oracle Text. Procedure startuser_search runs fine.
In case of long running queries, the procedure startuser_search must be executed in the background (web based application).

A second stored procedure job_submit executes startuser_search in the background. I have difficulties with the second one.

create or replace procedure startuser_search

(	p_table		varchar2 default 'SEARCH_1_MP',
	p_string	varchar2 default 'aids',
	p_hitlist_id	number	 default 5000000

)

is

  type rc is ref cursor;..<snip rest>

create or replace procedure job_submit (

	p_background    IN NUMBER DEFAULT  0,
	--identical to proc startuser_search
	p_table		IN varchar2 default 'SEARCH_1_MP',
	p_string	IN varchar2 default 'aids',
	p_hitlist_id	IN number   default 5000000

)

IS

	my_sqlerrm	varchar2(150);
	l_jobno	        number; --seq number for dbms_job
	l_p_string      varchar2(500) := p_string; 
	l_p_table       varchar2(20)  := p_table; 
	l_p_hitlist_id  number	      := p_hitlist_id;

BEGIN
  --print stuff out

   dbms_output.put_line('l_p_string :' ||l_p_string);
   dbms_output.put_line('l_p_table: ' || l_p_table);
   dbms_output.put_line('l_p_hitlist_id: ' || l_p_hitlist_id);

• short query, call startuser_search IF p_background = 0 THEN startuser_search(p_string=>l_p_string, p_table=>l_p_table, p_hitlist_id=>l_p_hitlist_id);
• long running query, dbms_job.submit ELSIF p_background = 1 THEN dbms_job.submit(l_jobno, 'startuser_search(''l_p_string'', ''l_p_table'', ''l_p_hitlist_id'');' ); commit; --explicit for job submission

   dbms_output.put_line('after job submission, l_jobno: ' || l_jobno); 
   dbms_output.put_line('l_p_string :' ||l_p_string);
   dbms_output.put_line('l_p_table: ' || l_p_table);
   dbms_output.put_line('l_p_hitlist_id: ' || l_p_hitlist_id);  

 --insert in helper table
 insert into param_hitlist_id (job_id, hitlist_id)

        values (l_jobno, l_p_hitlist_id);  commit; --for insert

     dbms_output.put_line('insert in table param_hitlist_id done'); end if;

• for all other exceptions: warning with error message, exit procedure. EXCEPTION WHEN OTHERS THEN my_sqlerrm := SUBSTR(SQLERRM, 1, 150); dbms_output.put_line (' ERROR inserting hits: '|| my_sqlerrm);

END;
/

output:
 exec job_submit(p_background=>1);

l_p_string :aids
l_p_table: SEARCH_1_MP
l_p_hitlist_id: 5000000

after job submission, l_jobno: 32

l_p_string :aids
l_p_table: SEARCH_1_MP
l_p_hitlist_id: 5000000

insert in table param_hitlist_id done

PL/SQL procedure successfully completed.

job_submit never executes startuser_search. <snip of trace file>
*** SESSION ID:(19.297) 2002-10-11 18:45:35.277 *** 2002-10-11 18:45:35.277
ORA-12012: error on auto execute of job 32 ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at line 1
</snip trace file>

I monitor dba_jobs with following results:

       JOB JOB_OWNER FAILURES B JOB_DEFINITION

---------- --------------- ---------- - ------------------------------
        32 GUIDE                    1 N startuser_search('l_p_string',
                                        'l_p_table',
                                        'l_p_hitlist_id');

I tried different syntax to call dbms_job.submit:

   dbms_job.submit(

	job => l_jobno,
	what => 'begin startuser_search(p_string=>''||l_p_string||'',
			p_table=>''||l_p_table||'',
			p_hitlist_id=>'||l_p_hitlist_id||'); end;'
	);

but result was the same.

With hard coded values, procedure startuser_search is executed in the background:
DECLARE
        jobno number;
BEGIN

        DBMS_JOB.SUBMIT
            (job => jobno
            , what => 'begin startuser_search(p_string=>''aids'',
p_table=>''SEARCH_1_MP'', p_hitlist_id=>6000000); end;'
            );
        COMMIT;

END;
/

Any suggestions are welcome, TIA and have a nice weekend Manuela Mueller Received on Fri Oct 11 2002 - 11:29:27 CDT