Re: dbms_job.submit, stored procedure with parameters

Manuela Mueller wrote:
>
> 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

How about put all the relevant variables (p_string,p_table,p_hit) into the helper table so that the job doesn't need any parameters (except 'job')

hth
connor

-- 
==============================
Connor McDonald

http://www.oracledba.co.uk

"Some days you're the pigeon, some days you're the statue..."