From: kennedy-downwithspammersfamily@attbi.net   
      
   "Prasanna"  wrote in message   
   news:fNOdndlsaKojMNLfRVn_vg@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   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)