I am using an 11g database and I have tried this with a 10g and a 9i client. I am using Delphi 2009 and the latest version of ODAC.
There is a problem when OraCall.OCIUnicode := true; and OraSession.Options.UseUnicode = true. When unicode is not enabled, there is no problem.
I am using a TSmartQuery, with all defaults, except ObjectView = true. I am using a TDBGrid to try to edit the data in the OPERATING_HOURS.MONDAY_HRS.OPEN_TIME.HOUR field from 8 to 9. When I try to post, I get ORA-21525. If I don't use the unicode options, it works without error.
Script to create required objects and table:
Code: Select all
CREATE OR REPLACE TYPE time_of_day AS OBJECT (
HOUR NUMBER,
MINUTE NUMBER,
am_or_pm nCHAR (10),
MEMBER FUNCTION minutes_since_midnight
RETURN NUMBER,
PRAGMA RESTRICT_REFERENCES (minutes_since_midnight, WNPS),
MEMBER FUNCTION valid_time
RETURN BOOLEAN,
PRAGMA RESTRICT_REFERENCES (valid_time, WNPS),
ORDER MEMBER FUNCTION compare_times (the_time_of_day IN time_of_day)
RETURN INTEGER
)
/
CREATE OR REPLACE TYPE BODY time_of_day
IS
MEMBER FUNCTION valid_time
RETURN BOOLEAN
IS
tvalid BOOLEAN;
BEGIN
IF (HOUR BETWEEN 1 AND 12) AND (MINUTE BETWEEN 0 AND 59) AND am_or_pm IN ('AM', 'PM')
THEN
RETURN TRUE;
ELSE
RETURN FALSE;
END IF;
END;
MEMBER FUNCTION minutes_since_midnight
RETURN NUMBER
IS
tmpvar NUMBER;
BEGIN
IF valid_time
THEN
tmpvar := 0;
IF am_or_pm = 'PM'
THEN
tmpvar := 12 * 60;
END IF;
tmpvar := tmpvar + 60 * HOUR + MINUTE;
RETURN tmpvar;
ELSE
raise_application_error (-20010, 'invalid time of day');
END IF;
END;
ORDER MEMBER FUNCTION compare_times (the_time_of_day IN time_of_day)
RETURN INTEGER
IS
othertime NUMBER := 0;
thistime NUMBER := 0;
BEGIN
othertime := the_time_of_day.minutes_since_midnight;
thistime := minutes_since_midnight;
IF thistime < othertime
THEN
RETURN -1;
ELSIF thistime = othertime
THEN
RETURN 0;
ELSE
RETURN 1;
END IF;
END;
END;
/
CREATE OR REPLACE FUNCTION minutes_between (time1 time_of_day, time2 time_of_day)
RETURN NUMBER
IS
tmpvar NUMBER;
BEGIN
tmpvar := time2.minutes_since_midnight - time1.minutes_since_midnight;
RETURN tmpvar;
EXCEPTION
WHEN OTHERS
THEN
-- Consider logging the error and then re-raise
RAISE;
END minutes_between;
/
CREATE OR REPLACE TYPE day_op_hours AS OBJECT (
open_time time_of_day,
close_time time_of_day
);
/
CREATE OR REPLACE TYPE operating_hours_type AS OBJECT (
sunday_hrs day_op_hours,
monday_hrs day_op_hours,
tuesday_hrs day_op_hours,
wednesday_hrs day_op_hours,
thursday_hrs day_op_hours,
friday_hrs day_op_hours,
saturday_hrs day_op_hours
);
/
CREATE TABLE VETERINARY_CLINICS
(
CLINIC_ID NUMBER (6) NOT NULL,
CLINIC_NAME nVARCHAR2(50),
specialty nVARCHAR2(50),
street nVARCHAR2 (50),
city nVARCHAR2 (15),
state nCHAR (20),
zip_code nCHAR (5),
OPERATING_HOURS operating_hours_type,
CONSTRAINT pk_veterinary_clinics PRIMARY KEY(CLINIC_ID)
);
CREATE INDEX CLINIC_NAME_idx ON VETERINARY_CLINICS (CLINIC_NAME);
INSERT INTO veterinary_clinics
(CLINIC_ID, CLINIC_NAME, specialty, street, city, state, zip_code, OPERATING_HOURS)
VALUES (5000, 'Cat Clinic of Cary', 'cats', 'Parkway Plaza', 'Cary', 'NC', '27513', NULL);
INSERT INTO veterinary_clinics
(CLINIC_ID, CLINIC_NAME, specialty, street, city, state, zip_code, OPERATING_HOURS)
VALUES (5001, 'Chatham Animal Hospital', 'general', 'W. Chatham', 'Cary', 'NC', '27513',
NULL);
INSERT INTO veterinary_clinics
(CLINIC_ID, CLINIC_NAME, specialty, street, city, state, zip_code, OPERATING_HOURS)
VALUES (5002, 'Hidden Valley Animal Hospital', 'general', 'Lynn Rd.', 'Raleigh', 'NC',
'27612', NULL);
INSERT INTO veterinary_clinics
(CLINIC_ID, CLINIC_NAME, specialty, street, city, state, zip_code, OPERATING_HOURS)
VALUES (5003, 'Emergency Animal Clinic of Cary', 'emergency vet', 'High House Rd.',
'Cary', 'NC', '27513', NULL);
INSERT INTO veterinary_clinics
(CLINIC_ID, CLINIC_NAME, specialty, street, city, state, zip_code, OPERATING_HOURS)
VALUES (5005, 'Green Level Road Animal Hospital', 'general', 'Green Rd.', 'Cary', 'NC',
'27511', NULL);
UPDATE veterinary_clinics
SET OPERATING_HOURS =
operating_hours_type (day_op_hours (NULL, NULL),
day_op_hours (time_of_day (8, 30, 'AM'),
time_of_day (6, 0, 'PM')
),
day_op_hours (time_of_day (8, 30, 'AM'),
time_of_day (6, 0, 'PM')
),
day_op_hours (time_of_day (8, 30, 'AM'),
time_of_day (6, 0, 'PM')
),
day_op_hours (time_of_day (8, 30, 'AM'),
time_of_day (6, 0, 'PM')
),
day_op_hours (time_of_day (8, 30, 'AM'),
time_of_day (6, 0, 'PM')
),
day_op_hours (time_of_day (8, 30, 'AM'),
time_of_day (12, 0, 'PM')
)
);
commit;and the select statement that I am using is:
Code: Select all
SELECT
ROWID, CLINIC_ID, CLINIC_NAME, SPECIALTY,
STREET, CITY, STATE,
ZIP_CODE, OPERATING_HOURS
FROM VETERINARY_CLINICS