Results 1 to 6 of 6

Thread: writing to file

  1. #1
    Join Date
    Mar 2004
    Posts
    4

    Unanswered: writing to file

    I want to write to a file based on a certain condition is met in PL/SQL procedure that is run every night. I am not allow to use utl package. Seem like the only alternative is spool. But spool cannot be used in PL/SQL. Is there a way to work around this problem?

  2. #2
    Join Date
    Jan 2004
    Location
    Sochaux (France)
    Posts
    55
    If you cannot use UTL_FILE_OPEN you can make an sql file.

    PHP Code:
    SET SERVER OUTPUT ON SIZE 1000000

    DECLARE
       -- 
    your code

    END
    ;
    /
    exit; 
    run on sql console like this :
    @myfile.sql > myfile.log => allows you to write in a file
    @myfile.sql >> myfile.log => allows you to append in a file

  3. #3
    Join Date
    Jan 2004
    Location
    India
    Posts
    191
    Hi,

    Oracle provides two methods for writing to a file.
    1) spool &
    2) by using Utl_file package.

    I dont think there is any other method for the same.

    Thanks,
    Pagnint
    (No need to search web before posting new question)

  4. #4
    Join Date
    Oct 2003
    Posts
    87
    Here's a working example of writing to spool from PL/SQL:

    set heading off
    set pagesize 0
    set feedback off
    set termout off
    set doc off
    spool build9_10_0_0script03GRNTS.sql

    /* FILE: build9_10_0_0script03.SQL
    CREATED BY: The Wiz, ABC Systems
    CREATED ON: 23 MAR 2004

    MODIFIED BY:
    MODIFIED ON:

    Create GRANT statements for all current table and column privileges on the affected tables so they can be regranted later.
    Spool the GRANTs to file build9_10_0_0script03GRNTS.sql

    N O T E N O T E N O T E N O T E N O T E N O T E Next line

    Be sure the spooled file has been created; verify its correctness B E F O R E proceding to next script!
    When tested 26 MAR 2004 there were NO column privs on 'AIS_TSK','AIS_TSK_ASC','AIS_TSK_INTF','TASK_ASSOC IATION','TSK_ASCTN_RSN'.

    N O T E N O T E N O T E N O T E N O T E N O T E Previous line

    */

    select 'GRANT '||PRIVILEGE||' ON '||OWNER||'.'||TABLE_NAME||' TO '||GRANTEE||';'
    from DBA_TAB_PRIVS
    where OWNER = 'CRIS'
    and TABLE_NAME in ('AIS_TSK','AIS_TSK_ASC','AIS_TSK_INTF','TASK_ASSO CIATION','TSK_ASCTN_RSN')
    ;
    select 'GRANT '||PRIVILEGE||' ('||COLUMN_NAME||') ON '||OWNER||'.'||TABLE_NAME||' TO '||GRANTEE||';'
    from DBA_COL_PRIVS
    where OWNER = 'CRIS'
    and TABLE_NAME in ('AIS_TSK','AIS_TSK_ASC','AIS_TSK_INTF','TASK_ASSO CIATION','TSK_ASCTN_RSN')
    ;

    /
    /* END OF build9_10_0_0script03.SQL */
    SPOOL OFF
    set heading on
    set termout on
    set pagesize 24
    set feedback on
    set doc on
    Last edited by N-ary; 04-12-04 at 17:27.
    Oracle - DB2 - MS Access -

  5. #5
    Join Date
    Mar 2004
    Posts
    4
    thanks for all the reply.

    would using Java Stored Procedure solve the problem?

  6. #6
    Join Date
    Mar 2004
    Posts
    4

    Red face

    ...

Posting Permissions

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