OraFAQ Forum: SQL & PL/SQL » user schema can't create or alter job (merged 3)

Home » SQL & PL/SQL » SQL & PL/SQL » user schema can't create or alter job (merged 3) (oracle 12GR2)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

user schema can't create or alter job (merged 3) [message #643669]

Thu, 15 October 2015 03:47

agung_prw
Messages: 9
Registered: October 2015
Location: indonesia

Junior Member

Dear oracle expert,

Please help me,

I have problem on our oracle server which 1 of user schema can't create or alter of Job.

with message as below

ORA-06550: line 4, column 16:
PLS-00225: subprogram or cursor 'SYS' reference is out of scope
ORA-06550: line 4, column 3:
PL/SQL: Statement ignored

the role and privilege is same with other user schema , but other users are not problem.
and i already add the privilege and role but no effect( as DBA )

any expert have experience about this case kindly to share to me.

Thank you

Best regards

agung

Report message to a moderator

Re: user schema can't create or alter job (merged 3) [message #643674 is a reply to message #643669]

Thu, 15 October 2015 03:57

cookiemonster
Messages: 13975
Registered: September 2008
Location: Rainy Manchester

Senior Member

What exact code are you running to get that error?

Report message to a moderator

Re: user schema can't create or alter job (merged 3) [message #643677 is a reply to message #643674]

Thu, 15 October 2015 04:34

agung_prw
Messages: 9
Registered: October 2015
Location: indonesia

Junior Member

i want to create job to run of store procedure.

DECLARE
X NUMBER;
BEGIN
SYS.DBMS_JOB.SUBMIT
(
job => X
,what => 'BEGIN
EXEC PROC_DELTA(TO_CHAR(SYSDATE,''YYYYMMDD''));
END;'
,next_date => to_date('10-15-2015 16:24:27','mm/dd/yyyy hh24:mi:ss')
,no_parse => FALSE
);
:JobNumber := to_char(X);
END;

Report message to a moderator

Re: user schema can't create or alter job (merged 3) [message #643680 is a reply to message #643677]

Thu, 15 October 2015 05:00

cookiemonster
Messages: 13975
Registered: September 2008
Location: Rainy Manchester

Senior Member

dbms_job should have a public synonym so the SYS. shouldn't be needed.
Also you can't put exec in a PL/SQL block like that. exec is a sqlplus command.

Report message to a moderator

Re: user schema can't create or alter job (merged 3) [message #643682 is a reply to message #643680]

Thu, 15 October 2015 05:01

cookiemonster
Messages: 13975
Registered: September 2008
Location: Rainy Manchester

Senior Member

You haven't created an object called SYS in that schema have you?
Query dba_objects where object_name = 'SYS'

Report message to a moderator

Re: user schema can't create or alter job (merged 3) [message #643703 is a reply to message #643682]

Thu, 15 October 2015 17:41

agung_prw
Messages: 9
Registered: October 2015
Location: indonesia

Junior Member

Object SYS already there..other schema is no problem but only 1 have problem..

Report message to a moderator

Re: user schema can't create or alter job (merged 3) [message #643704 is a reply to message #643677]

Thu, 15 October 2015 17:56

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

agung_prw wrote on Thu, 15 October 2015 02:34

i want to create job to run of store procedure.

DECLARE
X NUMBER;
BEGIN
SYS.DBMS_JOB.SUBMIT
(
job => X
,what => 'BEGIN
EXEC PROC_DELTA(TO_CHAR(SYSDATE,''YYYYMMDD''));
END;'
,next_date => to_date('10-15-2015 16:24:27','mm/dd/yyyy hh24:mi:ss')
,no_parse => FALSE
);
:JobNumber := to_char(X);
END;

https://www.google.com/webhp?hl=en&tab=ww#hl=en&q=oracle+dbms_job+example

Report message to a moderator

Re: user schema can't create or alter job (merged 3) [message #643708 is a reply to message #643677]

Thu, 15 October 2015 21:01

agung_prw
Messages: 9
Registered: October 2015
Location: indonesia

Junior Member

THE DBMS_JOB ALREADY AS PUBLIC SYNONYM

OWNER SYNONYM_NAME TABLE_OWNER TABLE_NAME DB_LINK
PUBLIC DBMS_JOB SYS DBMS_JOB

that is not create job command issue but can't create job looks not enough the privilege.
i already give all role and all privilege but not effect.

Report message to a moderator

Re: user schema can't create or alter job (merged 3) [message #643710 is a reply to message #643708]

Thu, 15 October 2015 23:15

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

using sqlplus copy the results from below then paste them back here

set role none
declare
BEGIN
EXEC PROC_DELTA(TO_CHAR(SYSDATE,''YYYYMMDD''));
END;

Report message to a moderator

Re: user schema can't create or alter job (merged 3) [message #643718 is a reply to message #643703]

Fri, 16 October 2015 03:49

cookiemonster
Messages: 13975
Registered: September 2008
Location: Rainy Manchester

Senior Member

agung_prw wrote on Thu, 15 October 2015 23:41

Object SYS already there..other schema is no problem but only 1 have problem..

So you have got an object called SYS in that schema?
Get rid of it.

Report message to a moderator

Re: user schema can't create or alter job (merged 3) [message #643719 is a reply to message #643703]

Fri, 16 October 2015 04:20

gazzag
Messages: 1119
Registered: November 2010
Location: Bedwas, UK

Senior Member

SQL> SELECT owner, object_type
     FROM dba_objects
     WHERE object_name='SYS';

Report message to a moderator

Re: user schema can't create or alter job (merged 3) [message #643759 is a reply to message #643719]

Sat, 17 October 2015 01:39

agung_prw
Messages: 9
Registered: October 2015
Location: indonesia

Junior Member

Enter user-name: mkt@ivili
Enter password:

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> SELECT owner, object_type
2 FROM dba_objects
3 WHERE object_name='SYS';
FROM dba_objects
*
ERROR at line 2:
ORA-00942: table or view does not exist

SQL> set role none
2 declare
3 BEGIN
4 EXEC PROC_DELTA(TO_CHAR(SYSDATE,''YYYYMMDD''));
declare
*
ERROR at line 2:
ORA-00933: SQL command not properly ended

SQL>

Report message to a moderator

Re: user schema can't create or alter job (merged 3) [message #643768 is a reply to message #643759]

Sat, 17 October 2015 09:45

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

using sqlplus copy the results from below then paste them back here

set role none;
declare
BEGIN
PROC_DELTA(TO_CHAR(SYSDATE,'YYYYMMDD'));
END;
/

Report message to a moderator

Re: user schema can't create or alter job (merged 3) [message #643795 is a reply to message #643768]

Sun, 18 October 2015 20:28

agung_prw
Messages: 9
Registered: October 2015
Location: indonesia

Junior Member

Dear Mr. BlackSwan,
below the result running scrip from sqlplus

Enter user-name: mkt@ivli
Enter password:

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> set role none;

Role set.

SQL> declare
2 BEGIN
3 PROC_DELTA(TO_CHAR(SYSDATE,'YYYYMMDD'));
4 END;
5 /

PL/SQL procedure successfully completed.

SQL>

Report message to a moderator

Re: user schema can't create or alter job (merged 3) [message #643797 is a reply to message #643795]

Mon, 19 October 2015 00:06

Michel Cadot
Messages: 68776
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

So everything is good now?

Report message to a moderator

Re: user schema can't create or alter job (merged 3) [message #643803 is a reply to message #643797]

Mon, 19 October 2015 02:08

agung_prw
Messages: 9
Registered: October 2015
Location: indonesia

Junior Member

NOT YET

Report message to a moderator

Re: user schema can't create or alter job (merged 3) [message #643804 is a reply to message #643803]

Mon, 19 October 2015 02:10

agung_prw
Messages: 9
Registered: October 2015
Location: indonesia

Junior Member

for create job , below the detail mesage

SQL> DECLARE
2 X NUMBER;
3 BEGIN
4 DBMS_JOB.SUBMIT
5 (
6 job => X
7 ,what => 'DECLARE
8 I_DATE VARCHAR2(8);
9
10 BEGIN
11 SELECT TO_CHAR(SYSDATE,''YYYYMMDD'') INTO I_DATE FROM DUAL;
12 EXEC PROC_DELTA(I_DATE);
13 END ;
14
15 '
16 ,next_date => to_date('10-19-2015 08:39:33','mm/dd/yyyy hh24:mi:ss')
17 ,interval => 'SYSDATE + 23/24'
18 ,no_parse => FALSE
19 );
20 END;
21 /
DECLARE
*
ERROR at line 1:
ORA-06550: line 6, column 9:
PLS-00103: Encountered the symbol "PROC_DELTA" when expecting one of the
following:
:= . ( @ % ;
The symbol ":=" was substituted for "PROC_DELTA" to continue.
ORA-06512: at "SYS.DBMS_JOB", line 82
ORA-06512: at "SYS.DBMS_JOB", line 140
ORA-06512: at line 4

Report message to a moderator

Re: user schema can't create or alter job (merged 3) [message #643806 is a reply to message #643804]

Mon, 19 October 2015 02:18

Littlefoot
Messages: 21826
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Remove EXEC (line 12).

[Updated on: Mon, 19 October 2015 02:18]

Report message to a moderator

Re: user schema can't create or alter job (merged 3) [message #643809 is a reply to message #643806]

Mon, 19 October 2015 02:57

agung_prw
Messages: 9
Registered: October 2015
Location: indonesia

Junior Member

SQL>
SQL> DECLARE
2 X NUMBER;
3 BEGIN
4 DBMS_JOB.SUBMIT
5 (
6 job => X
7 ,what => 'DECLARE
8 I_DATE VARCHAR2(8);
9
10 BEGIN
11 SELECT TO_CHAR(SYSDATE,''YYYYMMDD'') INTO I_DATE FROM DUAL;
12 PROC_DELTA(I_DATE);
13 END ;
14
15 '
16 ,next_date => to_date('10-19-2015 08:39:33','mm/dd/yyyy hh24:mi:ss')
17 ,interval => 'SYSDATE + 23/24'
18 ,no_parse => FALSE
19 );
20 END;
21 /
DECLARE
*
ERROR at line 1:
ORA-06550: line 6, column 4:
PLS-00201: identifier 'PROC_DELTA' must be declared
ORA-06550: line 6, column 4:
PL/SQL: Statement ignored
ORA-06512: at "SYS.DBMS_JOB", line 82
ORA-06512: at "SYS.DBMS_JOB", line 140
ORA-06512: at line 4

SQL>

Report message to a moderator

Re: user schema can't create or alter job (merged 3) [message #643810 is a reply to message #643809]

Mon, 19 October 2015 03:01

Michel Cadot
Messages: 68776
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Please read How to use [code] tags and make your code easier to read.

Report message to a moderator

Re: user schema can't create or alter job (merged 3) [message #643814 is a reply to message #643759]

Mon, 19 October 2015 03:19

gazzag
Messages: 1119
Registered: November 2010
Location: Bedwas, UK

Senior Member

Quote:

SQL> SELECT owner, object_type
2 FROM dba_objects
3 WHERE object_name='SYS';
FROM dba_objects
*
ERROR at line 2:
ORA-00942: table or view does not exist

The user that you're logging in as does not have the required privilege on the DBA_OBJECTS view.

Report message to a moderator

Previous Topic:	Need few clarification on Alter table.
Next Topic:	Filling values

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Jul 15 11:14:23 CDT 2026