| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: dbms_job.submit, stored procedure with parameters
Connor McDonald wrote:
>
> 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..."
Hello Connor,
thanks for your fast response. I'm not sure how to implement putting
all needed variables to the helper table.
Maybe we made mistakes in application design.
In our web based application the user enters a search string, and
selects one of
6 search tables, all indexec by Oracle text. After he hits the submit
button,
a perl program takes his search string, selected table, assigns a unique
hitlist_id to his search and passes all values to my startuser_search.
We decided to introduce a second stored proccedure for long running
queries (eg search string '%irus') to execute the procedure in the
backgound.
The perl program evaluates the search string, and if it contains wild
cards
in the query string, passes p_background=>1 to procedure job_submit
(dbms_job.submit comes to life).
In other cases (eg search string 'morbus crohn') the perl program passes p_background=>0, and job_submit executes startuser_search (without dbms_job.submit).
I decided to introduce the little helper table param_hitlist_id because
I must
monitor if the job is running or already finished. So I must create a
function or
third stored procedure which checks all x seconds if the job in
dba_jobs_running
has the same number as the job in the helper table, joined with
hitlist_id to
identify the user.
I added the code for proc startuser_search below.
create or replace procedure startuser_search
( p_table varchar2 default 'SEARCH_1_MP',
p_string varchar2 default 'aids',
p_hitlist_id number default 5000000
)
type rc is ref cursor;
l_cursor rc;
l_score number(3);
l_id number(16);
l_rownum number := 0; --rownum in table hits
my_sqlerrm VARCHAR2(150);
begin
open l_cursor for 'select score(1), id from ' || p_table ||
' where contains(DOC, :p_string , 1) > 0
and rownum <= 10000
order by score(1) desc '
using p_string;
loop
fetch l_cursor into l_score, l_id;
exit when l_cursor%notfound;
if ( l_cursor%found )
then
--increment counter (table hits)
l_rownum := l_rownum + 1;
-- test
dbms_output.put_line('l_score ' ||l_score || ', ' ||
'l_id ' || l_id || ', '
||'l_rownum ' || l_rownum ||
', ' || 'p_hitlist_id ' ||
p_hitlist_id);
insert into hits (score, document_id, hitidx,
hitlist_id)
values(l_score, l_id, l_rownum, p_hitlist_id);
end if; --l_cursor%found
end loop;
close l_cursor;
commit;
-- 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;
/
TIA
Manuela Mueller
Received on Fri Oct 11 2002 - 15:12:38 CDT
 |
 |