ODAC 8.2.7: Problem with stored procedure

[rdeutsch]

Hi

When executing a storedprocedure this error occured:

Unknown data type (D:\Projects\Delphi\Dac\Oracle\Source\OraClasses.pas, Zeile 4601)

In the storedproc we use code like this:

SQLtxt := 'insert into TxMitarbeiterInfo';
SQLtxt := SQLtxt ||' (DateVon, DateBis, MANDANT, LFNR_ORGANISATION, "OrgRechAbschnitt", "OrgName",';
SQLtxt := SQLtxt ||' "MemberFunktionNr", "MemberFunktionBez", "MemberAktiv", "MemberSeit", "MemberBis",';
SQLtxt := SQLtxt ||' LFNR_ADRESSEN, "MemberMitarbeiter","MemberName",';
SQLtxt := SQLtxt ||' "MemberEMailG", "MemberEMailP","MemberTelefonG","MemberTelefonP","MemberTelefax2")';
SQLtxt := SQLtxt ||' select distinct';
SQLtxt := SQLtxt ||' '''||to_char(myDateVon,'dd.mm.yyyy')||''',';
SQLtxt := SQLtxt ||' '''||to_char(myDateBis,'dd.mm.yyyy')||''',';
SQLtxt := SQLtxt ||' M.Mandant Mandant,';
.......

Is it possible that there is a similar problem with quoted fieldnames like in this thread: http://forums.devart.com/viewtopic.php?f=5&t=24197

Can you help or has anyone already reported this problem?

Kind regards
Robert

[sbslavonac]

Hi,
Reported same error, but different matter.

SmartQuery1 = select 1 from dual
Runtime parameter add :
SmartQuery1.AddWhere('sysdate = :datumod'); SmartQuery1.ParamByName('datumod').AsDate := now; SmartQuery1.Prepare; SmartQuery1.open;

Error
---------------------------
Project1
---------------------------
Unknown data type (D:\Projects\Delphi\Dac\Oracle\Source\OraClasses.pas, line 4601)
---------------------------
OK
---------------------------

The problem now is that reverting to 8.1.y is not possible.
Urgent help is needed
Boris

[xalion]

I have same error:
change SmartQuery1.ParamByName('datumod').AsDate to asdatetime
work ok.

[AlexP]

hello,

Thank you for the information, we have reproduced and fixed the problem.
This fix will be included in the next product version.

Presently, you can resolve this problem by replacing AsDate with AsDateTime

[sbslavonac]

Hi,
Same goes for QraSql.
Could not find in ODAC source code whats wrong.
Boris

[AlexP]

hello,

To solve the problem, you should change the following lines in the Oraclasses.pas file:

dtFloat: to dtFloat, dtCurrency:
and
dtDateTime: to dtDateTime, dtDate, dtTime:

in the methods: TOraParamDesc.AllocBuffer, TOraParamDesc.SetItemAsVariant, TOCICommand.GetOraType8

[sbslavonac]

Hi,
Already done that, something else is wrong.
I've made a test case.
Steps to reproduce :
Create procedure:
CREATE OR REPLACE PROCEDURE get_sql (datumod IN DATE, datumdo IN DATE, cur OUT LONG)
IS
TYPE t_p_cursor IS REF CURSOR;

c_cols t_p_cursor;
cmonth VARCHAR2 (7);
cols LONG := '';
BEGIN
OPEN c_cols FOR
SELECT month
FROM (SELECT DISTINCT TO_CHAR (dt, 'YYYY MM') month
FROM (SELECT TRUNC (TO_DATE (datumod), 'y') - 1 + ROWNUM dt
FROM all_objects
WHERE ROWNUM <= 365)
ORDER BY month)
WHERE month >= TO_CHAR (datumod, 'YYYY MM')
AND month <= TO_CHAR (datumdo, 'YYYY MM');

FETCH c_cols INTO cmonth;

WHILE c_cols%FOUND
LOOP
IF (LENGTH (cols) > 0) THEN
cols := cols || ',';
END IF;

cols := cols || 'Max(Decode(Month,''' || cmonth || ''',1,0)) AS "' || TRIM (cmonth) || '"';

FETCH c_cols INTO cmonth;
END LOOP;

CLOSE c_cols;

cur := 'SELECT 1,' || cols || 'FROM (SELECT TO_CHAR (sysdate, ' || '''YYYY MM'') month FROM DUAL)';
END get_sql;

Create new project .
Drop OraSql,button and memo
Assign "Get_sql" to orasql
on ButtonClick :

OraSQL1.ParamByName('DATUMOD').AsDate := now - 365;
OraSQL1.ParamByName('DATUMDo').AsDate := now;
OraSQL1.Prepare;
OraSQL1.Execute;
Memo1.Lines.Add(OraSQL1.ParamByName('cur').AsString);

First time you press button the result is (wrong):
SELECT 1,FROM (SELECT TO_CHAR (sysdate, 'YYYY MM') month FROM DUAL)

Click again on button and the result is correct :
SELECT 1,Max(Decode(Month,'2011 07',1,0)) AS "2011 07",Max(Decode(Month,'2011 08',1,0)) AS "2011 08",Max(Decode(Month,'2011 09',1,0)) AS "2011 09",Max(Decode(Month,'2011 10',1,0)) AS "2011 10",Max(Decode(Month,'2011 11',1,0)) AS "2011 11",Max(Decode(Month,'2011 12',1,0)) AS "2011 12"FROM (SELECT TO_CHAR (sysdate, 'YYYY MM') month FROM DUAL)

If you replace asdAte to asDAteTime result is correct on first click.

Best regards
Boris

[AlexP]

hello,

I have checked your sample - independently on whether I execute the given Delphi code the first time or the second time, the result is correct in both cases. Make sure that you have applied the changes I made in all three methods: TOraParamDesc.AllocBuffer, TOraParamDesc.SetItemAsVariant, TOCICommand.GetOraType8.

[sbslavonac]

Guess I'm missing something.....
Changed:
SetItemAsVariant
Line 5727
dtDateTime, dtDate, dtTime:
case VarType(Value) of
varDate:
AllocBuffer
Line 4564
dtDateTime, dtDate, dtTime:
FValueSize := 7;
dtBoolean:

GetOraType8
Line 6696
dtDateTime, dtDate, dtTime:
OraType := SQLT_DAT;
Boris

[AlexP]

hello,

Unfortunately, we cannot reproduce the problem.
The new version, in which this problem doesn't occur, will be released in two-three weeks