Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2002
    Posts
    11

    Unanswered: how to see text of procedure

    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

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: how to see text of procedure

    Originally posted by nsandya
    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;

  3. #3
    Join Date
    Apr 2002
    Location
    California, USA
    Posts
    482

    Cool

    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

  4. #4
    Join Date
    Apr 2002
    Posts
    22
    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

  5. #5
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    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

  6. #6
    Join Date
    Apr 2002
    Posts
    22
    Excellent!

    Thanks for the prompt response Bill!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •