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

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$來更新掉

oracle底層字典表obj$及source$與儲存過程procedure系列五

相關文章