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
source to share
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
source to share
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.
source to share
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>
source to share