CODEWITHSUNDEEP
oracle-databaseoracle11gsqlplusdescribe
akolmogorov
akolmogorov

Reputation: 33

How to describe an ORACLE package without using DESCRIBE clause?

I've been researching for how to describe TABLES and VIEWS without using 'DESCRIBE table/view;' clause. I found that it could 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 didn't found a workaround for 'DESCRIBE package_name' clause.

I have the following idea (yet in SQL*PLUS with a spool):

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 modify the 'x' variable like this:

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

I get the following error:

ERROR en línea 1: ORA-06550: línea 1, columna 16: PLS-00103: Encountered the symbol "BVGKFDCS" when expecting one of the following: := . ( @ % ; The symbol ":=" was substituted for "BVGKFDCS" to continue. ORA-06512: en línea 12

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

Thanks in advance.

P.D. I want to get something like that (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

Upvotes: 3

Views: 9216

Answers (4)

Sendi_t
Sendi_t

Reputation: 637

does this 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>

Upvotes: -1

Allan
Allan

Reputation: 17429

describe doesn't work within execute immediate because it is a SQL*Plus command, rather than part of either a SQL or PL/SQL command. It is interpreted by the client, rather than 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 suggested by @T.S..

There are data dictionary views for every kind of object in the database. When trying to get similar kinds of data for other object types, you should reference the dictionary view, which helpfully catalogs the data dictionary. As an example, the dictionary entry for user_source is:

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

The procedure names are part of the package specification. Querying all_source with the package name and type='package' will return the specification, including all of the public procedures and their parameters.

Upvotes: 0

Dave Costa
Dave Costa

Reputation: 48121

Take a look at USER_PROCEDURES. I suspect this is what DESCRIBE queries when you use it on a package, procedure, or function.

EDIT: Sorry, also incorporate USER_ARGUMENTS to list the parameters for each subprogram. The logic to join the two views is not immediately obvious because of the ways different object types are handled. This query looks like it produces reasonable results but I have not checked the results carefully:

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

Upvotes: 5

T.S.
T.S.

Reputation: 19350

You need to use User_Source, DBA_SOURCE or All_Source. All code is stored there.

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

Type of object: FUNCTION, JAVA SOURCE, PACKAGE, PACKAGE BODY, PROCEDURE, TRIGGER, TYPE, TYPE BODY

Upvotes: 2

Related Questions