Hello!
We forward you a previous email: we haven't had yet an answer, maybe because we
sent it to the private address of Becker Holger and not to mailing list (our mistake).
However we found a workaround for that problem.
We are forwarding you the old email just to remind you our situation, in
particular the functions that Holger has already analyzed.
Now we have a different problem.
The following statement
SELECT
M.N_LOADER_CLASS,
M.K_ENTRY,
M.N_ENTRY,
M.F_HIDDEN,
M.N_LINK,
M.E_MENU_K_ENTRY,
M.E_MENU_K_ENTRY_SEE_ALSO,
M.F_SEPARATOR,
M.C_NOTE,
M.N_PARAMS,
M.N_ICON,
M.F_FUNCTION
FROM
MENU M
WHERE
NVL(M.D_END,TRUNC(SYSDATE+1))>TRUNC(SYSDATE) AND
M.D_START<=TRUNC(SYSDATE)
AND 1=hasPermOnMenu(
M.K_ENTRY, /* menuEntryID */
to_number('1'), /* operatorCompanyID */
to_number('2'), /* operatorHumanResourceID */
to_date('15/11/2002','dd/mm/yyyy'), /* operatorDStartCHR */
to_number('1')) /* permissionTypeID */
ORDER BY M.K_PROG
gives the error "System error: Otherwise unknown errorcode".
The statement work fine
if we add the clause ROWNUM <=41
or
if we change third parameter's value (operatorHumanResourceID) with an operator
that has no permission
or
if we remove the order by clause (only works in SqlStudio and not via Java code).
The table used is
CREATE TABLE MENU
(K_ENTRY VARCHAR2(50) NOT NULL
,N_ENTRY VARCHAR2(250) NOT NULL
,F_HIDDEN CHAR(1) NOT NULL
,D_START DATE NOT NULL
,O_MODIFY DATE NOT NULL
,N_LINK VARCHAR2(250)
,K_PROG NUMBER(3,0)
,C_NOTE VARCHAR2(250)
,E_MENU_K_ENTRY VARCHAR2(50)
,E_MENU_K_ENTRY_SEE_ALSO VARCHAR2(50)
,E_OPERATOR_K_OPERATOR VARCHAR2(20) NOT NULL
, N_LOADER_CLASS VARCHAR2(250)
, N_ICON VARCHAR2(250)
, F_FUNCTION CHAR(1)
, N_PARAMS VARCHAR2(250)
, F_SEPARATOR VARCHAR2(1)
,D_END DATE
,C_TREE_PATH VARCHAR2(1000)
)
TABLESPACE TBS_PSF_DATI
If you need more information, we will give you.
Best regards,
Matteo
*************************************
Matteo Gattoni
ICTeam S.p.A.
Via Azzano S.Paolo, 139
24050 GRASSOBBIO (BG)
Tel.: +39 035 4232156
Fax: +39 035 4522034
e-mail: [EMAIL PROTECTED]
*************************************
----- Original Message -----
From: Fabio Pinotti
To: Becker, Holger
Sent: Tuesday, June 29, 2004 7:45 PM
Subject: Re: Join & Functions (2)
Thank you very very much for you answer. We have tried your suggestion and it now
works, even romoving rownum statment!
We also noticed that if a function that receive a timestamp parameter, uses it in a
sql statement (select, etc..) we get a Move Error. Instead, if we assign it to a local
variable and use this one, the error disappears. But we didn't know that "dispatcher"
functions had the same problem.
By the way, we'd like to submit you another question about the same query. In fact we
tried to make it simpler than the orignal form:
SELECT D.*
FROM DOCUMENT D
left join DOCUMENT_VERSION V on (D.K_DOCUMENT = V.E_DOC_K_DOCUMENT)
WHERE
1=hasPermOnDocument(
D.K_DOCUMENT,
D.E_DOC_TYP_K_DOCUMENT_TYPE,
to_number('1'),
to_number('200002'),
to_date('21/05/2004','dd/mm/yyyy'),
to_number('1'))
ORDER BY D.K_DOCUMENT
You can see that we removed the subquery, but the meaning of the two queries is the
same; in fact we uses the subquery just as a workaround for another error (Execution
failed, parse again).
Unfortunately this simplified query doesn't work. So i think that vTrace can be useful.
Our team appreciates your efforts.
Thanks again
Best Regards
Fabio Pinotti
----- Original Message -----
From: "Becker, Holger" <[EMAIL PROTECTED]>
To: "'Fabio Pinotti'" <[EMAIL PROTECTED]>
Sent: Tuesday, June 29, 2004 6:05 PM
Subject: AW: Join & Functions (2)
Hello Fabio,
after checking your dbfunctions and the vtrace I suppose that the timestamp parameter
ACTRESDSTARTCHR in dbfunction HASPERMONACTLINKTODOCUMENT is the problem.
Until now I couldn't tell exactly what the problem is but I think we have a problem
with the mix of sql modes used for dbfunctions (internal mode) and the
sql mode which is used for the select statement (oracle mode).
I noticed that you usally copy timestamp parameter into local varaibles and then
use this copy. I think this could be a workaround for this move error problem.
Could you try it and mail me the result.
In the meantime I would try to reproduce the problem here.
Kind regards
Holger
> -----Urspr�ngliche Nachricht-----
> Von: Fabio Pinotti [mailto:[EMAIL PROTECTED]
> Gesendet: Dienstag, 29. Juni 2004 12:15
> An: Becker, Holger
> Betreff: Re: Join & Functions (2)
>
>
> Hi Holger,
> the query doesn't work... it raises Move Error, but it
> also returns
> result (18 rows).
> The function is complex, because it calls other nested
> functions. It's quite
> difficult for me to extract these functions from the entire
> list, so I can
> send you all the functions that our application uses to test
> permissions. I
> hope you can find something useful there!
> To test it, you may need all the tables, but maybe you can
> find something
> wrong just by looking at the code (pay attention, because
> names of function
> are often similar!)
>
> I send you a piece of log retrieved from table "LOG_TABLE",
> used to log
> (look at "myputline" instruction inside functions), about the
> sql query
> below (the log is about the last record extracted)
>
>
> Thanks for your effort
> Fabio
>
>
> ----- Original Message -----
> From: "Becker, Holger" <[EMAIL PROTECTED]>
> To: "'Fabio Pinotti'" <[EMAIL PROTECTED]>
> Sent: Tuesday, June 29, 2004 11:25 AM
> Subject: AW: Join & Functions (2)
>
>
> Hi Fabio,
>
> the knldiag.err shows that the error is somewhere in the coding for
> dbfunctions.
> So could you please test if this statement also results in
> the move error:
>
> SELECT E.K_DOCUMENT
> FROM DOCUMENT E
> WHERE
> 1=hasPermOnDocument(
> E.K_DOCUMENT,
> E.E_DOC_TYP_K_DOCUMENT_TYPE,
> to_number('1'),
> to_number('2'),
> to_date('15/11/2002','dd/mm/yyyy'),
> to_number('1')
>
> And would you mind sending me the code of the dbfunction
> hasPermOnDocument?
>
> Thanks for your help.
>
> Kind regards
> Holger
>
> > -----Urspr�ngliche Nachricht-----
> > Von: Fabio Pinotti [mailto:[EMAIL PROTECTED]
> > Gesendet: Dienstag, 29. Juni 2004 09:07
> > An: Becker, Holger
> > Betreff: Re: Join & Functions (2)
> >
> >
> > Ok, thanks Holger, I send you the knldiag.err file with the
> > Move Error.
> >
> > Best Regards
> > Fabio
> >
> >
> > ----- Original Message -----
> > From: "Becker, Holger" <[EMAIL PROTECTED]>
> > To: "'Fabio Pinotti'" <[EMAIL PROTECTED]>
> > Sent: Monday, June 28, 2004 6:44 PM
> > Subject: AW: Join & Functions (2)
> >
> >
> > Hello Fabio,
> >
> > for a first a look the knldiag.err file from the rundirectory of the
> > database
> > is ok because there should be more information about the
> > reason for the move
> > errro.
> > Especially the module identification where the error comes from.
> >
> > Best regards
> > Holger
> >
> >
> > > -----Urspr�ngliche Nachricht-----
> > > Von: Fabio Pinotti [mailto:[EMAIL PROTECTED]
> > > Gesendet: Montag, 28. Juni 2004 15:46
> > > An: Becker, Holger
> > > Betreff: Re: Join & Functions (2)
> > >
> > >
> > > Ok, here's vTrace.
> > > The zip contains vTrace of both query (vTrace_rownum18 that
> > works, and
> > > vTrace_rownum19 that doesn't).
> > >
> > > About knldiag, which files do you need? Should I enable some
> > > particular
> > > option?
> > >
> > > Thanks
> > > Fabio Pinotti
> > >
> > >
> > >
> > > ----- Original Message -----
> > > From: "Becker, Holger" <[EMAIL PROTECTED]>
> > > To: "'Fabio Pinotti'" <[EMAIL PROTECTED]>;
> > > <[EMAIL PROTECTED]>
> > > Sent: Monday, June 28, 2004 3:29 PM
> > > Subject: AW: Join & Functions (2)
> > >
> > >
> > > Hi,
> > >
> > > it's a unknown bug and vtrace and knldiag would be appreciated.
> > >
> > > Kind regards
> > > Holger
> > >
> > > > -----Urspr�ngliche Nachricht-----
> > > > Von: Fabio Pinotti [mailto:[EMAIL PROTECTED]
> > > > Gesendet: Montag, 28. Juni 2004 12:32
> > > > An: [EMAIL PROTECTED]
> > > > Betreff: Join & Functions (2)
> > > >
> > > >
> > > > Hi,
> > > > about the problem discussed in the previous mail ("Join &
> > > > Functions) we discovered that the behaviuor of the kernel
> > > > changes in relation with the number of the rows retrieved.
> > > > In fact the following query works:
> > > >
> > > > SELECT D.*
> > > > FROM DOCUMENT D
> > > > left join DOCUMENT_VERSION V on (D.K_DOCUMENT =
> > V.E_DOC_K_DOCUMENT)
> > > > WHERE D.K_DOCUMENT IN(
> > > > SELECT E.K_DOCUMENT
> > > > FROM DOCUMENT E
> > > > WHERE
> > > > rownum<=18 and /*
> > > > <-------------------- ROWNUM */
> > > > (1=hasPermOnDocument(
> > > > E.K_DOCUMENT,
> > > > E.E_DOC_TYP_K_DOCUMENT_TYPE,
> > > > to_number('1'),
> > > > to_number('2'),
> > > > to_date('15/11/2002','dd/mm/yyyy'),
> > > > to_number('1'))))
> > > > ORDER BY D.K_DOCUMENT
> > > >
> > > > whereas increasing rownum we get "Move errror".
> > > > So we think that is due to some parameter, but can't find the
> > > > right one.
> > > >
> > > > We can attach vTrace of both queries (the one with
> > > > 'rownum<=18' and the one with 'rownum<=19') if you need more
> > > > information.
> > > >
> > > > Thanks again
> > > > Fabio Pinotti
> > > >
> > >
> > > --
> > > MaxDB Discussion Mailing List
> > > For list archives: http://lists.mysql.com/maxdb
> > > To unsubscribe:
> > > http://lists.mysql.com/[EMAIL PROTECTED]
> > >
> > >
> >
>
