0

i have written a test job like

begin
DBMS_SCHEDULER.CREATE_JOB
(
job_name => 'test_job'
,job_type =>'plsql_block'
,job_action => 'null'
,enabled => true
);
end;
/

after a while when the check the status

select * from user_scheduler_job_run_details where job_name='TEST_JOB';

the status is failed ,i see these two lines

ORA-20001: You are not allowed to logon from this terminal!!! ORA-06512: at line 39

how can i resolve this error. and how to find the job id of a scheduled job(created using dbms_scheduler)

7
  • Try logging in from a different terminal, and see here for Error 06512
    – eyoung100
    May 29 '14 at 14:27
  • there is no line 39 in my code.i have only those 10 lines
    – user37143
    May 29 '14 at 15:02
  • Look in Line 39 of the Scheduler, but before you do that fix your login problem...
    – eyoung100
    May 29 '14 at 15:12
  • I don't know if that is the reason for the error but the manual says the job_action value must end with a semicolon
    – miracle173
    May 30 '14 at 3:38
  • @E Carter Young: How should he fix the "login problem"? The user should analyze the Oracle source code? useless advice
    – miracle173
    May 30 '14 at 3:57
-1

Your question contain 2 items that must have been looked at before question itself arose:

  1. Have you looked at API of DBMS_SCHEDULER (any programmer should do that before knowing how to use built-in [package]) ; another words have you RTFM?
  2. Oracle (most of the time) is in-fact very good with "responses" - error signaling back to "client". Have you googled ora - 20001 ?

To your question:

  • Your job action is NULL; you need to have some procedure that does something AND also is not named as Oracle keyword or PLSQl block that must have at bare minimum begin and end - docs

  • Oracle error 20001 is custom error and if you want to see the code you need to contact DBA's.

  • Based on the message , seems that your schema also missing some grants (you obviously logged in to the database , so message itself is deceiving , but the exact error can only be identified by going thru the code or checking with DBA's directly - see above )
  • why do you need job id? your job name is providing your with information already! check API's!!
4
  • -1 the value of job_action must not be enlosed by begin ... end; Read the manual . Apperently the Line does not reference to the users code but the dbms_scheduler package code. I can't see how your answer could help the OP.
    – miracle173
    May 30 '14 at 3:33
  • Value for the job_action when job_type =>'plsql_block' must be VALID plsql block; which is null is not! if author wanted to go with anonymous plsql block then correct form is begin null; end;. Before an error can be analyzed job submission must be properly set! if it compiles , doesn't mean it runs. I've read the manual - have you? May 30 '14 at 13:51
  • No, that is not true. "For example, my_proc(); or BEGIN my_proc(); END;" and " Note that the Scheduler wraps job_action in its own block and passes the following to PL/SQL for execution: DECLARE ... BEGIN job_action END;" says the manual. null; is a valid job_action if job_type='PLSQL_BLOCK'
    – miracle173
    May 30 '14 at 15:23
  • indeed null; not null !!! do you see ; in original code? yes - my original statement instead mentioning must should've said should; but regardless of the way it was articulated there is an error in code, that must be addressed before one proceed to analyze the run-time error May 30 '14 at 15:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.