SQL & PL/SQL

I want to check the user input for validity and for file existance. When I run this in TOAD, nCount is = 0. I have no idea why.

Toad for Oracle Base v 10.6.1.3

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

DECLARE
IN_FNAME UTL_FILE.FILE_TYPE;

sDirName  varchar2(70);
sDirPath varchar2(25) := null;
sInFile  varchar2(70);
sOutputFile  varchar2(70);
bFileExists BOOLEAN;
nSubstrPos       NUMBER;
nCount           NUMBER := 5; -- to watch it change
sFileExists varchar(1);
nFileLength NUMBER;
nBlocksize NUMBER;
  
  
BEGIN
bFileExists := FALSE;
sInFile := ' /t/s/082011.txt';
sDirName := 'ES';-- Default directory_name in all_directories.  directory_path is /t/s
sInFile := TRANSLATE(sInFile,'\', '/'); -- user may enter path with \
nSubstrPos := INSTR(sInFile, '/', -1, 1);
IF nSubstrPos != 0 THEN 
   sDirPath := substr(sInFile,1,nSubstrPos-1);
   sInFile := substr(sInFile,nSubstrPos+1);
   DBMS_OUTPUT.PUT_LINE('sDirPath: '||sDirPath);
   SELECT COUNT(*) INTO nCount FROM all_directories WHERE directory_path = sDirPath;
   DBMS_OUTPUT.PUT_LINE('nCount: '||nCount);
   IF nCount > 0 THEN -- check to see if the path is an authorized path in DB
     SELECT directory_name INTO sDirName FROM all_directories WHERE directory_path = sDirPath;
   ELSE
     DBMS_OUTPUT.PUT_LINE('Directory: '||sDirPath||' cannot be accessed by this script.  Exiting');
   END IF;
END IF;

SELECT directory_path INTO sDirPath FROM all_directories WHERE directory_name = sDirName;

DBMS_OUTPUT.PUT_LINE('File: '||sInFile);
DBMS_OUTPUT.PUT_LINE('sDirName: '||sDirName);
DBMS_OUTPUT.PUT_LINE('sDirPath: '||sDirPath);

-- Open file if file exists....
UTL_FILE.FGETATTR(sDirName, sInFile, bFileExists, nFileLength, nBlocksize); 
IF bFileExists THEN
  DBMS_OUTPUT.PUT_LINE('File: '||sInFile||' in directory: '||sDirPath||' found.');
  IN_FNAME := UTL_FILE.FOPEN( sDirName, sInFile, 'R');
ELSE -- File not in directory...exit
  DBMS_OUTPUT.PUT_LINE('File: '||sInFile||' in directory: '||sDirPath||' does not exist or cannot be read.  Exiting');
END IF;
    
 EXCEPTION
  WHEN UTL_FILE.INVALID_MODE THEN
    DBMS_OUTPUT.PUT_LINE('No permission to read file.');
  WHEN UTL_FILE.INVALID_PATH THEN
    DBMS_OUTPUT.PUT_LINE('File location or filename was invalid.');
  WHEN UTL_FILE.INVALID_OPERATION THEN
    DBMS_OUTPUT.PUT_LINE('File could not be opened or operated on as requested.');
  WHEN UTL_FILE.ACCESS_DENIED THEN
   DBMS_OUTPUT.PUT_LINE('directory access denied.');

END;

output from TOAD. Unexpected output in red....
sDirPath: /t/s
<font color="red">nCount: 0</font>
<font color="red">Directory: /t/s cannot be accessed by this script. Exiting</font>
File: 082011.txt
sDirName: ES
sDirPath: /t/s
File: 082011.txt in directory: /t/s found.

Edited by: 887149 on Sep 23, 2011 7:44 AM