Results 1 to 3 of 3

Thread: Hi

  1. #1
    Join Date
    Oct 2002
    Posts
    11

    Unanswered: Hi

    how get the procedure as .sql file or .txt file after viewing the procedure code by

    select text from user_source where name = 'MONTHLYPERFORMANCE pPRODUCTION' ORDER BY LINE

    WITH THE ABOVE COMMAND I COULD SEE THE TEXT OF THE PROCEDURE BUT I WANT TO EXTRACT IN TO SOME TEXT FILE SO THE I CAN MODIFY AND RUN IT AGAIN..

    THANKS
    SAN

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

    Re: Hi

    Originally posted by nsandya
    how get the procedure as .sql file or .txt file after viewing the procedure code by

    select text from user_source where name = 'MONTHLYPERFORMANCE pPRODUCTION' ORDER BY LINE

    WITH THE ABOVE COMMAND I COULD SEE THE TEXT OF THE PROCEDURE BUT I WANT TO EXTRACT IN TO SOME TEXT FILE SO THE I CAN MODIFY AND RUN IT AGAIN..

    THANKS
    SAN
    Use SPOOL to write screen output to file:

    SET PAGES 0 FEEDBACK OFF

    select text from user_source where name = 'MONTHLYPERFORMANCE pPRODUCTION' ORDER BY LINE

    SPOOL filename
    /
    SPOOL OFF

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

    Cool

    If you are using Oracle 9i, then use this code:

    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');


    Hope that helps,

    clio_usa
    OCP - DBA

Posting Permissions

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