| Unable to read ma file !!!... [message #3162] |
Tue, 10 September 2002 00:54  |
Bruno PONTEPRIMO
Messages: 1
Registered: September 2002
|
Junior Member |
|
|
ALTHOUGHT I INITIALIZE THE init.ora parameter UTL_FILE_DIR to "/PM330/PDI/data' THE ABOVE CODE fails !!! I i don't understand why !!!
SET SERVEROUTPUT ON
DECLARE
doc_number PM.AVNTDREV.DOC_NUM%TYPE;
doc_revision PM.AVNTDREV.REV_NUM%TYPE;
objid_fmt PM.AVNTDFMT.OBJID%TYPE;
revision VARCHAR2(20);
CURSOR c_objid IS
SELECT fmt.OBJID, rev.DOC_NUM, rev.REV_NUM
FROM PM.AVNTDREV rev , PM.AVNTDFMT fmt
WHERE rev.DOC_NUM = doc_number
AND rev.REV_NUM = revision
AND rev.OBJID = fmt.OID_REVDOC
AND rev.OID_PR_CFG = fmt.OID_VERDOC
AND fmt.SEQUENCE_NUM = 2; /* Format Secondaire */
dir_in_fic_doc VARCHAR2(50); /* Fic des doc number */
name_in_fic_doc VARCHAR2(20);
dir_in_fic_rev VARCHAR2(50); /* Fic des revisions */
name_in_fic_rev VARCHAR2(20);
dir_out_fic_objid VARCHAR2(50); /* Fic des objid */
name_out_fic_objid VARCHAR2(20);
enreg VARCHAR2(100);
erreur VARCHAR2(100);
pfic_doc UTL_FILE.FILE_TYPE;
pfic_rev UTL_FILE.FILE_TYPE;
pfic_objid UTL_FILE.FILE_TYPE;
INVALID_PATH EXCEPTION;
INVALID_MODE EXCEPTION;
INVALID_OPERATION EXCEPTION;
INVALID_FILEHANDLE EXCEPTION;
READ_ERROR EXCEPTION;
WRITE_ERROR EXCEPTION;
BEGIN
dir_in_fic_doc := '/PM330/PDI/data';
dir_in_fic_rev := '/PM330/PDI/data';
dir_out_fic_objid := '/PM330/PDI/data';
name_in_fic_doc :='liste.document';
name_in_fic_rev :='liste.revision';
name_out_fic_objid :='liste.objid';
pfic_doc := UTL_FILE.FOPEN('/PM330/PDI/data','liste.document','r');
pfic_rev := UTL_FILE.FOPEN(dir_in_fic_rev,name_in_fic_rev,'r');
pfic_objid := UTL_FILE.FOPEN(dir_out_fic_objid,name_out_fic_objid,'a');
LOOP
UTL_FILE.GET_LINE(pfic_doc,doc_number);
UTL_FILE.GET_LINE(pfic_rev,doc_revision);
/* En base, revision stockee comme un numerique
par exemple : 00000000000000000003 */
revision := LPAD(RTRIM(doc_revision,' '),20,'0');
OPEN c_objid;
FETCH c_objid INTO objid_fmt,doc_number,doc_revision;
IF c_objid%FOUND
THEN
enreg := objid_fmt || ';' || doc_number || ';' || doc_revision;
UTL_FILE.PUT_LINE(pfic_objid,enreg );
ELSE
erreur := 'Not Found' || ';' || doc_number || ';' || doc_revision;
DBMS_OUTPUT.PUT_LINE(erreur);
END IF;
CLOSE c_objid;
END LOOP;
UTL_FILE.FCLOSE(pfic_doc);
UTL_FILE.FCLOSE(pfic_rev);
UTL_FILE.FCLOSE(pfic_objid);
EXCEPTION
WHEN INVALID_PATH THEN DBMS_OUTPUT.PUT_LINE('INVALID_PATH');
WHEN INVALID_MODE THEN DBMS_OUTPUT.PUT_LINE('INVALID_MODE');
WHEN INVALID_OPERATION THEN DBMS_OUTPUT.PUT_LINE('INVALID_OPERATION');
WHEN INVALID_FILEHANDLE THEN DBMS_OUTPUT.PUT_LINE('INVALID_FILEHANDLE');
WHEN READ_ERROR THEN DBMS_OUTPUT.PUT_LINE('READ_ERROR');
WHEN VALUE_ERROR THEN DBMS_OUTPUT.PUT_LINE('VALUE_ERROR');
WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('** Not Found **');
WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE(SQLCODE);
DBMS_OUTPUT.PUT_LINE(substr(SQLERRM,1,100));
RAISE;
END;
/
exit;
I GET THE MESSAGE :
$ sqlplus pm/pm@PMREC @/PM330/PDI/SQL/PLSQL/test05.sql
SQL*Plus: Release 8.0.5.0.0 - Production on Tue Sep 10 10:48:43 2002
(c) Copyright 1998 Oracle Corporation. All rights reserved.
Connected to:
Oracle8 Enterprise Edition Release 8.0.5.0.0 - Production
PL/SQL Release 8.0.5.0.0 - Production
1
User-Defined Exception
DECLARE
*
ERROR at line 1:
ORA-06510: PL/SQL: unhandled user-defined exception
ORA-06512: at line 72
Disconnected from Oracle8 Enterprise Edition Release 8.0.5.0.0 - Production
PL/SQL Release 8.0.5.0.0 - Production
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
