Sunday 24 February 2019

ORA-27475 Case sensitive

When you need to work with case-sensitive identifiers, you need to put them between double quotation marks, otherwise the database handles them as case-insensitive and automatically converts to uppercase.
begin
  dbms_scheduler.create_job
  (
   job_name             => '"job1"',
   job_type             => 'plsql_block',
   job_action           => 'begin null; end;'
  );
end;
/

select job_name from dba_scheduler_jobs where job_name = 'job1';

JOB_NAME                      
------------------------------
job1
This fails:
begin
  dbms_scheduler.drop_job('job1');
end;
/

Error report -
ORA-27475: "BP.JOB1" must be a job
...
But this works:
begin
  dbms_scheduler.drop_job('"job1"');
end;
/

PL/SQL procedure successfully completed.

No comments:

Post a Comment