Procedures and Functions in Packages

DBA Geral (instalação, configuração do banco, scripts do Sql*Plus, shell scripts, batch scripts, etc...)
  

Poston Thu, 21 Jul 2005 1:50 pm

Good afternoon guys, I wonder if there is any way I can find through selects, which procedures and functions a package contains.
Someone would tell me if it is possible?


Thank you Rafael Mascarello
Zida
Location: Toledo - PR

Poston Thu, 21 Jul 2005 2:29 pm

I found something that works.

Code: 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'
Zida
Location: Toledo - PR

Poston Thu, 21 Jul 2005 2:57 pm

I found the following, which only lists which procedures and functions that a package has:
Code: 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>
dr_gori
Location: Portland, OR USA

Thomas F. G

Você já respondeu a dúvida de alguém hoje?
http://glufke.net/oracle/search.php?search_id=unanswered

Poston Thu, 21 Jul 2005 3:22 pm

in this case would have no way of knowing if the returned objects are function or package?
DriwLL
Location: Toledo - PR

----------------------------
DriwLL
Programador Junior
Oracle/Forms

Poston Thu, 21 Jul 2005 4:28 pm

Oops, jewel, thank you very much for the cooperation of you:) I really appreciate even ... and answering the last question, Yes, need to know what type, whether it is procedure or function.



Once again, thank you very much for your help guys
Zida
Location: Toledo - PR

Poston Thu, 21 Jul 2005 6:01 pm

I think that gives a good help.


Code: 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:
DriwLL
Location: Toledo - PR

----------------------------
DriwLL
Programador Junior
Oracle/Forms

Poston Thu, 21 Jul 2005 6:11 pm

not bad ...:-
dr_gori
Location: Portland, OR USA

Thomas F. G

Você já respondeu a dúvida de alguém hoje?
http://glufke.net/oracle/search.php?search_id=unanswered



Return to DBA General

Who is online

Users browsing this forum: No registered users and 3 guests