New Build: ORA-01460 in Master Detail if Direct Mode = False

bahry · Post by **bahry** » Sun 15 Jun 2008 09:41

Hi,
I have a Master-Detail Qry if I run it in Direct Mode = True No Errors Appear
but when Direct Mode = False I Get Error ORA-01460 and here is the SQL

////////////////////////////////////////////////////////////////////////////
/// Master.

SELECT DISTINCT(B.BOOKINGNO), G.GUESTNAME, B.NETTOTAL, B.BALANCEAMT, B.PAIDAMOUNT,
DECODE(B.BSTATUS, 0, 'NO', 1, 'YES', 2, 'CANCEL') AS BSTATUS,
DECODE(B.BCHECKOUT, 0, 'NO', 1, 'YES') AS BCHECKOUT
FROM BOOKINGHDR B, BOOKINGDTL D, GUESTINFO G
WHERE B.GUESTNO = G.GUESTNO

/// Detail.

SELECT D.ROOMNO, R.ROOMNAME, D.DATEIN, D.DATEOUT, D.OTHERCHARGE,
DECODE(D.RENTTYPE, 0, 'HOURLY', 1, 'DAILY') AS RENTTYPE
FROM ROOMNAMES R, BOOKINGDTL D
WHERE D.ROOMNO = R.ROOMNO (+)
AND D.BOOKINGNO = :BOOKINGNO
////////////////////////////////////////////////////////////////////////////

This error never appear in the previous version.

All the Master - Detail Query giving the same Error.

Regards

Challenger · Post by **Challenger** » Tue 17 Jun 2008 08:06

Please provide a script to create these server objects.

bahry · Post by **bahry** » Tue 17 Jun 2008 09:00

Challenger wrote:Please provide a script to create these server objects.

You Can Create any Master/Detail you will get the Error : ORA-01460

Yuliy · Post by **Yuliy** » Tue 17 Jun 2008 15:24

Hello,

I have a lot of Master/Detail in the project and all of them don't work - the same Error : ORA-01460, but it worked fine with ODAC ver 6.25.

Yuliy

Yuliy · Post by **Yuliy** » Tue 17 Jun 2008 18:53

Hello,

I made some tests for Master/Details ORA-01460 error.

My old settings:

qryMaster.SQL.Text := 'Select TAR_CODE, ... FROM Master ...';
qryDetails.SQL.Text := 'Select ... FROM Details WHERE TAR_CODE=:TAR_CODE ...';
qryDetails.MasterSource := dsMaster;
qryDetails.DetailFields := '';
qryDetails.MasterFields := '';
qryDetails.ParamCheck := True;

And it worked fine with ODAC 6.25.12 - 6.25.15.

With ODAC 6.50.35 in Delphi IDE OraParams Editor does not show this Param.
When changed to qryDetails.ParamCheck := False - no errors, but there are no data in DetailGrid.

Next I changed
qryDetails.DetailFields := 'TAR_CODE';
qryDetails.MasterFields := 'TAR_CODE';

With those settings it works as before and Delphi IDE OraParams Editor does show the Param.

When changed again to
qryDetails.ParamCheck := True - I've got the same error ORA-01460 and
there are no data in DetailGrid.

I think the problem is (in my case):
Master table 'TAR_CODE' field is declared as VarChar(10),
but Detail table use Char(10) type and a new version of ParamCheck procedure raises this error: "unimplemented or unreasonable conversion requested".
I think it should be able to convert VarChar(10) to Char(10)...

Regards,
Yuliy

Challenger · Post by **Challenger** » Wed 18 Jun 2008 07:58

We could reproduce the problem neither with our MasterDetail demo nor with VARCHAR and CHAR fields. We also could not reproduce the problem with empty param list when ParamCheck is True. Please send us a small sample that demonstrates the problem and script to create server objects. Also specify the versions of Oracle server and client you use.

bahry · Post by **bahry** » Wed 18 Jun 2008 15:11

Challenger wrote:We could reproduce the problem neither with our MasterDetail demo nor with VARCHAR and CHAR fields. We also could not reproduce the problem with empty param list when ParamCheck is True. Please send us a small sample that demonstrates the problem and script to create server objects. Also specify the versions of Oracle server and client you use.

I sent you sample application with a dmp file,the Oracle server is 8i and 10g
with 8i client, but as I said the error occur if Direct mode = False.

Thanks

Yuliy · Post by **Yuliy** » Thu 19 Jun 2008 00:56

Hello,

I did reproduce this error with OdacDemo (MasterDetail module).

I added to Dept table a new col: DEPTNOSTR VARCHAR2(4)
And to Emp table: DEPTNOSTR CHAR(4).
And run 2 scripts:
Update EMP Set DEPTNOSTR = '00' || TO_CHAR(DEPTNO)
Update DEPT Set DEPTNOSTR = '00' || TO_CHAR(DEPTNO)

The changes in MasterDetail.pas:
(line 123)

if rbSQL.Checked then begin
quDetail.Close;
quDetail.SQL.Text := 'SELECT * FROM Emp WHERE DeptNoStr = :DeptNoStr';

The changes in MasterDetail.dfm:
1. I added DeptNoStr fields to quDetail and quMaster
2. quDetail.Options.QueryRecCount := True;

Run OdacDemo/MasterDetail and got this error.

I use Oracle 10.2.3 (server and client).
Direct Mode=False
WinXP Pro SP2.

Regards,
Yuliy

Challenger · Post by **Challenger** » Thu 19 Jun 2008 13:57

Thank you for your sample. We have fixed the problem. This fix will be included in the next build of ODAC.

Yuliy · Post by **Yuliy** » Mon 14 Jul 2008 15:20

Hello,

ODAC ver. 6.50.0.36 works fine.

Thank you.
Yuliy