Re: UTL_FILE(HELP)

From: Jim Kennedy <kennedy-downwithspammersfamily_at_attbi.net>
Date: Sun, 3 Apr 2005 09:06:36 -0700
Message-ID: <g_KdnYq_tfD-ic3fRVn-hQ_at_comcast.com>

"Prasanna" <prasannansl_at_rediffmail-dot-com.no-spam.invalid> wrote in message news:fNOdndlsaKojMNLfRVn_vg_at_giganews.com...
> Hi All,
> Nice to be in this Forum.
> I have questions to ask on UTL_FILE :roll:
> Let me explain the concept.
> 1.When the net is down the user will take the records into one
> floppy(CLIENT)
> 2.When the net is up he will update the database(SERVER) with all the
> trasaction including the records which he taken into the floppy.
>
> He creates one text file in the floppy. The records in that text file
> will be as follows.
>
> ( '12-Dec-2004','D
> ','1','111111111111','11',11111,11111,'',0,'Z','N','M','
> ','N',11111,0,' ','12-Dec-2004',' ',0,' ',' ',' ',' ',0,'
> ','12-Dec-2004','
>

',0,'1','315','tpsl','06:45:26',1,'N','C','Y','D','1111','F','192.100.10.1',
'N','1111111111111','','','','','','19-Jan-2005','','','','')


> ( '12-Dec-2004','D

> ','2','222222222222','22',1254,1254,'',0,'Z','N','M',' ','N',1254,0,'


>   ','12-Dec-2004','     ',0,'   ','   ',' ',' ',0,'

> ','12-Dec-2004','
>
',0,'2','315','TPSL','07:07:11',1,'N','C','Y','D','2222','F','192.100.10.1', 'N','2222222222222','','','','','','19-Jan-2005','','','','')
>
> Questions
> *********
> 1. Is it possible to pass floppy drive A: as parameter to UTL_FILE?
> 2. If yes then do I need to create directory for this?
> 3. If no then can I copy the file in floppy to other location from
> procedure?
> 4. If yes then how can i do that?
> 5. When I get the line from UTL_FILE handler Iam straight away
> inserting the line. Like for example
> INSERT INTO CDYYMMDD1 VALUES(vNewLine);
> Is is possible?
> 6. How to go to the next line of UTL_FILE?
> 7.How to open that file in a procedure?
>
> It will be very useful if you send in any sample code of same issue.
> Iam here with pasting you the procedure which I have implemented.But
> it is not working due to the above mentioned Errors(Questions).
> _______________________________________________________
> CREATE OR REPLACE PROCEDURE BackupRestore (file_name IN VARCHAR2) IS
> vSFile utl_file.file_type;
> vNewLine VARCHAR2(200);
> vTNO VARCHAR2(6);
> vTDate DATE;
> vMach VARCHAR2(3);
>
> BEGIN
> vSFile := utl_file.fopen('A:', file_name,'r');
> LOOP
> BEGIN
> utl_file.get_line(vSFile, vNewLine);
> DBMS_OUTPUT.PUT_LINE(vNewLine);
> vTDate = substr(vNewLine,1,13)
> vTNo := SUBSTR(vNewLine,21,1);
> vMach := SUBSTR(vNewLine,77,3);
> FOR I IN (SELECT TRNNO, CONS_NO,MACHINE FROM CDYYMMDD1
> WHERE TRNDATE = vTDate AND
> (TRNNo = vTNo AND MACHINE = vMach)) LOOP
> IF I = 0 then
> INSERT INTO CDYYMMDD1 VALUES(vNewLine);
> END IF;
> END LOOP;
>
> EXCEPTION
> WHEN NO_DATA_FOUND THEN
> EXIT;
> END;
> END LOOP;
> COMMIT;
> utl_file.fclose(vSFile);
> EXCEPTION
> WHEN utl_file.invalid_mode THEN
> RAISE_APPLICATION_ERROR (-20051, 'Invalid Mode Parameter');
> WHEN utl_file.invalid_path THEN
> RAISE_APPLICATION_ERROR (-20052, 'Invalid File Location');
> WHEN utl_file.invalid_filehandle THEN
> RAISE_APPLICATION_ERROR (-20053, 'Invalid Filehandle');
> WHEN utl_file.invalid_operation THEN
> RAISE_APPLICATION_ERROR (-20054, 'Invalid Operation');
> WHEN utl_file.read_error THEN
> RAISE_APPLICATION_ERROR (-20055, 'Read Error');
> WHEN utl_file.internal_error THEN
> RAISE_APPLICATION_ERROR (-20057, 'Internal Error');
> WHEN utl_file.file_open THEN
> RAISE_APPLICATION_ERROR (-20059, 'File Already Opened');
> WHEN utl_file.invalid_maxlinesize THEN
> RAISE_APPLICATION_ERROR(-20060,'Line Size Exceeds 32K');
> WHEN utl_file.invalid_filename THEN
> RAISE_APPLICATION_ERROR (-20061, 'Invalid File Name');
> WHEN utl_file.access_denied THEN
> RAISE_APPLICATION_ERROR (-20062, 'File Access Denied By');
> WHEN others THEN
> RAISE_APPLICATION_ERROR (-20099, 'Unknown UTL_FILE Error');
> END read_demo;
> /
> _____________________________________________________
> Sorry for taking your valuable time.Thanks for spending your time.
> Waiting for your reply.
>

Utl_file runs on the Server not the client so the server can't see the A: drive on the client. Why not have your client issue the commands to the server as SQL inserts.
Jim Received on Sun Apr 03 2005 - 18:06:36 CEST