Home » SQL & PL/SQL » SQL & PL/SQL » job name in DBMS_SCHEDULER.CREATE_JOB
| job name in DBMS_SCHEDULER.CREATE_JOB [message #417409] |
Fri, 07 August 2009 18:53  |
casttree
Messages: 83
Registered: August 2008
|
Member |
|
|
Hi,
when I try the create a job with hyphen in job name, it throws the ora-27452 error.
begin DBMS_SCHEDULER.CREATE_JOB(job_name => 'TEST-1', job_type => 'PLSQL_BLOCK', job_action => '', enabled => TRUE); end;
ORA-27452: TEST-1 is an invalid name for a database object.
ORA-06512: at "SYS.DBMS_ISCHED", line 124
I am not sure why it doesn't allow hyphen in job name and wondering what is the correct name for job name? I mean, except "-" in job name , anything else should be excluded as job name ?
Thanks,
|
|
|
|
|
|
|
|
| Re: job name in DBMS_SCHEDULER.CREATE_JOB [message #426604 is a reply to message #417409] |
Fri, 16 October 2009 20:28  |
casttree
Messages: 83
Registered: August 2008
|
Member |
|
|
Ok, Looks like it is OK when we quoted the job_name like ' "test_1" '
BEGIN
DBMS_SCHEDULER.CREATE_JOB (
job_name => '"job-name-with-hyphens"'
,job_type => 'PLSQL_BLOCK'
,job_action => 'begin null; end; '
,start_date => sysdate+1
,enabled => TRUE
);
END;
/
The use
select *
FROM dba_scheduler_jobs
where job_name = 'job-name-with-hyphens'
I can see the job name is shown as Quote:job-name-with-hyphens without " " . so oracle removed the "" automatically.
When I tried to use binding variable like below , and set $1 as Quote:"job-name-with-hyphens" , then from query to dba_scheduler_jobs, i found the job name as Quote:"job-name-with-hyphens " with "" , is there way that we can use bind variable and remove the ""
BEGIN
DBMS_SCHEDULER.CREATE_JOB (
job_name => $1
,job_type => 'PLSQL_BLOCK'
,job_action => 'begin null; end; '
,start_date => sysdate+1
,enabled => TRUE
);
END;
Thanks,
[Updated on: Fri, 16 October 2009 20:32] Report message to a moderator |
|
|
|
|
|
| Re: job name in DBMS_SCHEDULER.CREATE_JOB [message #426618 is a reply to message #426614] |
Sat, 17 October 2009 02:49 |
casttree
Messages: 83
Registered: August 2008
|
Member |
|
|
Yeah, I hope to
1. use hyphen in job name ,
2. get the job name in view dba_scheduler_jobs without double quote, like job-name-with-hyphens, (no double quote).
3. Use binding variable for the query.
For item 1, and 2,
The following code works fine.
BEGIN
DBMS_SCHEDULER.CREATE_JOB (
job_name => '"job-name-with-hyphens"'
,job_type => 'PLSQL_BLOCK'
,job_action => 'begin null; end; '
,start_date => sysdate+1
,enabled => TRUE
);
END;
/
I try to get the solution to satisfy the all items 1, 2 ,3
if using binding variable , and set the job name with double quote like "job-name-with-hyphens"
BEGIN
DBMS_SCHEDULER.CREATE_JOB (
job_name => ?
,job_type => 'PLSQL_BLOCK'
,job_action => 'begin null; end; '
,start_date => sysdate+1
,enabled => TRUE
);
END;
stmt.setString(1, "\"test-job-name\"");
, in dba_scheduler_jobs, the job name is stored as "job-name-with-hyphens", but not job-name-with-hyphens.
|
|
|
|
| Re: job name in DBMS_SCHEDULER.CREATE_JOB [message #426619 is a reply to message #426618] |
Sat, 17 October 2009 03:06 |
 |
Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
So you use JDBC, don't you think it was important to say it?
In PL/SQL:
SQL> declare jobname varchar2(30) := '"job-name-with-hyphens"';
2 BEGIN
3 DBMS_SCHEDULER.CREATE_JOB (
4 job_name => jobname
5 ,job_type => 'PLSQL_BLOCK'
6 ,job_action => 'begin null; end; '
7 ,start_date => sysdate+1
8 ,enabled => TRUE
9 );
10 END;
11 /
PL/SQL procedure successfully completed.
SQL> select job_name from user_scheduler_jobs;
JOB_NAME
------------------------------
job-name-with-hyphens
1 row selected.
Try it in this way, or try it without " and show us what you REALLY does and has, with a copy and paste. I doubt you have:
Quote:stmt.setString(1, "\"test-job-name\"");
and
Quote:the job name is stored as "job-name-with-hyphens"
Regards
Michel
|
|
|
|
|
|
| Re: job name in DBMS_SCHEDULER.CREATE_JOB [message #426662 is a reply to message #426644] |
Sun, 18 October 2009 01:42 |
|
Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
No reason, it is how it works. If you want to use non standard characters (and it is a bad iddea) you have to enclose the name between double-quotes and these ones are not part of the name (you cannot use " inside a name).
Regards
Michel
|
|
|
|
Goto Forum:
Current Time: Tue May 20 11:39:00 CDT 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
