Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
Connected as scott
SQL> create or replace procedure proc_non_sep
2 as
3 begin
4 null;
5 end;
6 /
Procedure created
SQL> create or replace procedure 'proc_non_sep'
2 as
3 begin
4 null;
5 end;
6 /
SQL> create or replace procedure "proc_non_sep"
2 as
3 begin
4 null;
5 end;
6 /
Procedure created
SQL> create or replace procedure "proc_NON_SEP"
2 as
3 begin
4 null;
5 end;
6 /
Procedure created
SQL> select * from user_objects uo where uo.OBJECT_NAME like '%PROC_NON%';
OBJECT_NAME SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE CREATED LAST_DDL_TIME TIMESTAMP STATUS TEMPORARY GENERATED SECONDARY NAMESPACE EDITION_NAME
-------------------------------------------------------------------------------- ------------------------------ ---------- -------------- ------------------- ----------- ------------- ------------------- ------- --------- --------- --------- ---------- ------------------------------
PROC_NON_SEP 132741 PROCEDURE 2012/11/16 2012/11/16 19 2012-11-16:19:13:17 VALID N N N 1
SQL> select * from user_objects uo where uo.OBJECT_NAME like '%proc%';
OBJECT_NAME SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE CREATED LAST_DDL_TIME TIMESTAMP STATUS TEMPORARY GENERATED SECONDARY NAMESPACE EDITION_NAME
-------------------------------------------------------------------------------- ------------------------------ ---------- -------------- ------------------- ----------- ------------- ------------------- ------- --------- --------- --------- ---------- ------------------------------
proc_NON_SEP 132743 PROCEDURE 2012/11/16 2012/11/16 19 2012-11-16:19:14:53 VALID N N N 1
proc_non_sep 132742 PROCEDURE 2012/11/16 2012/11/16 19 2012-11-16:19:14:33 VALID N N N 1
proc_obj_new 132740 PROCEDURE 2012/11/16 2012/11/16 19 2012-11-16:19:05:16 VALID N N N 1
SQL> create or replace procedure proc_test_single
2 as
3 begin
4 null;
5 end;
6 /
Procedure created
SQL> create or replace procedure 'proc_test_single_1'
2 as
3 begin
4 null;
5 end;
6 /
SQL> select length(object_name) from user_objects uo where uo.OBJECT_NAME like '%proc%';
LENGTH(OBJECT_NAME)
-------------------
12
12
12
SQL> select uo.OBJECT_ID from user_objects uo where uo.OBJECT_NAME like '%proc%';
OBJECT_ID
----------
132743
132742
132740
SQL> conn sys/system@orcl as sysdba
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
Connected as SYS
SQL> select * from obj$ ob where ob.obj# in
OBJ# DATAOBJ# OWNER# NAME NAMESPACE SUBNAME TYPE# CTIME MTIME STIME STATUS REMOTEOWNER LINKNAME FLAGS OID$ SPARE1 SPARE2 SPARE3 SPARE4 SPARE5 SPARE6
---------- ---------- ---------- ------------------------------ ---------- ------------------------------ ---------- ----------- ----------- ----------- ---------- ------------------------------ -------------------------------------------------------------------------------- ---------- -------------------------------- ---------- ---------- ---------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -----------
132740 84 proc_obj_new 1 7 2012/11/16 2012/11/16 2012/11/16 1 0 6 65535 84
132742 84 proc_non_sep 1 7 2012/11/16 2012/11/16 2012/11/16 1 0 6 65535 84
132743 84 proc_NON_SEP 1 7 2012/11/16 2012/11/16 2012/11/16 1 0 6 65535 84
SQL> select * from source$ sr where sr.obj# in (132743,132742,132740) order by sr.obj#,sr.line;
OBJ# LINE SOURCE
---------- ---------- --------------------------------------------------------------------------------
132740 1 procedure proc_obj_new
132740 2 as
132740 3 begin
132740 4 null;
132740 5 end;
132740 6
132742 1 procedure "proc_non_sep"
132742 2 as
132742 3 begin
132742 4 null;
132742 5 end;
132742 6
132743 1 procedure "proc_NON_SEP"
132743 2 as
132743 3 begin
132743 4 null;
132743 5 end;
132743 6
18 rows selected
SQL> update source$ set source=replace(source,'"','') where obj# in (132743,132742,132740) and line=1;
3 rows updated
SQL> commit;
Commit complete
SQL> select * from source$ sr where sr.obj# in (132743,132742,132740) order by sr.obj#,sr.line;
OBJ# LINE SOURCE
---------- ---------- --------------------------------------------------------------------------------
132740 1 procedure proc_obj_new
132740 2 as
132740 3 begin
132740 4 null;
132740 5 end;
132740 6
132742 1 procedure proc_non_sep
132742 2 as
132742 3 begin
132742 4 null;
132742 5 end;
132742 6
132743 1 procedure proc_NON_SEP
132743 2 as
132743 3 begin
132743 4 null;
132743 5 end;
132743 6
18 rows selected
SQL> conn scott/system@orcl
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
Connected as scott
SQL> select * from user_objects uo where uo.OBJECT_NAME like '%proc%';
OBJECT_NAME SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE CREATED LAST_DDL_TIME TIMESTAMP STATUS TEMPORARY GENERATED SECONDARY NAMESPACE EDITION_NAME
-------------------------------------------------------------------------------- ------------------------------ ---------- -------------- ------------------- ----------- ------------- ------------------- ------- --------- --------- --------- ---------- ------------------------------
proc_obj_new 132740 PROCEDURE 2012/11/16 2012/11/16 19 2012-11-16:19:05:16 VALID N N N 1
proc_non_sep 132742 PROCEDURE 2012/11/16 2012/11/16 19 2012-11-16:19:14:33 VALID N N N 1
proc_NON_SEP 132743 PROCEDURE 2012/11/16 2012/11/16 19 2012-11-16:19:14:53 VALID N N N 1
SQL>
小结:加双引号的存储过程名称可以用source$和obj$来更新掉
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/9240380/viewspace-749383/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/9240380/viewspace-749383/