Procedures and Functions in Packages

[Zida]

Good afternoon guys,
I wonder if there is any way I find through Selects, what procedures and functions a package contains.
Somebody know how to tell me if it's possible?


Thank you

Rafael Mascarello

[Zida]

I found something that works ...

Select all

SELECT * 
FROM ( 
SELECT NAME NAME_PACKAGE, 
       SUBSTR(LTRIM(TEXT), 
              INSTR(LTRIM(TEXT),' ')+1, 
              NVL(INSTR(SUBSTR(LTRIM(TEXT),INSTR(LTRIM(TEXT),' ')+1,LENGTH(LTRIM(TEXT))),'('), LENGTH(LTRIM(TEXT)))-1 
              ) NM_PROCEDURE, 
        SUBSTR(LTRIM(TEXT),1,INSTR(LTRIM(TEXT),' ')-1) TYPE 
    FROM SYS.ALL_SOURCE 
   WHERE OWNER = 'MAXICON'  
     AND TYPE = 'PACKAGE BODY' 
     AND SUBSTR(LTRIM(TEXT),1,INSTR(LTRIM(TEXT),' ')-1) IN ('PROCEDURE','FUNCTION') 
) 
WHERE NAME_PACKAGE = 'NOME_DA_PACK'

[dr_gori]

I found the following, which only list which procedures and functions that the package has:

Select all

SQL> select PROCEDURE_NAME 
  2  from USER_PROCEDURES 
  3  WHERE OBJECT_NAME = 'PK_IMPORTA_EMBARQUES' 
  4  / 
 
PROCEDURE_NAME 
------------------------------ 
PC_APAGA_EMBARQUES 
PC_EXCLUI_ARQUIVO 
PC_GERA_EMBARQUES 
PC_GERA_INDIVIDUAL 
PC_GERA_TODOS_DINF 
PC_IMPORTA_ARQUIVOS 
PC_IMPORTA_INDIVIDUAL 
PC_LOG_IMPORTACAO 
 
8 rows selected. 
 
SQL>  

[DriwLL]

In that case, how would you know if the objects returned are FUNCTION or PACKAGE?

[Zida]

Oops, jewel, thank you very much for the collaboration of you
I am very grateful ...

and answering the last question, yes, would need to know what type, if it is procedure or function ..



Once again, thank you very much for the bone help

[DriwLL]

I think this already gives good help.

Select all

CREATE OR REPLACE VIEW VIEW_PACKAGE_CONTENTS AS 
SELECT *  
FROM ( 
       SELECT ALL_OBJECTS.OBJECT_NAME NAME_PACKAGE, 
              ALL_ARGUMENTS.OBJECT_NAME NAME_CONTENTS, 
              'FUNCTION' OBJECT_TYPE 
          FROM ALL_ARGUMENTS , ALL_OBJECTS 
         WHERE ALL_ARGUMENTS.OBJECT_ID = ALL_OBJECTS.OBJECT_ID 
           AND ALL_OBJECTS.OBJECT_TYPE = 'PACKAGE' 
           AND ALL_ARGUMENTS.ARGUMENT_NAME IS NULL 
      UNION  
       SELECT DISTINCT ALL_OBJECTS.OBJECT_NAME NAME_PACKAGE, 
              ALL_ARGUMENTS.OBJECT_NAME NAME_CONTENTS, 
              'PROCEDURE' OBJECT_TYPE 
          FROM ALL_ARGUMENTS , ALL_OBJECTS 
         WHERE ALL_ARGUMENTS.OBJECT_ID = ALL_OBJECTS.OBJECT_ID 
           AND ALL_OBJECTS.OBJECT_TYPE = 'PACKAGE' 
           AND ALL_ARGUMENTS.ARGUMENT_NAME IS NOT NULL 
           AND ALL_ARGUMENTS.OBJECT_NAME NOT IN ( SELECT DISTINCT A.OBJECT_NAME 
                                                  FROM ALL_ARGUMENTS A 
                                                 WHERE A.OBJECT_ID = ALL_OBJECTS.OBJECT_ID 
                                                   AND A.ARGUMENT_NAME IS NULL ) ) 
ORDER BY NAME_PACKAGE ,NAME_CONTENTS,OBJECT_TYPE

: LOL:: LOL:

[dr_gori]

Nothing bad ...: -O