Oracle 的 dbms_metadata.get_ddl for object_type JOB

发布于 2024-09-09 09:01:56 字数 1054 浏览 1 评论 0原文

我想为大多数数据库对象创建 ddl 脚本。 dbms_metadata.get_ddl 适用于大多数对象类型。例如，以下内容为视图创建 ddl：

select dbms_metadata.get_ddl ( 'VIEW', 'SAMPLE_VIEW') from dual

另一方面，它不适用于 object_type 'JOB'。以下：

select dbms_metadata.get_ddl( 'JOB', 'SAMPLE_JOB' ) from dual

给出以下错误：

ORA-31604: invalid NAME parameter "NAME" for object type JOB in function SET_FILTER
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 116
ORA-06512: at "SYS.DBMS_METADATA_INT", line 4705
ORA-06512: at "SYS.DBMS_METADATA_INT", line 8582
ORA-06512: at "SYS.DBMS_METADATA", line 2882
ORA-06512: at "SYS.DBMS_METADATA", line 2748
ORA-06512: at "SYS.DBMS_METADATA", line 4333
ORA-06512: at line 1

如果我使用

select * from user_objects where object_type='JOB'

它列出我的作业，它会显示 SAMPLE_JOB （就像它在筛选 object_type='VIEW' 时显示 SAMPLE_VIEW 一样）。

为什么它适用于 VIEW（以及 TABLE、INDEX、TRIGGER...）而不适用于 JOB？

我正在使用Oracle 10g。

原文

I'd like to create ddl scripts for most of my database objects. dbms_metadata.get_ddl works for most of the object types. For instance the following creates the ddl for a view:

select dbms_metadata.get_ddl ( 'VIEW', 'SAMPLE_VIEW') from dual

On the other hand it's not working for object_type 'JOB'. The following:

select dbms_metadata.get_ddl( 'JOB', 'SAMPLE_JOB' ) from dual

gives the following error:

ORA-31604: invalid NAME parameter "NAME" for object type JOB in function SET_FILTER
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 116
ORA-06512: at "SYS.DBMS_METADATA_INT", line 4705
ORA-06512: at "SYS.DBMS_METADATA_INT", line 8582
ORA-06512: at "SYS.DBMS_METADATA", line 2882
ORA-06512: at "SYS.DBMS_METADATA", line 2748
ORA-06512: at "SYS.DBMS_METADATA", line 4333
ORA-06512: at line 1

If I list my jobs using

select * from user_objects where object_type='JOB'

it shows SAMPLE_JOB (just like it shows SAMPLE_VIEW if filtered for object_type='VIEW').

Why is it working for VIEW (and TABLE, INDEX, TRIGGER, ...) and not for JOB?

I'm using Oracle 10g.

分享到QQ

分享到微博

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

发布评论

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

咆哮 2024-09-16 09:01:56

select dbms_metadata.get_ddl('PROCOBJ', 'yourJobNameGoesHere') from dual;

PROCOBJ 是过程对象。

select dbms_metadata.get_ddl('PROCOBJ', 'yourJobNameGoesHere') from dual;

PROCOBJ's are procedural objects.

回复收藏 0 原文

岁月无声 2024-09-16 09:01:56

select dbms_metadata.get_ddl('PROCOBJ',['JOB'|'PROGRAM'|'SCHEDULE'],'OWNER') from dual;

PROCOBJ 可以是 JOB、PROGRAM 和 SCHEDULE。

select dbms_metadata.get_ddl('PROCOBJ',['JOB'|'PROGRAM'|'SCHEDULE'],'OWNER') from dual;

The PROCOBJ can be JOB, PROGRAM and SCHEDULE.

回复收藏 0 原文

孤独患者 2024-09-16 09:01:56

或者，使用 DDL 从数据库获取所有作业：

select owner, job_name, dbms_metadata.get_ddl('PROCOBJ', job_name, owner) as ddl_output from ALL_SCHEDULER_JOBS

Alternative, get all jobs from the database with their DDL:

select owner, job_name, dbms_metadata.get_ddl('PROCOBJ', job_name, owner) as ddl_output from ALL_SCHEDULER_JOBS

回复收藏 0 原文

疯狂的代价 2024-09-16 09:01:56

即使我尝试了上述所有方法来在 Oracle 版本 10g 中获取 DDL，但没有成功。

以下是我想了解的工作细节：

set pages 200 lines 200
col owner format a20
col job_name format a25
col JOB_ACTION format a75
col COMMENTS format a60  

select owner, job_name, next_run_date, state, enabled from dba_scheduler_jobs where job_name like '%AUDIT%'; 
-- get the detail of scheduled jobs.

select OWNER,JOB_NAME, JOB_ACTION, COMMENTS FROM DBA_SCHEDULER_JOBS where JOB_NAME='PURGE_AUDIT_LOG';
-- get the limited detail from the selected column.
    
select * FROM DBA_SCHEDULER_JOBS where JOB_NAME='PURGE_AUDIT_LOG';
-- to get the complete detail of a specific job along with code and other details.

Even I tried all above to get DDL in Oracle version 10g, but no success.

Here is what I figure out to get the detail of the job:

set pages 200 lines 200
col owner format a20
col job_name format a25
col JOB_ACTION format a75
col COMMENTS format a60  

select owner, job_name, next_run_date, state, enabled from dba_scheduler_jobs where job_name like '%AUDIT%'; 
-- get the detail of scheduled jobs.

select OWNER,JOB_NAME, JOB_ACTION, COMMENTS FROM DBA_SCHEDULER_JOBS where JOB_NAME='PURGE_AUDIT_LOG';
-- get the limited detail from the selected column.
    
select * FROM DBA_SCHEDULER_JOBS where JOB_NAME='PURGE_AUDIT_LOG';
-- to get the complete detail of a specific job along with code and other details.

回复收藏 0 原文

~没有更多了~