Procedure not executing

Question

I have the following procedure, but no scheduler job is getting created and I cannot find any errors while running the job. How can I debug this and what is wrong with the code?

CREATE OR REPLACE PROCEDURE test
IS
prod_no varchar2(32);
prod_date date;
employee varchar2(32);
assurer varchar2(32);
prod_description varchar2(32);
prod_days number;
assig varchar2(32);
reg_no number;
l_v number;

BEGIN

prod_no := '300';
prod_date  := sysdate;
employee := 'SAM';
assurer := 'MAT';
prod_description := 'test';
prod_days := 1;
assig := 'A';
reg_no := 1;

DBMS_SCHEDULER.create_job (
                    job_name      => DBMS_SCHEDULER.generate_job_name ('FR'),
                    job_type      => 'PLSQL_BLOCK',
                    job_action     =>    'BEGIN test_job('
                                        || i_prod_no
                                        || ','''
                                        || prod_date
                                        || ','''
                                        || employee 
                                        || ','''
                                        || assurer
                                        || ','''
                                        || prod_description
                                        || ','''
                                        || prod_days
                                        || ','''
                                        || assig
                                        || ','''
                                        || reg_no
                                        || '''); end;',
                    start_date     => SYSDATE,                   
                    end_date      => NULL,
                    enabled         => TRUE,
                    comments      => 'test product'
                );

    commit;

exception
when others then

dbms_output.put_line (dbms_utility.format_error_stack);
END test;
/

Two things to try: (1) SET SERVEROUT ON; (2) get rid of the unnecessary exception handler. — Jeffrey Kemp, Oct 04 '12 at 07:58
@JeffreyKemp I have added `DBMS_OUTPUT.put_line('Products');` and this is getting executed. — Jacob, Oct 04 '12 at 08:02

Rene · Accepted Answer · 2012-10-04T08:44:34.870

If job action is syntactically incorrect you won't get any warning, but you won't get a job either. Here is your statement:

BEGIN test_job(300,'04-OCT-12,'SAM,'MAT,'test,'1,'A,'1'); end;

You are missing some ' to close the string values.

CREATE OR REPLACE PROCEDURE test
IS
prod_no varchar2(32);
prod_date date;
employee varchar2(32);
assurer varchar2(32);
prod_description varchar2(32);
prod_days number;
assig varchar2(32);
reg_no number;
l_v number;
l_statement varchar2(32767);

BEGIN

prod_no := '300';
prod_date  := sysdate;
employee := 'SAM';
assurer := 'MAT';
prod_description := 'test';
prod_days := 1;
assig := 'A';
reg_no := 1;

l_statement :=  'BEGIN test_job('
                                        || i_prod_no
                                        || ','''
                                        || prod_date
                                        || ','''
                                        || employee 
                                        || ','''
                                        || assurer
                                        || ','''
                                        || prod_description
                                        || ','''
                                        || prod_days
                                        || ','''
                                        || assig
                                        || ','''
                                        || reg_no
                                        || '''); end;'

dbms_output.put_line(l_statement);

DBMS_SCHEDULER.create_job (
                    job_name      => DBMS_SCHEDULER.generate_job_name ('FR'),
                    job_type      => 'PLSQL_BLOCK',
                    job_action     =>  l_statement,
                    start_date     => SYSDATE,                   
                    end_date      => NULL,
                    enabled         => TRUE,
                    comments      => 'test product'
                );

    commit;

exception
when others then

dbms_output.put_line (dbms_utility.format_error_stack);
END test;
/

How do you manage to get the values inside test_job? `BEGIN test_job(300,'04-OCT-12,'SAM,'MAT,'test,'1,'A,'1'); end;` — Jacob, Oct 04 '12 at 08:13
Build your plsql block in a string variable and print this before passing it on to dbms_job.create_job. I will edit my answer. — Rene, Oct 04 '12 at 08:44
Another question which table has the details of scheduler jobs, I tried querying in `dba_scheduler_programs`, but cannot be found in that when I created a job scheduler with no parameters. — Jacob, Oct 04 '12 at 08:46

Procedure not executing

1 Answers1