Which has the higher priority in your organization: Deploying a new database or securing the ones you already have?
Looking for a website, and resources, dedicated solely to securing Oracle databases? Check out DBSecWorx.
Purpose
Used to get information about a PL/SQL object. When you specify an object name, DBMS_DESCRIBE returns a set of indexed tables with the results. Full name translation is performed and security checking is performed on the final object.
AUTHID
DEFINER
Data Types
TYPE VRCHAR2_TABLE IS TABLE OF VARCHAR2(30)
INDEX BY BINARY_INTEGER;
TYPE NUMBER_TABLE IS TABLE OF NUMBER
INDEX BY BINARY_INTEGER;
ORU 10035: cannot describe a package ('X') only a procedure within a package
ORA-20001
ORU-10032: procedure 'X' within package 'Y' does not exist
ORA-20002
ORU-10033: object 'X' is remote, cannot describe; expanded name 'Y'
ORA-20003
ORU-10036: object 'X' is invalid and cannot be described
ORA-20004
Syntax error attempting to parse 'X'
First Available
7.3.4
Security Model
Owned by SYS with EXECUTE granted to PUBLIC
Source
{ORACLE_HOME}/rdbms/admin/dbmsdesc.sql
DESCRIBE_PROCEDURE
Takes the name of a stored procedure and returns parameter metadata
dbms_describe.describe_procedure (
object_name IN VARCHAR2,
reserved1 IN VARCHAR2,
reserved2 IN VARCHAR2,
overload OUT NUMBER_TABLE,
position OUT NUMBER_TABLE,
level OUT NUMBER_TABLE,
argument_name OUT VARCHAR2_TABLE,
datatype OUT NUMBER_TABLE,
default_value OUT NUMBER_TABLE,
in_out OUT NUMBER_TABLE,
length OUT NUMBER_TABLE,
precision OUT NUMBER_TABLE,
scale OUT NUMBER_TABLE,
radix OUT NUMBER_TABLE,
spare OUT NUMBER_TABLE
include_string_constraints OUT BOOLEAN := FALSE);
CREATE OR REPLACE PROCEDURE testproc(tn_in INTEGER, td_out OUT VARCHAR2) IS
BEGIN
SELECT TO_CHAR(nc.type_num) || ' ' || nc.type_desc
INTO td_out
FROM numtype_codes nc
WHERE nc.type_num = tn_in;
END testproc;
/
set serveroutput on
DECLARE
over dbms_describe.number_table;
posn dbms_describe.number_table;
levl dbms_describe.number_table;
arg dbms_describe.varchar2_table;
dtyp dbms_describe.number_table;
defv dbms_describe.number_table;
inout dbms_describe.number_table;
len dbms_describe.number_table;
prec dbms_describe.number_table;
scal dbms_describe.number_table;
n dbms_describe.number_table;
dtdesc numtype_codes.type_desc%TYPE;
iodesc VARCHAR2(6);
BEGIN
dbms_describe.describe_procedure('TESTPROC', NULL, NULL,
over, posn, levl, arg, dtyp, defv, inout, len, prec, scal, n, n);
FOR i IN 1..over.COUNT LOOP
SELECT nc.type_desc
INTO dtdesc
FROM numtype_codes nc
WHERE nc.type_num = dtyp(i);
SELECT DECODE(inout(i), 0, 'IN', 1, 'IN-OUT', 'OUT')
INTO iodesc
FROM dual;