Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: dbms_job.submit, stored procedure with parameters

Re: dbms_job.submit, stored procedure with parameters

From: Manuela Mueller <520040906697-0001_at_t-online.de>
Date: Sun, 13 Oct 2002 23:17:56 +0200
Message-ID: <3DA9E304.CED9DAF7@t-online.de>


Connor McDonald wrote:
>
> Manuela Mueller wrote:
> >
> > 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
> > )
> > is
> >
> > 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
>
> What I meant is that in any situation where you intend submitting jobs
> on an ad-hoc basis, then you can end up with a shared pool littered with
> junk, because the parameters to the job are typically passed as
> literals.
>
> If you have table (say) job_parms, then you can do
>
> begin
> dbms_job.submit(v_job,'my_proc(job);')
> insert into job_parms values (v_job,parm1,parm2,parm3,etc)
> end;
>
> where parm1..n are plsql variables not literals. Then inside my_proc,
> all it need do is 'select .. from job_parms' for the current job.
>
> hth
> connor
>
> --
> ==============================
> Connor McDonald
>
> http://www.oracledba.co.uk
>
> "Some days you're the pigeon, some days you're the statue..."

Thanks Connor,
sat too long on this program, searching for missing and/or too much single quotes and ended
up caught myself in a loop.
Thanks for your input and time, implement your suggestions tommorow morning.

Manuela Mueller Received on Sun Oct 13 2002 - 16:17:56 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US