-
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!
-
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;
-
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
-
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
-
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
-
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
-
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
-
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
-
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?
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|