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 Go to next message
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 #417411 is a reply to message #417409] Fri, 07 August 2009 20:25 Go to previous messageGo to next message
BlackSwan
Messages: 24912
Registered: January 2009
Senior Member
>I am not sure why it doesn't allow hyphen in job name
You might call it a hyphen; while Oracle may view it as a minus sign.

Somewhat dated, but still valid.
http://download.oracle.com/docs/cd/B12037_01/server.101/b10759/sql_elements008.htm

When all else fails, Read The Fine Manual!

[Updated on: Fri, 07 August 2009 20:31]

Report message to a moderator

Re: job name in DBMS_SCHEDULER.CREATE_JOB [message #417427 is a reply to message #417409] Sat, 08 August 2009 06:30 Go to previous messageGo to next message
Frank Naude
Messages: 4502
Registered: April 1998
Senior Member
Hyphens are not allowed in Oracle identifiers. You can replace it with an underscore. Quoting the identifier (i.e. "TEST-1") may also work.
Re: job name in DBMS_SCHEDULER.CREATE_JOB [message #426604 is a reply to message #417409] Fri, 16 October 2009 20:28 Go to previous messageGo to next message
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 #426614 is a reply to message #426604] Sat, 17 October 2009 01:28 Go to previous messageGo to next message
Michel Cadot
Messages: 63810
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
$1 is not a bind variable.
But even if you use a bind variable you still have to give the " to create a job with this name.
Just try it.

Regards
Michel
Re: job name in DBMS_SCHEDULER.CREATE_JOB [message #426618 is a reply to message #426614] Sat, 17 October 2009 02:49 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Michel Cadot
Messages: 63810
Registered: March 2007
Location: Nanterre, France, http://...
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 #426644 is a reply to message #417409] Sat, 17 October 2009 16:22 Go to previous messageGo to next message
casttree
Messages: 83
Registered: August 2008
Member
Yes, it works. Thanks,

Any reason that the quoted job name can use hyphen (oracle doesn't to use hyphen in identifiers directly ), also why the double quote is removed in user_scheduler_jobs ?

Thanks,
Re: job name in DBMS_SCHEDULER.CREATE_JOB [message #426662 is a reply to message #426644] Sun, 18 October 2009 01:42 Go to previous message
Michel Cadot
Messages: 63810
Registered: March 2007
Location: Nanterre, France, http://...
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
Previous Topic: join
Next Topic: improve performance
Goto Forum:
  


Current Time: Thu Sep 29 22:47:12 CDT 2016

Total time taken to generate the page: 0.08464 seconds