PLS-00103: Encountered the symbol in DBMS

Calling the package inside the job gives an Error report -

ORA-06550: line 8, column 5:
PLS-00103: Encountered the symbol "
  );
end; : AUXSQLDBIND4: = SqlDevBind1Z_1; : AUXSQLDBIND3: = SqlDevBind1Z_2; : AUXSQLDBIND2: = SqlDevBind1Z_3; : AUXSQLDBIND1: = SqlDevBind "

Help call the packet with the passed variable work_date for a certain calculation for each day of the month.

Thanks in advance for your help!

DBMS_SCHEDULER.create_job (
job_name => 'J_ACCOUNT_TURNOVER_F',
job_type => 'PLSQL_BLOCK',
start_date => SYSTIMESTAMP, enabled => TRUE,
job_action => 
'declare
start_date number;
end_date number;
work_date date;
begin
start_date := to_number(to_char(to_date('2018-01-01', 'yyyy-MM-dd'), 'j'));
end_date := to_number(to_char(to_date('2018-01-31', 'yyyy-MM-dd'), 'j'));
for cur_r in start_date..end_date loop
 work_date := to_char(to_date(cur_r, 'j'), 'yyyyMMdd');
 dma.fill_account_turnover_f.fill(work_date);
end loop;
end;'
);
end;

CodePudding user response：

Wouldn't it be simpler if you created a stored procedure and called it, instead of embedding that anonymous PL/SQL block into DBMS_SCHEDULER.CREATE_JOB call? Now you have to escape single quotes, i.e. use two consecutive ones every time. Something like this:

begin
DBMS_SCHEDULER.create_job (
job_name => 'J_ACCOUNT_TURNOVER_F',
job_type => 'PLSQL_BLOCK',
start_date => SYSTIMESTAMP, enabled => TRUE,
job_action => 
'declare
start_date number;
end_date number;
work_date date;
begin
start_date := to_number(to_char(to_date(''2018-01-01'', ''yyyy-MM-dd''), ''j''));
end_date := to_number(to_char(to_date(''2018-01-31'', ''yyyy-MM-dd''), ''j''));
for cur_r in start_date..end_date loop
 work_date := to_char(to_date(cur_r, ''j''), ''yyyyMMdd'');
 dma.fill_account_turnover_f.fill(work_date);
end loop;
end;'
);
end;
/

CodePudding user response：

Maybe you're allergic to stored procedures :) Here is how you can do it without having to escape each individual quote. It is called the "Q Quote syntax".

DBMS_SCHEDULER.create_job (
job_name => 'J_ACCOUNT_TURNOVER_F',
job_type => 'PLSQL_BLOCK',
start_date => SYSTIMESTAMP, enabled => TRUE,
job_action => 
q'!declare
start_date number;
end_date number;
work_date date;
begin
start_date := to_number(to_char(to_date('2018-01-01', 'yyyy-MM-dd'), 'j'));
end_date := to_number(to_char(to_date('2018-01-31', 'yyyy-MM-dd'), 'j'));
for cur_r in start_date..end_date loop
 work_date := to_char(to_date(cur_r, 'j'), 'yyyyMMdd');
 dma.fill_account_turnover_f.fill(work_date);
end loop;
end;'
);
end;!'