Home » SQL & PL/SQL » SQL & PL/SQL » Please help for resolving issue for UTL_FILE (Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production)
| Please help for resolving issue for UTL_FILE [message #646351] |
Mon, 28 December 2015 08:17  |
 |
msol25
Messages: 396
Registered: June 2011
|
Senior Member |
|
|
Dear,
Please help for getting the issue why file is not writing to the directory location:
A procedure code is here
CREATE OR REPLACE PROCEDURE STC_FTP_DAS_FILE_DUMMY (
p_file_name VARCHAR2,
p_frequency IN VARCHAR2 DEFAULT 'daily',
p_ebcdic_format BOOLEAN DEFAULT FALSE
)
IS
/*----------------------------------------------------------
Sridhar Talatam (Original procedure: STC_UPLOAD_DAS_FILE1)
Decription - Procedure for FTPing the file to DAS
*-----------------------------------------------------------*/
-- Declaring the variables ...
TYPE c_das_t IS REF CURSOR;
v1 c_das_t;
fname UTL_FILE.FILE_TYPE;
dltmr VARCHAR2 (10) := '|';
ERROR_CODE VARCHAR2 (100);
error_mesg VARCHAR2 (500);
l_procedure_name VARCHAR2 (100) := 'stc_upload_das_file';
l_encoding VARCHAR2 (15);
l_count NUMBER := 0;
l_fcount NUMBER := 0;
l_das_row LONG;
l_header_row VARCHAR2 (32767);
l_codepoint NUMBER := 10;
l_nline VARCHAR2 (6);
BEGIN
-- Checking here if p_ebcdic_format flag is set to True or False which will decide whether the format need tobe in ascii ot EBCDIC ....
IF p_ebcdic_format --- If true then EBDIC characters ....
THEN
l_encoding := 'AR8EBCDIC420S';
l_codepoint := 21;
ELSE ---- If false then Ascii character ......
l_encoding := 'AR8MSWIN1256';
l_codepoint := 10;
END IF;
SELECT CHR (l_codepoint)
INTO l_nline
FROM DUAL;
-- Opening the UTL file Directory ...
-- Running the procedure for Daily
IF p_frequency = 'daily'
THEN
fname := UTL_FILE.FOPEN ('SAURAUBH_DIR', p_file_name || '_1', 'W');
/* FOR v1 IN (SELECT *
FROM stc_das_daily_new)
LOOP
SELECT CONVERT ( CONVERT ( v1.updatedata
|| dltmr
|| v1.serviceid
|| dltmr
|| v1.customerclass
|| dltmr
|| v1.asccode
|| dltmr
|| v1.firstname
|| dltmr
|| v1.fathername
|| dltmr
|| v1.grandfathername
|| dltmr
|| v1.familyname
|| dltmr
|| v1.foreignsurname
|| dltmr
|| v1.foreignfirstname
|| dltmr
|| v1.foreignfathersname
|| dltmr
|| v1.foreigngfname
|| dltmr
|| v1.streetaddline1
|| dltmr
|| v1.streetaddline2
|| dltmr
|| v1.streetaddline3
|| dltmr
|| v1.streetaddline4
|| dltmr
|| v1.pobox
|| dltmr
|| v1.zipcode
|| dltmr
|| v1.locname
|| dltmr
|| v1.suburbname
|| dltmr
|| v1.listingsecrecy
|| dltmr
|| v1.kindoftel
|| dltmr
|| v1.phonenumber
|| dltmr
|| v1.ministryname
|| dltmr
|| v1.departmentname
|| dltmr
|| v1.customernameformat
|| dltmr
|| v1.regionname
|| dltmr
|| v1.ccflag
|| dltmr
|| v1.emailflag
|| dltmr
|| v1.faxflag
|| dltmr
|| v1.smsflag
|| dltmr
|| dltmr
|| dltmr
|| dltmr
|| dltmr
|| dltmr
|| dltmr
|| dltmr
|| dltmr
|| dltmr,
'AR8MSWIN1256',
'AL32UTF8'
)
|| ' ',
l_encoding,
'AR8MSWIN1256'
)
--|| l_nline
INTO l_das_row
FROM DUAL;
l_count := l_count + 1;*/
-- If the records in the file exceeds 140 records then close the previous file and open a new file to write ....
-- IF (MOD (l_count, 60) = 0)
-- THEN
-- UTL_FILE.FCLOSE (fname);
-- l_fcount := l_fcount + 1;
-- fname :=
-- UTL_FILE.FOPEN ('IMP_DUMP',
-- p_file_name || '_' || (l_fcount + 1),
-- 'W'
-- );
-- END IF;
-- DBMS_OUTPUT.PUT_LINE (l_das_row);
--UTL_FILE.PUT (fname, c_das.temp_row || '%');
--UTL_FILE.PUT (fname, 'O%'||c_das.temp_row);
/* UTL_FILE.PUT_LINE (fname, l_das_row); */
--DBMS_OUTPUT.PUT_LINE (CONVERT(UTL_RAW.CAST_TO_VARCHAR2(l_das_row),l_encoding,'AL32UTF8'));
--UTL_FILE.PUT_LINE (fname, '%' || c_das.temp_row);
/* END LOOP; */
/* UTL_FILE.PUT_LINE (fname, l_nline); */
--DBMS_OUTPUT.PUT_LINE (l_nline);
/* UTL_FILE.FCLOSE (fname); */
-- Creating the file for header
/* fname := UTL_FILE.FOPEN ('IMP_DUMP', p_file_name || '_0', 'W');*/
/*SELECT CONVERT ( CONVERT ( '000000|'
|| TO_CHAR (SYSDATE, 'YYYYMMDD')
|| '|'
|| TO_CHAR (SYSDATE, 'HH24MISS')
|| '|U|1|'
|| '1000'
|| '|'
|| '1000'
|| '|'
|| (l_count + 1)
|| '|UDATA_DAS_'
|| TO_CHAR (SYSDATE, 'MMDDYY')
|| '.txt',
'AR8MSWIN1256',
'AL32UTF8'
)
|| ' ',
l_encoding,
'AR8MSWIN1256'
)
-- || l_nline
INTO l_header_row
FROM DUAL; */
--DBMS_OUTPUT.PUT_LINE (l_header_row);
-- DBMS_OUTPUT.PUT_LINE (CONVERT(UTL_RAW.CAST_TO_VARCHAR2(l_header_row),l_encoding,'AL32UTF8'));
--UTL_FILE.PUT (fname, l_header_row);
/* UTL_FILE.PUT_LINE (fname, l_header_row);
UTL_FILE.FCLOSE (fname);
UPDATE DAS_EXTRACTS
SET VALUE = SYSDATE
WHERE NAME = 'U';
COMMIT;*/
ELSE
-- Creating the file for the monthly
fname := UTL_FILE.FOPEN ('SAURAUBH_DIR', p_file_name, 'W');
FOR v1 IN (SELECT *
FROM stc_das_monthly_modified)
LOOP
SELECT CONVERT ( CONVERT ( v1.updatedata
|| dltmr
|| v1.serviceid
|| dltmr
|| v1.cust_class_cd
|| dltmr
|| v1.asccode
|| dltmr
|| v1.fst_name----v1.firstname
|| dltmr
|| v1.father_name
|| dltmr
|| v1.x_grand_father_name
|| dltmr
|| v1.family_name
|| dltmr
|| v1.foreign_surname
|| dltmr
|| v1.foreign_fname
|| dltmr
|| v1.foreign_fathersname
|| dltmr
|| v1.foreign_gfname
|| dltmr
|| v1.streetaddline1
|| dltmr
|| v1.streetaddline2
|| dltmr
|| v1.streetaddline3
|| dltmr
|| v1.streetaddline4
|| dltmr
|| v1.pobox
|| dltmr
|| v1.zipcode
|| dltmr
|| v1.locname
|| dltmr
|| v1.suburbname
|| dltmr
|| v1.listingsecrecy
|| dltmr
|| v1.kindoftel
|| dltmr
|| v1.phonenumber
|| dltmr
|| v1.ministryname
|| dltmr
|| v1.departmentname
|| dltmr
|| v1.customernameformat
|| dltmr
|| v1.regionname
|| dltmr
|| v1.ccflag
|| dltmr
|| v1.emailflag
|| dltmr
|| v1.faxflag
|| dltmr
|| v1.smsflag
|| dltmr
|| dltmr
|| dltmr
|| dltmr
|| dltmr
|| dltmr
|| dltmr
|| dltmr
|| dltmr
|| dltmr,
'AR8MSWIN1256',
'AL32UTF8'
)
|| ' ',
l_encoding,
'AR8MSWIN1256'
)
-- || l_nline
INTO l_das_row
FROM DUAL;
l_count := l_count + 1;
-- IF (MOD (l_count, 50) = 0)
-- THEN
-- UTL_FILE.FCLOSE (fname);
-- l_fcount := l_fcount + 1;
-- fname :=
-- UTL_FILE.FOPEN ('IMP_DUMP',
-- p_file_name || '_' || (l_fcount + 1),
-- 'W'
-- );
-- END IF;
-- UTL_FILE.PUT (fname, l_das_row);
UTL_FILE.PUT_LINE (fname, l_das_row);
END LOOP;
-- UTL_FILE.PUT (fname, l_nline);
UTL_FILE.PUT_LINE (fname, l_nline);
UTL_FILE.FCLOSE (fname);
/* fname := UTL_FILE.FOPEN ('IMP_DUMP', p_file_name || '_0', 'W', 32767);
SELECT CONVERT ( '000000|'
|| TO_CHAR (SYSDATE, 'YYYYMMDD')
|| '|'
|| TO_CHAR (SYSDATE, 'HH24MISS')
|| '|U|1|'
|| '1000'
|| '|'
|| '1000'
|| '|'
|| (l_count + 1)
|| ' ',
l_encoding,
'AR8MSWIN1256'
)
|| l_nline
INTO l_header_row
FROM DUAL;
UTL_FILE.PUT (fname, l_header_row);
UTL_FILE.FCLOSE (fname);*/
UPDATE DAS_EXTRACTS
SET VALUE = SYSDATE
WHERE NAME = 'F';
COMMIT;
END IF;
EXCEPTION
WHEN UTL_FILE.INVALID_MODE
THEN
ERROR_CODE := SQLCODE;
error_mesg := 'Invalid Mode Parameter ' || SUBSTR (SQLERRM, 1, 500);
INSERT INTO BCCS_DASCRM_ERRORS
(error_number, error_msg, error_date, procedure_name
)
VALUES (ERROR_CODE, error_mesg, SYSDATE, l_procedure_name
);
WHEN UTL_FILE.INVALID_PATH
THEN
ERROR_CODE := SQLCODE;
error_mesg := 'Invalid File Location ' || SUBSTR (SQLERRM, 1, 500);
INSERT INTO BCCS_DASCRM_ERRORS
(error_number, error_msg, error_date, procedure_name
)
VALUES (ERROR_CODE, error_mesg, SYSDATE, l_procedure_name
);
WHEN UTL_FILE.INVALID_FILEHANDLE
THEN
ERROR_CODE := SQLCODE;
error_mesg := 'Invalid Filehandle ' || SUBSTR (SQLERRM, 1, 500);
INSERT INTO BCCS_DASCRM_ERRORS
(error_number, error_msg, error_date, procedure_name
)
VALUES (ERROR_CODE, error_mesg, SYSDATE, l_procedure_name
);
WHEN UTL_FILE.INVALID_OPERATION
THEN
ERROR_CODE := SQLCODE;
error_mesg := 'Invalid Operation ' || SUBSTR (SQLERRM, 1, 500);
INSERT INTO BCCS_DASCRM_ERRORS
(error_number, error_msg, error_date, procedure_name
)
VALUES (ERROR_CODE, error_mesg, SYSDATE, l_procedure_name
);
WHEN UTL_FILE.READ_ERROR
THEN
ERROR_CODE := SQLCODE;
error_mesg := 'Read Error ' || SUBSTR (SQLERRM, 1, 500);
INSERT INTO BCCS_DASCRM_ERRORS
(error_number, error_msg, error_date, procedure_name
)
VALUES (ERROR_CODE, error_mesg, SYSDATE, l_procedure_name
);
WHEN UTL_FILE.INTERNAL_ERROR
THEN
ERROR_CODE := SQLCODE;
error_mesg := 'Internal Error ' || SUBSTR (SQLERRM, 1, 500);
INSERT INTO BCCS_DASCRM_ERRORS
(error_number, error_msg, error_date, procedure_name
)
VALUES (ERROR_CODE, error_mesg, SYSDATE, l_procedure_name
);
WHEN UTL_FILE.CHARSETMISMATCH
THEN
ERROR_CODE := SQLCODE;
error_mesg :=
'Opened With FOPEN_NCHAR
But Later I/O Inconsistent '
|| SUBSTR (SQLERRM, 1, 500);
INSERT INTO BCCS_DASCRM_ERRORS
(error_number, error_msg, error_date, procedure_name
)
VALUES (ERROR_CODE, error_mesg, SYSDATE, l_procedure_name
);
WHEN UTL_FILE.file_open
THEN
ERROR_CODE := SQLCODE;
error_mesg := 'File Already Opened ' || SUBSTR (SQLERRM, 1, 500);
INSERT INTO BCCS_DASCRM_ERRORS
(error_number, error_msg, error_date, procedure_name
)
VALUES (ERROR_CODE, error_mesg, SYSDATE, l_procedure_name
);
WHEN UTL_FILE.invalid_maxlinesize
THEN
ERROR_CODE := SQLCODE;
error_mesg := 'Line Size Exceeds 32K ' || SUBSTR (SQLERRM, 1, 500);
INSERT INTO BCCS_DASCRM_ERRORS
(error_number, error_msg, error_date, procedure_name
)
VALUES (ERROR_CODE, error_mesg, SYSDATE, l_procedure_name
);
WHEN UTL_FILE.invalid_filename
THEN
ERROR_CODE := SQLCODE;
error_mesg := 'Invalid File Name ' || SUBSTR (SQLERRM, 1, 500);
INSERT INTO BCCS_DASCRM_ERRORS
(error_number, error_msg, error_date, procedure_name
)
VALUES (ERROR_CODE, error_mesg, SYSDATE, l_procedure_name
);
WHEN UTL_FILE.access_denied
THEN
ERROR_CODE := SQLCODE;
error_mesg := 'IFile Access Denied By ' || SUBSTR (SQLERRM, 1, 500);
INSERT INTO BCCS_DASCRM_ERRORS
(error_number, error_msg, error_date, procedure_name
)
VALUES (ERROR_CODE, error_mesg, SYSDATE, l_procedure_name
);
WHEN UTL_FILE.invalid_offset
THEN
ERROR_CODE := SQLCODE;
error_mesg := 'FSEEK Param Less Than 0 ' || SUBSTR (SQLERRM, 1, 500);
INSERT INTO BCCS_DASCRM_ERRORS
(error_number, error_msg, error_date, procedure_name
)
VALUES (ERROR_CODE, error_mesg, SYSDATE, l_procedure_name
);
WHEN OTHERS
THEN
ERROR_CODE := SQLCODE;
error_mesg := 'Unknown error ' || SUBSTR (SQLERRM, 1, 500);
INSERT INTO BCCS_DASCRM_ERRORS
(error_number, error_msg, error_date, procedure_name
)
VALUES (ERROR_CODE, error_mesg, SYSDATE, l_procedure_name
);
END;
/
Calling the Procedure through command:
begin
STC_FTP_DAS_FILE_DUMMY (
'test.ascii',
'monthly'
);
end;
Please help for getting the issue why file is not getting generated at given directory location.
[EDITED by LF: applied [spoiler] tags]
[Updated on: Mon, 28 December 2015 13:32] by Moderator Report message to a moderator |
|
|
|
|
|
| Re: Please help for resolving issue for UTL_FILE [message #646361 is a reply to message #646352] |
Mon, 28 December 2015 12:11  |
Bill B
Messages: 1971
Registered: December 2004
|
Senior Member |
|
|
Also is the directory SAURAUBH_DIR defined and at the OS level have your given the directory the permissions to write into that directory. To find out run the folowing script
select * from Sys.All_directories
where directory_name = 'SAURAUBH_DIR';
And then outside of oracle see if the permissions on the directory and see if the oracle user can read and write the directory.
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Tue Apr 23 05:44:23 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
