从系统中运行DBMS_SCHEDULER.CREATE_JOB，以获取由另一个模式[Oracle SQL]拥有的存储过程

发布于 2025-02-13 19:18:40 字数 1001 浏览 0 评论 0 原文

我想计划运行以执行存储过程，该过程属于SYS用户时属于模式架构。

存储的过程程序将带有输入参数vara。我运行调度程序时遇到了一些麻烦。

BEGIN
    DBMS_SCHEDULER.create_job(
        job_name          => 'test1',
        job_type          => 'STORED_PROCEDURE',
        job_action        => 'schemaA.procedureA(''varA''); ',
        start_date        => SYSTIMESTAMP,
        repeat_interval   => 'FREQ=minutely;BYMINUTE=0,10,20,30,40,50;BYSECOND=0',
        enabled           => TRUE,
        comments          => 'Your description of your job'
    );

END;

我有错误：

Error report -
ORA-27452: "schemaA.procedureA('varA'); " is an invalid name for a database object.
ORA-06512: at "SYS.DBMS_ISCHED", line 175
ORA-06512: at "SYS.DBMS_SCHEDULER", line 286
ORA-06512: at line 3
27452. 00000 -  "\"%s\" is an invalid name for a database object."
*Cause:    An invalid name was used to identify a database object.
*Action:   Reissue the command using a valid name.

想知道我应该如何解决这个问题

原文

I want to schedule runs to execute a stored procedure which belonged to a schema schemaA when logging in as SYS user.

The stored procedure procedureA takes in an input parameter varA. I was having some trouble running the scheduler.

BEGIN
    DBMS_SCHEDULER.create_job(
        job_name          => 'test1',
        job_type          => 'STORED_PROCEDURE',
        job_action        => 'schemaA.procedureA(''varA''); ',
        start_date        => SYSTIMESTAMP,
        repeat_interval   => 'FREQ=minutely;BYMINUTE=0,10,20,30,40,50;BYSECOND=0',
        enabled           => TRUE,
        comments          => 'Your description of your job'
    );

END;

I got error:

Error report -
ORA-27452: "schemaA.procedureA('varA'); " is an invalid name for a database object.
ORA-06512: at "SYS.DBMS_ISCHED", line 175
ORA-06512: at "SYS.DBMS_SCHEDULER", line 286
ORA-06512: at line 3
27452. 00000 -  "\"%s\" is an invalid name for a database object."
*Cause:    An invalid name was used to identify a database object.
*Action:   Reissue the command using a valid name.

Wondering how should I fix this

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

真心难拥有 2025-02-20 19:18:40

您无法在 Action 参数中指定输入，仅在过程的名称中指定输入。使用 number_of_arguments 和参数参数来指定输入。

You can't specify inputs in the ACTION parameter, only the name of the procedure. Use the NUMBER_OF_ARGUMENTS and ARGUMENTS parameters to specify the inputs.

See the documentation here: https://docs.oracle.com/en/database/oracle/oracle-database/19/arpls/DBMS_SCHEDULER.html#GUID-1BC57390-C756-4908-A4D8-8D1EEC236E25

See an example here: Creating a dbms_scheduler.create_job with arguments

dbms_scheduler.create_job (
  job_name=>'script_dbms_scheduler_test',
  job_action=>'/data/home/workflow/script_dbms_scheduler.ksh',
  job_type=>'executable',
  number_of_arguments=>1,  
  auto_drop => TRUE,
  comments=> 'Run shell-script script_dbms_scheduler.ksh');
dbms_scheduler.set_job_argument_value (
  job_name =>'script_dbms_scheduler_test', 
  argument_position => 1, 
  argument_value => v_text);
dbms_scheduler.enable('script_dbms_scheduler_test');

回复收藏 0 原文

~没有更多了~