Oracle Forms

Hello all,

my platforms: oracle db 10g, forms 6i and 9i, client windows XP SP3

i am trying to use ole2 (client_ole2 in web forms) to find files LIKE a given name in a given folder on the clients PC.

essentially i am attempting to duplicate in pl/sql what can be achieved in vb (or vbscript) using the following code;

------------------------------------------------------------
sub testFileIteration()

Dim i As Integer
Dim fso As FileSystemObject
Dim hFolder As Scripting.Folder
Dim hFiles As Scripting.Files
Dim hFile As Scripting.File

Set fso = New Scripting.FileSystemObject
Set hFolder = fso.GetFolder("c:\temp")
Set hFiles = hFolder.Files

MsgBox "there are " & hFiles.Count & " in " & hFolder.Name

For Each hFile In hFiles

MsgBox hFile.Name
'<compare file name to that specified, increment count and set boolean accordingly>

Next

End Sub
-------------------------------------------------

The bolded line is the problem because the For Each statement has no eqivalent in pl/sql that i am aware of. Does anyone know how to either:

1. duplicate For Each functionality
2. an alternative way to perform a windows file name comparison in pl/sql


This is what i have in form-side pl/sql so far: i am attempting to duplicate For Each functionality by using the files collection's Item(key) property to get a handle to each file in turn but this does not work (as i do not know the key value).

----------------------------------------------------------

--wild card file exists function
----------------------------------------------------------------------
function exists(i_folder_path in varchar2,
i_wild_file_name in varchar2,
o_count out integer) return boolean is

hArgs ole2.list_type;--handle to arguments
hApplication ole2.obj_type; --handle to application

hFolder ole2.obj_type;
hFiles ole2.obj_type;
hFile ole2.obj_type;

cn number := 0;
iii number := 0;
atEnd boolean := false;
rtnVal boolean := false;

invalid_folder exception;
files_not_found exception;

-------------------------------------
function to_boolean (p_fso_boolean in pls_integer) return boolean is
begin
if p_fso_boolean = -1 then
return true;
else --0 is false
return false;
end if;
end;
-----------------------------------------
begin

hApplication := ole2.create_obj('Scripting.FileSystemObject');

--get folder object (using pl/sql library function)
hFolder := fso.GetFolder(hApplication,i_folder_path);

if hFolder is null then
raise invalid_folder;
end if;

--get fileS property of folder object
hFiles := ole2.get_obj_property(hFolder,'Files');

if hFiles is null then
raise files_not_found;
end if;

alerts.info('There are a total of '||ole2.get_num_property(hFiles,'Count')||' files in folder '||ole2.get_char_property(hFolder,'Name'));

--iterate through FILES collection objects comapring each's name to wild card name
for i in 1..ole2.get_num_property(hFiles,'Count') loop

--DOES NTO WORK as based on jscript example*
--atEnd := to_boolean(ole2.invoke_num(hFiles,'atEnd',hArgs));
--exit when atEnd;
--iii := iii + 1;

alerts.info('getting file handle for file number '||i);
--get handle to current file
hArgs := ole2.create_arglist;
ole2.add_arg(hArgs,i);
hFile := ole2.get_obj_property(hFiles,'Item', hArgs); --FAILS HERE*
ole2.destroy_arglist(hArgs);

alerts.info('file '||i||' is '||ole2.get_char_property(hFile,'Name'));

if ole2.get_char_property(hFile,'Name') like i_wild_file_name then
cn := cn + 1;
rtnVal := true;
end if;

--DOES NOT WORK as based on jscript example*
ole2.invoke(hFiles,'moveNext');

end loop;

o_count := cn;
return rtnVal;

exception
when invalid_folder then
alerts.info('invalid_folder');

when files_not_found then
alerts.info('files_not_found');

when others then
alerts.info('other error'||sqlerrm);
end;
-----------------------------------------------------------------

Any suggestions would be appreciated.