Can some one please tell me how to see the text or code of procedure.
assume that i have procedure calculate sal.


I want to view the code of that procedure for modification..
Thanks,
san

The code is in USER_SOURCE if you created the procedure, or ALL_SOURCE if someone else did (but then you may not be able to read it).

SELECT text
FROM user_source
WHERE name = 'MYPROC'
and type = 'PROCEDURE'
ORDER BY line;

In Oracle 9i you can use DBMS_METADATA package. It is making DDL extraction a snap even for tables DDL. An example is:

set pagesize 0
set long 90000

execute DBMS_METADATA.SET_TRANSFORM_PARAM(
DBMS_METADATA.SESSION_TRANSFORM,'STORAGE',false);

SELECT DBMS_METADATA.GET_DDL('TABLE',u.table_name)
FROM USER_ALL_TABLES u
WHERE u.nested='NO'
AND (u.iot_type is null or u.iot_type='IOT');

execute DBMS_METADATA.SET_TRANSFORM_PARAM(
DBMS_METADATA.SESSION_TRANSFORM,'DEFAULT');

It will get you the DDL for all "complete" tables in the current schema, filtering out nested tables and overflow segments.

For procedure body use 'PROCEDURE_BODY' instead 'TABLE' or 'PROCEDURE_SPEC' for procedure bodies.


Hope that helps,

clio_usa
OCP - DBA

I notice that I can't use the USER_SOURCE view to view my View or Trigger code? Can you tell me what data dictionary tables and/or views I need to get at this information?

thanks in advance

You find the info you want in user_triggers and user_views.

Incidentally for a list of other "user_" type views you can execute

select * from all_objects where object_name like 'USER_%'

HTH

Excellent!

Thanks for the prompt response Bill!