How to describe an ORACLE package without using the DESCRIBE clause?

I am learning how to describe TABLES and VIEWS without using "DESCRIBE table / view"; paragraph. I found that this can be solved using something like:

SELECT column_name, data_type, data_length, data_precision, data_scale, nullable
FROM all_tab_columns
WHERE table_name = 'TABLE'

      

Which is perfectly programmable in a computer language like PHP, but I haven't found a workaround for the DESCRIBE package_name suggestion.

I have the following idea (still in SQL * PLUS with a coil):

DECLARE
  x VARCHAR2(50);
  CURSOR cursorPaquetes IS
    SELECT DISTINCT OBJECT_NAME
    FROM ALL_OBJECTS
    WHERE OBJECT_TYPE = 'PACKAGE' AND OWNER = 'SATURN';
BEGIN
  FOR item IN cursorPaquetes LOOP
    x := 'DESCRIBE '||item.OBJECT_NAME||';';
    EXECUTE IMMEDIATE x;
  END LOOP;
END;

      

But it didn't work :(. Throws the following error:

ORA-00900: sentencia SQL no válida ORA-06512: en línea 12

And when change the variable "x" like this:

x := 'BEGIN DESCRIBE '||item.OBJECT_NAME||'; END;';

      

I am getting the following error:

ERROR en línea 1: ORA-06550: línea 1, columna 16: PLS-00103: Encountered symbol "BVGKFDCS" expecting one of the following: equal sign (@%; Symbol ": =" replaced with "BVGKFDCS" to Continue. ORA -06512: en línea 12

Where "BVGKFDCS" is the name of one of the packages in my schema.

Thanks in advance.

PD I want to get something like this (but for each package):

DESCRIBE BVGKFDCS;

PROCEDURE P_GETPROFESSORS
 Nombre de Argumento            Tipo                    E/S   ¿Por Defecto?
 ------------------------------ ----------------------- ------ --------
 P_CAMP_IN                      VARCHAR2                IN
 P_COLL_IN                      VARCHAR2                IN
 P_DEPT_IN                      VARCHAR2                IN
 P_TERM_IN                      VARCHAR2                IN
PROCEDURE P_GETTERMSBYPROF
 Nombre de Argumento            Tipo                    E/S   ¿Por Defecto?
 ------------------------------ ----------------------- ------ --------
 NAME_ARRAY                     TABLE OF VARCHAR2(32000) IN
 VALUE_ARRAY                    TABLE OF VARCHAR2(32000) IN
PROCEDURE P_INIT
 Nombre de Argumento            Tipo                    E/S   ¿Por Defecto?
 ------------------------------ ----------------------- ------ --------
 PIDM                           VARCHAR2                IN
PROCEDURE P_OBTENER_VALOR
 Nombre de Argumento            Tipo                    E/S   ¿Por Defecto?
 ------------------------------ ----------------------- ------ --------

      

Note that this output is different from:

select * from user_source

      

+3


source to share


4 answers


Take a look at USER_PROCEDURES

. I suspect this is what DESCRIBE offers when you use it in a package, procedure, or function.

EDIT: Sorry, also include USER_ARGUMENTS

to list the parameters for each subroutine. The logic of combining the two views is not immediately obvious due to the way different types of objects are handled. This query looks like it gives reasonable results, but I haven't tested the results:



SELECT * FROM all_arguments A
JOIN all_procedures p
  ON ( ( p.object_type = 'PACKAGE' AND A.package_name = p.object_name AND A.object_name = p.procedure_name)
       OR
       ( p.object_type <> 'PACKAGE' AND A.package_name IS NULL AND A.object_name = p.object_name )
    )
  AND p.owner = A.owner

      

+4


source


You need to use User_Source, DBA_SOURCE or All_Source. This is where all the code is stored.

select text 
from user_source
where name = 'procedure_name' 
order by line;

      



Object type: FUNCTION, JAVA SOURCE, PACKAGE , BODY BODY , PROCEDURE, TRIGGER, TYPE, BODY TYPE

+2


source


describe

does not work internally execute immediate

because it is a SQL * Plus command and not part of a SQL or PL / SQL command. It is interpreted by the client, not the server.

The best way to get the source for any database object (including packages and package bodies) is DBA_SOURCE

or USER_SOURCE

as @TS suggests.

There is dictionary data for each type of object in the database. When trying to get similar data for other types of objects, you should reference a view dictionary

that helps catalog the data dictionary. For example, an entry dictionary

for USER_SOURCE

:

TABLE_NAME  | COMMENTS
------------+------------------------------------------------
USER_SOURCE | Source of stored objects accessible to the user

      


Procedure names are part of the package specification. A request all_source

with the package name and type='package'

will return a BOM including all public procedures and their parameters.

0


source


does it work for you?

SQL> SELECT DBMS_METADATA.GET_DDL('PACKAGE','EMP_PKG','SCOTT') FROM DUAL;

SQL> SELECT DBMS_METADATA.GET_DDL('PACKAGE_BODY','EMP_PKG','SCOTT') FROM DUAL;

SQL> show user
USER is "SYSTEM"
SQL>
SQL> l
  1* SELECT DBMS_METADATA.GET_DDL('PROCEDURE','P1','HR') FROM DUAL
SQL> /

DBMS_METADATA.GET_DDL('PROCEDURE','P1','HR')
-----------------------------------------------------------------------------

  CREATE OR REPLACE PROCEDURE "HR"."P1" ( p_id int) as
begin
   dbms_output.put

SQL>

      

-1


source







All Articles