DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 19

Thread: Spool to text file?

  1. #1
    Join Date
    Jan 2000
    Posts
    387

    Spool to text file?

    Hi

    I have the following procedure and I do not know I am encountering the errors even I have checked the error messages and set permission for the directory and file... please help!

    ----------------------

    CREATE OR REPLACE PROCEDURE test_write
    AS
    v_output_file1 utl_file.file_type;
    BEGIN

    v_output_file1 := utl_file.fopen('/oracle/temp', 'my_test.txt', 'W');
    FOR cursor_emp IN (SELECT * FROM EMP; )
    LOOP
    utl_file.put_line(v_output_file1, cursor_emp.username || ',' || cursor_emp.designation);
    END LOOP;
    utl_file.fclose_all;
    END;
    /

    ERROR at line 1:
    ORA-06510: PL/SQL: unhandled user-defined exception
    ORA-06512: at "SYS.UTL_FILE", line 98
    ORA-06512: at "SYS.UTL_FILE", line 157
    ORA-06512: at "SI.TEST_WRITE", line 8
    ORA-06512: at line 1

    -------------------------

    By the way, how to write to the file with current date and time as the name of the file? Thanks!

  2. #2
    Join Date
    Jan 2003
    Location
    Hull, UK
    Posts
    220
    Hi

    HTH

    Srini

    CREATE OR REPLACE PROCEDURE test_write
    AS
    v_output_file1 utl_file.file_type;
    FILENAME VARCHAR2(14);
    BEGIN

    SELECT TO_CHAR(SYSDATE,'DDMMYYYYHH24MISS') INTO FILENAME FROM DUAL;

    OUTPUT_FILE := UTL_FILE.FOPEN ('/tmp',FILENAME||'.dat', 'W');

    UTL_FILE.PUT_LINE(OUTPUT_FILE,'=======>RECORDS FILE CREATED ON :' || to_char(sysdate,'dd-mon-yy-hh24-mi') ||'<==========');

    UTL_FILE.FCLOSE(OUTPUT_FILE);

    EXCEPTION

    WHEN UTL_FILE.INVALID_MODE THEN
    RAISE_APPLICATION_ERROR(-20000, 'ERROR: INVALID MODE.');

    WHEN UTL_FILE.INVALID_FILEHANDLE THEN
    RAISE_APPLICATION_ERROR(-20000, 'ERROR: INVALID FILEHANDLE.');

    WHEN UTL_FILE.INVALID_OPERATION THEN
    RAISE_APPLICATION_ERROR(-20000, 'ERROR: INVALID OPERATION.');

    WHEN UTL_FILE.WRITE_ERROR THEN
    RAISE_APPLICATION_ERROR(-20000, 'ERROR: WRITE ERROR.');

    WHEN UTL_FILE.INTERNAL_ERROR THEN
    RAISE_APPLICATION_ERROR(-20000, 'ERROR: INTERNAL ERROR.');

    WHEN UTL_FILE.INVALID_PATH THEN
    RAISE_APPLICATION_ERROR(-20000, 'ERROR: INVALID PATH FOR FILE OR PATH NOT IN INIT.ORA.');

    WHEN OTHERS THEN
    RAISE_APPLICATION_ERROR(-20000,'SOME EXCEPTION OCCURRED');
    End;

  3. #3
    Join Date
    Aug 2002
    Location
    Atlanta
    Posts
    1,187
    8i or 9i?

    this works in 9i

    CREATE DIRECTORY test_dir AS 'c:\temp';

    CREATE OR REPLACE PROCEDURE test
    AS
    v_output_file1 utl_file.file_type;
    BEGIN
    /* Ensure TEST_DIR below is in all caps */
    /* Third argument below is W which overwrites file each time, change to A to append */
    v_output_file1 := utl_file.fopen('TEST_DIR', 'my_test.csv', 'W');
    FOR cursor_emp IN (SELECT empnom,ename, deptno FROM emp)
    LOOP
    utl_file.put_line(v_output_file1, cursor_emp.empno || ',' || cursor_emp.ename || ',' || cursor_emp.deptno);
    END LOOP;
    utl_file.fclose_all;
    END;
    I'm stmontgo and I approve of this message

  4. #4
    Join Date
    Oct 2003
    Posts
    21

    Unhappy

    I tried but got this error. any clues..

    I have only changed /tmp and given full path like /home2/staff/cbr

    trg02> exec test_write
    BEGIN test_write; END;

    *
    ERROR at line 1:
    ORA-20000: ERROR: INVALID PATH FOR FILE OR PATH NOT IN INIT.ORA.
    ORA-06512: at "EFSUSER.TEST_WRITE", line 33
    ORA-06512: at line 1


    regards
    Bhaskara

  5. #5
    Join Date
    Aug 2002
    Location
    Atlanta
    Posts
    1,187
    Originally posted by NayaDBA
    I tried but got this error. any clues..

    I have only changed /tmp and given full path like /home2/staff/cbr

    trg02> exec test_write
    BEGIN test_write; END;

    *
    ERROR at line 1:
    ORA-20000: ERROR: INVALID PATH FOR FILE OR PATH NOT IN INIT.ORA.
    ORA-06512: at "EFSUSER.TEST_WRITE", line 33
    ORA-06512: at line 1


    regards
    Bhaskara
    8i or 9i ? !!!
    I'm stmontgo and I approve of this message

  6. #6
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Originally posted by NayaDBA
    I tried but got this error. any clues..

    I have only changed /tmp and given full path like /home2/staff/cbr

    trg02> exec test_write
    BEGIN test_write; END;

    *
    ERROR at line 1:
    ORA-20000: ERROR: INVALID PATH FOR FILE OR PATH NOT IN INIT.ORA.
    ORA-06512: at "EFSUSER.TEST_WRITE", line 33
    ORA-06512: at line 1


    regards
    Bhaskara
    Is utl_file_dir set in init.ora?
    Jeff Hunter

  7. #7
    Join Date
    Aug 2002
    Location
    Atlanta
    Posts
    1,187
    Originally posted by marist89
    Is utl_file_dir set in init.ora?
    in 9i you do not need to set utl_file_dir

    steve
    I'm stmontgo and I approve of this message

  8. #8
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Originally posted by stmontgo
    in 9i you do not need to set utl_file_dir

    steve
    That seems like a security problem...

    Code:
    CREATE DIRECTORY test_dir AS 'c:\oracle\bin'; 
    
    CREATE OR REPLACE PROCEDURE test
    AS
    v_output_file1 utl_file.file_type; 
    BEGIN
    /* Ensure TEST_DIR below is in all caps */
    /* Third argument below is W which overwrites file each time, change to A to append */
    v_output_file1 := utl_file.fopen('TEST_DIR', 'oracle.exe', 'W'); 
    FOR cursor_emp IN (SELECT empnom,ename, deptno FROM emp)
    LOOP
    utl_file.put_line(v_output_file1, cursor_emp.empno || ',' || cursor_emp.ename || ',' || cursor_emp.deptno);
    END LOOP;
    utl_file.fclose_all;
    END;
    Jeff Hunter

  9. #9
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Originally posted by marist89
    That seems like a security problem...
    Why? I don't see it like a security problem. Anyway, not much more of a security problem than it is with the old-fashioned UTL_FILE_DIR init parameter. You can do the same thing as you have demonstrated with UTL_FILE_DIR, if you set it to 'c:\oracle\bin' or '*' - where is the difference?

    My point is: a responsible DBA would not use '*' or anything simmilar to '$ORACLE_HOME/bin' in the UTL_FILE_DIR. Similarly, a responsible DBA would create directories (as database objects) personaly, he/she would never give a CREATE ANY DIRECTORY privilege to an iresponsible DBA or (god forbid!) developer.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  10. #10
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Agreed, a responsible DBA wouldn't let that happen. A responsible DBA wouldn't use export as his only backup method, but it happens.

    The difference to me is active management vs. passive management. If I must setup utl_file_dir, then I control where files get written should a "secure" user's authentication be compromised.
    Jeff Hunter

Posting Permissions

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


Click Here to Expand Forum to Full Width