在DBA_PROCEDURES视图中找不到过程 [英] Unable to find procedure in DBA_PROCEDURES view

问题描述

我创建了一个过程:

create or replace procedure gg as
begin
insert into book values ('prashant','prashant','prashant');
commit;
end;
/

过程已成功创建.现在，我想检查相应过程的程序包名称，但我无法执行此操作.

Procedure has been created successfully.Now i want to check the package name for the corresponding procedure but i am not able to do so.

我正在使用以下查询:

 > SELECT *
        FROM SYS.DBA_PROCEDURES
        WHERE procedure_name ='gg';

它选择了0行.请帮忙.

Its giving 0 rows selected.Please help.

推荐答案

注意.请查看更新"部分以获取正确答案.

NOTE Please look at the UPDATE section for correct answer.

在DBA_PROCEDURES视图中，过程名称不能小写.使用大写字母或应用大写功能.

The procedure name cannot be in lower case in the DBA_PROCEDURES view. Use upper case, or apply UPPER function.

SELECT *
  FROM SYS.DBA_PROCEDURES
 WHERE procedure_name ='GG';

更新

唯一可以使用小写形式命名的情况是，在编译时将其用双引号引起来.

The only case when you could have the name in lower case is if you enclose it within double-quotation marks while compiling.

例如，

SQL> CREATE OR REPLACE
  2  PROCEDURE "p"
  3  AS
  4  BEGIN
  5    NULL;
  6  END;
  7  /

Procedure created.

SQL> SELECT object_name, procedure_name, object_type FROM user_procedures where procedure_name='p';

no rows selected

SQL>

但是上面的视图仍然不会为 PROCEDURE_NAME 返回任何结果.

But still the above view will not return any result for PROCEDURE_NAME.

原因

PROCEDURE_NAME 列将仅包含 PACKAGE 包中的过程的过程名称.对于单独程序，您需要使用 OBJECT_NAME .

PROCEDURE_NAME column will only have the procedure name for the procedures which are part of a PACKAGE. For STAND ALONE PROCEDURES you need to use OBJECT_NAME.

SQL> -- stand alone procedure in lower case
SQL> CREATE OR REPLACE
  2  PROCEDURE "p"
  3  AS
  4  BEGIN
  5    NULL;
  6  END;
  7  /

Procedure created.

SQL>
SQL>  -- package
SQL> CREATE OR REPLACE
  2  PACKAGE test_p
  3  IS
  4    PROCEDURE p;
  5  END test_p;
  6  /

Package created.

SQL>
SQL> -- package body with a procedure
SQL> CREATE OR REPLACE
  2  PACKAGE BODY test_p
  3  IS
  4  PROCEDURE p
  5  IS
  6  BEGIN
  7    NULL;
  8  END;
  9  END test_p;
 10  /

Package body created.

SQL>
SQL> SELECT object_name, procedure_name, object_type FROM user_procedures;

OBJECT_NAME     PROCEDURE_NAME  OBJECT_TYPE
--------------- --------------- ---------------
TEST_P          P               PACKAGE
p                               PROCEDURE
TEST_P                          PACKAGE

SQL>

因此，如您所见，procedure_name仅具有package's procedure，但是stand-alone procedure仅在object_name下列出.

So, as you can see, the procedure_name is only having the package's procedure, however the stand-alone procedure is only listed under object_name.

这篇关于在DBA_PROCEDURES视图中找不到过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！