Password change function which validates minimum Length, Upper Case, Lower Case and digit functionality

CREATE OR REPLACE FUNCTION change_password
(userid VARCHAR2,
old_password VARCHAR2,
new_password VARCHAR2)

RETURN VARCHAR2
IS

upperchar_found NUMBER;
lowerchar_found NUMBER;
digit_found NUMBER;
not_include_list_found NUMBER;
valid_pass VARCHAR2 (100);
pass_length NUMBER;
digits_list VARCHAR2 (20);
upperchar_list VARCHAR2 (26);
lowerchar_list VARCHAR2 (26);
not_include_list VARCHAR2 (100);

BEGIN

valid_pass := '0';
upperchar_found := 0;
lowerchar_found := 0;
digit_found := 0;
not_include_list_found := 0;
digits_list := '1234567890';
not_include_list := '`~!@#$%^&*()-_=+\|]}[{";:/?.>, <''';
upperchar_list := 'QWERTYUIOPASDFGHJKLZXCVBNM';
lowerchar_list := 'qwertyuiopasdfghjklzxcvbnm';
pass_length := LENGTH (new_password);

IF new_password = old_password
THEN
valid_pass := 'Old and New password not same! Please enter different';
ELSIF LENGTH (new_password) < 6
THEN
valid_pass := 'Minimum length of new password shall be 6 characters';
ELSE


--1. Check special characters
FOR i IN 1 .. LENGTH (not_include_list)
LOOP
FOR j IN 1 .. pass_length
LOOP
IF SUBSTR (new_password, j, 1) = SUBSTR (not_include_list, i, 1) AND not_include_list_found = 0
THEN
not_include_list_found := 1;
END IF;
END LOOP;
END LOOP;


--2. Check for at least one upper case letter

FOR i IN 1 .. LENGTH (upperchar_list)
LOOP
FOR j IN 1 .. pass_length
LOOP
IF SUBSTR (new_password, j, 1) = SUBSTR (upperchar_list, i, 1) AND upperchar_found = 0
THEN
upperchar_found := 1;
END IF;
END LOOP;
END LOOP;


--3. Check for at least one lower case letter

FOR i IN 1 .. LENGTH (lowerchar_list)
LOOP
FOR j IN 1 .. pass_length
LOOP
IF SUBSTR (new_password, j, 1) = SUBSTR (lowerchar_list, i, 1) AND lowerchar_found = 0
THEN
lowerchar_found := 1;
END IF;
END LOOP;
END LOOP;


--4. Check for at least one digit

FOR i IN 1 .. LENGTH (digits_list)
LOOP
FOR j IN 1 .. pass_length
LOOP
IF SUBSTR (new_password, j, 1) = SUBSTR (digits_list, i, 1) AND digit_found = 0
THEN
digit_found := 1;
END IF;
END LOOP;
END LOOP;


IF not_include_list_found = 1
THEN
valid_pass := 'Special Character not allowed in password';
ELSIF upperchar_found = 0
THEN
valid_pass := 'Password must contain at least 1 upper character';
ELSIF lowerchar_found = 0
THEN
valid_pass := 'Password must contain at least 1 lower character';
ELSIF digit_found = 0
THEN
valid_pass := 'Password must contain at least 1 digit character';
ELSE
valid_pass := '1';
END IF;
END IF;


RETURN (valid_pass);
END;
/

Oracle 10g Webutil Delay on Loading

Slow performance for form embeded with WebUtil 1.0.6

The form takes an extra 30-50 seconds to load. This happens while using Webutil 1.0.6 with Forms 9.0.4.1, 10.1.2 and later releases.

Causes:
1. The problem occurs when the NetBIOS over TCP/IP is enabled for the intranet web clients. WebUtil 1.0.6 has issues when NetBIOS over TCP/IP is enabled.

2. When client access the application using IP address instead of URL with domain name. NetBIOS does computer name to IP address mapping, name resolution via NETBT.SYS in Windows NT and VNBT.VXD in Windows 95. NetBIOS will be invoked when IP address is used instead or domain name.

Solution:
1. The NetBIOS over TCP/IP should be disabled for the intranet web clients accessing the web forms. IP address to computer name mapping and name resolution could also be achieved via DNS.

To disable the NetBIOS over TCP/IP, please execute the following steps:

a. Go to the properties of "Network Configuration" on the client machine.
b. Click on "Internet Protocol(TCP/IP)".
c. Click on "Properties" push button.
d. Click on "Advanced" push button.
e. Click on "WINS" tab.
f. Click the radio button "Disable NetBIOS over TCP/IP.

2. Manually map the IP address at Host file in client machine.
a. Go to path in Windows "C:\WINDOWS\system32\drivers\etc"
b. Edit the "hosts" file using text editor
c. Put in the application IP address and mapping name.
d. Save and close the document.


Note: "If you are running your forms on local area network using application server then add DNS entry on Domain Server"

How to Read data from an Excel Spreadsheet into an Oracle Form using WebUtil

Description of included files:
1. create_planets.sql – script to create the table used by this sample code
2. planets_ole_excel_read.fmb – sample form which demonstrates the concept
3. planets.xls – Excel (version 2003) sample spreadsheet

Setup steps:
1. Log into your database and run the “create_planets.sql” script
2. Ensure your environment is properly configured to run WebUtil. This document does not cover this configuration – there is plenty of information available in the OTN forums as well on the Internet that covers configuring WebUtil so I will not included it here.
3. Place “planets.xls’ some place on the client computer. I have modified Oracle’s original form to display a “File Select dialog” rather than hard code the file name and location in the code as Oracle demo did.
4. Ensure you have configured the Forms Builder to allow running a form from the Forms Builder. This document does not cover how to perform this configuration.
5. Run the form from the Forms Builder or on the client computer and push the “Read from Excel” button. Choose the ‘planets.xls’ file from the “Select Client filename to Open” dialog and the data will be read from the Excel spreadsheet into the form. You can then push the save button in the toolbar to commit the data to the table.

Here is the code behind the “Read from Excel” button with some explanatory comments:

DECLARE
application Client_OLE2.Obj_Type;
workbooks Client_OLE2.Obj_Type;
workbook Client_OLE2.Obj_Type;
worksheets Client_OLE2.Obj_Type;
worksheet Client_OLE2.Obj_Type;
worksheet2 Client_OLE2.Obj_Type;
cell Client_OLE2.OBJ_TYPE;
args Client_OLE2.OBJ_TYPE;
cell_value varchar2(100);
num_wrkshts NUMBER;
wksht_name VARCHAR2(250);
eod Boolean := false;
j integer := 1;
v_fName VARCHAR2(250);
BEGIN
-- Get the name of the file to open
--v_fName :=
'D:\MyDevelopment\Forms\Samples\WebUtil\Read_Excel\planets3.xls';
-- My Way: Use a File Open Dialog to let the user select the file.
v_fName := WebUtil_File.File_Open_Dialog(
directory_name => 'C:\'
,File_Filter => null
,Title => 'Select Client filename to Open.'
);

-- Make sure the user selected a file
IF ( v_fName IS NOT NULL ) THEN
-- The following sets up communication with the excel spreadsheet
-- --------------------------------------------------------------

-- Open the OLE application
application := Client_OLE2.create_obj('Excel.Application');
-- Keep the application hidden
Client_OLE2.set_property(application,'Visible','false');

workbooks := Client_OLE2.Get_Obj_Property(application, 'Workbooks');
args := Client_OLE2.CREATE_ARGLIST;

-- Open the selected File
-- ----------------------
Client_OLE2.add_arg(args,v_fName);
workbook := Client_OLE2.GET_OBJ_PROPERTY(workbooks,'Open',args);
Client_OLE2.destroy_arglist(args);

worksheets := Client_OLE2.GET_OBJ_PROPERTY(workbook, 'Worksheets');

-- Get number of worksheets
-- ------------------------
num_wrkshts := Client_OLE2.GET_NUM_PROPERTY(worksheets, 'Count');
worksheet := Client_OLE2.GET_OBJ_PROPERTY(
application,'activesheet');

--Go to the first record
go_block('planets');
first_record;

-- Loop through the Block and create a new row if needed.
loop
If :system.record_status <> 'NEW' then
create_record;
end if;

-- Exit when the last row of the spreadsheet is reached.
exit when eod;

-- Loop through the spreadsheet and get cell values
for k in 1..3 loop --3 fields per record
-- You have to know fields there are
args:= Client_OLE2.create_arglist;
Client_OLE2.add_arg(args, j);
Client_OLE2.add_arg(args, k);
cell:= Client_OLE2.get_obj_property(worksheet, 'Cells', args);
Client_OLE2.destroy_arglist(args);
cell_value :=Client_OLE2.get_char_property(cell, 'Value');

-- Check for End of Data…
if upper(cell_value) = 'EOD' then
eod:=true;
Message('End of Data');
exit;
end if;

-- Copy the value from Excel to the Forms block item
-- This is how the Oracle example copied values
/*if k =1 then
:dept.deptno:=cell_value;
end if;

if k =2 then
:dept.dname:=cell_value;
end if;

if k =3 then
:dept.loc:=cell_value;
end if;
*/

-- This is my way; which is more efficient and less code
copy(cell_value,name_in('system.cursor_item'));
next_item;

end loop; --for

j:=j+1;
end loop; --main loop

-- Release the Client_OLE2 object handles
IF (cell IS NOT NULL) THEN
Client_OLE2.release_obj(cell);
END IF;
IF (worksheet IS NOT NULL) THEN
Client_OLE2.release_obj(worksheet);
END IF;
IF (worksheets IS NOT NULL) THEN
Client_OLE2.release_obj(worksheets);
END IF;
IF (worksheet2 IS NOT NULL) THEN
Client_OLE2.release_obj(worksheet2);
END IF;
IF (workbook IS NOT NULL) THEN
Client_OLE2.release_obj(workbook);
END IF;
IF (workbooks IS NOT NULL) THEN
Client_OLE2.release_obj(workbooks);
END IF;
Client_OLE2.invoke(application,'Quit');
Client_OLE2.release_obj(application);
ELSE
Message('No File selected.');
message(' ');
RAISE Form_Trigger_Failure;
END IF;
END;